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.)
Orders
OrderID
OrderedAmt
OrderedQty
Customer
OrderStatus
OrderStatuses
OrderStatus
(In the QVD, Orders and OrderStatuses are connected by OrderStatus.)
Order contains --
OrderID OrderedAmt OrderedQty Customer OrderStatus
1 100 10 Acme Open
2 200 20 Acme Complete
3 300 30 MegaCo Open
4 400 40 MegaCo Open
5 500 50 LittleCo Canceled
OrderStatuses contains --
OrderStatus
Open
Complete
Canceled
Hold
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.
Order contains --
OrderID OrderedAmt OrderedQty Customer OrderStatus
1 100 10 Acme Open
2 200 20 Acme Complete
3 300 30 MegaCo Open
4 400 40 MegaCo Open
OrderStatuses contains --
OrderStatus
Open
Complete
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...
Open Complete Canceled Hold Totals
Amount 800 200 0 0 1000
Quantity 80 20 0 0 100
Thanks!
