You can try using a calculated field or a conditional aggregation in your crosstab. Basically, you need to create a formula that calculates the year-to-date sales regardless of the month filter. For the first column, you could set the calculation to ignore the month filter, so it always shows the total for the entire year.

If you're pulling data from different hierarchies or dimensions, the problem could be that the relationships between them aren't properly defined or that the query is misinterpreting how those should be linked. Check the cardinality and ensure that your joins in Framework Manager are correctly configured to handle the cross-dimension aggregation.

I've had a similar issue before, and what worked for me was using the "Set to" option in the summary cell to manually specify the value from the singleton. Instead of using "Define Contents" to pull the singleton directly, I used a simple conditional statement in the summary cell to check if the row was a summary and then show the calculated value. This way, the summary cell doesn't try to re-aggregate the values and you don't end up with the blank cell.

Hello,

I have a report I need to re-create in Cognos Analytics from Excel, that uses a TM1 cube as a data source. Hopefully the screen shot I've included help explain things to the fullest.

I'm running into an issue because the report is going to use 4 different hierarchies within the Time dimension, all with different MUN's. The requirement is the user will choose a Month and Year from a value prompt, take March 2025 for example, and March 2025 needs to be passed to the 4 different hierarchies within the Time dimension. If the user chose April 2024 in the value prompt, April 2024 would need to be passed to the different hierarchies, etc./etc.

Is is possible to create these MUN's in the screen shot based off the value prompt selection?

Thank You in advance.

Mike

Hi everyone,

I've created a crosstab with three columns: A, B, and C, where C is a calculated average (A / B). The issue I'm facing is with the total row. By default, when the data is summarized, column C displays a total (sum), but I want it to show (Total A / Total B) instead.

Here's what I've tried so far:

Set 'Define Content' to Yes.

Hid the default total for column C.

Created a calculated field with the expression (Total A / Total B).

Unlocked the crosstab layout and dragged this calculated field into the total column cell.

Then disabled 'Define Content'.

However, when I ran the report, column C still displayed the average instead of the expected total calculation (Total A / Total B).

To work around this, I created a separate calculated column for (Total A / Total B) and used a singleton to display it in the total field. This worked and showed the correct result.

Issue: When I try to hide the extra calculated column using Box Type: None, the layout gets affected—specifically, the hidden column overlaps with another header in the crosstab.

Question:

 Is there a way to hide the separate calculated column cleanly—without using Box Type None in a way that affects the layout or causes header overlap?

Thanks in advance for your help!

#cognos,#DefineContent,#Crosstab,#List,#cognosreport

Hello friends, I'm facing the same issue. I followed the steps provided in the link, but when I try to hide the column by setting the box type to 'none', the column still shifts and overlaps with another header

Hello friends, I'm facing the same issue. I followed the steps provided in the link, but when I try to hide the column by setting the box type to 'none', the column still shifts and overlaps with another header

Hello Cognos SMEs, I'm facing a peculiar problem with multiple charts of dashboards after performing the upgrade from 11.2.4 to 12.0.4 version.

I have a line chart showing 2 lines (Rolling 12 months CY and PY) with the Months column (Data type: Text, Usage: Attribute, Aggregation: None) shown on the x-axis. Only the month names are shown as I wanted the two lines to overlap and give a better view of comparisons of CY with PY. This worked well in 11.2.4.

After the upgrade was performed, the same chart shows only 1 line, and there is a yellow Asterix icon with the following message: "The data displayed has been limited.
Unaggregated data points are not supported by this visualization. Only the first values will be displayed."

I believe the problem is with the 'Members Display' section in the Properties for the month column, as when I select Display Options as 'Automatic', the chart works as expected, and I see both lines. However I lose the sorting order and the charts show from Jan to December (instead of April-March).

Has anyone experienced this issue or a similar one? And what can be some possible solutions, as I need to show the Rolling 12 months in that order instead of Jan-Dec.

Thanks in advance!

We recently migrated cognos from 10.2 to 11.1.7, one of my schedule reports is failing however when i run it on-demand in background or manually it is running fine, but whenever i schedule to run it is failing with the below error. Please share me your thoughts.

RQP-DEF-0177 An error occurred while performing operation 'sql�Prepare�With�Options' status='-271'. UDA-SQL-0607 Native SQL expressions ( "{sysdate}" ) cannot be used for local processing.

Quote from: Harl on 11 Apr 2025 04:05:47 PMHi, I have a crosstab and I have a column that is showing the totals for the year of the members in the different rows.

For example, I have in the rows cars, tables and chairs.
There is a column showing the sales for those products for example for the whole year.

Then I have a prompt value with the months. So if I select august, in another column it is showing the sales of august.

But I want the first column to still show the total of the year ignoring that filter of month.

If anyone could help me please. I have tried different ways but selecting the month it changes also the first column.

Thank you!!

Hi,

The first (and probably most important) question is whether your report is based on a relational package or a dimensional package. This will drive how best to obtain the result you need. If you are using a dimensional package, the approach would be to replace the filter with a dimensional expression for the month column in the crosstab (for example, a calculation with the expression [Your month level] -> ?MonthParameter? ). There would then be no impact on the Year column, or if there was, you could use a completeTuple() expression to get the year values regardless.
If using a relational package, things are a little more complex. You would need to add a separate query to the report to retrieve the Year values, join it to the main query based on Year, then use the resultant query from the join to feed your crosstab.

If you are unsure whether you are using a relational or dimensional package, check the FAQs post below. It's old, but still valid:

https://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.