not getting correct totals - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

not getting correct totals

Author

Topic

dand

13 Posts

Posted - 2006-09-22 : 12:01:32

Using RS2000.

I have a report in which within a group I want to count distinct instances
of a company name. If the company shows up 3 times in the group, I only want
to count it once. I use =CountDistinct(Fields!CompanyName.Value) in the group
and this works. However, in the table footer I used the same formula but it
doesn't give me a correct count. I think I understand why but I don't know
how to get the correct count. I tried
=Sum(CountDistinct(Fields!CompanyName.Value)) but I get an error that says
"the value expression for the textbox 'textbox10' contains an aggregate
function (or RunningValue or RowNumber functions) in the argument to another
aggregate function. The report looks like this:

Enterprise 1 Company A Item 1
Enterprise 1 Company B Item 2
Enterprise 1 Company C Item 1
Enterprise 1 Company A Item 2
Enterprise 1 Company C Item 2
Group Total for Enterprise 1 Company Count 3 - because we only count Company A & C
once. the expression used for this group is =CountDistinct(Fields!CompanyName.Value)

Enterprise 2 Company A Item 1
Enterprise 2 Company B Item 2
Group Total for Enterprise 2 Company Count 2

Total Company Count 5
I originally used =CountDistinct(Fields!CompanyName.Value)
and I would get a count of 3 but I need to add the total for each group
instead.

Any idea how to do this?

DanPD

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-22 : 16:39:12

Have you tried specifying a scope (second parameter to the CountDistinct function)?

Try =CountDistinct(Fields!CompanyName.Value, Nothing)
If that isn't right, then replace Nothing with the name of the group that contains the table (in quotes).

dand

13 Posts

Posted - 2006-09-25 : 09:14:07

When I try that with CountDistinct I get an error that the scope function is not valid for that aggregate. I tried using =RunningValue(Fields!CompanyName.Value,Sum,Nothing) but I get the error "‘textbox2’ uses a numeric aggregate function on data that is not numeric". When I try to put in the group name which is "table1_group1_entnbr" I get the error: "c:\inetpub\wwwroot\smclms\smclsmreports\Billing Detail Report.rdl The value expression for the textbox ‘textbox2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." I'm getting the group name by clicking on the group row while on the Layout tab in VS2003, then go to "edit group" and the "name" field on the "General" tab. RunningValue seems like the right function to use since I want to accumulate the total of each group.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources