Hi Paul...thank you for responding. I followed you suggestion in the recommended article and applied an aggregate function to the Date field. I am using Sql Server 2016 so instead of listagg I used string_agg. My expression looked like this: aggregate:string_agg([Sales (query)].[Time].[Date],', ').
After running the report again, it did render correct dates across the first row. But all other rows have the same column data (dates) as the first row.
The row (Region) already has the Date field checked in the Properties option under Data for the Crosstab node member. I also have the Date field checked in the Properties tab for the crosstab node member Order method (Column). Please advice on how I might go about getting each row to render results.
I did include the report specs for the GO SALES report sample out of Cognos that I modified to replicate the issue that I am experiencing but just for a visual, this is what I am actually trying to accomplish:
This data will show each employee, courses that they have taken, when they last completed the course and when the course is due to be taken again.
Name = Row
Course = Column
DateStatus = Column (Completed or NextDue)
Date = Column (Date)
Course Course Completed | Due Completed | Due
Name
Name
Name
------------------------------
Towandra Grant
------------------------------
Original Message:
Sent: Thu September 23, 2021 02:01 PM
From: Paul Mendelson
Subject: Crosstab with Non-numeric Data
I wrote about adding a text field to crosstabs, try using that was a basis: https://cognospaul.com/2014/10/20/quickie-aggregating-text-cognos-crosstab/
If you're still having trouble reply back and I'll set up an example with dates.
------------------------------
Paul Mendelson
Original Message:
Sent: Thu September 23, 2021 11:20 AM
From: Towandra Grant
Subject: Crosstab with Non-numeric Data
I have a requirement to create a report that lists an employee's current training status of specific courses. The desired output is that of a crosstab. Employee's name as the row. Overall column heading is Course Code and the sub-column is Status. Status has two columns: Completed Date, Next Due Date. The data item for this crosstab is a date. I figured out how to manually add this non-numeric data item to the crosstab BUT the results are not rendering correctly. It is choosing the earliest (Completed Date/Next Due Date) and populating all employees (each row) with the same two dates.
I modified a GO SALES sample report to allow easy viewing of the scenario that I have described above. Currently IBM Support says that non-numeric data in a cross-tab is not supported in Cognos Analytics (unless it's from a TM1 based data source). However, I thought I would see if anyone has gotten this far within the workaround process and figured out a way to fix the accuracy of rendering the data.
--Please see the attached report spec and output file
version: Cognos Analytics 11.1.6
DB: MS SQL Server
------------------------------
Towandra Grant
------------------------------
#CognosAnalyticswithWatson