Calculated columns in crosstab in CR 2008

[Former Member]

Hello all,

I need to add calculated columns in this exisiting crosstab report:

What I need is to have two difference columns inserted after each year that would show diff (value and percentage) in comparison to the precedent one - except for the very first year (example in indicated columns for years 2010 and 2009)

I have been trying to insert calculated columns, but I cannot find the right formulas for those.

Attached is the exported view of the current crosstab state (without calculated columns) in .txt format.

Many thanks for any hints, much appreciated.

crosstabDataExport.txt.zip

[Former Member · Accepted solution]

Hi

Please try the below:

1.1. right click on column 2010 --> calculated member --> insert column

1.2. right click on column 2010 --> calculated member --> edit -->

paste the below logic:

gridvalueat(currentrowindex,currentcolumnindex-1, currentsummaryindex)-gridvalueat(currentrowindex,currentcolumnindex-2, currentsummaryindex)

2.1. right click on column inserted above --> calculated member --> insert column

2.2. right click on column inserted above --> calculated member --> edit -->

paste the below logic:

((gridvalueat(currentrowindex,currentcolumnindex-1, currentsummaryindex)-gridvalueat(currentrowindex,currentcolumnindex-2, currentsummaryindex))*100)/(gridvalueat(currentrowindex,currentcolumnindex-2, currentsummaryindex)

now

3.1. right click on column 2011 --> calculated member --> insert column

3.2. right click on column 2011 --> calculated member --> edit -->

and paste the below logic:

gridvalueat(currentrowindex,currentcolumnindex-1, currentsummaryindex)-gridvalueat(currentrowindex,currentcolumnindex-4, currentsummaryindex)

and

4.1. right click on column inserted above --> calculated member --> insert column

4.2. right click on column inserted above --> calculated member --> edit -->

paste the below logic:

((gridvalueat(currentrowindex,currentcolumnindex-1, currentsummaryindex)-gridvalueat(currentrowindex,currentcolumnindex-4, currentsummaryindex))

*100)/(gridvalueat(currentrowindex,currentcolumnindex-4, currentsummaryindex)

Now repeat step 3.1, 3.2 & 4.1, 4.2 for column next to 2012 and 2013 years

Hope this works.

Cheers,

Kiran

[Former Member · Accepted solution]

Hello Kiran,

many thanks for your prompt answer, really appreciated.

The problem I am having is that I can not just insert a column as you suggest -> I do not have the years listed in the report template: the year in question is a part of filter (e.g if wants to see year 2013 and last 4 years the report would display years 2009 to 2013, and if user wants e.g. year 2010 and last 4 years the report would display years 2006 to 2010 and so on).

Therefore my crosstab in the CR template looks like this:

So there is no possibility to add columns like you suggest - there is only one column and its context menu has an option "Advanced Calculation -> Calculated Member".

I added a new column (see attached screenshot) there but I don not know how to define (which formulas) need to be set in "Edit Insertion Formula", "Edit Column Value Formula", "Edit Header Formula" in "Calculated Member" section and in "Edit Value Formula" in "Value Formulas" section

Any other suggestions?

Many thanks again.

[abhilash_kumar · Accepted solution]

Hi Marin,

Here's what you need to do:

1) While in the Preview Mode, Right-click the second year column (2010 in your screenshot pasted initially) and select Calculated Member > Insert Column

2) A Blank column with zero values is inserted beside 2010

3) Now, right-click the blank header of this column and select Calculated Member > Edit Insertion formula and use this code:

GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and 
CurrentColumnIndex <> 0

You should see that blank rows are now inserted after each year except the first year

4) Right-click the blank column again and select Calculated Member > Edit Header formula and use this code:

"Difference"

5) Right-click the header and select Calculated Member > Edit ColumnValue formula and use this code:

cdate(1890,01,01)

If the field is a datetime field, use this:

cdatetime(1890,01,01,0,0,0)

6) Right-click the same header and select Calculated Member > Insert Column

You should now see a new blank column beside each 'Difference' column

7) Right-click the blank header of this new column and select Calculated Member > Edit Header formula and use this code: 

"Percentage"

😎 Right-click one of the zero values in the Difference column and select Calculated Member > Edit Calculation formula and use this code: 

if CurrentColumnIndex - 1 = 1 then
(
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex)-
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-2, CurrentSummaryIndex)
)
else
(
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex)-
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex)
)

9) Right-click one of the zero values in the Percentage Column and select Calculated Member > Edit Calculation formula and use this code: 

if CurrentColumnIndex - 2 = 1 then
(
    If GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex) = 0 then
    0
    else
    (GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex)/
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-3, CurrentSummaryIndex)) * 100
)
else
(
    If GridValueAt(CurrentRowIndex, CurrentColumnIndex-5, CurrentSummaryIndex) = 0 then
    0
    else
    (GridValueAt(CurrentRowIndex, CurrentColumnIndex-1, CurrentSummaryIndex)/
    GridValueAt(CurrentRowIndex, CurrentColumnIndex-5, CurrentSummaryIndex))*100
)

That's it! Let me know how this goes.

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

many thanks for your quick response.

As I already explained above, I can not add the columns as you describe, but I can add a calculated member and set the attributes in edit popup.

So now I have managed to display the difference value column, but the diffenrence percentage column is not showing yet:

Does it hae to do with the fact that insertion formula for percentage column is not specified (like it is for diff value column

• GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and  
• CurrentColumnIndex <> 0 

)?

Many thanks again, it seems that we're almost there 🙂

[abhilash_kumar · Accepted solution]

Not too sure what you mean by

As I already explained above, I can not add the columns as you describe, but I can add a calculated member and set the attributes in edit popup.

How did you add the 'Difference' column then? Didn't the steps I posted above for inserting a column work?

Moreover, you do not need an Insertion formula for the Percentage column. The reason being, we always want it to be after the "Difference" column. When you right-click the "Difference" column and click 'Insert a Column", CR automatically creates the insertion formula for you and always places the column next to the Difference column.

Sometimes, even though you insert Columns (also know as Calculated Members), they are not added to the Crosstab. This is still a mistery and I've faced this numerous times - users on this forum have also faced the issue with no sight of a resolution. The only workaround in such situations is to open the report in another copy of CR Designer on another machine and continue working there .

If you followed each step I posted above, you should see the Crosstab exactly as you wanted. I have a working copy of the report with the Crosstab with the Difference and Percentage Columns so I don't see why the steps shouldn't work for you.

If the issue is that you can't find the Percetage column although you've added it, then as I said, try opening the report on other machine. Or, if possible. just attach the report with saved data to this thread and I'll look at it.

To attach the report, change its extension from .rpt to .txt and click the 'use advanced editor' link in the reply window where you should find the 'Attach' button.

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

thanks for the reply.

When I mentioned that I can not add the column as you described I meant this:

"...

1) While in the Preview Mode, Right-click the second year column (2010 in your screenshot pasted initially) and select Calculated Member > Insert Column

..."

As I explained above in previous post, I do not have the columns with years in header in the template -> year is a part of filter and grouping criterion, therefore I have only one single column in the cross-tab in preview (left of "Total column).

Hence, I can not add the difference column by clicking in the header of column with year 2010 as it does not exist.

What I did is right click into the value cell of the first column (not in the header itself) -> there it is an option "Advanced Calculations -> Calculated Member".

I followed your instruction about adding the first additional column -> set insertion formula and edited value formula to the mentioned values, and now this works for the difference value column.

Then I added one more column (difference pct.)  in the same way and edited the value formula as you suggested:

-> but this new column will not display. This is where I thought that insertion formula might be missing... I am not sure if I am doing anything wrong?

I attached the report as .txt file (renamed suffix .rpt).

Thanks very much for looking into this, really appreciated.

Marin

crossTab.txt.zip

[abhilash_kumar · Accepted solution]

You don't see the column because CR doesn't know where to insert it.

If you read my initial post again, you've missed point 5. Since the field in the Column of your crosstab is a Number field, use this as the ColumnValue formula:

999999999

Then use this as the Insertion formula for the Percentage column:

GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and  

GridRowColumnValue("REV_YEAR") = 999999999

To access the ColumnValue formula go to Advanced Calculations > Calculated Member > Select Diff Val. > Click Edit ColumnValue Formula

Hope this helps.

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

many thanks for your quick response, apologies for missing point 5.

I now added the latest formulas as you suggested, but now an error is thrown:

"Error in formula code. Please contact Business Objects... Error in formula CustomMember_InsertCondition..."

I guess it must be something with insertion formula for Pct. column?
I attached the reworked .rst - could you chenck once more please?

Many thanks again,

Marin

crossTabReworked.txt.zip

[abhilash_kumar · Accepted solution]

Are you running this report in a Viewer?

Have you tried running it in the CR Designer?

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

yes, I am running the report in a viewer -> unfortunately I can not run it directly from the IDE.

This is because we have a custom "bridge" to CR engine from our main application -> we just use the .rst template and all other stuff (query, field definitions) is provided from an external file.

Anyway, I have managed to display the percent column eventually: instead of using the approach with insert formula referencing the column value of 99999999 (which would throw an error), I now used this as insertion formula for percent column:

GetColumnGroupIndexOf(CurrentColumnIndex) = 1 and   
    (CurrentColumnIndex = 2 OR 
       CurrentColumnIndex = 5 OR 
         CurrentColumnIndex = 8 OR 
           CurrentColumnIndex = 11 OR 
             CurrentColumnIndex = 13)

This seems to work although a bit of quick and dirty.

In any case thank you very much for attending to this and for pointing me in the right direction -> your answers were extremely helpful.

Best regards,

Marin

[abhilash_kumar · Accepted solution]

Glad I could help.

You could have an issue with the above insertion formula though - especially when the crosstab grows with respect to columns.

If you're very sure the crosstab will always have a minimum of 13 columns, then this is OK.

Have a great day!

-Abhilash