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 Query to include 0 values

I have to sum(NoWidgets) produced GROUP'ed By Station. The Widgets Produced
are in a subtable of the Widget Stations, so it works for the most part by
doing this:
Select Sum(NoWidgets) From WidgetData Inner Join StationData GROUP BY Station
Where I run into problems is when I want to sum the number of blue widgets
by Station. Because I want to know even if a station produced 0 blue widgets.
If I say:
Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
'Blue' GROUP BY Station
Then the stations that had no blue widgets are not included in the output.
I want ALL stations in the output, even if they have a value of 0. I'm also
wanting to do an average, and I want that based upon all stations, whether or
not any blue widgets were produced.
Seems like it should be easy to do, and I'm sure I'm showing myself to be a
novice for not know how that is done.
Thanks!
On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
(snip)
>Where I run into problems is when I want to sum the number of blue widgets
>by Station. Because I want to know even if a station produced 0 blue widgets.
>If I say:
>Select Sum(NoWidgets) From WidgetData INNER JOIN StationData Where Color =
>'Blue' GROUP BY Station
>Then the stations that had no blue widgets are not included in the output.
>I want ALL stations in the output, even if they have a value of 0. I'm also
>wanting to do an average, and I want that based upon all stations, whether or
>not any blue widgets were produced.
Hi PolarBears,
Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
BY clause:
SELECT SUM(NoWidgets)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
WHERE Color = 'Blue'
GROUP BY ALL Station
Note that this works in SQL Server 2000 and SQL Server 2005, but the
GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
it will be removed in a future version).
If you prefer a portable, ANSI-standard version, you can use
SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
FROM WidgetDate
INNER JOIN StationData
ON somethin you forgot to include in your post
GROUP BY Station
(Note: both queries above are untested. See www.aspfaq.com/5006 ff you
prefer a tested query.)
Hugo Kornelis, SQL Server MVP
|||Thanks a ton!
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 13:50:28 -0800, PolarBears wrote:
> (snip)
> Hi PolarBears,
> Yes, this is simple. You can use the GROUP BY ALL version of the GROUP
> BY clause:
> SELECT SUM(NoWidgets)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> WHERE Color = 'Blue'
> GROUP BY ALL Station
> Note that this works in SQL Server 2000 and SQL Server 2005, but the
> GROUP BY ALL clause is marked as deprecated in SQL Server 2005 (meaning
> it will be removed in a future version).
> If you prefer a portable, ANSI-standard version, you can use
> SELECT SUM(CASE WHEN Color = 'Blue' THEN NoWidgets ELSE 0 END)
> FROM WidgetDate
> INNER JOIN StationData
> ON somethin you forgot to include in your post
> GROUP BY Station
> (Note: both queries above are untested. See www.aspfaq.com/5006 ff you
> prefer a tested query.)
> --
> Hugo Kornelis, SQL Server MVP
>