on 11-14-2013 5:04 AM
Dear All Guru,
I have a report in which i need to show previous month and last 12 month data based on a UserResponse from a month selection.
Report Scenario Example:
User was ask to select [Successful Date] from the prompt which using 'BETWEEN' (From and To). Based on the selection, e.g
1 Nov 2013 to 31 Nov 2013.
Based on the selection above, i should show the result for previous month and last 12 months. Example
1. Previous Month = October 2013 to Nov 2013 result (1 October 2013 - 31 Nov 2013)
2. Last 12 month = Nov 2012 - Nov 2013 (1 Nov 2012 - 31 Nov 2013)
I am wondering:
1. How should i write the previous month and last 12 month variable based on UserResponse?
2. How do i filter it based on this variable
Regards,
Joe
Just happened to see this post. Late reply but still hopes it helps someone.
My approach was to use a formula field in webi
=[CumlMonth] Where (Year([CumlMonth])<Year(CurrentDate()) And Month([CumlMonth])="September")
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, I am working on SAP BO4.0.I have a requirement of creating a month filter on a date which will be between two ranges.The first range is the user response and the second range will be 6 months added to that user response.For eg:-If a user enters 1 as the response for month prompt then the date will be filtered between 1 and (1+6)=7.The database used is SAP HANA. Please let me know if further clarifications are required. Any help on this will be appreciated. Thanks in advance.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Guys,
I was struggling to the formula since last week and got the one.
I had a requirement to design a webi report which will show 13 months (in total) along with their values. Backend was BW/BEX and I got Calendar Year /Month object in yyyy.MM format with data of 6 years. I dragged Cal Year/Month object with Value on report panel. There were around 70 rows on a report block. To restrict it to show for only previous 12 months along with current Month, I followed below steps
Report requirement to show,
Mar 2013 | 200
Apr 2014 | 120
May 2014 | 120
.
.
.
.
Mar 2014 | 120
Follow the procedure to get this designed on your web intelligence report;
Create a Variable (Name it the way you want) and write a formula as is;
13 Months =((DaysBetween(ToDate([Cal. year / month] ;"yyyy.MM");ToDate((Max([Cal. year / month]) In Block);"yyyy.MM"))+1)/31)
And now apply a block filter as;
Right Click on your report block; select ADD FILTER option from the menu. Make sure you are in a MODIFY mode.
Click on Add Filter on upper right corner of window (which will be opened)
Select the Object (Created Variable) and select LESS THAN OR EQUAL TO as operator and set a value of 12.
Click OK...
And your report with Last 12 Months along with current month ll be ready.
Warm regards,
Chaitanya
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Yeat,
Please refer to below article from Dave which provides different type of logic to handle dynamic dates in Webi. These logic can be implemented in any version of Webi (3.x, 4.x).
http://www.dagira.com/category/design/dynamic-dates/
In case if you face any issue while implementing then let us know.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Hardik,
I read this before. But i am still struggling. Currently , i am trying to write a variable based on user selection for a start date and a end date. Based on the input, i will then need to generate the result for previous month.
Example:
if user select 1 nov 2013 to 30 Nov 2013.
I shall return a previous month result which is from 1st October 2013 to 30 Nov 2013.
I have try to create a variable But i am struggling for a week already.
=DaysBetween(Format(ToDate(UserResponse("Enter Value for Completed Date (Start):");""); "MM-dd-yyyy hh:mm:ss a");
(Format(ToDate(UserResponse("Enter Value for Completed Date (End):");""); "MM-dd-yyyy hh:mm:ss a"))
I got some error for this.
The expression or sub-expression at position 13 in the 'Days Between' function uses an invalid data type
Hi Joe,
You can utilize query-on-query/Results from Another Query option where :
For more info on Results from Another query:
Hi Joe,
I am not sure how would you achieve this at report level because you want the query to fetch data based on user response. So this user response function has to be defined at universe level in order to fetch required data.
Other option is that you fetch all the data from database in report, and based on user response and "RelativeDate()" function in webi, filter and show only required data.
Ok. I have done till the part the date between.
DaysBetween(ToDate(UserResponse("Enter Value for Completed Date (Start):");"MM-dd-yyyy");
ToDate(UserResponse("Enter Value for Completed Date (End):")); "MM-dd-yyyy"))
I would like to know, what is the next step i should do? I wonder how am i going to filter from previous month and Last 12 month? Do i need to create another new variable?
For this requirement here are the steps that will work..Although there is some negative performance implication.. you can do similar thing using input control as well..
1.Create an additional separate dummy query with with one date object only..
2. Create the prompt on that dummy query which will not affect your selection
3. Get a variable in report using userresponse() to capture the prompt value and get the last month of the data retrieved by the logic
4. Then create another variable to get the if date falls within the range and assign the records as 1 and if they are outside the range then 0
5.. Create a block level filter to show only records where the value is 1
If you face any challenges with this let me know, I have one more option to do the same thing which is more complex but will be better performing which I can explain..
Hi Durga,
I am trying different way now. I just prompt the user to select the Successful Date (Stat) and Successful Date (End)
However, I have a report requirement in which I need to schedule a report for the 1st day of the next month.
Thus, the user would like to see the below report:
1. Previous Month
2. Last 12 Month
Please guide me through this.
Regards,
Joseph
For the scheduling requirement I will suggest to do the magic date approach that Dave suggested here.. http://www.dagira.com/2008/07/21/using-a-magic-date-value-in-prompts/
This approach works pretty nicely for automatic scheduling requirement..
Hi Durga,
My requirement is also the same as Yeat, to get the last 13 months based on the user response.
But in the solution you have discussed above, you have referred some range. that is not clear to me.
Range Yeat has mentioned is something like this:
User was ask to select [Successful Date] from the prompt which using 'BETWEEN' (From and To).Based on the selection, e.g
1 Nov 2013 to 31 Nov 2013.
However in my case, the user has to select just a date only(not range using between) and based on that last 13 months should be displayed.
thanks
Vinod
Hi Hardik,
I have similar issue....
I need to display data based on user response. If user selects 201406 then I need to display data for 201403
prompt year_month: 201406(eg, it can be any year/month)
Current status (eg user input ) Previous status (based on user input - 3months i.e 201403)
0 0
1 1
Hi Hardik,
I have a requirement where i have to display only one year data in report at webi level.
For example:- One Year is May 2016 to June 2015 and once the month changes like june 2016 then the data in the report should show june 2016 to july 2015..
how can we achieve this at report level, there are no prompts . i have two Universe level object.
which i can use in creation of the variable.
Regards,
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.