Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
muthuram_shanmugavel2
Contributor
0 Kudos
3,749

This is my sample sales data.

Sales_amount by Product and Sold_on date.

Requirement is Report has to be created CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

So Expected sample report is,


This can be achievable by creating Restricted Measures. But Limitations are there.

Limitations:

If we create Restricted Measures - CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES withstatic values as condition,

Models has to be changed for every month.


Else If we create Restricted Measures - CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES with Input Parameters as condition,

User has to enter Current_Month and Previous_Month Value whenever he check the report.


How To Make Restricted Measures Calculation Dynamic?


So Restricted Measures - CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES has to be calculated dynamically based on report execution date.

How to make CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES value to be Dynamic?


This is the Steps I have followed to achieve this:

1. I created Calculation view with default node as Aggregation node. Then I included Projection node and connected it to Aggregation node.

    I added the "SALES_DATA" table into Projection node.

         

2. In Projection node, I have created one Calculated Column - "MONTH"

    "SOLD_ON" is DATE field. From this field, Month is calculated.

    

3. In Aggregation node, I have created two Restricted Measures - CURRENT_MONTH_SALES and PREVIOUS_MONTH_SALES

 

    

      In this Restricted Measures, Conditions will be dynamic and It is written in Expression editor.

    

     RM: CURRENT_MONTH_SALES:

          Restriction Condition: ("MONTH" = Int(midstr (string(now()), 6, 2)))

          Current_Month calculated using "midstr" and "now()" functions.

          Then Restriction condition is applied on "MONTH" Column.

        

    

     RM: CURRENT_MONTH_SALES:

        

          Restriction Condition: ("MONTH" = Int(midstr (string(now()), 6, 2))-1)

          Previous_Month calculated using "midstr" and "now()" functions and Substrcted with "1".

          Then Restriction condition is applied on "MONTH" Column.

         

   

4. Finally I created the report - CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES by PRODUCT wise.

    CURRENT_MONTH_SALES, PREVIOUS_MONTH_SALES would be calculated dynamically based on report execution date.

    No need to change the model for every month.

    

         

Best Regards,

Muthuram

1 Comment
Labels in this area