Matrix - Add Difference Column For each row

[Rich Ulichny]

Using Reporting Services 2005, I have a matrix, For example:

_____________________ 2007 ______________ 2008___________
ITEM NAME________SUM(PRICE)________SUM(PRICE)________

I would like to add an additional column to calculate the difference
in the 2 columns.

_________________ 2007 ____________ 2008___________Diff
Sample 1________ $1000 ________$1500_________ + 500
Sample 2________ $1000 ________$2000_________ + 1000

Seems simple enough but I can't seem to figure it out. Does anyone know how
to do this
or can point me to a resource/tutorial that would help. thanks.

[Antoon]

Different possibilities.
(1) You can do the math in the sql when retrieving the data
(2) you can enter an expression fr ReportItems!Textbox2.Value -
ReportItems!Textbox1.Value (textbox 1 and 2 being the two columns of the
table), that will work with a table but not with a matrix
(3) In a matrix you can use custom code to create a function that, for the
first two columns will return the value of the cell and for the third column
the difference. (the table cells are created left-to-right, top-to-bottom).
You will still have to add a column header in you sql to get a culumn in you
matrix.

The third option isn't pretty, but it can come in handy for some very
complex stuff. For a simple sum I would either (a) keep the matrix and solve
it in the datsource or (b) use a table

hope this helps

[Rich Ulichny]

Thanks for your suggestions. I think I simplified my problem too much so I'm
not sure if options A or B would work. The two columns I am getting are
actually each coming from different tables that I was grouping within the
report itself using the Matrix (sorry if I'm using wrong terminology but I
am brand new to Reporting Services)

Rows being returned via a SQL union are as follows:

Sample1 2007 1000
Sample1 2008 1500
Sample2 2007 1000
Sample2 2008 2000

I can't seem to figure out how to do the calculation via SQL since my result
set is actually a union from a bunch of different tables. One result has the
2007 data (row) while the other gets the 2008 data (row) from an entirely
different set of tables. Option C sounds interesting but since I lack RS
experience it's pretty daunting.

Rich

"Antoon" <Ant...@discussions.microsoft.com> wrote in message
news:0E3602FB-D4F6-43ED...@microsoft.com...

[Antoon]

If you know you will only have two columns (year1 and year2) then I would use
a table instead of matrix and go for option 2. That is easy and efficient.

Solution (1):

I understand your sql is somthing like
Select name, year, value from ...
union
Select ... from ...
lets call this <your sql>

the sql would the become

(<your sql>)
union
(
Select b.name, 0 , b.value - a.value
from
( <your sql> ) a,
( <your sql> ) b
where
a.name = b.name and
a.year = b.year-1
)

This is not very efficient, you could improve by using a temporary table to
store <your sql> in.

[buddhagautama]

I have a similar issue. Here is the situation
Data is taken from a cube and queried by MDX.
There is the Ser name (customer), weight and time.
I need to create a report with the following rows and columns
Rows- Ser Name
Columns-weight (2006),weight(2007).
I need to add a another column for difference between weight (2006) and weight(2007) and another two columns as well for % and rank.

SerName weight(2006) weight(2007)Difference % Rank
a 1000 2000 1000
b 200 500 300

I tried to create report in the table format, but I couldn't give this lay out after filtering by year.
I was able to give this layout with the matrix, but I cannot add a column in the matrix for the difference.
I know there has to be some way to do this with the MDX query. I tried this query:
SELECT
{ [Time].[Fiscal Year].&[2007] ,
[Time].[Fiscal Year].&[2008] }
ON COLUMNS,
[Customer].[Ser].MEMBERS ON ROWS
FROM [cube]
WHERE ([Measures].[weight])
Here is the error I received:”Query preparation failed. Additional information: Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)."
Tried the following suggestion without any positive result

In order to write a drill through query, choose to manually enter a DMX query.

1. Start with a blank dataset query against a Microsoft SQL Server Analysis Services type source
2. Use the dataset toolbar to change from "MDX" to a "DMX" type query
3. Use the dataset toolbar to switch "Design Mode" so it is possible to enter MDX manually

The query should now run without the parser complaining.

I appreciate any suggestions