Crosstab calculated member column deleted on refresh

[Former Member]

Hello,

I have a CR 2011 crosstab report, and created 2 calculated columns for year to year change and % change.  The columns were fine until I refreshed the data to preview the report with a new date range - then they were gone.  I created them again, and saved the report.  I opened the report, previewed, and gone again! 

Also, for this same report, I wish to show a "%" sign in the % Change column.  If I change the calculated cell value formula to ToText(value) & "%", I get an error that the formula must be a number. 

Also, if I try to format the field in the % Change column to remove the currency format, then the other cells take on that format too, and I lose the currency format for the dollar valuation columns.

A snapshot is attached.

Thank you!

[abhilash_kumar · Accepted solution]

Hi Toni,

--- Part 1----

The position of the Calculated Member or let's say the column where the Calculated Member appears, depends on the "Insertion Formula".

When you add a Calculated Member after the last column, Crystal Reports creates an "Insertion Formula" for you. To look at the formula, right-click the Header of the Calculated Member > Select Calculated Member > Edit Insertion Formula.

If you look at the formula, you'll observe that CR creates a Static formula by referencing the value of the previous header. In your case since the columns are Date fields, a part of that formula might read:

GridRowColumnValue("Database_Field") = 2012

or something similar.

The Calculated Member will appear as long as the report contains the 2012 column.

So, instead of referencing the value of the previous cell, it is best to insert the column based on a column index. Suppose your report always contains 3 columns and you wish to insert the Calculated Member towards the end, you can modify the Insertion Formula with this code:

CurrentColumnIndex = 3

--- Part 2 ---

If you select one of the cells in the Crosstab, you'll see that all the cells including the cells of the newly added columns are also selected. The point I'm trying to make here is that Calculated Members are not separate entities, they're a part of the Crosstab.

If the other cells are numbers, then the Calculation Formula of the Calculated Members should also return numbers. So, the error that you get is obvious.

Similarly, if you change the formatting (like adding a % sign or removing decimals), then this applies to all cells in the Crosstab.

To work around this, you would need to create a "Display String" formula. The Display string returns String, so this will convert all the cells to string; that's the only drawback.

Right-click one of the cells (The Summary field) and Select Format Field > Common tab > Select the formula button beside Display String and use this code:

if currentcolumnindex = 3 then

     totext(currentfieldvalue)&"%"

else

     totext(currentfieldvalue)

I can see from the screenshot that there are multiple summaries. I'm not sure which of these summaries would you like to show the % sign, but let me know how this goes for a start.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Thank you, I indeed found and edited the insertion forumula. 

The second part - I could not use the ToText function becasue the content of the summary cell is a number.  I was able to use the Display string and the % as the currency indicator to display the data as needed.

Thanks again.

[abhilash_kumar · Accepted solution]

Glad I could help!

Take care.

-Abhilash