I am having problem creating a cross tab report with populating measures which have no values. The report I am trying to create is based on measure count of status (column) against a date which is agregated to week of year (row). I can produce a report with the correct count but it does not show any values if there were no occurance of a prticular status or week. I would like to display zero if there were no measures available.
I tried doing something like this which I found in one of the forum and I have manage to get all the possible rows and columns displayed but the measures are all blank. Can anyone suggest what I am doing wrong or is this a workable solution.

1) Create a "Column Query", containing only the column information and a dummy data item with a value of 1. In the attached example, this is named "Years"

2) Create a "Row Query", containing only the row information and a dummy data item with a value of 1. In the attached example, this is named "Product Lines"

3) Create a "Dimension Query" query that joins the queries from steps 1 and 2 on dummy. This requires that the Outer Join Allowed property of the query be set to Allowed. This creates a crossjoin that includes all possible combinations of rows and columns

4) Create a fourth query that contains the data for the crosstab. This is the same as a normal crosstab report.

5) Join the queries from steps 3 and 4, using cardinality of 1..1 and 0..n respectively. When dragging data items into this new query, ensure that you are dragging in the row and column headings from the "Dimension Query". This ensures that all possible rows and columns will be returned, even if there is no data associated with them.

------------------------------
Walter Lim
------------------------------

#CognosAnalyticswithWatson

I think the problem is with this joint. If I remove one of the links some measurements are displayed but the values are not sum correctly.




------------------------------
Walter Lim
------------------------------

@Walter Lim If this is just for display purposes, you can apply data format on the crosstab fact cells. ​

------------------------------
DENNY NAREZNY
------------------------------

Original Message:
Sent: Mon June 17, 2019 08:55 PM
From: Walter Lim
Subject: CrossTab reporting blank measures

I think the problem is with this joint. If I remove one of the links some measurements are displayed but the values are not sum correctly.




------------------------------
Walter Lim
------------------------------

Its not a formatting issue. I know there are values in some cells. The original cross tab report I manage to generate with the correct numbers looks like this.


We want to improve on the report to show columns and rows with no values as well, ie the status columns should have Open, In Progress, .... with no values which we want to display as well. Same for the rows. The method I am trying to use comes from a old post 2016 so not sure if it still works. I manage to get most of the method working upto to 5th point. There are 2 cross links, if I remove the Date week (row) link I can see some results but the numbers are wrong. 


------------------------------
Walter Lim
------------------------------

Original Message:
Sent: Tue June 18, 2019 09:58 AM
From: DENNY NAREZNY
Subject: CrossTab reporting blank measures

@Walter Lim If this is just for display purposes, you can apply data format on the crosstab fact cells. ​

------------------------------
DENNY NAREZNY
------------------------------


Original Message:
Sent: Mon June 17, 2019 08:55 PM
From: Walter Lim
Subject: CrossTab reporting blank measures

I think the problem is with this joint. If I remove one of the links some measurements are displayed but the values are not sum correctly.




------------------------------
Walter Lim

1)
For a crosstab you probably need to change the crosstab suppression option.
By defaults rows and columns with all null values are suppressed.
2)
In addition you need to model the relationships, such that the minimum cardinality at the fact table is set to 0.
E.g. DateInfo(1:1)------(0:N)FactData
and Status(1:1)-----(0:N)FactData
In the crosstab,
select row header from the DateInfo table
select column header from the Status table
for the crosstab corner select the measure from the FactData table.

Setting the joins in this manner allows all the dimension information to be retained even if there is not related FactData.
The suppression option in the crosstab needs to be turned off, otherwise it will get suppressed during rendering.
//Henk

------------------------------
HENK CAZEMIER
------------------------------

Original Message:
Sent: Tue June 18, 2019 08:11 PM
From: Walter Lim
Subject: CrossTab reporting blank measures

Its not a formatting issue. I know there are values in some cells. The original cross tab report I manage to generate with the correct numbers looks like this.


We want to improve on the report to show columns and rows with no values as well, ie the status columns should have Open, In Progress, .... with no values which we want to display as well. Same for the rows. The method I am trying to use comes from a old post 2016 so not sure if it still works. I manage to get most of the method working upto to 5th point. There are 2 cross links, if I remove the Date week (row) link I can see some results but the numbers are wrong. 


------------------------------
Walter Lim
------------------------------


Original Message:
Sent: Tue June 18, 2019 09:58 AM
From: DENNY NAREZNY
Subject: CrossTab reporting blank measures

@Walter Lim If this is just for display purposes, you can apply data format on the crosstab fact cells. ​

------------------------------
DENNY NAREZNY


Original Message:
Sent: Mon June 17, 2019 08:55 PM
From: Walter Lim
Subject: CrossTab reporting blank measures

I think the problem is with this joint. If I remove one of the links some measurements are displayed but the values are not sum correctly.




------------------------------
Walter Lim