There is already an Analysis->Show Empty Rows/Columns option, but that only pads the domain based on the underlying data for the given dimensions. Those two customers don't have any rows for the missing sub-categories in the superstore sales data, so Show Empty Rows does nothing for them. There's likely some wacky solution using domain completion and crazy pill arrangements, but that would overcomplicate all further calculations.
One option is to create a scaffold data source that includes all dimensions at the necessary level of detail, so you'd build a source that has every Customer Name/Sub-Category combination. Then you can use that scaffold source as the primary in the blend. However, that can get a little funky if you're trying to do certain combinations of filters and blends at different levels of detail than the view. More on how to dynamically build a scaffold at the bottom of this post.
Alternatively, you can pad out the data so there are extra rows in the data source for all of the combinations. This is the combination I use more often, because I'm comfortable writing SQL and making this dynamic. The only caveat is that Number of Records is no longer accurate because the data is padded out, I'll use a different calc for that. What I do for this solution is to:
1. use a SELECT DISTINCT to get the list of customers
2. use another SELECT DISTINCT to get the list of sub-categories
3. create a cartesian join between them
4. UNION the original data source to the cartesian join
Steps 1-3 can all be wrapped inside a sub-query, as in the attached. Also, you could use steps 1-3 to build the scaffold data source.
Interesting - I had guessed the scaffold source would be the best answer but I don't have the SQL chops for creating it. I certainly would not have thought of the dynamic creation of the scaffold which makes this very neat for sources that change even infrequently. This puts my aversion to the custom SQL scaffolding to bed. Thank you Jonathan.
1 of 1 people found this helpful
Since you're working with SQL and JET, here's a bit on my process for dealing with JET's horrendous lack of informative error messages: I'll use very small steps, testing each sub-query on a separate (duplicate) data source, starting with the lowest-level sub-query and working my way up the tree of sub-queries from there until I have the final data source ready. Otherwise a misplaced comma or parentheses can make me bang my head on my desk in frustration.