SSRS Sum Expression for Adding Percent Column

0
votes

I think I've exhausted my searches with Google. Before it gets suggested, and I know it will, I cannot update my stored procedure to do this because of how the data is returned. I have to do this calculation in the report.

My table is grouped by Service Month-Year and it's the only group. I have a column in the table that calculates a percentage and that expression works great (see below).
=Sum(Fields!POSTED_AFTER_SVC_MO.Value,"SvcMo_Yr") / Sum(Fields!POSTED_IN_SVC_MO.Value,"SvcMo_Yr") + Sum(Fields!POSTED_AFTER_SVC_MO.Value,"SvcMo_Yr"))

What I'm unable to do is calculate a future percentage based on the results from the expression above.

In the example below, in order to get the future percentage for April 2017 (bottom table in image), I need to add the percentages for March through October and divide by 6. In Excel I use an Offset function to get those 6 rows but in SSRS I can't figure it out.

Example Data

One thing I have been able to do is calculate the average percentage for each month using an indicator I added to my stored procedure. If there was a way that I could just sum these I would be home free. Adding a total row and an extra Sum in the expression below did not work.

=Sum(Avg(IIF(Fields!FUTURE_MNTH_1_CALC_IND.Value = 1, CDbl(Sum(Fields!POSTED_AFTER_SVC_MO.Value) / (Sum(Fields!POSTED_IN_SVC_MO.Value) + Sum(Fields!POSTED_AFTER_SVC_MO.Value))),Cdbl(0))))/6

Thank you in advance for any advice you can throw my way!

John

1
ssrs-2008-r2ssrs-2012
Can you not dump the results of you SP into a temp table then use that to do your calculations? I think this would be easier done in SQL and if your SP output is anything like your sample it should not be too tricky. – Alan Schofield
I've tried but haven't been able to because of how the grouping is in the SP. In the report I have two different tablix tables that I'm trying to do this in. I don't know enough about VB coding to maybe create a custom function or something could sum it and return the value. I do have an indicator that I'm using in the SP that indicates which rows are used for which future percentage sum. – JohnD
I figured it out. I just recreated my stored procedure to do the totaling because I only needed those two totals. Then I added that new stored procedure as another dataset in my report. Thank you @AlanSchofield! – JohnD

1 Answers

0
votes

I figured it out. I just recreated my stored procedure to do only the totaling because I only needed just those two totals. Then I added that new stored procedure as another dataset in my report and created at hidden table so I could use the Lookup function to retrieve the totals. Worked like a charm!