Query as a Web Service is a small but powerful tool in the SAP BusinessObjects BI portfolio. It allows us to create a query on top of a SAP BusinessObjects Universe and publish its results as a Web Service. This recipe shows you how to create a QaaWS and how to consume the data in SAP BusinessObjects Dashboards.
First, you will need SAP BusinessObjects BI Platform to be installed on your machine (along with server and client components). You will also need a Universe that is connected to an underlying database.
- Open Query as a Web Service and log in to your SAP BusinessObjects BI server. You can find this application in the SAP BusinessObjects BI 4 platform Client Tools folder.
- Click the New Query button on the upper left. Here you can enter a name for this Web Service and a description (not mandatory). Enter a name for your Web Service and click the Next button.
- Select a Universe and click Next.
- The following screen allows us to build our query. The left column contains the available dimensions, measures, and filters from the selected Universe. Drag the ones you want to use into the Result Objects window and click Next.
- If the Answer prompts screen pops up, just click the OK button. The There's more... section of this recipe will discuss the use of prompts.
- The Preview screen provides us with an overview of the QaaWS setup we just created. The most important part of the Preview screen is that it shows how the data and its layout will look in SAP BusinessObjects Dashboards after setting up the data connection and binding the columns to the spreadsheet. When you are satisfied with the preview, you can click on Publish.
- Now click the To Clipboard button to copy the Web Service URL to your clipboard.
- In SAP BusinessObjects Dashboards open the Data Manager window.
- Add a Web service query (Query as a Web Service) connection.
- Paste the URL into the WSDL URL field and click Import.
- Select the row folder in the Output Values section. Since we have three columns of data in this Web Service, we have to bind it to a three-column area in the spreadsheet. Bind it to cells A6:C30.
- Go to the Usage tab and select Refresh Before Components Are Loaded. Close the Data Manager.
- To see if and how the data is fetched by SAP BusinessObjects Dashboards from the Web Service, add a Spreadsheet Table component to the canvas and bind it to cells A6:C30.
- Preview the dashboard. A User Identification popup will appear. Enter your SAP BusinessObjects BI Platform credentials to log in. The Spreadsheet Table component will now be filled with data from the Web Service.
In this recipe, we used three stages to get data into our dashboard. First, the SAP BusinessObjects Universe; second, the QaaWS definition on top of the Universe that created a Web Service; and third, the QaaWS-connection setup in the Data Manager to connect to the Web Service.
Because we cannot preview the data in the SAP BusinessObjects Dashboards spreadsheet like we did in the Creating a news ticker with Excel XML Maps recipe, we used the Spreadsheet Table component to check how the cells are populated when running the dashboard.
Some other important concepts when using Query as a Web Service include the use of prompts and methods.
QaaWS prompts make it possible to load only the data that the dashboard user needs when using the dashboard.
- Repeat steps 1 until 4 of the recipe. Before clicking Next in the fourth step, add the dimension that you want a filter on into the Filter Objects area. Select the Prompt option by clicking the little arrow on the right.
- The Answer prompts window will appear. Here, select one of the available values to enable QaaWS to create a preview of the data in the next screen and click OK.
- As you can see in the Preview screen, the prompt filter object we added is now shown as an Input Parameters. Publish the QaaWS.
- Repeat steps 8 until 11 of the recipe.
- The prompt is now displayed in the Input Values area. Bind the prompt to cell A1.
- Go to the Usage tab and bind the Trigger Cell field to cell A1 and ensure that When Value Changes is selected. Now the data will only be refreshed when the value in this cell changes. Close the Data Manager.
- Set up a Spreadsheet Table component like we did in step 13 of the recipe.
- Add a Combo Box component and add the Labels for the prompt. Select Label as the Insertion Type and bind the Destination field to cell A1.
- Preview the dashboard and switch between the different labels to see the different data selections being loaded into the dashboard.
As you might have noticed while creating a QaaWS connection in SAP BusinessObjects Dashboards, there are several different methods you can use in the Definition tab. These methods can be split into two groups: to retrieve data and to list available parameter values:
runQueryAsAService: This is the default selected option that we also used for the examples in this recipe. It enables us to send out parameter values and retrieve data.runQueryAsAServiceEx: This method gives the same output results asrunQueryAsAService, but instead of providing an exact input parameter value, we can now also provide its index.valuesOf_parameter: This method will return a list of values for its input parameter, which can be used to create a selector like we did in the Using prompts section.
Remember that each QaaWS connection can only use a single method, so you might have to set up more than one connection.
