1

Currently I'm using this expression in a cell above the column headers of a table:

= "Num Days " & CountDistinct(Fields!Date.Value)

In the report designer this is the context of the expression:

enter image description here

Here is what the result looks like:

enter image description here

The expression is working ok i.e in the data set there are 60 distinct dates which is what the expression returns. However there is a filter on the group (Date2) so that it only has 10 visible days in the tablix.

How do I amend the expression to return just the dates that are visible?

Pedram
  • 6,256
  • 10
  • 65
  • 87
whytheq
  • 34,466
  • 65
  • 172
  • 267

1 Answers1

2

The easiest solution is to move the filter upstream: can you put the filter on the Dataset instead of the Tablix Group? Then these rows won't be included in your total.

Other options include custom code to keep a running tally of values, or putting a conditional in your aggregate expression, maybe something like:

   = "Num Days " &
     (COUNTDISTINCT( IIF(DateDiff("d", Fields!Date.Value, Now) <= 10,  Fields!Date.Value, 1)) -1 )
Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • don't quite follow; the dataset has 60 days whereas the tablix is this set of 60 filtered down to 10 - so how does going upstream to the dataset help? The 10 days returned in the screenprint was coincidentally the first ten days in Oct - could as easily have been 25Sep - 4Oct inclusive in which case this expression would be wrong – whytheq Oct 11 '12 at 16:00
  • can I not access a count of the columns that are visible in a tablix? – whytheq Oct 11 '12 at 16:01
  • If the only data region in this report referencing the data set is this one, then you can add the filter to the dataset. (Right click on Dataset in BIDS, go to properties -> filter tab.) This doesn't require changing the query. Then your expression will work. Yes, I get that you may be doing somethine other than October, but since I don't know what your filter is, I put in some arbitrary filter. It is not too easy to get at the visible columns: rendering is pretty late in the SSRS process. – Jamie F Oct 11 '12 at 16:06
  • hmmm - this request might be tricky then: you can see a very small 60days sparkline in the screenprint i.e I need all 60 days into the tablix region for the sparkline to use - then in the columns Date group I filter down to just the last 10 days. – whytheq Oct 11 '12 at 16:55
  • maybe if we look at [getdate - min(date)] then if this is less than 10 it should return the result - otherwise if more than 10 then just return 10; is this possible? – whytheq Oct 11 '12 at 16:57
  • I changed my answer to one that might match your filter. If you want more details, please share the filter that you are using. – Jamie F Oct 11 '12 at 23:25