Wednesday, March 7, 2012

Matrix - Divide one column by another

Hello,
Is there a way to divide a "dynamic column" by another "dynamic column"
?
Example:
Assume matrix has 2 columns column1 and column2. I want a third dynamic
column called column3 based on an expression column2/column1
Matrix will look like
colmn1 | column2 | column3 (value is column2/column1)
----
10 | 5 | 2
----
15 | 5 | 3
----
20 | 10 | 2
----
10 | 100 | 0.1
----
Is this possible? Any help wlll be greatly appreciated.
Thanks,
BRThere are a couple alternatives for tables:
If you have Col1 and Col2 coming in from your data set, you can create a
virtual field by right clicking in dataset field list and adding your own
calculated field. Then your table would only need to drop the new field into
Col3.
The alternative is to create a function in Col3 as
= Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
If I am making the wrong assumption on what you are doing, please explain
further.
"Ray" wrote:
> Hello,
> Is there a way to divide a "dynamic column" by another "dynamic column"
> ?
> Example:
> Assume matrix has 2 columns column1 and column2. I want a third dynamic
> column called column3 based on an expression column2/column1
> Matrix will look like
> colmn1 | column2 | column3 (value is column2/column1)
> ----
> 10 | 5 | 2
> ----
> 15 | 5 | 3
> ----
> 20 | 10 | 2
> ----
> 10 | 100 | 0.1
> ----
> Is this possible? Any help wlll be greatly appreciated.
> Thanks,
> BR
>|||First, Thanks for the reply.
I cannot do a calculated field as the col1 and col2 are coming from the
same Table column. It is the column grouping in the Matrix (not a
table) .
In the new column I have to do
col2.value in scope of the current row / col1.value in scope of the
current row
Does this make sense?
William wrote:
> There are a couple alternatives for tables:
> If you have Col1 and Col2 coming in from your data set, you can create a
> virtual field by right clicking in dataset field list and adding your own
> calculated field. Then your table would only need to drop the new field into
> Col3.
> The alternative is to create a function in Col3 as
> = Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
> If I am making the wrong assumption on what you are doing, please explain
> further.
> "Ray" wrote:
> > Hello,
> >
> > Is there a way to divide a "dynamic column" by another "dynamic column"
> > ?
> >
> > Example:
> >
> > Assume matrix has 2 columns column1 and column2. I want a third dynamic
> > column called column3 based on an expression column2/column1
> >
> > Matrix will look like
> >
> > colmn1 | column2 | column3 (value is column2/column1)
> > ----
> > 10 | 5 | 2
> > ----
> > 15 | 5 | 3
> > ----
> > 20 | 10 | 2
> > ----
> > 10 | 100 | 0.1
> > ----
> > Is this possible? Any help wlll be greatly appreciated.
> >
> > Thanks,
> > BR
> >
> >|||I have the same exact problem. Actually I needed to add several
columns.
To clarify, I have a matrix, and I have a subtotal. But in addition to
the subtotal I need to have "percentage of subtotal" columns for some
of my "important" data columns. And the lame matrix object does not let
me do it.
I ended up doing the following. Added N=no. of required columns to my
matrix "data region". Put the formulae for each of N of these columns
(since 1 column is for the subtotal). In my case N=3 (not including the
subtotal column) So I ended up with "garbage" of 3 extra data columns
for EACH of my original data columns. I then went and used "Inscope" to
"hide" the values in these 3 junk columns.
So now I have my report EXCEPT I have 3 blank columns for each of my
data columns followed by my subtotal and 3 computed percentage columns.
"CanShrink" blah blah properties will not get rid of the empty columns.
And "Hide" does not get rid of the columns only of the textboxes within
leaving the blank space.
Told my business user to deal with it or find someone else to do his
report. But if someone has a solution let me know. I'm not the only
person having issues with the matrix columns. In their infinite wisdom,
microsoftees have left critical practical considerations out of the
table and matrix implementations. It's either ALL static or ALL
dynamic. Which is okay for "hello world" reports. Welcome to reality
folks.
Ray wrote:
> First, Thanks for the reply.
> I cannot do a calculated field as the col1 and col2 are coming from the
> same Table column. It is the column grouping in the Matrix (not a
> table) .
> In the new column I have to do
> col2.value in scope of the current row / col1.value in scope of the
> current row
> Does this make sense?
>
> William wrote:
> > There are a couple alternatives for tables:
> >
> > If you have Col1 and Col2 coming in from your data set, you can create a
> > virtual field by right clicking in dataset field list and adding your own
> > calculated field. Then your table would only need to drop the new field into
> > Col3.
> >
> > The alternative is to create a function in Col3 as
> > = Fields!clm_fin_curr_loss_reserve.Value/ Fields!clm_no.Value
> >
> > If I am making the wrong assumption on what you are doing, please explain
> > further.
> >
> > "Ray" wrote:
> >
> > > Hello,
> > >
> > > Is there a way to divide a "dynamic column" by another "dynamic column"
> > > ?
> > >
> > > Example:
> > >
> > > Assume matrix has 2 columns column1 and column2. I want a third dynamic
> > > column called column3 based on an expression column2/column1
> > >
> > > Matrix will look like
> > >
> > > colmn1 | column2 | column3 (value is column2/column1)
> > > ----
> > > 10 | 5 | 2
> > > ----
> > > 15 | 5 | 3
> > > ----
> > > 20 | 10 | 2
> > > ----
> > > 10 | 100 | 0.1
> > > ----
> > > Is this possible? Any help wlll be greatly appreciated.
> > >
> > > Thanks,
> > > BR
> > >
> > >

No comments:

Post a Comment