nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Show empty-rows for cross-tab using date/time groups

Michael Ih

I'm trying to create a cross-tab that looks like the following: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
X

|
Sun. | 3 |

|
Mon. | 0 |

|
Tue. | 1 |

|
Wed. | 2 |

|
Thr. | 0 |

|
Fri. | 1 |

|
Sat. | 4 |

|
</pre>
 
I've setup my cross tab to have a Date/Time group where the "Day of Week" is selected. When I run the report, the cross tab only shows the rows with data it them (i.e. Mon. and Thr. are omitted). 
 
I know about the "Empty Rows/Columns" setting...but it is grayed out unless I add a higher level date/time grouping (i.e Month) to the cross-tab. 
 
My primary objective is to create a histogram (bar-chart) that shows the values grouped by Day of Week (or hour). If there is an alternate way of accomplishing that (without cross-tabs), I could use that approach. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
*
* *
* * *
* * * * *

S M T W T F S
</pre>
 
FWIW, I also want to build a similar chart with hours 00:00-23:00. 
 
Thanks, 
~Michael

Find more posts tagged with

Comments

kclark

I think the problem is Monday and Tuesday don't have any data, not a 0 value. If Monday and Tuesday had 0 as values then they would still show in the crosstab. What are you using as a data source? One way to solve this is to create a scripted data source, grab the data, and give the empty rows a 0 value so it will show in your crosstab.

Michael Ih

The cross-tab I depicted was the desired output; you are correct that the rows that have zeros are actually not displayed at all (furthermore, the data presented is a gross-simplification of my actual data set). 
 
My data-source is a large SQL source, hypothetically it's structure is like this... 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
id(INT) | date(DATE) | type (VARCHAR) | ...
</pre>
 
I create a data-cube with a DateGroup (using Day of Week) and a TypeGroup; the measure is the COUNT of id. 
 
The problem I'm trying to solve is that, depending on the filters applied my data-cube may not have data for each day of the week but I'd like my cross-tab (or chart) to always display all 7 days even if the cube doesn't have any data. 
 
I looked and it doesn't appear possible to script a data-cube; but perhaps I can script the cross-tab using onPrepare or onCreate. I thought about deselecting the "Category X-Axis", but it appears that although you can set the origin for the X-Axis there doesn't seem to be a way to set the maximum (or the number of steps)

Clement Wong

Michael, 
 
To fill in the empty dates, your best bet is to have a separate calendar table with all possible dates in your SQL database. In your data set query, you can use an outer join on the calendar table with your original result table to show the zero value dates. 
 
For more information regarding this technique, <a class='bbc_url' href='http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/'>read this blog entry</a> and although it is specific to MySQL, the methodology can be applied to other database servers.