Say you need to analyze data on a customer level. A simple Web application report shows several aspects of the customer but you want to navigate easily between customers without having to select a different customer each time.

The following simple business scenario explains how to accomplish that using XML functionality within BEx Web Application Designer (WAD) 7.0. My scenario provides a basic understanding of JavaScript inside WAD 7.0 and the use of XML Path Language (XPath) to read XML content. You can embed Xpath in a script object. You predefine the list of customers via a basic parameters screen. 

For the demonstration I created a basic InfoCube with sales and revenue data by country, customer, and material. My main goal is to have a Web template with two buttons:

• Next: move to the next customer within the preselected country
• Previous: move to the previous customer within the preselected country

The WAD also contains an analysis table with revenue data by material and a pie chart with sales data by material.

The first step is to create the two basic queries, revenue by material and sales by material. Both should have a filter over Country with the same variant. In my sample it is called Vcountry. In addition, I created another query to use for the list of customers. I have called it CUSTOMER_LIST_BY_COUNTRY. It is a simple query with the customer on the rows and the same filter with the same variable on Country.

Once the queries are done, you move to WAD to create the template.

In the template, you first need to create three data providers over the three predefined queries.

In my sample I use the following data providers:

DP_CUSTOMERS_LIST

DP_CUST_SALES

DP_CUST_REV

Afterwards I created a container layout with an Analysis item in the first row and a Chart item in the second row over the two sales queries. The container is hidden by default. Otherwise it shows aggregated data of all customers. I will make it visible on the first run.

A simple info field item holds the customer name on the top of the template. A Button group item holds the next and previous buttons (BUTTON_GROUP_1). The main goal of my business scenario is going to be accomplished with the Data provider – Information item found in the miscellaneous section of the Web items, as shown in Figure 1. For my example I named it DATA_PROVIDER_LIST_ DP_CUSTOMERS_LIST:.

Figure 1

Drag the Data provider – Information item and add it to the model. After you drag it the information item looks like the xml button shown here.

That item adds the query information or the query result set in an XML format to the end of the HTML page of the report. You read that information later on for the navigation buttons. The item first needs to be connected to a data provider. In my example I connected it to my third query with the list of the Customers. It has two simple properties (Figure 2).

Figure 2

Properties screen

Output Navigation Status means that the XML contains data from your navigation such as filters or variable values. Output Result Data means that the XML contains the result set of the query. In my example, I checked the Output Result Data check box to get the list of the customers in the XML. Once that is done, you create the JavaScript code with the Script Web item. Before diving into the code, here is an explanation of a few JavaScript techniques.  

1. JavaScript variables: I use them to hold the customer index number while navigating back and forth in the list. The variables value is saved on the global level of the portal and not on the local level of the template because I don't want it to be refreshed during regular BW navigation steps. Write it as window.top.VariableName, (in my case I have three variables):

1.  window.top.NextCust
2. window.top.PrevCust
3. window.top.First (used to check if you are on the first load)

2. To search within the XML file, use Xpath. First you need to load the XML content to a local variable, docSearch in my example. You use the following code to load it:

var docSearch=new ActiveXObject("Microsoft.XMLDOM");

docSearch.async="false"; docSearch.loadXML(document.getElementById('DATA_PROVIDER_LIST').innerHTML);

Then you search within the XML for the number of rows to alert on the last row.

var numOfNodes = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/@size")[0].text;

 if (window.top.NextCust > numOfNodes )

{ alert('Last record') };

And last, you search for the next/previous customer with the help of global variables:

(window.top.NextCust and window.top.PrevCust).var SearchCust = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/TUPLE/ELEMENT/MEMBER/@name")[window.top.NextCust].text;

Or you could use this code to search:

var SearchCust = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/TUPLE/ELEMENT/MEMBER/@name")[window.top.PrevCust].text;

The code is going to have three JavaScript functions: 

1. function GetNextCust() is activated from the Next button within BUTTON_GROUP_1
2. function GetPrevCust() is activated from the Previous button within BUTTON_GROUP_1
3. function FilterData( aaa ) is called from within the other functions (the Next and Previous buttons) to filter the data according to the Next/Previous customer. aaa is an internal parameter that holds the customer ID.

The following code gets the next customer from the query and transfers it to the FilterData function to filter on it:

function GetNextCust()

{

// Check if we are on the first load

            if (window.top.NextCust == null)

            {window.top.NextCust=0}

            else

            { window.top.NextCust=window.top.NextCust+1;

                        window.top.PrevCust=window.top.NextCust;}

// Load XML content to docSearch variable

                        var docSearch=new ActiveXObject("Microsoft.XMLDOM");

                        docSearch.async="false";

                        docSearch.loadXML(document.getElementById('DATA_PROVIDER_LIST').innerHTML);

// Check number of customers to alert on the last row

                        var numOfNodes = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/@size")[0].text;

                        if (window.top.NextCust > numOfNodes )

                        {

                                    alert('Last record');

                                    window.top.NextCust = window.top.NextCust-1;

                                    window.top.PrevCust=window.top.NextCust;

                        }

                        else

                        {       

// Find the next customer                                              

                                    var SearchCust = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/TUPLE/ELEMENT/MEMBER/@name")[window.top.NextCust].text;

// Call FilterData fuction and transfer the customer ID.

                                    FilterData(SearchCust );

                        }

}

The following code gets the previous customer from the query and transfers it to the FilterData function to filter on it:

function GetPrevCust()

{

// Alert if we are on the first customer

            if (window.top.PrevCust == 0 || window.top.PrevCust == null)

            {alert("First record");

            return;}

            else

            { window.top.PrevCust=window.top.PrevCust-1;

                        window.top.NextCust =window.top.PrevCust;}

// Load XML content to docSearch variable

                        var docSearch=new ActiveXObject("Microsoft.XMLDOM");

                        docSearch.async="false";

                        docSearch.loadXML(document.getElementById('DATA_PROVIDER_LIST').innerHTML);

// Find the previous customer

                        var SearchCust = docSearch.selectNodes("/BICS_VIEW/RESULT_SET/AXES/AXIS/TUPLES/TUPLE/ELEMENT/MEMBER/@name")[window.top.PrevCust].text;

                        FilterData(SearchCust );

}

function FilterData( aaa ){

            var commandSequence = new sapbi_CommandSequence();

// Filter characteristic TESTCUST by 'aaa' customer

            var commandSET_SELECTION_STATE_SIMPLE_1 = new sapbi_Command( "SET_SELECTION_STATE_SIMPLE" );

            var paramTARGET_DATA_PROVIDER_REF_LIST = new sapbi_Parameter( "TARGET_DATA_PROVIDER_REF_LIST", "" );

            var paramListTARGET_DATA_PROVIDER_REF_LIST = new sapbi_ParameterList();

            var paramTARGET_DATA_PROVIDER_REF1 = new sapbi_Parameter( "TARGET_DATA_PROVIDER_REF", "DP_CUST_SALES" );

            paramListTARGET_DATA_PROVIDER_REF_LIST.setParameter( paramTARGET_DATA_PROVIDER_REF1, 1 );

            var paramTARGET_DATA_PROVIDER_REF2 = new sapbi_Parameter( "TARGET_DATA_PROVIDER_REF", "DP_CUST_REV" );

            paramListTARGET_DATA_PROVIDER_REF_LIST.setParameter( paramTARGET_DATA_PROVIDER_REF2, 2 );

            paramTARGET_DATA_PROVIDER_REF_LIST.setChildList( paramListTARGET_DATA_PROVIDER_REF_LIST );

            commandSET_SELECTION_STATE_SIMPLE_1.addParameter( paramTARGET_DATA_PROVIDER_REF_LIST );

            var paramCHARACTERISTIC = new sapbi_Parameter( "CHARACTERISTIC", "TESTCUST" );

            commandSET_SELECTION_STATE_SIMPLE_1.addParameter( paramCHARACTERISTIC );

            var paramRANGE_SELECTION_OPERATOR = new sapbi_Parameter( "RANGE_SELECTION_OPERATOR", "EQUAL_SELECTION" );

            var paramListRANGE_SELECTION_OPERATOR = new sapbi_ParameterList();

            var paramEQUAL_SELECTION = new sapbi_Parameter( "EQUAL_SELECTION", "MEMBER_NAME" );

            var paramListEQUAL_SELECTION = new sapbi_ParameterList();

            var paramMEMBER_NAME = new sapbi_Parameter( "MEMBER_NAME", aaa );

            paramListEQUAL_SELECTION.addParameter( paramMEMBER_NAME );

            paramEQUAL_SELECTION.setChildList( paramListEQUAL_SELECTION );

            paramListRANGE_SELECTION_OPERATOR.addParameter( paramEQUAL_SELECTION );

            paramRANGE_SELECTION_OPERATOR.setChildList( paramListRANGE_SELECTION_OPERATOR );

            commandSET_SELECTION_STATE_SIMPLE_1.addParameter( paramRANGE_SELECTION_OPERATOR );

            commandSequence.addCommand( commandSET_SELECTION_STATE_SIMPLE_1 );

//Display report on the first run

if (window.top.first == null)

            {

                        var commandSET_ITEM_PARAMETERS_1 = new sapbi_Command( "SET_ITEM_PARAMETERS" );

                        var paramITEM_TYPE = new sapbi_Parameter( "ITEM_TYPE", "CONTAINER_LAYOUT_ITEM" );commandSET_ITEM_PARAMETERS_1.addParameter( paramITEM_TYPE );

                        var paramINIT_PARAMETERS = new sapbi_Parameter( "INIT_PARAMETERS" );

                        var paramListINIT_PARAMETERS = new sapbi_ParameterList();commandSET_ITEM_PARAMETERS_1.addParameter( paramINIT_PARAMETERS );

                        var paramVISIBILITY = new sapbi_Parameter( "VISIBILITY", "VISIBLE" );

                        paramListINIT_PARAMETERS.addParameter( paramVISIBILITY );

                        paramINIT_PARAMETERS.setChildList( paramListINIT_PARAMETERS );

                        var paramTARGET_ITEM_REF = new sapbi_Parameter( "TARGET_ITEM_REF", "CONTAINER_LAYOUT_ITEM_1" );

                        commandSET_ITEM_PARAMETERS_1.addParameter( paramTARGET_ITEM_REF );

                        commandSequence.addCommand( commandSET_ITEM_PARAMETERS_1 );

            window.top.first = 1;

            }

            //Send the command sequence to the server     

    return sapbi_page.sendCommand( commandSequence );

}

Now you connect the functions to the buttons. Go to the properties of each button and in the Action section select Script Function (Figure 3). Afterwards in the Script function itself (one row lower) you can select the function from the list (Figure 4).

Figure 3

Select Script Function

Figure 4

Select the function

Be aware that the function list is available only after you create the Script item correctly.

When executing the report you first get the variable screen (Figure 5). I chose Belgium for my example. Figure 6 shows the final view of the report.

Figure 5

The regular query variable screen

Figure 6

Final report with Next and Previous buttons added

Shlomi Weiss

Shlomi Weiss is CTO at BICS Israel LTD. He has 10 years of experience in classic BI and SAP NetWeaver BW. BICS Israel LTD is a niche BI consulting services company that provides a full spectrum of integrated enterprise information management consulting ser­vices and solutions, serving a wide variety of enterprises in many industries.  

If you have comments about this article or BI Expert, or would like to submit an article idea, contact the BI Expert editor.

You may contact the author at Shlomi.Weiss@bics.co.il.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.