Dynamic Charts in the Workbook
This tutorial will give you a brief understanding of how to create Dynamic Charts in the Business Explorer Analyzer 7.1 Workbook
We use Business Explorer Analyzer to create workbooks for the queries which are created using query designer in SAP BI, Many times we would be asked to create Graphs/Charts as part of the requirement in the workbook, and we use excel graphs to create the same, but the range of the data or number of rows displayed in the Workbook will be dependent on the user input given in the selection screen, Generally we select the range of data for the graph is static but static selection of the range of data to the graph will not work in this case, therefore selecting the range of data to the graphic should be dynamic or should be selected during runtime, this can be achieved using excel functions to our workbook to create Dynamic graphs which selects the range of data automatically and graph gets adjusted accordingly.
Dynamic Graphs in Workbook with an Example.
Run the workbook, once the output of the report is displayed,
Goto Formulas-> Name Manager in the Formula Tab as shown in the below screen.
Define OFFSET value for rows , use the below formula
OFFSET(Tab name and starting point of the data cell ,row, column, Height , width)
Use COUNTA in place of Height ( COUNTA returns number of non empty data cells)
In the below example , A47 is the starting point of the data. And the Offset would be defined as below
Here IF condition is used to handle the excel error when there is no data.
Same way define OFFSET value for all Column :
Same way define OFFSET value for Column : %Spend
Now the OFFSETS are defined for all the columns as show below
After creating OFFSET values for all the columns in the Name manager, we can use them in our graph in that workbook
Drag the chart in the same tab or another tab and on the formula bar define the series
Click on the chart and paste the below formula (Calyrmon and Spend are OFFSET values which we defined earlier)
Then press ENTER
Then again click on the chart as shown below
Define second series on the formula bar
Then press ENTER
Change the type of the series two as Lines and chose it as Secondary Axis.
You can also make Labels of X, Y axis dynamic.
In the below screen shot the graph is dynamic which selects its range of data dynamically and the graph gets adjusted accordingly