Creating a Dashboard with “From” and “To” month combos
Requirement and issues
- A component such as a chart or a table (referred to from now on as “the affected component”) must be filtered using two combo boxes representing “From” and “To” months.
- The platform used is Dashboards sp02 over BW using SAP Netweaver BW connections built in the Dashboards Data Manager.
- The months in the “To” combo must be greater than the selected month in the “From” combo.
- For the year 2010, month values returned have formatting problems (1.201 instead of 01.2010)
- Refreshing the affected component according to the cell containing the calculated “from-to” range results in a bex error regarding a mandatory variable that was not filled.
- Use “insert selected value” from the combos to get the selected “from” and “to” values.
- Use a BEX user exit variable in order to create a query that returns all the months that are greater than a given month.
- Use an excel formula to fix the problem with 2010 values.
- Refresh the affected component according to the change of the “To” value rather than a change in the range to avoid a BEX error.
The Months-To query
The first order of business is to create a query that returns all the months that are greater than a given month. We do this by using an input variable to get the given month on a selection within the key figure structure of the query. We then calculate the “greater than” restriction in a user-exit variable in the characteristic restrictions for the query.
- Create a query on the source
data with 0calmonth in the rows and two hidden selections in the columns.
- In one of the selections in the key figure structure,
use a month selection variable to filter its value.
3. Then create a user-exit variable for 0calmonth and use it in the characteristics restrictions panel for the query . The code for the variable is presented below.
IF I_STEP <> 2.
L_S_RANGE–OPT = ‘GE’.
L_S_RANGE–SIGN = ‘I’.
LOOP AT I_T_VAR_RANGE INTO W_T_VAR_RANGE.
L_S_RANGE–LOW = W_T_VAR_RANGE–LOW.
APPEND L_S_RANGE TO E_T_RANGE.
Linking the data and spreadsheet calculations
Create data sources for the “from” query, the “to” query and the query for the affected component. Link the result (“Cross-Tab data”) for each query to a place on the embedded spreadsheet.
Once you’ve linked the data to the spreadsheet, use the combos’ “insert selected item” function to put the selected value in a cell in the spreadsheet (I usually use the cell above the results, and color it).
You might encounter a problem with the year 2010, showing up as “1.201” instead of “01.2010”. in order to fix this issue, use the following excel formula, where L3 is the cell into which the data was inserted.
You might also want to hide the “0” value with an additional formula:
Make sure to link the final calculated value for the result of the “from” query to a variable in the data connection of the “to” query:
Getting the final value for the months range (e.g. 01.2010 – 02.2010) , where P3 is the calculated from value and Q3 is the calculated to value:
=IF(P3=””,””,IF(Q3=””,P3&” – “&P3,P3&” – “&Q3))
Where P3 is the cell with the calculated from value and Q3 is the cell with the calculated to value.
Link the cell with final calculated value for the months range to a variable in the data connection of the affected component.
However, DO NOT use the cell with the range as a trigger cell for refreshing that data connection. Instead, use the cell with the calculated “TO” value as the trigger (in this example, Q3).
This may sound counter-intuitive, but at least in my case using the cell with the range created a BEX error regarding a missing value for a mandatory variable (while none of the variables are, in fact, mandatory). This will also work because any change to the “from” value will automatically refresh the “to” value as well. As a bonus, this creates a nifty effect where the range between the selected values in the “from” and “to” combos is always kept the same (that is, 01.2009 – 12.2009 becomes 01.2010 – 12.2010 when you change the from date).