Percentage change column in Matrix tablix

Mark Palmberg 1

I'm using SQL Server 2016 Report Builder.

I have a very simple matrix that displays sum of revenue for my metrics by fiscal year. The dataset is a stored procedure that only returns data for two fiscal years, based on an input date parameter. Here's what my matrix looks like:

I'd like to have a column outside to the right of the FiscalYearName column that would hold an expression that returns the percentage change in revenue between the first and last fiscal year values. Like so:

Thanks for any recommendations you have!

2 answers

Joyzhao-MSFT 15,571 Reputation points

2020-09-18T03:41:50.487+00:00

Hi @Mark Palmberg .
How many years are there in your dataset ?
I may be not easy to calculate the percentage directly use RevenueAmount field in SSRS .
I would suggest to aggregate first in SQL with sql query. Calculate first in SQL , the year summary column. Then we could use the result directly in SSRS.
You could provide us some dummy test data if you find difficulty with SQL query.
Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Mark Palmberg 1 Reputation point

2020-09-18T14:43:02.45+00:00

There are only ever two years, Joy, so I was hoping I could get at this with using FIRST and LAST functions within my FiscalYearName column group, but I don't want an additional column inside that group (showing up twice, once for each year value), and I haven't had any luck with InScope, either.

Thanks for taking the time to reply. I appreciate it.

Joyzhao-MSFT 15,571 Reputation points

2020-09-21T06:57:57.95+00:00

Hi @Mark Palmberg ，
I did multiple tests. We could not use first/last function directly for this scene, because the two aggregation (for column group and row group each) could not be handled by current SSRS features.
We could change the tablix structure, try handle the data in Table with multiple row groups.
Or we need to change the dataset, and aggregate the data with SQL query.
Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Mark Palmberg 1 Reputation point

2020-09-23T21:05:27.43+00:00

@Joyzhao-MSFT what we ended up doing was adding a column inside the FiscalYearName column group that looks like this:

=(Previous(Sum(Fields!RevenueAmount.Value), "FiscalYearName") - Sum(Fields!RevenueAmount.Value))
/ Sum(Fields!RevenueAmount.Value)

Then we hid the column for the previous value, since it'll always be nothing:

= IIF(Previous(Sum(Fields!RevenueAmount.Value), "FiscalYearName") = nothing, True, False)

This seems to work OK. The main problem now is that, when I copy this matrix and change the MetricType column so that it only includes two values instead of three (as in the original matrix), the formulas no longer work. It's almost as if there's something going on behind the scenes in the matrix that's keeping the row and column references from the original matrix when I copy and paste it....

Joyzhao-MSFT 15,571 Reputation points

2020-09-25T10:04:24.107+00:00

Hi @Mark Palmberg ,
Have you resolve the issue ?
It seems that it was pretty close and could be done ?
Sorry not quite clear what this MetricType column is, could you give some screenshot or more detail?

Best Regards,
Joy

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Sign in to comment
Joyzhao-MSFT 15,571 Reputation points

2020-09-25T10:02:03.133+00:00

If you have any question, please feel free to let me know.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

Percentage change column in Matrix tablix

2 answers