1

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 "--". I want it to show North America or South America, depending on which country is selected on the prompt page.

What did I do wrong?

I have developed a contrived example using the Samples data. Here is the report spec and the steps I used to create it.

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

Process:

Create report using GO Sales (query) package.
Create Query1 and add

  • Sales.Time.Year
  • Sales.Branch.Country
  • Region =
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 =
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 =
case
    when [Query1].[Country] = ?country?
        then [Query1].[Revenue]
    else 0
end
  • This Region
  • Region Value =
case
    when [Query1].[Region] = [Query1].[This Region]
        then [Query1].[Revenue]
    else 0
end
  • All Americas Value =
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')
dougp
  • 2,810
  • 1
  • 8
  • 31
  • Want to see if you are getting a NULL 1) Try changing the value to show zero when NULL 2) Try... For this region, try with an else statement case when ?country? in ('Canada', 'Mexico', 'United States') then 'North America' when ?country? in ('Brazil') then 'South America' else('Other') end – VAI Jason Jun 30 '21 at 20:32
  • I can try that tomorrow. But, like I said, "view tabular data" produces the correct results. – dougp Jun 30 '21 at 20:43
  • Actually, that's a highly-simplified example. My original isn't coded like that. It's `case when dataitem in then a else b end`. So the else -> null is not the culprit. – dougp Jul 01 '21 at 16:56
  • Can you create a simple list and just have a column label be the data item value – VAI Jason Jul 02 '21 at 13:12
  • Did that work for you? Does it produce the desired results? I have created a report expression that does it. I don't like that solution because now I'm maintaining the same code in two places. – dougp Jul 02 '21 at 19:10

0 Answers0