Hello, Listers! I hope someone can help me out with this!!! I am a BO4.1 developer, creating reports for non-technical BO users who need to see reports by both Policy_Year (say, Y1) and separately by Event_Year (say, Y2). Each Year references a different dimension object within our star schema. Users only want to see data with the year ranges that they specify for each year type at run time. Currently, I have one query (one SQL statement) referenced by 2 reports/tabs that are identical except the first tab has sorts/breaks/etc on Y1 and the second tab on Y2. These are the variable conditions in that query:
Y1 Between Prompt(‘Y1 range start’) And Prompt(‘Y1 range end’) OR
Y2 Between Prompt(‘Y2 range start’) And Prompt(‘Y2 range end’)
So, JoeUser runs this report; inputs 1990 and 1995 for Y1; 1980 and 1985 for Y2. He can then view data by Y1 in tab one or by Y2 in tab two. This has been working fine. Problem is, this means that a single data row/record having Y1=1990 AND Y2=1970 would also be returned in the data set, such that 1970 would show up on the second report tab, even though the user does not expect it, based on his input at run time.
I’d like to avoid forcing users to create additional year filters, after already inputting year parameters. I’ve played with the UserResponse() function, but haven’t gotten it to work. I am not familiar with a lot of these functions and the FunctionHelp button is not very helpful. My temporary solution has been to split the query into 2 separate sql statements, one for Y1 and the other for Y2. However, this decreases performance, increases report creation time, and makes maintenance very messy! I have 100+ reports to create in similar fashion, and need to find a more elegant solution.
Any help would be GREATLY appreciated!!! Thanks in advance.
Anne D. Choi
Braun Technology Group
30 West Monroe, Suite 300
Chicago, IL 60603
Hello, Listers! I hope someone can help me out with this!!! I am a BO4.1 developer, creating reports for non- technical BO users who need to see reports by both Policy_Year ( say, Y1) and separately by Event_Year (say, Y2). …
These are the variable conditions in that query:
Y1 Between Prompt(‘Y1 range start’) And Prompt(‘Y1 range end’) OR
Y2 Between Prompt(‘Y2 range start’) And Prompt(‘Y2 range end’)
So, JoeUser runs this report; inputs 1990 and 1995 for Y1; 1980 and 1985 for Y2. He can then view data by Y1 in tab one or by Y2 in tab two. This has been working fine. Problem is, this means that a single data row/record having Y1=1990 AND Y2=1970 would also be returned in the data set, such that 1970 would show up on the second report tab, even though the user does not expect it, based on his input at run time. …
Anne,
One thing you can do is define a filter on each report that is based on the UserResponse variable. Via the userresponse variable you can get all four years that the user enters at query time. By then defining either a global filter on the report or a table level filter, you can then create filter the report based on those values at run time. In your case, select the table from report2 and select format->Filters. Select the variable corresponding to Year2 and press define. Then enter the following filter:
= Between (UserResponse(“DataProviderName” ,“Y2BeginPrompt”),
UserResponse(“DataProviderName” ,“Y2EndPrompt”))
where DataProvidername is the name of your data provider and Y2BeginPrompt, Y2EndPrompt are the text of your user prompts for the years. You can find the dat provider name from the Data->View menu if you do not know it.
The end result of this will be a query that returns all the rows and dynamically filters each report based on the variables the user wanted, exactly what you need.
Bob, thanks for the tips on UserResponse(). An additional question: although this works in terms of display, it does not limit the rows of returned data which hampers execution time by simply returning everything, THEN filtering just within the report display. Is there a way to create a report variable that works with the where clause prompts already in place?
Bob Molby wrote:
In your case, select the table from report2 and select format->Filters. Select the variable corresponding to Year2 and press define. Then enter the following filter: = Between (UserResponse(“DataProviderName”, “Y2BeginPrompt”), UserResponse(“DataProviderName” ,“Y2EndPrompt”)) where DataProvidername is the name of your data provider and Y2BeginPrompt, Y2EndPrompt are the text of your user prompts for the years. You can find the dat provider name from the Data->View menu if you do not know it. The end result of this will be a query that returns all the rows and dynamically filters each report based on the variables the user wanted, exactly what you need.
Anne D. Choi wrote:
Currently, I have one query (one SQL statement) referenced by 2 reports/tabs that are identical except the first tab has sorts/breaks/etc on Y1 and the second tab on Y2. These are the variable conditions in that query: Y1 Between Prompt(‘Y1 range start’) And Prompt(‘Y1 range end’) OR
Y2 Between Prompt(‘Y2 range start’) And Prompt(‘Y2 range end’) So, JoeUser runs this report; inputs 1990 and 1995 for Y1; 1980 and 1985 for Y2. He can then view data by Y1 in tab one or by Y2 in tab two. This has been working fine. Problem is, this means that a single data row/record having Y1=1990 AND Y2=1970 would also be returned in the data set, such that 1970 would show up on the second report tab, even though the user does not expect it, based on his input at run time.