If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Combining Numeric Values as Text in Crosstab

Started by TraderRef, 06 Dec 2023 12:59:53 PM

Previous topic - Next topic

TraderRef

We have a requirement to place a text field in the crosstab cells based on the expression at the end of this which looks something like:

12,345 (67.89%)

Where:
12,345 is the number of error records found
67.89% is the percentage of error records (12,345) divided by the total records (18,184)

Is it possible to do this? We are using Redshift for the reporting database. I created a column named Errors and Percentages based on the following expression and another column named Aggregate Errors with aggregate:[Errors and Percentages] as the expression and added that to the crosstab, but that does not seem to work.

CASE

WHEN [Details].[Error Count] / [Details].[Detail Record Count] = 1
THEN [Details].[Error Count] + ' (100.00%)'

WHEN [Details].[Error Count] / [Details].[Detail Record Count] >= .10 AND [Details].[Error Count] / [Details].[Detail Record Count] < 1
THEN [Details].[Error Count] + ' (' + substring (cast ([Details].[Error Count] / [Details].[Detail Record Count], varchar(22)), 1, 5) + '%)'

WHEN [Details].[Error Count] / [Details].[Detail Record Count] < .10
THEN [Details].[Error Count] + ' (' + substring (cast ([Details].[Error Count] / [Details].[Detail Record Count], varchar(22)), 1, 4) + '%)'

END

I chose to do it this way because the Cognos SuperStar, CognosPaul, had suggested in https://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/#comment-59474 to prefix expressions with aggregate: beginning with Cognos 10.2.1 FP3.