- I have a reporting scenario where I need to do the trend analysis of a product’s defect ratio over a period of 36 months.
- The Product Defect ratio trend is to be shown for the next 36 month from the first sale of the product based on the date input by the user
- For e.g. the user enters date Apr 2012 and the first sale of product is on Apr 2013 then we need to show the trend for next 36 months starting from Apr 2013 We need to show this data in a crosstab and line chart. The report should have a section on Product , to show data each product wise and line chart will be showing data for the all the products over 36 month period where distinct colour will represent each product.
- Database 1 : Sales data (BW query)
- Database 2 : Defect Data (SQL server)
- Both the databases have product number , Month year and year dimension in common Objects Used in the report
Objects Used in the report
Objects Type Source Formula (if Variable) Model Number Dimension BW Month Year Dimension BW Sales Measure BW Cumulative Sum Measure Variable Runningsum(Sales) Defect Measure SQL Cumulative Defect Measure Measure Runningsum(defect)
Now If a user selects a product and date i.e. Product” Temp” and date i.e Apr 2013.
- The first sale of the product is on august 2013 as shown above but the sale of product is stopped after September 2014, we still want to show the trend till next 36 months from the first sale of the product .
- The Data for the product “ temp” is not there in the database after September 2014, we need to generate the subsequent months after that to show total of 36 months starting from the first sale of the product
- after the user input fromdate prompt.
- We also need to generate the missing months between the period which is done using Timedim() function as shown above.
- For eg if the first sale of the product is at August 2013, we need to show the trend till next 36 months even if the sale actually stopped at septemeber 2014 as we are doing a cumulation of sales and defects and we want to see product performance over a period of 36 months.
- But the 36 months lifecycle should not exceed current month i.e if user want to see the trend of a product sold in Jan 2016, we have only have to show till current month i.e for 10 months but it could be possible that sales of that product stopped at mar 2016.
The data for full 36 months is not there in the DB, is there any way to generate the subsequent months for each product based on its first sale month?