Morning,
I have a cross tab report and when I perform a HTML output I notice the rows that contain no data auto suppress. I need the rows to show blank if there are no data. I also confirmed that No Suppression is selected.
Any ideas would be greatly appreciated,
Thank you,
Jonathan
ok, so if your suppression settings is already set to No then you need to check in the report query's filters and slicers to check if some sort of suppression is being done. Also check the expressions for the crosstab rows (or columns) to make sure the expressions have not been edited to automatically incorporate suppressing
HI Arsenal
I checked all my filters, and no suppression is being done.
As per my expressions,
These are the only two complicated onese:
IF (total([Hours Worked]) <> 0) THEN
(total([Earned])/total([Hours Worked]))
ELSE (0)
and this one:
CASE
WHEN [G/L Date]>=[cWeek1Srt] AND [G/L Date]<[cWeek1End] THEN (1)
WHEN [G/L Date]>=[cWeek2Srt] AND [G/L Date]<[cWeek1Srt] THEN (2)
WHEN [G/L Date]>=[cWeek3Srt] AND [G/L Date]<[cWeek2Srt] THEN (3)
WHEN [G/L Date]>=[cWeek4Srt] AND [G/L Date]<[cWeek3Srt] THEN (4)
WHEN [G/L Date]>=[cWeek5Srt] AND [G/L Date]<[cWeek4Srt] THEN (5)
WHEN [G/L Date]>=[cWeek6Srt] AND [G/L Date]<[cWeek5Srt] THEN (6)
WHEN [G/L Date]>=[cWeek7Srt] AND [G/L Date]<[cWeek6Srt] THEN (7)
WHEN [G/L Date]>=[cWeek8Srt] AND [G/L Date]<[cWeek7Srt] THEN (8)
ELSE (0)
END
Thats it, any other ideas?
Thanks!
It's an usual Cross-tab tendency when you don't have data for that combination (Rows & Columns) it suppress it automatically.
However, if want the rows to still appear then you may write a condition for that particular measure something like below,
If [Measure] is missing
then cast(null,integer)
else [Measure]
So if no data is available for a combination then it would appear as Blank....
Does it make a sense?
Thanks
P
Thanks for the reply!,
I had no luck,
I created a String Variable under Condition Explorer and receive the attached error.
Am I missing something?
Thank you!
Jonathan
jdonais, for your IF THEN expression error from screenshot, the IF, THEN and ELSE need to be within ()
I think I've misunderstood your original question. You talked about zero suppression - do you have 0's in the DB or nulls for items with no values? Nulls are suppressed by crosstab. When 0's values are properly present on the DB, they will show up on the crosstab.
It seems you may have nulls on your DB for an entire column (for example) which will cause the crosstab to drop that column (or row as the case may be). The key here is to "fool" the crosstab into thinking that there are 0's and not nulls so the columns or rows show up. This is done by creating queries with dummy data, joining them with an outer join, creating a new query from items from the joined query and then creating another actual crosstab query to use in your crosstab.
Here';s an article that explains the steps in detail:
https://www-304.ibm.com/support/docview.wss?uid=swg21341708
HI,
Did you have any cardinality set in FM , if it's set 1-1 or 1-n , if a fact is null the corresponding dimension value will not show. Try to change the cardinality to 0-n , I think you will get your answer there.
regards Jacques
Will that cause any issues with my other reports?
Thanks!