sql server reporting services: How to create computed columns(dynamic) in matrix -- sql server reporting services

Re: How to create computed columns(dynamic) in matrix

venkat.oar NO[at]SPAM gmail.com
7/12/2006 11:47:03 AM

Seems like this is not possible with reporting services!!...

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

Amarnath
7/12/2006 11:09:01 PM

Yes when you select datasets from view menu and right click on the datasets
window you can see a add option. click and you can add calculated fields.

Amarnath

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

venkat.oar NO[at]SPAM gmail.com
7/13/2006 12:50:58 PM

Sorry.. i think u got me wrong here. I am able to add calculated fields
but the data what i have is part of 2 rows.
here's a brief description for you.

I am using matrix control for which the rows, columns are all dynamic(
based on the group i specify). I am able to get the Subtotal for both
the rows and colums, which is Great!!..

my problem is i need to add more columns(some formula columns) which
might have "% difference", "varience" etc..

the report should look something like this (M1, M2 are model_id's)

Div/Sec M1 M2 Variance % Difference
---------------------------------------------------------------------------=
=AD--------

O3580 0.71 1.47 -0.76 -107.04
8040 1.33 1.33 0 0
8110 9.98 14.47 -4.49 -44.99
11210 5.44 6.36 -0.92 -16.91

and my query is :

** *** READ BELOW QUERY..
select div_sec, model_ID, model_time from table1
the output of my query is below( seperated by commas)
div_sec, model_ID, model_time
O3580,M1,0.7
O3580,M2,1.47
8040 ,M1,1.33
8040 ,M2,1.33
8110 ,M1,9.98
8110 ,M2,14.47
11210 ,M1,5.44
11210 ,M2,6.36

Any help is greatly appreciated.

Thanks,
Venkat

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

Michael Cervantes
7/13/2006 5:51:58 PM

Is there a way in this scenario to use the ReportItems!<textbox>.Value
syntax? Or, does it fall apart because it's a matrix?

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

venkat.oar NO[at]SPAM gmail.com
7/14/2006 6:13:28 AM

Mike,

I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).

Still no clue how to do this or is it anyway possible?.

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

Amarnath
7/14/2006 6:55:03 AM

Hi try getting all the required data from query, here is the query for that.
see if it works for you.. I think you can put this in table object in SSRS as
well.

select div_sec,
sum((case (model_ID) when 'M1' then (model_time) end)) M1,
sum((case (model_ID) when 'M2' then (model_time) end)) M2,
sum((case (model_ID) when 'M1' then (model_time) end)) - sum((case
(model_ID) when 'M2' then (model_time) end)) Variance
from #temp
group by div_sec
order by 1

You can replace #temp with your table and you can add more columns as well.
This is basically converting all vertical data to horizontal ie pivoting.

Amarnath

[quoted text, click to view]

Re: How to create computed columns(dynamic) in matrix

Pete D
7/14/2006 3:14:15 PM

Hi Venkat, my problem (as posted on 5th July, "Calculations in Matrix
report") is almost identical, so I'll let you know if a solution/workaround
can be found.

Pete

[quoted text, click to view]
Mike,

I can't even go that far to use ReportItems!<txtbox>.value, the reason
is when i add column to the matrix, the columns appear for each column
it is created dynamically( in my case the columns appear for each
model_ID).

Still no clue how to do this or is it anyway possible?.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.