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
------------------------------