Crosstab Rows - value from data

[dougp]

Using Cognos Analytics 11.7.1IF1021

In the past I have added a label in a row label using the result of a data item.  Today, I'm having trouble.

View tabular data returns the expected results.
When I run the report, I get all of the correct measures, but the label for This Region Value shows as "--".

What did I do wrong?


I have developed a contrived example using the Samples data.  Since I can't upload attachments to this site (Cannot access attachments upload path!):


Sample report spec:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8f3167857422503644e1c1b3fb6dfaac - (There's a hack if I've ever seen one.)
https://pastebin.com/Lcr3AtSy


If that doesn't work, here is the process to create the report:

Create report using GO Sales (query) package.
Create Query1 and add
    Sales.Time.Year
    Sales.Branch.Country
    Region =

Code Select Expand

case
when [Sales (query)].[Branch].[Country] in ('Canada', 'Mexico', 'United States')
then 'North America'
when [Sales (query)].[Branch].[Country] in ('Brazil')
then 'South America'
else 'Other'
end
    This Region =

Code Select Expand

case
when ?country? in ('Canada', 'Mexico', 'United States')
then 'North America'
when ?country? in ('Brazil')
then 'South America'
end
    Sales.Sales.Revenue
Create Query2 from Query1
    Year
    This Country Value =

Code Select Expand

case
when [Query1].[Country] = ?country?
then [Query1].[Revenue]
else 0
end
    This Region
    Region Value =

Code Select Expand

case
when [Query1].[Region] = [Query1].[This Region]
then [Query1].[Revenue]
else 0
end
    All Americas Value =

Code Select Expand

case
when [Query1].[Region] <> 'Other'
then [Query1].[Revenue]
else 0
end
    Global Value = Revenue
Add a crosstab to the page and make it use Query2
Drag Year to the Crosstab columns.
Select (CTRL+click) This Country Value, This Region Value, All Americas Value and Global Value and drag them to the Crosstab rows.
Change the Text Source properties for the crosstab rows:
     Global Value:  Data item label
     All Americas Value:  Data item label
     This Region Value:  Data item value for This Region
     This Country Value:  Report expression = ParamDisplayValue('country')

[MFGF]

Using Cognos Analytics 11.7.1IF1021

In the past I have added a label in a row label using the result of a data item.  Today, I'm having trouble.

View tabular data returns the expected results.
When I run the report, I get all of the correct measures, but the label for This Region Value shows as "--".

What did I do wrong?


I have developed a contrived example using the Samples data.  Since I can't upload attachments to this site (Cannot access attachments upload path!):


Sample report spec:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=8f3167857422503644e1c1b3fb6dfaac - (There's a hack if I've ever seen one.)
https://pastebin.com/Lcr3AtSy


If that doesn't work, here is the process to create the report:

Create report using GO Sales (query) package.
Create Query1 and add
    Sales.Time.Year
    Sales.Branch.Country
    Region =

Code Select Expand

case
when [Sales (query)].[Branch].[Country] in ('Canada', 'Mexico', 'United States')
then 'North America'
when [Sales (query)].[Branch].[Country] in ('Brazil')
then 'South America'
else 'Other'
end
    This Region =

Code Select Expand

case
when ?country? in ('Canada', 'Mexico', 'United States')
then 'North America'
when ?country? in ('Brazil')
then 'South America'
end
    Sales.Sales.Revenue
Create Query2 from Query1
    Year
    This Country Value =

Code Select Expand

case
when [Query1].[Country] = ?country?
then [Query1].[Revenue]
else 0
end
    This Region
    Region Value =

Code Select Expand

case
when [Query1].[Region] = [Query1].[This Region]
then [Query1].[Revenue]
else 0
end
    All Americas Value =

Code Select Expand

case
when [Query1].[Region] <> 'Other'
then [Query1].[Revenue]
else 0
end
    Global Value = Revenue
Add a crosstab to the page and make it use Query2
Drag Year to the Crosstab columns.
Select (CTRL+click) This Country Value, This Region Value, All Americas Value and Global Value and drag them to the Crosstab rows.
Change the Text Source properties for the crosstab rows:
     Global Value:  Data item label
     All Americas Value:  Data item label
     This Region Value:  Data item value for This Region
     This Country Value:  Report expression = ParamDisplayValue('country')

I can confirm I see this same result in 11.1.7. It's bizarre! I added a calculated item called MFRegion to Query2 with the expression if (1=1) then ('True') else ('False') and tried using this as the data item value for the row header for Region Value, and I still get the same result. This rules out any issues with bringing over a calculated item from another query. As far as I can see, it should work. Very intriguing!

Cheers!

MF.

[MFGF]

Hmmm. I think I have a workaround, but not sure if it will work for you, Doug.

Set the Text Source property for the Region Value row to be Report Expression, and set the expression to be:

case
  when ParamDisplayValue('country') in ('Canada', 'Mexico', 'United States') then 'North America'
  when ParamDisplayValue('country') in ('Brazil') then 'South America'
  else 'Elsewhere'
end

Cheers!

MF.

[dougp]

That's what I did, but it seems like a hack.  I remember this working properly in 11.0.13.

[MFGF]

That's what I did, but it seems like a hack.  I remember this working properly in 11.0.13.

I'd like to test this in 11.2. I'll see if I can get access to an instance.

Cheers!

MF.