How to compare column totals in an ssrs matrix?

Posted on 2008-06-19
Last Modified: 2012-06-21
I have a matrix report that looks like this:

Product        Month1     Month2    Month3
xxx               123.45     234.56    345.67
yyy               987.65     876.54   765.43

Column group is month (there can be more than 3), row group is product (many rows)

I need a column after months 2 and 3 (etc..) with the differences:

Product        Month1     Month2   Diff 1-2  Month3  Diff-2-3
xxx               123.45     234.56    111.11  345.67    111.11
yyy               987.65     765.43    222.22  654.32    111.11

How do I set up the column with the differences in it to be hidden the 1st time, and subsequent times have the difference between the current and previous iteration?


Question by:phoobar
  • 4
  • 3
LVL 15

Expert Comment

ID: 21829525
Try setting the Hidden property to be based on an expression which comes out as True if the value of the first column isn't the minimum one (based on the outer scope).

I should point out that this isn't something I've tried before...


Author Comment

ID: 21831925
How do I know if it's the 1st column or it's subsequent columns?  What variable holds the column number?
LVL 15

Expert Comment

ID: 21837111
You'd need to compare it to something you know is the minimum.

Author Comment

ID: 21847628
Perhaps I'm not asking the question in the right way.  

Specifically, how do I set up the column with the differences in it?  What is the expression that will give "diff between this amount and the previous amount"?

Can it be done with custom code?  How is the custom code executed?
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


Author Comment

ID: 21857575
Can anyone describe how custom code might be written to save the previous column's data and use it to compare to the current column?
LVL 15

Expert Comment

ID: 21862463
Reporting Services has a function called Previous which may do it for you - have a play and see.


Accepted Solution

phoobar earned 0 total points
ID: 22047350
I figured out how to use code to acomplish my task.  I was using the 7 day free trial, and since I did not get answers to either of the questions I posed, I opted to cancel.  I didn't realize that I could still post a reponse, which would have been the polite thing to do.  I apologize for letting the question linger into abandonement.

Expert Comment

ID: 22326958
I have a similar problem. What is the solution?

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now