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 =
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')
Quote from: dougp on 28 Jun 2021 02:51:57 PM
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 =
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')
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.
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.
That's what I did, but it seems like a hack. I remember this working properly in 11.0.13.
Quote from: dougp on 01 Jul 2021 11:52:28 AM
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.