Hello,

  I have a list report with a few dimension columns (Year/IndexCode/IndexName) and a measure column. The measure I am calculating inside a variable as below:

  IF ( FUND_CODE='AA') THEN (1) ELSE (0) where I am keeping my measure's Detail Aggregation Property as "Total"
My goal is to count the number of fund_codes of value 'AA'

When I check the generated sql, it creates an additional column "FUND_CODE_u" (as below- due to which only one row will have value but others will be null ) and hence the total is not working.
Any inputs why this is occurring and how to fix?


Generated SQL:
"FUND_CODE",
        CASE
            WHEN
                ROW_NUMBER()
                    OVER(
                        PARTITION BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR",
                            "IDX_DIM"."IndexCode",
                            "IDX_DIM"."IndexName",
                            "FDX_DIM"."FUND_CODE"
                        ORDER BY
                            "ACADEMIC_YEAR_DIM0"."ACADEMIC_YEAR" ASC,
                            "IDX_DIM"."IndexCode" ASC,
                             "IDX_DIM"."IndexName" ASC,
                            "FDX_DIM"."FUND_CODE" ASC
                    ) = 1
                THEN
                    "FDX_DIM"."FUND_CODE"
            ELSE NULL
        END AS "FUND_CODE_u"

Thanks,

Mark this Resolved! 

Thanks dougp, I had trouble finding that Property as I was looking for it relevant to either the Query or the Crosstab container when it is instead a Property of the Report page.

Adjust the "Group repeating cells - export to Excel" property of the Report object.

I'm using v11.2.3.

My Report Crosstab contain has nested date-parts as row headings - like Year, Month Name, and Day.

By default, the multiple occurrences of the Year and the Month Name are being "grouped" which looks fab in HTML. 

But my audience needs to be able to easily export to MS Excel for further manipulations.  The grouped row heading make it difficult on them to use the rows within Excel.

I've Googled for ideas but seems like all hints I've come across seem to want to help prevent auto aggregation of the values in the body of the Crosstab.  None of those hints influenced the formatting of the row headings.


Any other ideas anyone might be able to share???

   

Yes, Cognos is running on a windows server. We have two dispatcher servers, one content manager server, one gateway and one application server.

I found in the IBM Cognos Analytics forum other users with the same issue, and that looks particular on the scheduled reports. The rest is not a problem, is the scheduler that gets affected and you need to renew the credentials, which is kind of what I did the following day when I notice the issue.

I will revie the different suggestions on the other forum and will write here what was applied to have a generic scheduler user.

I may be wrong, but...

I'm pretty sure we've had report/schedule owners leave the organization, their Active Directory accounts destroyed, and their report schedules continue to work.

Are you running Cognos on a Windows Server?  How is the IBM Cognos service configured?  On the Log On tab in the service properties, is your account running Cognos?

Three weeks ago, we encountered an issue after I changed my password-the scheduler stopped running reports.

The following day, when I noticed the problem, I manually ran the critical reports using the "Run Once" option. Shortly after, the scheduled reports began processing normally again.

We'd like to prevent this from happening in the future.

Does anyone know if it's possible to assign a generic user as the Scheduler User? If so, how can that be set up?

Thank you,

Quote from: eklund86 on 19 Sep 2025 06:59:02 AMThat worked beautifully in 2025 - thanks! :-)

Great to hear this is still useful fifteen years later :-)

Cheers!

MF.

Thanks dougp for your comments.  They are helpful and in fact one of the axis on my heat map is in fact an "Hour" (of the day) column.  So, you're example was perfect.

I was thinking someone like a "data scientist" or maybe a "statistician" might bite on my ask.

I can't imagine what "credential" would be helpful in this area.  Pick up some books by Ed Tufte.


Possible causes of confusion:

Low information resolution.  This is basically what people do in PowerPoint.  Data is so highly summarized it's meaningless because there is insufficient context.

Choosing the wrong visualization to present the information.  If basic sorting doesn't show patterns, maybe a heat map is not the appropriate visualization.  For example, looking at Cognos report usage counts, I see that my busiest times are 11 a.m. to 2 p.m., usage ramps up at 6 a.m. and drops off at 5 p.m., and Friday afternoons are really slow.  This pops out quickly on a heat map with day of week along the top and hour of day along the left side.  You'd get the same thing with traffic volumes, electricity usage, water usage -- with the same axes.  There are several versions of a visualization that looks basically like https://dataschool.com/assets/images/how-to-design-a-dashboard/find_the_best_chart_for_your_metrics/visualizationWeb.png that shows which type of viz to use when.  But you must not follow such a recommendation blindly.  You should also consider the outputs your data presents through these options and whether or not the viz you have chosen tells a clear story.

Confusing colors.  Does blue = hot and red = cold?  Do some of your report viewers suffer from some form of colorblindness?