This is the fourth in a series of blogs about how to use math to filter out values from an SSAS dataset, and then create aggregations in a report. You want to filter out some values from a report column and then sum them, but you can’t use an IIF statement because it can’t be aggregated. Related blogs : Filter Most-Recent-Day, Week-To-Date and Full Week, Filter from Percentage where Last Year = Zero, and Aggregate Last Child
In this situation the client would like an Inventory Aging report. The report needs to include columns for inventory received 0-3 months, 4-6 months, 7-9 months and 10-12 months ago. The user can choose a Fiscal Week for the On Hand Inventory, and the report must calculate the aging based on the Last Receipt Date, which is built into the Inventory On Hand table. This seems simple enough until you get into the details.
The end results should look like this:
1. CUBE MUST HAVE TWO DIFFERENT TIME DIMENSIONS
The user must be able to select the week of the On Hand Inventory, and the report must render the aging based on Receipt Date. So two different date fields are required in your Inventory FACT table – Inventory Date and Receipt Date, and two different time dimensions should be created from these.
2. CUBE DIMENSION NEEDS TWO WAYS TO ACCESS FISCAL MONTH ID
The next thing you need is a dataset with the right fields in it to allow you calculate the aging. You’ll need the Fiscal Month ID of the selected Inventory Date as well as the Fiscal Month ID of the Receipt Dates, so you can calculate how many months apart they are for the aging. Unfortunately you can’t include two fields in your dataset with the same name, which is what would happen if I just pulled in each of the Fiscal_Month_ID fields from the two Time dimensions.
If I try to build a calculated field in order to rename one of the fields I still need something from the Time dimension that will populate the CURRENTMEMBER aspect. So if I built a calculated field like this [TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME, it will return [TIME OH Last Receipt Date].[Fiscal Month].[All] unless I pull in the Fiscal Month ID field to give the CURRENTMEMBER it’s context. This will mean two fields with the same name again, and the query will not accept it.
I worked around this by including Fiscal_Month_ID AND Fiscal_Month in my Time dimensions, with Fiscal_Month using the Fiscal_Month_ID as it’s key. This gives me two ways to access the Fiscal Month ID, one from each Time dimension.
3. REPORT DATASET NEED CORRECT FIELDS TO YIELD DESIRED RESULTS
In the Dataset Query I included Fiscal_Month_ID from the OH Inventory Time dimension and Fiscal_Month from the Last Receipt Date Time dimension. Then I added a calculated field called ReceiptMonth to my query which yields the tuple showing the underlying Fiscal Month ID.
[TIME OH Last Receipt Date].[Fiscal Month].CURRENTMEMBER.UNIQUENAME
I will reference this field to find the number of months difference between the user Selected Month and the Last Receipt Date.
4. ADD CACULATED FIELDS
Right click on your Dataset and select Dataset Properties. Go to the Fields selection and add the following calculated fields:
1. ReceiptMonthID – This pulls the Month ID out of the tuple. My month IDs are in the form of 201001 for example for January 2010.
=Mid(Fields!RecepitMonth.Value,InStr(Fields!RecepitMonth.Value,"].&[")+4,6)
2. MonthDiff – Because the Month IDs are in the form of 201001, they need to be turned into dates to calculate the number of months between them. The +1 will prevent division by zero in the next calculations.
=DateDiff("M",
CDate(RIGHT(Fields!ReceiptMonthID.Value,2)+"-01-"+LEFT(Fields!ReceiptMonthID.Value,4)),
CDate(RIGHT(Fields!Fiscal_Month_ID.Value,2)+"-01-"+LEFT(Fields!Fiscal_Month_ID.Value,4)))+1
3. ThreeMonths – This actually included 4 months, the current month plus the previous 3 months. It returns a 1 if the MonthDiff between 1 and 4, otherwise it returns a 0.
=CEILING(FLOOR(4/Fields!MonthDiff.Value)/(4/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value
I take the Floor of 4/MonthDiff to get a positive value if the MonthDiff <= 4. Then I divide it by (4/MonthDiff) to get a value of between 0 and 1. And I take the Ceiling of this to end up with either a 1 or a zero.
4. SixMonths – This includes the current month plus the previous 6 months. It returns a 1 if the MonthDiff between 1 and 6, otherwise it returns a 0.
=CEILING(FLOOR(7/Fields!MonthDiff.Value)/(7/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value
5. NineMonths – This includes the current month plus the previous 9 months. It returns a 1 if the MonthDiff between 1 and 9, otherwise it returns a 0.
=CEILING(FLOOR(10/Fields!MonthDiff.Value)/(10/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value
6. TwelveMonths – This includes the current month plus the previous 12 months. It returns a 1 if the MonthDiff between 1 and 12, otherwise it returns a 0.
=CEILING(FLOOR(13/Fields!MonthDiff.Value)/(13/Fields!MonthDiff.Value))*Fields!Inv_Dollars.Value
5. ADD A DATASET FILTER TO REMOVE FUTURE MONTHS
Right click on the Dataset and go to the Filters tab. Add this filter. Be sure to select the data type of Integer or you will get an error.
6. DO A BIT OF MATH IN THE REPORT TO USE THE CALCULATED FIELDS
0 –3 MONTHS =Sum(Fields!ThreeMonths.Value)
4 – 6 MONTHS =SUM(Fields!SixMonths.Value)-SUM(Fields!ThreeMonths.Value)
7 – 9 MONTHS =SUM(Fields!NineMonths.Value)-SUM(Fields!SixMonths.Value)
10 – 12 MONTHS =SUM(Fields!TwevleMonths.Value)-SUM(Fields!NineMonths.Value)
OLDER THAN 12 MONTHS =SUM(Fields!Inv_Dollars.Value)-SUM(Fields!TwevleMonths.Value)
This is just junk data, but you can get the idea of how the report slots the inventory into the right aging based on the MonthDiff.
Now hide the details to show the correct aggregates for your report.
This is not as complex as some situations, but it’s nice to be able to filter out values from your query and aggregate the results without having to use custom code for to do the aggregation for every value that has an Iif statement. I find this much cleaner, once you get the math right.