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

grouping question

Hi,

I have a cube which has a "Product" dimension that has 4 levels which are:

- High Level Product Name
- Mid Level Product Name
- Low Level Product Name
- Product Name

My fact table has measures for all 4 levels.

I have a report with an MDX data source. The report contains a table control which has 2 columns, "product name" and a measure column.

In my report I have defined two groups, one for product level 1, and another for product level 2. This will mean that my data will be grouped by products as follows: (below products a, b and c are level 1, x, y and z are level 2)...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Running the report works ok, but for some reason an additional unwanted total row is appended at the beggining of each of my level 2 groups which shows the total for that group. To explain what I mean, the report looks like this...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
$1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Note that under product B a blank row has been added with the total. I don't want this and I can't figure out how to get rid of it. Anyone know what I am doing wrong?

If it helps my query is below (reporting services matches the product names to group them)...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty { [Product].[High Level Product Name].members, [Product].[Mid Level Product Name].members,[Product].[Low Level Product Name].members, [Product].[Product Name].members } ON rows
FROM
[MyCube]

Thanks,

Lachlan

Hi,

I fixed my problem by changing my query to the following...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty {[Product].[Product Name].members} ON ROWS
FROM tacticaleva

Notice I now only select the lowest level product [Product].[ProductName] rather than selecting all of my product levels in one mdx query.

I then changed the measure expressions in the product group rows to use =Sum(Fields!Measures_Ytd_Average_Capital.Value) rather than just the measure without Sum(). The lowest level product is not in a group it's just in a row so I didnt need to use Sum(), I just used =Fields!Measures_Ytd_Average_Capital.Value.

I'm not sure why it works this way and not the first way that I tried it, but I'm new to both MDX and SSRS and still learning :)

Lachlan

grouping and summing

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

the query to return your desired result would look something like this...

select date, prod, class, sum(qty)

from YourTable

group by date, prod, class

thus what you are saying in this query is aggregate the qty per date, prod, class. So if any of these values are different a new record is created. Thus the same product with two diff. class values would result in two records.

HTH,

Derek

|||Thanks for your help Derek!! I was putting the sum and group opposite of what you said.|||no prob dude, take it easy.

Group By, Max(date) query problem

Hello all:

I have an invoice header and detail tables and a customer table using sqlserver 2005. The Detail invoice table has price and product id. The header has the date and customerID.

I need to create a list of the most recent invoice date (and the product price) for each product for each customer. I can't use the group by because when I select both the max(date) and the price (as well as the productID and customerID which all have to be included in the group by) , I get more than one date per product per customer. I can only get the most recent date when I leave out the price and headerdetailID from the field selection.

Any help would be appreciated. Here is sample data & results.

invoiceHeadertable

invheaderID CustomerID InvoiceDate

1 40 1/1/2006

2 40 4/1/2006

3 80 3/1/2006

4 80 7/1/2006

5 80 8/12/2006

invoicedetailtable

invdetail ID invheaderID productcode price

11 1 AA 1.50

12 1 BB 1.30

13 1 CC 1.00

21 2 AA 1.40

23 2 CC 2.00

24 3 AA 2.00

25 3 CC 2.10

26 3 EE 1.10

27 4 AA 1.00

28 4 CC 2.00

29 4 EE 0.99

34 5 EE 1.55

CustomerTable

CustomerID Customername

40 johnCorp

80 maryCorp

Results

customer product most recent invoice(for this product) price

JohnCorp 40 AA 4/1/2006 1.40

JohnCorp 40 BB 1/1/2006 1.30

JohnCorp 40 CC 4/1/2006 2.00

maryCorp 80 AA 7/1/2006 1.40

maryCorp 80 CC 7 /1/2006 2.00

maryCorp 80 EE 8/12/2006 1.55

Something like this 'should' work for you. (Untested)

SELECT
dt.Customer,
dt.Product,
dt.InvoiceDate
d.Price
FROM InvoiceDetailTable d
JOIN ( SELECT
Customer,
Product,
InvoiceDate = max( InvoiceDate )
FROM InvoiceHeaderTable h
JOIN InvoiceDetailTable d
ON h.InvHeaderID = d.InvHeaderID
GROUP BY
Customer,
Product
) dt
ON ( d.Customer = dt.Customer
AND d.Product = dt.Product
AND d.InvoiceDate = dt.InvoiceDate
)

|||Crapola, didn't check your tables first. So ignore the prevoius exercise in 'egg on my face'.|||

IF there was no more than one invoice per day for a customer, you could, in the derived table (dt), include in the SELECT list:

InvHeaderID = max( InvHeaderID )

And then JOIN ON InvHeaderID instead of the three fields I indicated.

Of course, if there were two invoices for the same customer in a day, that would still not be the correct solution.

|||

Well, most of the time there would only be one invoice. But as is always the case, there can be an exception.

I had also wondered if the rank and partition function in sql server 2005 could apply, then one could just use a select query (if this is possible) to return the # 1 invoice per group but I have found no examples showing this used in a group by and where there are multiple tables involved.

Thanks

smhaig

|||

select

ct.CustomerName as Customer,

ct.CustomerID as CustomerID,

idt.ProductCode as Product,

iht.InvoiceDate as MostRecentInvoiceDate,

idt.Price as Price

from InvoiceDetailTable as idt

join InvoiceHeaderTable as iht

on idt.invheaderID = iht.invheaderID

join CustomerTable as ct

on ct.CustomerID = iht.CustomerID

where not exists

(select 1

from InvoiceDetailTable as idtx

join InvoiceHeaderTable as ihtx

on idtx.InvHeaderID = ihtx.InvHeaderID

where idtx.ProductCode = idt.ProductCode

and ihtx.CustomerID = iht.CustomerID

and ihtx.InvoiceDate > iht.InvoiceDate)

order by

ct.CustomerName,

idt.ProductCode

|||

-- Using SQL Server 2005

set nocount on
set dateformat mdy

create table invoiceHeadertable(invheaderID int,CustomerID int,InvoiceDate datetime)
insert into invoiceHeadertable(invheaderID ,CustomerID ,InvoiceDate )
select 1, 40, '1/1/2006' union all
select 2, 40, '4/1/2006' union all
select 3, 80, '3/1/2006' union all
select 4, 80, '7/1/2006' union all
select 5, 80, '8/12/2006'

create table invoicedetailtable(invdetailID int, invheaderID int, productcode char(2), price decimal(5,2))
insert into invoicedetailtable(invdetailID , invheaderID , productcode , price )
select 11, 1, 'AA', 1.50 union all
select 12, 1, 'BB', 1.30 union all
select 13, 1, 'CC', 1.00 union all
select 21, 2, 'AA', 1.40 union all
select 23, 2, 'CC', 2.00 union all
select 24, 3, 'AA', 2.00 union all
select 25, 3, 'CC', 2.10 union all
select 26, 3, 'EE', 1.10 union all
select 27, 4, 'AA', 1.00 union all
select 28, 4, 'CC', 2.00 union all
select 29, 4, 'EE', 0.99 union all
select 34, 5, 'EE', 1.55

create table CustomerTable(CustomerID int, Customername varchar(10))
insert into CustomerTable(CustomerID , Customername )
select 40, 'johnCorp' union all
select 80, 'maryCorp';

with cte(customer,product,[most recent invoice(for this product)],price,rn)
as (
select c.Customername,
d.productcode,
h.InvoiceDate,
d.price,
rank() over(partition by c.Customername,d.productcode order by h.InvoiceDate desc)
from CustomerTable c
inner join invoiceHeadertable h on h.CustomerID=c.CustomerID
inner join invoicedetailtable d on d.invheaderID=h.invheaderID
)
select customer,
product,
[most recent invoice(for this product)],
price
from CTE
where rn=1
order by customer,product

|||

I want to thank Mark and Ron for their solutions and Arnie for getting me to think about two invoices on the same day for the same product and customer (which do exist actually).

I found these solutions to be on the level of advanced lessons for me to study. I have always had trouble with group by when I needed a unique ID on a table where I was selecting a max or min or first one, etc. on another field in the same table.

I have not found any good examples other than very basic ones for rank and partition so if anyone has a good site let me know. Meanwhile I will study what I have as I now have two great ways to solve my problem.

I did not give a duplicate item with my sample data so I will see how this sql 2005 query deals with this. I seem to remember something about ties and ranking and perhaps I could also use select distinct when I select the rank = 1.

The second standard sql query (Ron's) shows me both invoices when there are 2 on same date. It may be that this is the way the data should be displayed if the prices are different, so I will need to check further on that and see if I can tweak these 2 queries to deal with that.

Thank you all again

smHaig

|||

And my thanks to Mark for demonstrating the more modern solution of the two!

Ron