Crosstabs - Getting Headings to Show When There is No Data

Crosstabs (cross tables) are a great element to use in your report but since the data becomes the column and row headings, it is challenging when there is missing data. For example, in the crosstab below you'll notice California has all the months but Colorado doesn't have $ for February or July so those months are missing in the Colorado crosstab.

crosstab_misalignedheaders.jpg

One way to handle that is to create a second query that pulls in all the months and then merge the months from both queries. To demonstrate:

Edit the query and add a second query with only the objects used in the column header. (Note that the crosstab in our example uses Month for sorting, but it is a hidden dimension, which is why you don't see it in our screenshot above).

crosstab_query.jpg

Be aware that if you are accessing a very large table to grab all the heading values you may want to uncheck the "Retrieve duplicate rows" checkbox in the query properties.

dup rows.jpg

Run the new query. You now have one query with all your data and the other with just months, which returned Janauary-December.

crosstab_objects.jpg

Depending on your document properties, these objects may automatically merge. In this case, they did not so we will merge them.

merging.jpg

merged.jpg

Once all crosstab heading object are merged you replace the existing crosstab heading(s) with the merged dimension.

crosstab_replaceheader.jpg

Notice Colorado now displays February and July even though there are no values for those.

crosstab_done.jpg

This is a great technique that can be used anytime you have a crosstab. The alignment makes the crosstab much easier to read. Happy Thanksgiving.

#crosstab #webintelligence #merging

Featured Posts
Recent Posts
Archive
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square