1

I have an existing SSRS report that shows some overall counts on the top of the report. The report data looks like:

column1    column2
A1         true
-          true
B5         false
A1         true
-          false
C3         false

What I need is a distinct count of the items in column1, but including all the rows with the "-". So the result for this example data should be 5, counting the "A1" rows as one. The catch is that this needs to be done in an expression or some method on the report itself. I cannot change the dataset queries.

I have tried: =COUNTDISTINCT(Fields!column1.Value) but that counts the "-" rows as one.

Is it possible to add another dataset and develop a query for that one that queries the existing dataset? Not sure if that can be done.

Blaze
  • 1,863
  • 7
  • 23
  • 40
  • If you add another row like `- false` how does that affect the overall count? Just trying to get a sense of how multiple `-` rows should be counted. – Ian Preston May 07 '14 at 16:35
  • That would increase the count by 1. All "-" rows are counted as unique and the rest should be grouped. – Blaze May 07 '14 at 19:43

1 Answers1

1

This works OK for me:

=CountDistinct(IIf(Fields!column1.Value = "-", Nothing, Fields!column1.Value))
  + Sum(IIf(Fields!column1.Value = "-", 1, 0))

I'm using two aggregate IIf expressions for the two cases.

The first is CountDistinct against all the non - rows, and the second is simply a count of all - rows.

Seemed to work OK with all - rows and with no - rows.

Edit after comment

If you're using this expression outside of a tablix, you may need to specify the Scope of the aggregate:

=CountDistinct(IIf(Fields!column1.Value = "-", Nothing, Fields!column1.Value), "MyDataSet")
    + Sum(IIf(Fields!column1.Value = "-", 1, 0), "MyDataSet")
Ian Preston
  • 38,816
  • 8
  • 95
  • 92
  • I'm getting this error twice when using that: [rsMissingAggregateScope] The Value expression for the text box ‘Textbox25’ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one dataset. I added the dataset to each of the calculations and it only shows the error once now. =CountDistinct(IIf(Fields!column1.Value = "-", Nothing, Fields!column1.Value), mydataset) + Sum(IIf(Fields!column1.Value = "-", 1, 0), mydataset) Not sure how to fix this, the stuff I'm finding on it is not very clear. – Blaze May 08 '14 at 11:19
  • Yes, if you're using this outside a tablix you'll see that error with multiple datasets. I've updated the answer with an expression that worked for me in that case. In your comment, I note your Dataset name is not in quotes; it needs to be. But could just be a typo. Other than that it's case sensitive, too. To troubleshoot it might help to only look at one part of the statement at a time, i.e. confirm the `CountDistinct` works, then the `Sum`, then finally add them together when they're both working. – Ian Preston May 08 '14 at 11:41
  • Yes I forgot to add in the dataset quotes, it was just a typo. I'm unable to test at the moment but it builds fine now without that error. I do notice that the first Fields!column1.Value right after the IIf statement has a squiggly red line under it and says "Unknown collection member". I've never seen that before. – Blaze May 08 '14 at 12:34
  • If you're referencing a field in a Dataset in a textbox outside of a tablix/chart, SSRS is not smart enough to realise its source even if you specify a Scope parameter, so it displays the warning you're seeing. It's a bit annoying but should be fine at runtime.. – Ian Preston May 08 '14 at 12:45