Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

MS SQL 2000 Report Services. Conditional Counting

Hello,

I don't exactly remember how it was called in Crystal Reports (I think it was running totals), but I can't find a similar function in MS Reporting Services. Simple totals are simple, just use Count(...), but if I need to add conditioning, it becomes a real pain as there is nothing I could find so far in Report Services themselves.

Normally, I would create a couple of extra columns in my query, where I'd put 1 and 0 for different conditioning using CASE and then SUM on the proper column, but in this case I've already got 5 extra columns and I'll need 5 more just with 1s and 0s... I will probably have to do it as I did it before, but at least I can try to find how to do it properly.

To summarize: I need to know how to count the number of rows in the set under a condition specified, e.g.:

count( Fields!MyValue.Value ) only where Fields!MhDate = Today() for example.

Thanks,
Yurich
0
Yurich
Asked:
Yurich
1 Solution
 
Mr_PeerapolCommented:
Yes, in my opinion, I think SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the [only good] way to do that.
(SELECT COUNT(*) FROM the_same_table b WHERE b.group_by_field = a.group_by_field AND b.another_field = some_condition) is look even longer.
0
 
YurichAuthor Commented:
In fact, I found a solution of how to do it in the Report Services, here is a smiplified example:

=RunningValue(
      IIF(      Format( Fields!MyDate.Value, "dd/MM/yyyy" ) =
                  Format( Parameters!TheDate.Value, "dd/MM/yyyy" ) AND
                  Fields!days.Value > 20, 1, 0 ),
      sum, "grpTypes" )

if this running value is placed in a group footer, it will correctly calculate the number of records for a day specified in TheDate variable with the days field greater than 20. it is in fact a derivation of the query with cases, but a bit more clearer and doesn't depend on the sp I run for a dataset.

Regards,
Mik
0
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
hi can you give me sample example so that i can give the exact solution.
0
 
YurichAuthor Commented:
For example, I have a record set returning from my sp that populate a dataset that has negative and positive values, I want to count the number of rows (in my report) where the value is only negative.

thanks,
yurich
0
 
BooModCommented:
Closed, 500 points refunded.
BooMod
Special Ops Mod
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now