Dynamic Charts in the Workbook

[Former Member]

Dynamic Charts in the Workbook

Summary

This tutorial will give you a brief understanding of how to create Dynamic Charts in the Business Explorer Analyzer 7.1 Workbook

Overview:

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.

=OFFSET(Table!$A$47,0,0,IF(COUNTA(Table!$A$47:$A$1000)=0,1, COUNTA(Table!$A$47:$A$1000)))

Same way define OFFSET  value for all Column :

Name :Spend

=OFFSET(Table!$B$47,0,0,IF(COUNTA(Table!$B$47:$B$1000)=0,1, (COUNTA(Table!$B$47:$B$1000)))

Same way define OFFSET  value for Column : %Spend

=OFFSET(Table!$C$47,0,0,IF(COUNTA(Table!$C$47:$C$1000)=0,1, COUNTA(Table!$C$47:$C$1000)))

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

Series one

Click on the chart and paste the below formula (Calyrmon and Spend are OFFSET values which we defined earlier)

=SERIES(,Table!Calyrmon,Table!Spend,47)

Then press ENTER

Series Two:

Then again click on the chart as shown below

Define second series on the formula bar

=SERIES(,Table!Calyrmon,Table!PerSpend,47)

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.

=Table!Calyrmon

=Table!PerSpend

In the below screen shot the graph is dynamic which selects its range of data dynamically and the graph gets adjusted accordingly