If you are unable to create a new account, please email support@bspsoftware.com

 

pick values from different columns into new column depending on row/rowdataitem

Started by colt, 28 May 2010 07:05:07 AM

Previous topic - Next topic

colt

Hi,
I am trying to build a crosstab-report based on cube.
For each row, I have specified a dataitem as set expression including only 1 member.
As columns I have Period and below different measures. Now I want to include a column, that
picks value from one of the existing columns depending on dataitem/rownumber.

                     Period1
              M1      M2    M3         NEW Column
   
ROW1      1M1    1M2   1M3         1M1
ROW2      2M1    2M2   2M3         2M1
ROW3      3M1    3M2   3M3         3M2
ROW4      4M1    4M2   4M3         4M3
ROW5      5M1    5M2   5M3         5M3

Can anybody help me to create the NEW column ?
I know, I can achieve this by adding all 3 rows again and hiding the values with BoxType= None, but I am looking for a different solution.

MFGF

Your question is a bit vague, unfortunately.  Can you be a bit more precise on exactly how this should work?

MF.
Meep!

colt

Dear MFGF,

I want to get a crosstab that looks like:


                            Period1
item1=Customer1     Debitvalue
item2=Customer2     Debitvalue
item3=Customer2     Creditvalue
item4=Customer2     Debitvalue-Creditvalue
item5=Customer3     Debitvalue-Creditvalue

therefore the first idea was to have 3 columns under Period1:  1 for measure Debitvalue, 1 for measure Creditvalue and 1 for Debitvalue-Creditvalue.
Then I could hide the unwanted cells in each column and finally get 1 column with the values I want.
But the hiding of cells causes troubles when I try to import the crosstab to Powerpoint (by GoOffice).
Therefore I thought, if there is a way to directly define a conditional measure depending on rownumber, or row-dataitemname.
I thought of some kind of
measure= Case when rownumber=1 then Debitvalue
                     when rownumber=2 then Debitvalue
                     when rownumber=3 then Creditvalue
        .....


Alternative   Case when dataitemname=item1 then Debitvalue .....


Regards
Colt