I have came across this situation couple of times when user wants to see 0 against the dimensions for which Fact value is not Available. i.e. we need to take a left outer join between dimension and Fact.
As this requirement is not very common its better to handle it on the webcat side. The following example elaborates this requirement.
In the Area dimension I have 10 values
Fact contains the Booked Amount. However Booked Amount values are not available for all Areas. So after including Fact column number of rows in the report decrease and we get the result as shown in image below:
As seen in the above results, Area 7 and Area 8 are missing as the Fact table does not hold any value for this value of Dimension. The requirement is to show Area7 and Area 8 in the report with Booked Amount = 0.
The Procedure to implement this is given below:
1. Include the required columns and filters in criteria.
2. Click on Combine with Similar Report button and select the same subject Area
3. Add the same dimension and fact columns again
4. Change the formula of Fact column and write 0, (ZERO) in its column Formula.
5. Change the Set Operation to Union All
6. Always show the results in Pivot Table with Aggregation rule as SUM on the Fact column
Area 7 and Area 8 are available in the results with Booked Amount = 0
No comments:
Post a Comment