Counts and Total of a group

(0) Share

Report

Posted on by Community Member

I have the following table:

CustomerGroup	Type	Amount
10	B2B	10.00
10	B2C	20.00
20	B2B	100.00
20	B2B	200.00

I need to create a SSRS Report which contains consolidated data of the above

Count	Customer Group	Type	Amount
1	10	B2B	10.00
1	10	B2C	20.00
2	20	B2B	300.00

I created a report where I "GROUPED BY" first by Customer Group and then by "Type".

1. How do I get the count within a group. I used CountRows(CustomerGroup) but this does not count within the group. Can you please advise on what the expression should be so as to get the count within a group.

2. How to get total amount inside the group. I read that using "ADD TOTALS" will add a separate row outside the group having the total amount. But I do not a separate row. I need the total in the same row. Can you help me with the same?

Many thanks in advance.

I have the same question (0)

All responses (5)

Answers (2)

Martin Dráb 235,968 Most Valuable Professional on at

Like (0)

Report

RE: Counts and Total of a group

Can you elaborate your problem, please?

Which row do you mean?

Can you add a screenshot that shows groups? That's the crucial thing and it's completely missing from your picture.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Counts and Total of a group

Can you tell where I am going wrong.

The row seems to be repeating.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 235,968 Most Valuable Professional on at

Like (0)

Report

RE: Counts and Total of a group

Then you must remove grouping from the query. The report will receive individual records and it will group and aggregate them on its own.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Counts and Total of a group

If any queries defined via "Expression" can help achieve this, that would be my goal

Was this reply helpful? Yes No
Verified answer

Martin Dráb 235,968 Most Valuable Professional on at

Like (0)

Report

RE: Counts and Total of a group

You must decide where you want to group the data - in the query or in the report. Currently you're grouping the query but then you're trying to use SSRS functions that would work if the report received ungrouped data.

If you continue grouping the query, use the SUM aggregation function on Amount and COUNT to get the count.

Was this reply helpful? Yes No