SSRS Report - Number of Subcategories in Category

Question

SSRS Report - Number of Subcategories in Category

btio_3000

SSCrazy

Points: 2662
More actions
April 29, 2010 at 12:18 pm

#224013

Hi,
Let's say I have a table with three fields, Group, Subgroup and Net, as follows:
Group.......Subgroup.......Net
====.......=======.......===
1.............11................10.00
1.............11................20.00
1.............13................30.00
2.............21................40.00
2.............22................50.00
2.............22................55.00
2.............23................56.00
3.............31................60.00
3.............32................70.00
3.............33................80.00
3.............34................90.00
and I want my report to show Total Net along with a number of unique Subgroups for each Group, so that
for Group1 it would be 2 (11 and 13),
for Group2 it would be 3 (21, 22 and 23),
for Group3 it would be 4 (31, 32, 33 and 34)
Of course, there is no problem with showing total Net, but how do I do the number of unique subs inside each Group?
I mean, is there a way of getting that value not through the dataset query, but in the report itself?
Thank you.
.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

Your table/tablix would have 2 groups, the Group (grouped on the group column) and then a second group under the first group (grouped on the subgroup column). You can then do totals at each level if desired.

It would look something like this, if you had a header row fro each group:

Group 1

SubGroup 11

Net 10.00

Net 20.00

SubGroup 11 Total: 30:00

SubGroup 13

Net 30.00

SubGroup 13 Total: 30.00

Group 1 Total 60.00

Group 2

Subgroup 21

details

Subgroup 21 Total

etc...]

You can choose to not do sub totals or group headers as well.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

btio_3000 SSCrazy Points: 2662 More actions · Answer 2

Thanks Jack, but totaling Net at the group/subgroup level is not a problem, sorry if I did not explain it well.

My question is if there is a way of showing a number of unique Subgroups inside each Group using just the report functionality instead of returning those numbers in a query.

Thanks again

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Okay, what determines what subgroup an item belongs in? There has to be something in the data that identifies it or else you can't report on it.

What's your query look like? What is the business definition of a subgroup?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

btio_3000 SSCrazy Points: 2662 More actions · Answer 4

Jack,

The record's unique key consists from two fields, GroupCode & SubgroupCode

The report would look like below:

GROUP 1

........Subgroup 11

...................10.00

...................20.00

...................====

...................30.00

........Subgroup 13

...................30.00

...................====

...................30.00

.....Number of Subgroups in Group: 2

GROUP 2

........Subgroup 21

...................40.00

...................====

...................40.00

........Subgroup 22

...................50.00

...................55.00

...................====

................. 105.00

........Subgroup 23

...................56.00

...................====

..................56.00

.....Number of Subgroups in Group: 3

and so on

Now, the question is how to return the boldfaced values of number of subs inside each Group.

Currently I am doing that in my query, but is there a way of implementing that through the report itself?

Unfortunately, the only way to distinguish a "header" from a "detail" in my case is to check if the Group/Subgroup combination breaks, so what I tried was using SSRS's "Previous" BUT:

- I can not use "previous" in Calculated field, and

- If I add a column with "Previous" to return 1 or 0 depending on either that Group/Subgroup combination breaks, then how do I sum that column values? Does SSRS somehow allow to sum not by "field value", but by "column value"?

Or any other idea for that matter.

Almost forgot, I have also tried to use a public variable in Code to store the accumulated value in it, but for some reason it seems to be reset to 0 as soon as it hits a pagebreak

Thanks very much once again

btio_3000 SSCrazy Points: 2662 More actions · Answer 5

Ok, the question is still there, and here comes another one.

I am currently returning those Num of Subs in a group as fields in a query, where the query looks like this:

SELECT t1.Group,t1.Subgroup,t1.Net,t2.Cnt,t3.TotCnt

FROM dbo.myTable t1

LEFT JOIN

(SELECT Group,Cnt=COUNT(DISTINCT Subgroup) FROM dbo.myTable GROUP BY Group) t2 ON t1.Group=t2.Group,

(SELECT TotCnt=COUNT(DISTINCT Group+Subgroup) FROM dbo.myTable) t3

ORDER BY Group,Subgroup

where Cnt is a number af Subgroups inside each Group, and TotCnt is a number of Subgroups altogether

So then on the report, I am just showing Cnt at each Subgroup total line, and TotCnt at the bottom of the report

so far so good.

But then I decided to make that query less ugly by using DENSE_RANK instead:

SELECT t1.Group,t1.Subgroup,t1.Net,

Cnt=DENSE_RANK() OVER (PARTITION BY t1.Group ORDER BY t1.Group,t1.Subgroup),

TotCnt=DENSE_RANK() OVER (ORDER BY t1.Group,t1.Subgroup)

FROM dbo.myTable t1

ORDER BY t1.Group,t1.Subgroup

Now, the query does return the values I expect, but on the report, it always shows 1 for all Cnt's as well as TotCnt - I am talking about "GroupTotal" lines, the "details" do show proper Dens_rank's values

Any ideas? Does it show "current" line in one case and "previous" in other, any like that? Drives my crazy.

Thanks!

Siva Gurusamy SSCrazy Points: 2711 More actions · Answer 6

=CountDistinct(Fields!SubgroupCode.Value,"GroupCodeName")

In the footer row of 'GroupCode' place the above expression.

"GroupCodeName" is the Name of GroupCode group

It should work.

"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation

btio_3000 SSCrazy Points: 2662 More actions · Answer 7

Siva Gurusamy (5/3/2010)
=CountDistinct(Fields!SubgroupCode.Value,"GroupCodeName")
In the footer row of 'GroupCode' place the above expression.
"GroupCodeName" is the Name of GroupCode group
It should work.

Hi Siva,

That did the trick, thank you!