How to get data based on calmonth on the input of Fiscal year period(Real time Scenario)
Real time Scenario:
In one of our Major KPI project where we were integrating the KPI’s from different modules like SD,MM,FI,QM,PP and of course inventory as well into WebI.
In our final WebI report the input was based on Fiscal year period and at very later stage we realized that our inventory cube does not have data based on fiscal year period rather it is based on calmonth.As it was inventory cube and we did not want to mess up with it because of huge data and for the fact it was inventory cube.Options like Remodeling was ruled out for the same reason. So we thought of some workaround without disturbing the inventory cube.I would not be doing justice to this document if i do not give credit to my colleague ASHUTOSH SINGH .
Approach:
We initially thought to create a Multiprovider combining inventory cube and 0FISCPER Infoobject but as 0FISCPER is not an infoprovider so multiprovder did not allow us to add that.Eventually we came up with an idea to create a dummy DSO and in it add the 0FISCPER Infoobject and some keyfigure to complete its definition.We did the characteristic assignment for necessary objects required to develop the report and also for 0FISCPER from dummy DSO.
Data Flow Display:
At BEx Level:
- Take any Keyfigure and drag and drop 0FISCPER–restrict with user input variable–Single value.This will act as dummy keyfigure which will facilitate the user with the input based on fiscal year period.Refer the screenshot
- Now make the copy of same KF and drag and drop 0CALMONTH–Restrict with customer exit variable.This Keyfigure will finally be used in report.
Refer the Screenshot.
CMOD Code:
******************Code for webi to get calmonth based on Fiscper input*******************
DATA: zcalyear type /BI0/OICALYEAR,
MM(2) type n,
ZMONTH type /BI0/OICALMONTH.
when ‘ZVAR_MONTH_CMOD’. —–Customer Exit variable
READ TABLE i_t_var_range INTO l_t_var_range WITH KEY vnam = ‘ZFPER_STEP2’. —–User input variable based on Fiscal year period
IF sy–subrc = 0.
clear l_s_range.
zcalyear = l_t_var_range–low+0(4).
MM = l_t_var_range–low+5(2).
CONCATENATE zcalyear mm into ZMONTH.
L_S_RANGE–LOW = ZMONTH.
L_S_RANGE–SIGN = ‘I’.
L_S_RANGE–OPT = ‘EQ’.
APPEND L_S_RANGE TO E_T_RANGE.
ENDIF.
Conclusion:
After following the above approach we got that data based on calmonth on the input of Fiscal year period that too without disturbing the existing inventory cube.
You rock ... Helpfull scenario ...thanks for sharing...
Rated n Bookmarked...
Regards
KP
Thanks Prashant for your wonderful comment.much appreciated.
Regards,
AL
Really useful article and handy one. Thanks for sharing Anshu.
Thanks Raman for your feedback.Really appreciated
Regards,
AL
Well thanks for the credits man . Indeed it was a bit tricky . But eventually we came up with a simple 100% workable solution.
To add here . Our WEBi report contains almost 30 BEx queries . And out of 30, 29 were running on 0FISCPER. Only this query was running on 0CALMONTH . So it was important for us to somehow get the input on 0FISCPER.
And to add very well documented too. I am sure it'll be helpful to other guys here . Because its a real time scenario.
Regards,
Ashutosh
Yes indeed it was tricky solution and thanks for giving more insight on the background of our project and on the need of fiscper.
Your valuable support on this is much appreciated.
Regards,
AL
Very Good information...
Thanks Ganesh for your feedback.
Regards,
AL
Informative ℹ and Very Well Documented 🙂 🙂 ,
Thanks For Making & sharing 😎 😎 .
Regards,
Seshu.P
Thanks Seshu for your nice feedback with all those lovely smileys.. 🙂 .
Regards,
AL
Very nice posting.
Thank you Anshu.
I am glad that you liked it..
Regards,
AL
Very Help document Anshu.
Thanks Manna for your feedback.
Regards,
AL
Very nice help anshu 🙂
Regards,
SM
Thanks Satendra Mishra for reading and commenting on my blog
Regards,
AL
Very good post Anshu. Its very helpful for fiscal to cal and viceversa. Thanks for sharing.
Regards
Venkat..
Thanks Venkateswarlu Ravula for your feedback.Yes this approach might come handy in such scenarios.
Regards,
AL
Hi Anshu,
We have a similar kind of scenario. It would be great if you give some inputs. Could you pls check the URL "HOW TO GET FISCAL PERIOD WISE DATA BY GIVING FISCAL YEAR AS INPUT
Best Regards
Venkat...
Hi Anshu,
Seems to be nice code. 🙂 . Thanks for sharing. Why didn't you post this on BEx?
Regards,
Suman
Hi suman,
Thanks for your feedback..The tricky part here is modeling approach that we used so according to me 80% its modeling technique and 20% BEx.I felt BW is a better place for it with a tag of BEx as well.
Regards,
AL
Nice trick you applied. Wouldn't it also have worked if you just added 0fiscper to the cube and MP without reloading it?
Martin
Hi Martin,
Thanks for your comments.I have already stated in my Blog that we were not supposed to play around with inventory cube so had to come up with different approach.
We have n number of MP's on this cube so enhancing the cube means deactivation of MP's and update rules and then activating them again would have become a cumbersome task.
To avoid all this we followed this smart approach and shared with all.
Regards,
AL
Very good solution. You always rock. Keep it up!
Thanks for your rocking comment.. 🙂
Regards,
AL
Hi Anshu,
Simply superb... 🙂
Regards,
Krishna Chaitanya.
Thanks Krishna for your nice feedback.
Regards,
AL
Hi Anshu,
This is Awesome. Good thinking.
Regards,
Karan
Thanks Karan for your wonderful feedback.Much appreciated.
Regards,
AL
Hi Anshu..... It is great that always you are taking some real time scenario and providing blogs and the same is helping to lot to solve their problems as well.
With many creative/live expectations - Kumar.
Yes sunny,i always try to share something meaningful which might come handy for others in their projects and assignments.
Thanks for your feedback..Much appreciated.
Regards,
AL
Hi Anshu,
I lied this blog for two reasons. First is that you have clearly explained how something can be done and the second is you have given us the background of why that particular solution was thought of in the first place. This gives anyone who is reading this blog a good perspective.
Regards,
Benedict
Hi Vasanth,
Glad that you liked my blog.Thanks for your feedback and i appreciate that you got the whole idea of this blog correctly.
Good luck for your new project..Hope you do well.
Regards,
AL
Very useful doc..well explained Anshu Lilhori
Thanks Farid i appreciate your feeback.
Regards,
AL
Very usefl info 🙂
Thanks Kavitha for your feedback..
Regards,
AL
Such a nice and informative document Anshu.
Very well explained by taking Real Time Scenario.
Regards,
Priyanka
Thanks Priyanka for your wonderful feedback..I am glad that you liked the content.
Regards,
AL
Thanks Anshu for this very useful document 🙂
Amine
Thanks Amine for your valuable feedback..Much appreicated.
Regards,
AL
Nice Work Anshu.
Regards,
Sushant
Thanks Sushant for your nice feeback..
Regards,
AL
Useful information!!
Wonderful document Anshu great work!!!! 😉
Thanks Thejas Krishnamoorthi & Rithika Kameshhwaren for your wonderful feedback.
Regards,
AL
This Blog shows how passionate Anshu Lilhori is towards BW ....
Thanks Vijay for such a nice feedback...This really matters to me.. 🙂
Regards,
AL
This blog demonstrates that how simply and concisely you can share such widely used scenarios. Thanks Anshu Lilhori
Hello Kush Kashyap,
It feels great to receive such a nice feedback.Thanks for all your your kind words.
Regards,
AL
Hi,
Very usefull information
Thanks Puja for your feedback..
Regards,
AL
Hi AL,
Nice one..Well documented.
I had worked on these kind of scenario's previously with a different approach.
Create Text Variable ,Name that as Fiscper use this one as a prompt, read in CMOD as you did. This might save you backend work.
Thanks,
SB.
Helpful solution . Keep rocking "Rockstar".
Hi Venkatesh,
Thanks for your rocking comments and that title really elates me.. 🙂
Regards,
AL
Hi Anushu,
Very useful one 🙂
Thanks Purushothama Reddy.
Regards,
AL
Hi Anshu,
Thanks for sharing such a nice real time example. Good job 🙂 .
I have a query:
For the months of January to March, FISCAL YEAR would be lesser by 1 than CALYEAR.
Pls help me to understand how this code will work in that case.
Regards,
Nitesh
Thanks Nitesh Kumar for your comments..In my case the variant was K4 so in that the relation between month and fiscal year period is one to one.Lets say for eg:
If months is 01.2012 then fisper will be 001.2012.So basically it depends on the fiscal year variant,based on that we can change the code.
Hope this clarifies.
Regards,
AL
HI Anshu,
Thanks for the reply.
Got the complete story now. Thanks 🙂 .
Regards,
Nitesh
Glad to know that..
Regards,
AL
Hi Anshu,
Thanks for sharing Real time example..
Very helpful.. 🙂 🙂
Thanks Lalit Peswani for your feedback..
Regards,
AL
Great Document AL......
Regards
Shyamala
Thanks Shyamala Kalluri for your feedback..
Regards,
AL
Thanks for sharing this Anshu.
Thanks for stopping by and reading my blog.
Regards,
AL
really useful in my project
Hi AL,
Recently while seeing your document i had handson exp our project.
anyway bookmarked 🙂 🙂 🙂
Thanks,
Phani.
Good to know that phani.. 🙂
Wow, thats really a smart trick Anshu Lilhori
Thanks for sharing,
LS
I believe tricky solutions are part and parcel of BW projects.. 😉
Regards,
AL
Hi,
its very good one doc.
Thanks for sharing. 🙂 🙂
Regards
Wasen
I am happy that you liked it..
Regards,
AL
Hi Anshu,
It is what I was looking for and it is good inform you that performed your suggested steps for the same.......:-)
Appreciate your help.
Great to know that this blog helped you in your requirement as well.. 🙂
Regards,
AL
Very nice blog Anshu. Thanks for sharing yours and Ashutosh efforts with us. Such a nice approach you guys have applied. Really helpful.
Keep Sharing.
Regards,
Abdullah
Thanks Abdullah for spending some time in reading my blog..Much appreciated.
Hi Anshu,
Very helpfull document.Thanks for sharing it to enhance our knowledge. 🙂
Keep posting.
Regards,
Lalit
Thanks Lalit for your feedback and am happy to know that its an add on to your knowledge..
Regards,
AL
Simply superb blog.
Thanks for sharing buddy.. 🙂
Regards,
Poorna
Good one,Keep it up.
Thanks Poorna and Giri..For you nice feedback..Much appreciated.
Regards,
Al