If you followed my last couple of blogs you should now have a running BusinessObjects Edge system with Crystal Reports, Xcelsius, Web Intelligence and Live Office as client tools installed and configured. In addition the SAP Integration Kit should be installed and configured and you should be able to leverage your SAP credentials for authentication by now.
As promised after TechED in Las Vegas and the great keynote from Donald MacCormick we are now going to build a Xcelsius Dashboard based on SAP BI data.
This short blog series is broken down into 4 parts:
- Part 1 : Using Crystal Reports and Live Office to create the source objects
- Part 2 : Creating the initial part of the Xcelsius dashboard
- Part 3 : Using Universes and Query as a WebService to create the parameter list
- Part 4 : Finalize the Xcelsius dashboard
First we create a SAP BI query. In my case I use the SAP Demo DalSegno Reporting cube and create a BI Query which includes
- Characteristic Product in the rows
- Characteristic Calendar Year in the Free Characteristic with a SAP Variable (singe Value)
- 12 Restricted Keyfigures showing the Net Sales broken down by Calendar month in the columns
As part of the settings available in the SAP toolbar the report designer can choose to use the SAP BW MDX driver for creating new reports against SAP BW.
Make sure you set the checkbox "Use MDX driver with support for Multiple Structures" to leverage the newer and enhanced version of the connectivity towards SAP BI.
To do so select the menu SAP > Settings and set the check box Use MDX Driver with support for Multiple structures.
Now I select the menu SAP > Create New Report from a Query and select my SAP system from the SAP Logon and provide the required details.
After the authentication the system is displaying a list of BI queries and I can search for the query I created and then click OK to receive the meta-data from the system.
Crystal Reports is generating an empty report and in the field explorer I am now able to see all the characteristics and keyfigures from my BI Query.
- Each characteristic in the query will result in an element in the field explorer with a key and a description item (Example: Product Key and Product Medium Name).
- In addition each characteristic will also generate a Node ID and a Parent Node ID which can be used for hierarchical reporting.
- Each characteristic will also generate a Member Unique Name field which represents the technical key value.
- Each Keyfigure will show up with 3 items : the numeric value, the unit and the formatted value (based on settings in transaction Su3)
In my example I am not interested in creating a great report design. All I need for now is the fields in my report so that I can leverage this report later on in Xcelsius to create my dashboard.
I put the characteristic Product and my 12 restricted keyfigures into the Detail section of my report
... and select the menu View > Print Preview to view the result.
Because my underlying SAP BI query contains a SAP variable I am asked to select a value from the list before I can run the report.
Now I select the menu SAP > Save Report and select a role for my report in the SAP BI system.
After clicking OK I am asked if the report should be prepared for Translation (which would mean that all translation relevant strings are being stored in the translation tables and can be leveraged in transaction Se63) and if the report should be published to my BusinessObjects Edge system.
I select to publish the report and receive a message that the report has been published successfully.
To confirm I logon to InfoView with my SAP credentials and can now select the shortcut "My Roles".
The shortcut "My Roles" shows my assigned roles (assigned in the SAP BI system) which contain content (Crystal Reports objects) in the BusinessObjects system.
Now in the next step I start Microsoft Excel and will leverage LiveOffice to create a Excel spreadsheet that will leverage the Crystal Report that we just created as a source for the data.
The reason for doing so ? Xcelsius has several options to connect to SAP BI (see my previous blog on TechEd Las Vegas - Are you up for the Business Objects Community Xcelsius Challenge) and here we will leverage the option of Live Office and in the second part we will leverage Query as a WebService.
Inside Microsoft Excel I select the menu Live Office > Options and navigate to the Enterprise tab.
I set the option Use specified logon criteria to configure my default logon credentials and authentication
In the web service URL I enter the following the syntax:
http://APPSERVER:PORT/dswsbobje/services/session.
The APPSERVER entry here is the application server where you deployed the web services from BusinessObjects Edge
After I entered the correct URL the Authentication listbox becomes enabled and I can select "SAP" as the authentication method so that later in on Xcelsius we still are able to achieve a SSO down to the SAP BI system.
Now I select the menu Live Office > Insert > Crystal Reports content.(Ignore the initial error message in case you did not enter a user into the Options dialog)
For the authentication screen I enter my SAP Credentials in the format [System ID]~[Client]/[Username] to authenticate (example: CIM~003/i819882)
After successfully authenticated against the system I can select the previously created report from BusinessObjects Edge.
Also in LiveOffice I can leverage the item "My Group" to navigate to the roles with content. I select the Crystal Report that I created previously and move to the next screen.
The report is being presented and in my example I select the option to "Switch to Fields" because my goal is to leverage the data from Crystal Reports and leverage it in Xcelsius. The other option here is to leverage report parts like a chart inside of Live Office.
Now I can select the fields from the report and put them into Microsoft Excel.
I click Finish and the data is being presented in Microsoft Excel.
Important to mention here is that the data is still "live" and that the user can refresh the data inside Microsoft Excel at any point of time.
Now I select the menu Live Office > Properties for all objects
... and Navigate to the tab "Prompts"
and I can here select the prompt from the underlying BI query and click "Parameter Values".
I select the option "Choose Excel data range" and select a cell on a separate sheet in my spreadsheet.
This allows me to bind the actual parameter (SAP Variable) to a single cell in my spreadsheet. We will leverage this in the second part where we will create a listbox with years in Xcelsius then sending the value the user selected to this cell.
Now I select the menu Live Office > Publish to BusinessObjects Enterprise > Save to BusinessObjects Enterprise to save the Live Office document back to the BusinessObjects Edge Server.
I created a report on top of a SAP BI query, published this report to BusinessObjects Enterprise and created a Live Office document leveraging the report as a source object.
In the next part of this short series we will go to Xcelsius and leverage the Live Office for our dashboard and then continue to leverage a OLAP Universe in combination with Query as a web service to create the listbox with values from the SAP BI system.
For those that missed the installation parts, here are the links to the blogs:
BusinessObjects and SAP - Installation and Configuration Part 1 of 4
BusinessObjects and SAP - Installation and Configuration Part 2 of 4
BusinessObjects and SAP - Installation and Configuration Part 3 of 4
BusinessObjects and SAP - Installation and Configuration Part 4 of 4
BusinessObjects and SAP - Configure SAP Authentication
BusinessObjects and SAP - Publishing of Crystal Reports Part 1 of 3
BusinessObjects and SAP - Publishing of Crystal Reports Part 2 of 3
BusinessObjects and SAP - Publishing of Crystal Reports Part 3 of 3
- SAP Managed Tags:
11 Comments
