Add database field after summarized column in Cross Tab Crystal reports

[former_member560465]

Hi!

How do i place the Balance column after the 3 - March column?

My desired output is:

Thanks!

Ritchie

[abhilash_kumar · Accepted solution]

Hi Ritchie,

I assume "Balance" is a precalculated field already measured at the "CardCode" level?

And I believe this is a Crosstab?

Here's what you need to do:

1. Create a formula with this code:

{CarCode} & ">" & {Balance}

2. Go to the Crosstab Expert and replace the existing row field with this formula field.

3. Right-click the "3-March" header cell > Calculated Member > Insert Column. A blank column is inserted to the end of the Crosstab.

4. Right-click the blank header cell > Calculated Member > Edit Insertion Formula > use this code and save the formula:

GetColumnGroupIndexOf(CurrentColumnIndex) = 0

5. Right-click the blank header cell > Calculated Member > Edit Header Formula > use this code and save the formula:

"Balance"

6. Right-click one of the blank summary cells in this new column > Calculated member > Edit Calculation Formula > use this code:

tonumber(split(GridLabelAt("@Formulafield", CurrentRowIndex),">")[2])

Note: Replace "Formulafield" above with the name of the formula you created in Step 1. It needs to be enclosed in double quotes exactly like above.

7. Right-click one of the cells in the Row field > Format Field > Common tab > Click the formula button beside "Display String" and use this code:

split(currentfieldvalue, ">")[1]

-Abhilash

[former_member560465 · Accepted solution]

Hi Abhilash!

Thank you for your response and it worked! 🙂

Yes "Balance" is a precalculated field as a result of a query, and yes it is a crosstab.

Only i skipped the number 4 step "GetColumnGroupIndexOf(CurrentColumnIndex)=0", because the

Balance field appeared in the left(before 1 - January) instead of after 3 - March.

Thanks a lot!

Ritchie

[abhilash_kumar · Accepted solution]

Hi Ritchie,

If you skip step number 4, the Calculated Column will not be added when future months are pulled in or if the report does not have data for "3-March".

At the moment, without that formula, the report will be hardcoded to always insert a column after "3-March"!

If you need the Column at the end without worrying about future months, then here's what you need to do:

1. Follow Step 4 and add the Insertion Formula back in.

2. Right-click the blank white space to the top left of the crosstab > Advanced Calculations > Calculated Member.

3. Under "Properties" to the right, look for an option called "Insert Evaluation". Select "After" for this label.

-Abhilash

[former_member560465 · Accepted solution]

Got it. Thanks again!