Changing Display String of Total in CrossTab

[saurabh_pathak]

I have a subreport containing crosstab designed in CR2008

I need to change the display string of Total dynamically and is based on the value Row 2.

I am using the Formula to add the required string dynamically, however if doesn't work for all the values of Row 2.

Please suggest.

~ Saurabh

[former_member1093147]

Abhilash,

I'm attempting to change the the total by Account Group (i.e. Balance Sheet / Income Statement), and I already have this as a group in the Crosstab Report.

I've attempted to follow your instructions above, but I don't get the blank row showing up.

Cross Tab is set-up as follows:

Columns are Divisions

Row #1: B/S or I/S determined by a formula field

Row #2: Account# determined by a formula field (the report can be run on 3 different levels, and this formula field determines the value based on the parameter selection made)

Row #3: Account Description

Using Crystal Reports 2016 Support Pack 2 Version 14.2.2.1975

I'm fairly new to Crystal Reports, but have been getting by slowly. Any assistance you can provide on this, I'd appreciate.

Please let me know if you need any other information from me.

Thanks.

John

[abhilash_kumar]

Hi John,

Would you mind posting this as a new question, please?

-Abhilash

[abhilash_kumar · Accepted solution]

Hi Saurabh,

Do you have a 'Preview' mode screenshot?

What formula have you used?

Also, how do you want the Totals to look?

-Abhilash

[saurabh_pathak · Accepted solution]

if {SP_DM_DBS6;1.GROUP_ID} = 1 then

"Text A" else "Text B"

Total should like "Total Text A" if value is 1 otherwise "Total Text B".

~ Saurabh

[Former Member · Accepted solution]

Hi Saurabh,

Somehow, I dont see a value 1 or 2 for Row #2, have you changed the group display name.

I think, you need the sub-total label text of Row # 3 to show as per Row # 2's value, is that correct?

Have you checked the 'Edit Text' option in the right click menu of the label already?

Please test the result after you put the formula field in the 'Total' text box.

-Prathamesh

[saurabh_pathak · Accepted solution]

Hi Prathamesh,

I have already tried whatever u have suggested me, but it doesn't work.

~ Saurabh

[abhilash_kumar · Accepted solution]

Hi Sourabh,

I think I understand what you're trying to say. The Total (Grand Totals and Subtotals) fields don't have 'Display String' formulas. You need to add a dummy row for the Subtotals and Suppress the original Subtotals. Assuming you're using CR 2008 or higher, here's what you need to do:

1) While in the Preview Mode, right-click the 2nd field that you have placed in the Row of the Crosstab. From the screenshot above it should be the cell that shows the value "(a)"

2) Select Calculated Member > Insert Child. You should see a blank row above the "Total" for the (a) row.

3) Right-click the blank cell exactly above the "Total" cell and select Calculated Member > Edit Insertion Formula:

Remove everything after and including the 'and' operator. It should just be:

GetRowGroupIndexOf(CurrentRowIndex) = 2

4) You should now see a blank row above each "Total" row in each Group

5) Right-click the blank cell exactly above the "Total" cell once again and select Calculated Member > Edit header formula and use this code:

"Total for "& GridLabelAt("database_field", CurrentRowIndex)

Replace database_field in the above field code with the field name that is used as the 2nd row in the Crosstab. Make sure the double quotes are there and remove any brackets such as { or } that CR automatically places for database fields.

6) You should now see that each row above the original subtotal shows the text you wanted with name of the 2nd group

7) Next, right-click one of the blank cells beside this header cell and select Calculated Member > Edit Calculation formula and use this code:

GetTotalValueFor("database_field")

Replace database_field in the above code with the field name that is used as the 3rd row in the Crosstab this time.

😎 Right-click the blank space at the top left of the Crosstab and select Crosstab Expert > Customize Style > Select the 2nd row in the Crosstab and check 'Suppress Subtotal'

That's it! I've also attached a sample report for your reference. Extract and change its extension to .rpt.

Let me know if this is how you wanted the Crosstab to look like.

-Abhilash

Crosstab_Sourabh.txt.zip

[former_member949984 · Accepted solution]

Hi Abhilash,

I want to use a function instead of database field in below snippet.

"Total for "& GridLabelAt("database_field", CurrentRowIndex)

When I am trying to do so its not giving me any error but also not showing any output. Below is the code snippet, in which accountable is the function

"Total for "& GridLabelAt("@accountable", CurrentRowIndex).

It is not even typing Total for. Can you please assist how to use a function here?

[Former Member · Accepted solution]

I saw your posted answer. I recommend raising your question again as a new question since this question is from 2013. Also please check out our rules of engagement: https://community.sap.com/resources/rules-of-engagement. Feel free to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. With these tips you'll be able to prepare questions that draw responses from our members.

Thanks!