Skip to Content
0

Format first record of each group in a crosstab

Feb 27, 2017 at 07:23 AM

191

avatar image

I've created a crosstab (& it was placed in the Group Header area) using CR2008. I'm trying to format the first line of each group to Currency. I can't seem to get it to work using RecordNumber > 1. The result of RecordNumber >1 yields all lines in the first group of my crosstab to be formatted to Currency. Any help would be greatly appreciated. Thank you.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

10 Answers

Best Answer
Abhilash Kumar
Mar 03, 2017 at 07:56 AM
0

OK, I realize why this is happening:

1) Right-click one of the values in a summary cell > Format Field > Number tab > Click the Customize button > Currency Symbol tab > Check 'Enable Currency Symbol and click the formula button beside it.

2. Use this code:

if CurrentRowIndex > 0 AND GridRowColumnValue("GLCONSOL.FISCAL_YEAR") <> 3 then
crNoCurrencySymbol
else
crFloatingCurrencySymbol

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Feb 28, 2017 at 08:37 AM
0

Hi Fione,

Could you attach a screenshot of the crosstab with the expected result please?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Fione Tang Feb 28, 2017 at 09:29 PM
0

crosstab-balance-sheet-v1.jpg

Attached is a sample of what my crosstab currently looks like. I'd like to have only the highlighted rows to show Currency symbols (meaning the 1st row of each group and the associated group totals).

My crosstab is grouped by Account_ID and placed in the Group Header section.

I'd appreciate any help to resolve this issue. Thank you.



Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 01, 2017 at 07:49 AM
0

Hi Fione,

Here's what you need to do:

1) Right-click one of the values in a summary cell > Format Field > Number tab > Click the Customize button > Currency Symbol tab > Check 'Enable Currency Symbol and click the formula button beside it.

2. Use this code:

if CurrentRowIndex > 0 then
crNoCurrencySymbol
else
crFloatingCurrencySymbol

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Fione Tang Mar 01, 2017 at 09:44 PM
0

It worked:) Thank you so much for your help. I knew I can count on your expertise.

For the Variance% column in previous my attachment , is it possible to format every row in the entire group to show the % sign? The other 3 columns were perfect after inputting the codes you provided.

Also, I'm trying to insert a new row below the first 2 groups of data for in my report to give me a subtotal, but I ended up having a new row before my 2nd group total instead of after. Any idea how to go about fixing it? Thank you.

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 02, 2017 at 08:44 AM
0

Hi Fione,

1) Is Variance% a Calculated Column? If yes, right-click the Calculated Column Header > Calculated Member > Edit ColumnValue Formula.

The commented text on the top should show whether this formula should return a Date, DateTime, String or Number.

If it says Date use this code:

cdate(1890,01,01)

If it says String, use:

'Variance%'

If it says Number, use:

9999

2) Right-click one of the Values in the summary cells > Format Field > Number tab > Customize > Currency Symbol tab > Click the formula button beside 'Currency Symbol':

Use this code if the Datatype in Step 1 is Date:

If Year(GridRowColumnValue("Field_used_as_crosstab_column")) = 1890 then"%" else "$"

If the Datatype in Step 1 is String, use:

If GridRowColumnValue("Field_used_as_crosstab_column") = 'Variance%' then"%" else "$"

If the Datatype in Step 1 is Number, use:

If GridRowColumnValue("Field_used_as_crosstab_column") = 9999 then"%" else "$"

Note: 'Field_used_as_crosstab_column' with the field name you've used as the Column in the Crosstab. The double-quotes ARE required and you should remove any curly braces that CR adds automatically.

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Fione Tang Mar 02, 2017 at 04:50 PM
0

It doesn't seem to be working this time, please see below:

I'm still getting only the 1st row of each group of the Variance% above to display the % symbol.

I gave the Variance% column a column value of 3 since my data type is integer & input the following formula in the Currency Symbol x2 formula box:

If GridRowColumnValue("GLCONSOL.FISCAL_YEAR") = 3 Then
"%"
Else
"$"

Is there something wrong? Please advice. Thank you.


mvtyf.png (59.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
Fione Tang Mar 03, 2017 at 04:28 PM
0

Wonderful! Works like a charm. Thanks a million for your help. Now my report looks much better.

I'm encountering another problem while continuing to work on the same report. I need to insert a grand total by adding the first 3 group's subtotals (all short-term investments groups) together and place this grand total right under the 3rd group's subtotal. How do I go about referencing the subtotals that are automatically generated by the crosstab which has no GridRowColumnValue? I ended up having the grand total before the 3rd group's subtotal instead. Please advice. Thanks.

Please see sample below:


eldmm.png (45.8 kB)
Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 06, 2017 at 08:09 AM
0

Hi Fione,

Could you please create a separate discussion for this question?

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded
Fione Tang Mar 06, 2017 at 10:31 PM
0

Sure will. Thanks.

Share
10 |10000 characters needed characters left characters exceeded