Getting pop-up message in Webi report
My requirement is something like this. There is a date prompt on the report. When the date entered is either 1/1/2014 or 12/31/2013, then there has to be an message in front end Webi report saying no data to retrieve even though something comes up from backend.
Is it possible to achieve this.
BI 4.1 SP5
Back end is BW 7.4
We have 2 data providers to get current and previous year data.
Mahboob Mohammed replied
I've never used BW as my reporting database, so I don't know how the Userresponse function works using a BW. Let me still answer your question assuming that Userresponse function works the same way.
Create the below 4 variables - update the prompt text "Enter Reporting Date" with the text you have in the prompt for selecting a date.
- v_SelectedDate =ToDate(UserResponse("Enter Reporting Date");"")
This gives you the date selected in prompt.
- v_Date1 =ToDate("1/1/2014";"M/d/yyyy")
This is the first date for which you want to show "No data to retrieve" message.
- v_Date2 =ToDate("12/31/2013";"M/d/yyyy")
This is the second date for which you want to show "No data to retrieve" message.
- v_NoDataToRetrieve = If([v_SelectedDate]=[v_Date1] Or [v_SelectedDate]=[v_Date2]) Then "No data to retrieve" Else "Hide"
This is a formula to show the text "No data to retrieve" when those 2 dates are selected in prompt.
After creating these objects, insert a blank cell in the report (from Report Elements -> Cell -> Blank).
- Drag the last variable =[v_NoDataToRetrieve] in the cell, update the format of that cell to show white color by Right-clicking on the cell -> Format Cell -> Appearance -> Color -> change from No Color to the option to select a color and select White color from the drop down list.
- Bring the cell to front by Right-clicking on the cell -> Order -> Bring to front
- Increase the size of the cell to as big as the page is say, width 40cm and height 40cm, by Right--clicking on the cell -> Format cell -> General -> set Width to 40cm and Height to 40cm
- In the same Format cell window, update Layout as Relative Position -> Horizontal -> 0cm from Left of Report and Vertical 0cm from Top of Report
- Increase the font size of the text in the cell to 44
- Finally, in the Format cell window, go to General -> in the last option on bottom right, check the radio button for Hide when following formula is true and write the formula as =v_NoDataToRetrieve="Hide"
- Testing: Now when you refresh the report and select 1/1/2014 or 12/31/2013, you'll see that "No data to retrieve" message in front even though there is data in the report.
Voila, you got it. Let me know if this helped.
Oops, I missed a part of your question that you've 2 data providers. Keeping that in mind, you'll need 2 variables in place of the 1st one that I have. So, create the 5 variables that I have (in red) and rest of the steps are same.
- v_SelectedDateCurrYr (use the exact prompt text that you see)
=ToDate(UserResponse("Enter Reporting Date Curr Yr");"")
- v_SelectedDatePrYr (use the exact prompt text that you see)
=ToDate(UserResponse("Enter Reporting Date Pr Yr");"")
And then, update the v_NoDataToRetrieve as
= If([v_SelectedDateCurrYr]=[v_Date1] Or [v_SelectedDateCurrYr]=[v_Date2] or [v_SelectedDatePrYr]=[v_Date1] Or [v_SelectedDatePrYr]=[v_Date2])Then "No data to retrieve" Else "Hide"
What this does is, doesn't matter if you choose Current Year or Prior Year date from those 2 dates, it'll show you "No data to retrieve."