Grouping reports

We have a designed a table report to show the hierarchy like "Locations -> Station -> Prod. category -> Product Name -> Product Model" and the report have some measures like Quantity in stock, Quantity sold.......

By default the report shows all the Locations with sum of all the measures, on expanding any location all the Stations are shown and on expanding Stations all the product names are shown and on expanding any product name all the product models are shown.

Now the requirement is, the same report should show the details of all the Product names in a single group, from all the locations and all the stations. And when i click on All stations link i should be shown the same report with all the stations under all locations.

i.e. the same report should have one row with the static text in columns like All Locations, All Stations, All Prod. Category, All Product Names and All Product Models. When the user click on All Product Names column the report should expand to show all the products in all locations and all stations.

I have looked for many examples but couldn't find such type of report, Is it possible to create such type of grouping using SQL server reporting services (MSSQL 2005). Pl. provide me a example to implement this type of requirement.

Thanks,
Sri

Hi,

Can you explain why you can't use the grouping features of a normal table or matrix? In your query make sure every row has a column indicating the location, station, prod. cat. etc. In your table, make different groups on these columns.

Regards, Jeroen

|||

With grouping i cannot get all the product names under all the locations and stations. With grouping, i need to select specific location and station to see the product names under that location and station. Also i need to show all the product names under all the stations in any specific location and i also need to show the product names under selected station this is the requirement.

I think this is the basic feature any OLAP reporting engine should provide for analysis of data. The report should show how many Products are there in stock in the country and the same report should have options to expand and see which locations in the country have what stock and further drill-down show which station has what products in stock without navigation to other screens.

I am trying to use sub-reports for achiving this requirement, if there are any other simple solutions for this type of reports Pl. suggest with a example.

Thanks & Regards
Sri

|||

Hi Sri,

There have been a few cases in which I managed to built a more dynamic report using multiple tables in a report and using the navigation property of a cell to run the same report again using parameters which get their value based on the cell which was clicked on. So lets say you have a table with a dataset of locations and a table with a dataset of stations. Now clicking a particular location runs the same report again, with the location parameter set to the clicked location. The station dataset is filled with all stations of this location and displayed in the table with stations. But I don't think this approach will cover all your requirements though.

Regards, Jeroen

|||

Sri,

i am thinking that maybe the reason why you are not getting a list of all products when grouping by station is because empty rows are being returned from the cube, these get eliminated from the dataset. To fix this, use a COALESCEEMPTY in the MDX, that way you can return a zero for empty rows, and should get a complete listing of products.

|||

I tried using COALESCEEMPTY in the MDX but still not getting all the products in all the stations as a single group. I know this is becaue all the products are defined under some station and the there are no products with section empty.

In this report i should be able to see all the products in the country with the measures defined (Quanity in stock and quantity sold) and i should be able to select some location and should able to see all the products in that location with measures showing the measures within that location and if i select any station i should be able to show all the products and measures within that location and station.

Looks it is not possible to create such a report using MS SQL 2005 reporting services. If it is possible or if there are any alternative ways (to achive this without navigationg to other report screen) Pl. guide me through some example.

Thanks and regards,
Sri

GROUP BY and SUBQUERY for sum

hello everyone
i hope you could help me in this query.

I have one tables: tblA

tblA columns and data are as follows:

Prod Prodchid Req Process

Banana bread ingredientC 1 Y

ingredientC ingredientA 2 Y

ingredientC ingredientB 1 Y

ingredientA white sugar 2 N

ingredientA flour 1 N

ingredientB banana 1 N

ingredientB flour 1 N

I can to query the sum of Req group by Prodchid

SELECT Prodchid,sum(Req) as Qty

FROM tblA

GROUP BY Prodchid

the result is

Prodchid Qty

white sugar 2

flour 2

banana 1

How can I query by multiply require ingredientA.

How can I use SUBQUERY to get anwser below:

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Try

SELECT Prodchid,sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prodchid

or

SELECT Prod, Prodchid,sum(case Prod when 'ingredientA' then 2*Req else Req) as Qty

FROM tblA

GROUP BY Prod,Prodchid

|||

Anyway the ideea is to use case...when... in select command|||

"sum(case Prodchid when 'ingredientA' then 2*Req else Req) as Qty"

This only can apply for 'ingredientA' only, how to replace the '2' by subquery result.

|||

pps1 wrote:

How can I query by multiply require ingredientA.

Prodchid Qty

white sugar 2x2=4

flour 1X2+1x1=3

banana 1x1=1

Why do you have to multiply with 2, cause of line

ingredientA white sugar 2 N

or what ?|||

Here it is,

Code Snippet

Create Table #data (

[Prod] Varchar(100) ,

[Prodchid] Varchar(100) ,

[Req] int ,

[Process] char

);

Insert Into #data Values('Banana bread','ingredientC','1','Y');

Insert Into #data Values('ingredientC','ingredientA','2','Y');

Insert Into #data Values('ingredientC','ingredientB','1','Y');

Insert Into #data Values('ingredientA','white sugar','2','N');

Insert Into #data Values('ingredientA','flour','1','N');

Insert Into #data Values('ingredientB','banana','1','N');

Insert Into #data Values('ingredientB','flour','1','N');

Code Snippet

Select

data.[Prodchid],

Sum(data.[Req] * ingredient.[Req]) Qty

From

#data as data

Join

(

Select

[Prodchid],

[Req]

From

#data

Where

[Process] = 'Y'

) as ingredient

On ingredient.[Prodchid] = data.[Prod]

Where

data.[Process] = 'N'

Group By

data.[Prodchid]

|||

Dear Manivannan,

Thanks, That is what I needed.

In this case, I only can multiply one Level of 'Process', But anywhere it is OK.

Dear ggciubuc,

Multiply with 2, because ingredientA require 2.

All material in ingredientA should multiply with 2.

Thanks