Grouping and Aggregate Functions in Reporting Services

Hi All,
Can I ask you something about Grouping and Aggregate Functions in
Reporting Services?
My question is, I have a grouping in a table like the following;
Group 1 - Manufacturer - TotalInvoicePerManufacturer
Group 2 - Region - TotalInvoicePerRegion
Group 3 - Distributor - TotalInvoicePerDistributor
Group 4 - RetailerType - Nothing
Detail - Retailer - Nothing
And my stored procedure gives the resultset like this;
Manufacturer - Region - Distributor - InvoiceAmount - Retailer -
RetailerType
As you can see above, since I have retailer name and type in my
resultset, even though InvoiceAmout is only for Distributor, I have a
couple of rows (as much as a distributor has) with the same
InvoiceAmount.
What I want is;
Group 1 - Manufacturer - TotalInvoicePerManufacturer
(=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
multiplied by distributor's retailer count, but I need only sum of
distributors' Invoice amounts)
Group 2 - Region - TotalInvoicePerRegion
(=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
multiplied by distributor's retailer count, but I need only sum of
distributors' Invoice amounts)
Group 3 - Distributor - TotalInvoicePerDistributor (If
amount is A, to see A - for this I use =Fields!TotalInvoice.Value and
it's OK)
Group 4 - RetailerType - Nothing
Detail - Retailer - Nothing
Can you help me?
Thanks a lot..Hi, or "Merhaba"
Even though I am not very clear about what you are trying to accomplish, I
would suggest you to use
matrix report.
-Put Group 1 > Group 2 > Group 3 > Group 4 in columns. Each group should
refer to its parent.
-Put TotalInvoice in to the data cell.
-At the end play with collapse/expand row features, for aggregates.
Good luck.
Regards,
Cem Demircioglu
"Sema Yuce" <sema.yuce@.eczacibasi.com.tr> wrote in message
news:9844673f.0412072205.70170fd5@.posting.google.com...
> Hi All,
> Can I ask you something about Grouping and Aggregate Functions in
> Reporting Services?
> My question is, I have a grouping in a table like the following;
> Group 1 - Manufacturer - TotalInvoicePerManufacturer
> Group 2 - Region - TotalInvoicePerRegion
> Group 3 - Distributor - TotalInvoicePerDistributor
> Group 4 - RetailerType - Nothing
> Detail - Retailer - Nothing
> And my stored procedure gives the resultset like this;
> Manufacturer - Region - Distributor - InvoiceAmount - Retailer -
> RetailerType
> As you can see above, since I have retailer name and type in my
> resultset, even though InvoiceAmout is only for Distributor, I have a
> couple of rows (as much as a distributor has) with the same
> InvoiceAmount.
> What I want is;
> Group 1 - Manufacturer - TotalInvoicePerManufacturer
> (=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
> multiplied by distributor's retailer count, but I need only sum of
> distributors' Invoice amounts)
> Group 2 - Region - TotalInvoicePerRegion
> (=sum(Fields!TotalInvoice.Value) gives me here InvoiceAmount
> multiplied by distributor's retailer count, but I need only sum of
> distributors' Invoice amounts)
> Group 3 - Distributor - TotalInvoicePerDistributor (If
> amount is A, to see A - for this I use =Fields!TotalInvoice.Value and
> it's OK)
> Group 4 - RetailerType - Nothing
> Detail - Retailer - Nothing
> Can you help me?
> Thanks a lot..