Preventing a dimension from being reduced, or still showing all the values
I cannot post the actual example for this question because all of it contains confidential information, so I will give a similar example.Please read the entire post and it should be relatively straightforward to see what I'm trying to do.
How do I prevent a specific dimension that is connected to reduced data from being reduced? Or, how do I work around that to show the missing values?
Suppose I have the following tables in my QVD. (I've kept them separate and shown only the relevant fields, for clarity.)
(In the QVD, Orders and OrderStatuses are connected by OrderStatus.)
The reason there is a separate OrderStatuses table is because I want to show all possible statuses even if there are no orders.
Using this design, a straight table showing totals, such as the one below is simple to make:
Open Complete Canceled Hold Totals
Amount 800 200 500 0 1500
Quantity 80 20 50 0 150
Here's the problem...
Suppose that I'm using Section Access and Strict exclusion. I've authorized users to specific Customers. If you are only authorized to Acme and MegaCo then when you open the document, your tables will look like the example below.
As a result, the stratight table will look like the example below.
Open Complete Totals
Amount 800 200 1500
Quantity 80 20 150
Orders has been reduced, but so has OrderStatuses.
How can I enforce the reduction, but still show the user all the statuses. I want the straight table to contain every possible status, even if the user doesn't have any orders for them.
I've tried adding a MasterStatuses table that is not connected to the other tables and then use MasterStatus as the primary dimension, with the actual OrderStatus as a secondary dimension, allowing nulls. That didn't work either.
Can someone please post the solution and/or a simple QVW that illustrates it? I'd like the resulting table to contain...
Just an idea, what if you add this to your data model script:
LOAD OrderStatus Resident OrderStatuses;
LOAD Customer Resident Orders;
LOAD OrderStatus, Customer Resident TMP;
DROP TABLE TMP;
I.e. Create each possible combination of Customer / OrderStatus without any real facts (OrderID, Amount, QTY etc will be NULL for these records). Reducing to certain customer values should then keep all OrderStatus possible.