​Hello,
First, apologies for the 'confusing subject line'.  I don't see where I can 'update/edit' it.


Second, here is my issue.  I have a cross tab where I'm doing a count by location by month.  There are sporadic locations that may have 0 for any given month.  These 0 locations are being dropped from the monthly detail.  I need these locations to show up in the crosstab.  Suppression is set to 'No Suppression', but the location still does not show up.  Any ideas would be helpful!

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
​
#CognosAnalyticswithWatson

Hello, I guess you are using a Data Module or a relational model as data source for the Crosstab.

In this case if there are locations without data for the months in the report the locations will note be rendered in the Crosstab.

As a solution I can only think of making a cross product join inside the report between the Location query and the fact query.

In a quick search I found this article, maybe is helpful.
https://www.ibm.com/support/pages/columns-or-rows-missing-crosstab-if-they-contain-no-data

------------------------------
Alex Santamarta
------------------------------

Original Message:
Sent: Wed April 15, 2020 06:54 AM
From: Jenifer Broughton
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

​Hello,
First, apologies for the 'confusing subject line'.  I don't see where I can 'update/edit' it.


Second, here is my issue.  I have a cross tab where I'm doing a count by location by month.  There are sporadic locations that may have 0 for any given month.  These 0 locations are being dropped from the monthly detail.  I need these locations to show up in the crosstab.  Suppression is set to 'No Suppression', but the location still does not show up.  Any ideas would be helpful!

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
​
#CognosAnalyticswithWatson

Hi Jennifer,
Alex's solution is basically the way to go, but I found the linked article a bit overly-complicated.
You do need to create a "dummy" query in your report to pull in a result for every Month.  The trick is to sub in a measure value of zero on this dummy query, so it only needs to include basically two data items, one for month, and a measure (call it whatever you want) and set its expression to 0 (the number zero).  If you base your month expression on the same source data as your main query, this new query should return a row for EVERY month with a corresponding measure value of zero.  Now, join this to your main query using a full outer join on the Month item.  I use a 0..n on the dummy query and a 0..1 on the main query.  Build a third query as the result of this join, and be sure to pull the Month item from the dummy query, and everything else from the original/main query.  Let us know how this works.

------------------------------
Albert Valdez
albert@intelalytics.com
------------------------------

Original Message:
Sent: Wed April 15, 2020 03:01 PM
From: Alex Santamarta
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Hello, I guess you are using a Data Module or a relational model as data source for the Crosstab.

In this case if there are locations without data for the months in the report the locations will note be rendered in the Crosstab.

As a solution I can only think of making a cross product join inside the report between the Location query and the fact query.

In a quick search I found this article, maybe is helpful.
https://www.ibm.com/support/pages/columns-or-rows-missing-crosstab-if-they-contain-no-data

------------------------------
Alex Santamarta

Original Message:
Sent: Wed April 15, 2020 06:54 AM
From: Jenifer Broughton
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

​Hello,
First, apologies for the 'confusing subject line'.  I don't see where I can 'update/edit' it.


Second, here is my issue.  I have a cross tab where I'm doing a count by location by month.  There are sporadic locations that may have 0 for any given month.  These 0 locations are being dropped from the monthly detail.  I need these locations to show up in the crosstab.  Suppression is set to 'No Suppression', but the location still does not show up.  Any ideas would be helpful!

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
​
#CognosAnalyticswithWatson

Thanks Alex and Albert.  Yes, I was looking at the article and it was a bit complicated.  I had started to 'try' to interpret it. 
Thanks for the consolidated directions.  I'm going to give this a go.  Thanks again for the feedback!!
I'll update this thread with what I find.

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Thu April 16, 2020 04:56 PM
From: Albert Valdez
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Hi Jennifer,
Alex's solution is basically the way to go, but I found the linked article a bit overly-complicated.
You do need to create a "dummy" query in your report to pull in a result for every Month.  The trick is to sub in a measure value of zero on this dummy query, so it only needs to include basically two data items, one for month, and a measure (call it whatever you want) and set its expression to 0 (the number zero).  If you base your month expression on the same source data as your main query, this new query should return a row for EVERY month with a corresponding measure value of zero.  Now, join this to your main query using a full outer join on the Month item.  I use a 0..n on the dummy query and a 0..1 on the main query.  Build a third query as the result of this join, and be sure to pull the Month item from the dummy query, and everything else from the original/main query.  Let us know how this works.

------------------------------
Albert Valdez
albert@intelalytics.com

Original Message:
Sent: Wed April 15, 2020 03:01 PM
From: Alex Santamarta
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Hello, I guess you are using a Data Module or a relational model as data source for the Crosstab.

In this case if there are locations without data for the months in the report the locations will note be rendered in the Crosstab.

As a solution I can only think of making a cross product join inside the report between the Location query and the fact query.

In a quick search I found this article, maybe is helpful.
https://www.ibm.com/support/pages/columns-or-rows-missing-crosstab-if-they-contain-no-data

------------------------------
Alex Santamarta

Original Message:
Sent: Wed April 15, 2020 06:54 AM
From: Jenifer Broughton
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

​Hello,
First, apologies for the 'confusing subject line'.  I don't see where I can 'update/edit' it.


Second, here is my issue.  I have a cross tab where I'm doing a count by location by month.  There are sporadic locations that may have 0 for any given month.  These 0 locations are being dropped from the monthly detail.  I need these locations to show up in the crosstab.  Suppression is set to 'No Suppression', but the location still does not show up.  Any ideas would be helpful!

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
​
#CognosAnalyticswithWatson

Hello.  Just checking back in to let you know this concept did work!  Thank you both for the insight and assistance!

Stay have and have a great day.
Thanks,
Jen​

------------------------------
Jenifer Broughton
------------------------------

Original Message:
Sent: Fri April 17, 2020 09:12 AM
From: Jenifer Broughton
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Thanks Alex and Albert.  Yes, I was looking at the article and it was a bit complicated.  I had started to 'try' to interpret it. 
Thanks for the consolidated directions.  I'm going to give this a go.  Thanks again for the feedback!!
I'll update this thread with what I find.

Thanks,
Jen

------------------------------
Jenifer Broughton

Original Message:
Sent: Thu April 16, 2020 04:56 PM
From: Albert Valdez
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Hi Jennifer,
Alex's solution is basically the way to go, but I found the linked article a bit overly-complicated.
You do need to create a "dummy" query in your report to pull in a result for every Month.  The trick is to sub in a measure value of zero on this dummy query, so it only needs to include basically two data items, one for month, and a measure (call it whatever you want) and set its expression to 0 (the number zero).  If you base your month expression on the same source data as your main query, this new query should return a row for EVERY month with a corresponding measure value of zero.  Now, join this to your main query using a full outer join on the Month item.  I use a 0..n on the dummy query and a 0..1 on the main query.  Build a third query as the result of this join, and be sure to pull the Month item from the dummy query, and everything else from the original/main query.  Let us know how this works.

------------------------------
Albert Valdez
albert@intelalytics.com

Original Message:
Sent: Wed April 15, 2020 03:01 PM
From: Alex Santamarta
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

Hello, I guess you are using a Data Module or a relational model as data source for the Crosstab.

In this case if there are locations without data for the months in the report the locations will note be rendered in the Crosstab.

As a solution I can only think of making a cross product join inside the report between the Location query and the fact query.

In a quick search I found this article, maybe is helpful.
https://www.ibm.com/support/pages/columns-or-rows-missing-crosstab-if-they-contain-no-data

------------------------------
Alex Santamarta

Original Message:
Sent: Wed April 15, 2020 06:54 AM
From: Jenifer Broughton
Subject: Crosstab dropping zero row with suppressing zero. Need row to show

​Hello,
First, apologies for the 'confusing subject line'.  I don't see where I can 'update/edit' it.


Second, here is my issue.  I have a cross tab where I'm doing a count by location by month.  There are sporadic locations that may have 0 for any given month.  These 0 locations are being dropped from the monthly detail.  I need these locations to show up in the crosstab.  Suppression is set to 'No Suppression', but the location still does not show up.  Any ideas would be helpful!

Thanks,
Jen

------------------------------
Jenifer Broughton
------------------------------
​
#CognosAnalyticswithWatson