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