How to create computed columns(dynamic) in matrix

Hello,
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
----
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 :
select div_sec, model_ID, model_time from table1
Any help is greatly appreciated.
Thanks,
VenkatSeems like this is not possible with reporting services!!...
venkat.oar@.gmail.com wrote:
> Hello,
> 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
> ----
> 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 :
> select div_sec, model_ID, model_time from table1
> Any help is greatly appreciated.
> Thanks,
> Venkat|||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
"venkat.oar@.gmail.com" wrote:
> Seems like this is not possible with reporting services!!...
> venkat.oar@.gmail.com wrote:
> > Hello,
> >
> > 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
> > ----
> > 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 :
> >
> > select div_sec, model_ID, model_time from table1
> >
> > Any help is greatly appreciated.
> >
> > Thanks,
> > Venkat
>|||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
Amarnath wrote:
> Yes when you select datasets from view menu and right click on the datase=ts
> window you can see a add option. click and you can add calculated fields.
> Amarnath
> "venkat.oar@.gmail.com" wrote:
> > Seems like this is not possible with reporting services!!...
> >
> > venkat.oar@.gmail.com wrote:
> > > Hello,
> > >
> > > 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
> > > ---=--
> > > 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 :
> > >
> > > select div_sec, model_ID, model_time from table1
> > >
> > > Any help is greatly appreciated.
> > > > > > Thanks,
> > > Venkat
> > > >|||Is there a way in this scenario to use the ReportItems!<textbox>.Value
syntax? Or, does it fall apart because it's a matrix?
venkat.oar@.gmail.com wrote:
> 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
> ----­--
> 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
> Amarnath wrote:
>>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
>>"venkat.oar@.gmail.com" wrote:
>>
>>Seems like this is not possible with reporting services!!...
>>venkat.oar@.gmail.com wrote:
>>Hello,
>>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
>>----
>>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 :
>>select div_sec, model_ID, model_time from table1
>>Any help is greatly appreciated.
>>Thanks,
>>Venkat
>>
>|||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?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > 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
> >
> > Amarnath wrote:
> >
> >>Yes when you select datasets from view menu and right click on the data=sets
> >>window you can see a add option. click and you can add calculated field=s=2E
> >>
> >>Amarnath
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>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
> >>---=--
> >>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 :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >|||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
"venkat.oar@.gmail.com" wrote:
> 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?.
>
> Michael Cervantes wrote:
> > Is there a way in this scenario to use the ReportItems!<textbox>.Value
> > syntax? Or, does it fall apart because it's a matrix?
> >
> >
> >
> > venkat.oar@.gmail.com wrote:
> >
> > > 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
> > > ----­--
> > >
> > > 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
> > >
> > > Amarnath wrote:
> > >
> > >>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
> > >>
> > >>"venkat.oar@.gmail.com" wrote:
> > >>
> > >>
> > >>Seems like this is not possible with reporting services!!...
> > >>
> > >>venkat.oar@.gmail.com wrote:
> > >>
> > >>Hello,
> > >>
> > >>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
> > >>----
> > >>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 :
> > >>
> > >>select div_sec, model_ID, model_time from table1
> > >>
> > >>Any help is greatly appreciated.
> > >>
> > >>Thanks,
> > >>Venkat
> > >>
> > >>
> > >
>|||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
<venkat.oar@.gmail.com> wrote in message
news:1152882808.590984.275540@.i42g2000cwa.googlegroups.com...
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?.
Michael Cervantes wrote:
> Is there a way in this scenario to use the ReportItems!<textbox>.Value
> syntax? Or, does it fall apart because it's a matrix?
>
> venkat.oar@.gmail.com wrote:
> > 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
> > ----­--
> >
> > 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
> >
> > Amarnath wrote:
> >
> >>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
> >>
> >>"venkat.oar@.gmail.com" wrote:
> >>
> >>
> >>Seems like this is not possible with reporting services!!...
> >>
> >>venkat.oar@.gmail.com wrote:
> >>
> >>Hello,
> >>
> >>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
> >>----
> >>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 :
> >>
> >>select div_sec, model_ID, model_time from table1
> >>
> >>Any help is greatly appreciated.
> >>
> >>Thanks,
> >>Venkat
> >>
> >>
> >