Skip to Content

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. 

1.JPG

2.JPG

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)))

2.9.JPG

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)))

3.JPG

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)))

4.JPG

Now the OFFSETS are defined for all the columns as show below

5.JPG

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

6.JPG

7.JPG

Change the type of the series two as Lines and chose it as Secondary Axis.

8.JPG

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

9.JPG

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Manohar Delampady

    Hi Sham kumar,

        Really good article, but if its just a matter of having a graph change with the number of records displayed in the underlying table then BEx has already taken care of it. I’ve not gone through the entire article word by word so if I’m assuming something here then I’m really sorry.

    I found it easier to check the “associated graph” corresponding to the table in table properties to do the trick. Please find the screenshot of the table properties attached.Grid properties.JPG

    Just updating this so that it helps anyone facing the same problem.

    Thanks,

    Manohar. D

    (0) 

Leave a Reply