How Do I Count Groups in SSRS 2005?

[timfoster]

OK. This has been bugging me for the best part of a day now. I have a report in SSRS 2005 that is grouped. So, referrals for an individual with events within the referrals. So my top level is my individual. Each can have multiple referrals and each referral can have multiple events.

How the hell do I count the referrals? I know I can do it in the SQL, but I want to do it in the report.

I've tried all sorts of methods. The obvious (Count, CountDistinct etc) with and without a scope. Counting values in a column. I've even tried coding it. Nothing seems to work!

What have I missed? this should be incredibly straightforward.

 

[xlbo]

Should be able to do a runningcount on referral using scope to determine where to restart the count from then access the max running count for each group

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timfoster]

Hi Geoff,

I tried using the scope but I get errors every time. This is in 2005,bit that shouldn't matter.

If I try =Count(ReferralID, "GroupHeader") Or CountDistinct(ReferralID, "GroupHeader") I get an out of scope error.

It may also make a difference that I'm trying to put the count into the table header, but I can't see the precendence of writes.

 

[xlbo]

Where do you want to do the count of referrals? If you are doing it by individual, presumably, you want the count next to the group somewhere rather than in the table header?

You may get away with just having a count(referralID) in the group header or footer....if this is thrown out by having multiple events then you will need to create a RUNNINGcount column using scope to determine when to restart the count. you then set the runningcount column width to zero so it doesn't show and reference that in the group header / footer.


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timfoster]

Hi Geoff,

I'm trying to count the refs in the table header. I want to display the count of refs along with a descriptive text in the header with the actual refs and their notes/events below.

 

[xlbo]

So is each report for one individual or are there multiple individuals in each report? I had the impression that you had multiple individuals as groups within a table rather than the table being for one individual...

If it is the case that the report is for one indicidual (ie there is not a group for them), you probably need to create a data item using Inscope to return a 1 or 0 depending at what level the row in the table is at i.e a 1 for inscope at your referral group level, a 0 otherwise. Add that data item into the table and set the width of that column to 0

Then reference the SUM of that data item in the table header

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question