How to calculate percentage row in SSRS Tablix?

Question

I am using SSRS 2008 and trying to calculate the following percentage in my tablix:

sum(Fields!Last14Days_Ct.Value) / countdistinct(Fields!Client.Value)

So Last14Days_Ct can be = 1 or 0. I want to sum all of these integer values. Client field is a VARCHAR, and there can be multiple rows / Client. So what I want to do is to calculate the % of Clients within the last 14 days. In other words, each Client has one field "Last14Days_Ct" = 1 or 0. So I just want to calculate the percentage of clients that happened in last 14 days. Here is the SSRS formula I tried right now:

=iif(countdistinct(Fields!Client.Value)=0,0,sum(Fields!Last14Days_Ct.Value)
/iif(countdistinct(Fields!Client.Value)=0,1,countdistinct(Fields!Client.Value)))

And I grouped that row on Fields!Last14Days_Ct.Value.

But problem is that now when I view it in ReportViewer, it shows 2 rows: when Last14Days_Ct = 1 and 0. I only want the row where it = 1 to appear. But if I apply a filter to that group on = 1, it always shows 100%. How can I properly calculate this value please?

Lurker Lurker · Accepted Answer · 2012-04-06T14:48:21

When you leave the filter off, is the calculation displaying correctly? If so, then open group properties for Last14Days_Ct, go to Visibility, and for "When the report is initially run" select "Show or hide based on an expression." Use the expression:

=Fields!Last14Days_Ct.Value = 0

That way, you only view the 1 values, but all the values are still in scope for your report, so the math in your calculation still works.

How to calculate percentage row in SSRS Tablix?

1 Answers