Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bassist695
Starting Member
9 Posts |
 Posted - 2005-03-21 : 10:30:14
|
I have dollar amounts grouped by year. When running the report, there will be at most two years' worth of data. I need to compare the latest year to the prior year, and take action if there has been a loss of business (please see attached screenshot).At very least, I would like to turn the dollar amount red if it is less than the prior year. If I can do that, I would like to somehow signify a % change since that year.It's REALLY tough to do this within a matrix. I've tried everything, but I cannot seem to reference both amounts at the same time in order to act on them.Please let me know how you might do it, I think I may have to alter the dataset (which I don't want to do), or create another report, but if I can do it within this report, I'd like to. Is custom code a solution?Thanks,Mike
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-03-21 : 10:36:54
|
| the screenshot doesn't seem to work.- Jeff |
 |
|
|
bassist695
Starting Member
9 Posts |
Posted - 2005-03-21 : 10:47:40
|
| Sorry about that...I'll have to explain it with words.I'm new to matrices so please bear with my lack of terminology.I have two columns, each with a different dollar amount (GWP and GC). The dataset returns data for two years selected by the user, so the layout looks like this (run-time): OBU GWP GCBroker Year1 SUM(GWP.Value) SUM(GC.Value) Year2 SUM(GWP.Value) SUM(GC.Value)I need a third row under Year2 which contains the percentage change between the years (for each GWP and GC), so the expression would be something like:=(Last(Fields!GWP.Value) - First(Fields!GWP.Value))/First(Fields!GWP.Value)If I "Add Row" by right-clicking on one of the detail cells, it ends up adding a column between the year cell and the GWP column, and the calculations are all wacky.I cannot figure out how to configure the matrix to add another row under the year. Should I try editing my dataset to force a percentage calculation performed in SQL to appear in the matrix without altering the matrix itself?I've been struggling with this for awhile, and am wondering if it's even feasible with this RS release. I've dabbled in custom code, but I don't even think that can solve this.Thanks,Mike |
|
|
|
|
|
|
|
|
% Calculation in Matrix