First of all let’s give a scenario that leads us to do that. In this case we wanted to create a dashboard for a SAP GRC module. The problem was that we could not connect to the system directly with SAP BusinessObjects Dashboards (Xcelsius for the most nostalgic ones). Apart from that, there is a good thing about having a dashboard embedded in Crystal, you will have a dashboard that can be refreshed from Crystal Reports without needing a previous authentication. You will also be able to save the “report” (you can show the dashboard) in PDF with saved data and the dashboard will be completely clickable and navigable.

After having a clear reason why we need to embed the dashboard into a Crystal Report let’s jump to how to do it.

Once you have your dashboard ready go to “Manage Connections” and add a new one by choosing “Flash Variables”.

fig. 1 – Data Manager in SAP Dashboards

Once you have it ready give a name to this connection and choose the variable format as CSV. Now you might wondering why that is.

The variable format indicates how you have to prepare the data in Crystal Reports in order to let Excel read the variables, by putting them into the specific columns.

Virtually, what more or less happens between the dashboard embedded and the data from the Crystal report is the following:

Fig. 2 – Data flow between Crystal data and SAP Dashboards

For the moment we will step forward and later we will get back to this topic again.

Continuing with the Flash variable connection we were working at. Create as many variables as you need in you Dashboard. In the example below we have one flash variable for each column of the raw data table (see the image below).

Finally, save the dashboard and export it to flash file.

Fig. 3 – Adding flash variables to the connection

After those steps in SAP Dashboards, open Crystal Reports and open the report in which you want to add the Dashboard (it needs to have data inside).

Right click on the place you want to locate the dashboard and choose “Insert Flash Object”. A window will be opened and you will be able to select your flash file and select if you want it linked or embedded. If you want to have it connected to the Crystal data choose “Embedded”.

Once the dashboard is embedded you need to create the variables which will be linked with the flash variables in the Excel file. This is the time to go back to our figure 2! As we said the format you need to send the data to the flash variables is CSV; that means you should cook your data stored in columns (objects in your crystal reports) in order to have it in a singles string with values separated by comas.

Fig. 4 – Concept of what needs to be done before mapping variables

Now here is the trick to creating these variables so that you can avoid fighting with Crystal reports for a while.

Take each column or data you need and type for each one of them the following formula:

Note: Be careful if the data to convert into CSV is number formatted as #,##.##. The thousand separator will create errors in your dashboard if you are showing them. To avoid that kind of issues replace Cstr({Field}) by Cstr({Field},’#’).

Now that you have the dashboard placed and the variables created in Crystal reports, right click on the dashboard (in design mode) and choose “Flash Data Export”.

Once the pane is open you will insert the name of the Excel flash variables in the left column and the Crystal Reports variables in the right column. Is VERY important that the names of the variables in the left columns match perfectly (exactly the same name) with the names of the variables defined in the data manager in SAP Dashboards (see figure 1).

Fig. 5 – Flash Data Expert in Crystal Reports

By following these steps you will be able to add a Dashboard into a Crystal reports and what is better is that you will be able to refresh a dashboard that is reading data from you system without any logging needed!

I hope this is useful to you in your upcoming projects. If you have any tips our doubts, leave a comment below.

SAP Forum and SUGMENA Conference Experience the Future of Business in Dubai, May 29 Register Now     Join Clariba in the SAP Forum and SUGMENA Conference Dubai, and experience the future of business. A perfect storm of technology innovations – such as social, cloud, Big Data, and mobile computing – is changing the ways businesses operate. How can you keep pace with the latest business trends? At the SAP Forum and SUGMENA Conference, you can connect with hundreds of industry peers and explore breakthrough technology innovations that can help your business run like never before.At this year’s SAP Forum and SUGMENA Conference, you can: Listen to visionary keynote from Sanjay Poonen, President & Corporate Officer, Technology & Innovation Products, Head of Mobile Division at SAP and learn about the latest essential business technology trends and innovations Visit the Exhibition Showcase and interact with Clariba’s team of Business Intelligence experts. Participate in highly informative panel discussions and learn how to use technological innovations to rethink, reimagine, and reshape the future of your business. Gain valuable insights by listening to real-world implementation examples from SAP customers. Explore SAP applications that support your growth strategies, help you deliver new customer experiences, and unleash new value through your social and business networks. Register Now as seats are limited. Register Now     Mark Your Calender Dubai – UAE Date: 29 May, 2013 Timings: 08:00 – 17:00 Venue: Dubai International Convention and Exhibition Center Cost: Free to attend       Follow Clariba Visit our Website Follow us on Twitter Watch on YouTube Subscribe to our blog Follow us on Linkedin

 

 perfect storm of technology innovations – such as social, cloud, Big Data, and mobile computing – is changing the ways businesses operate. How can you keep pace with the latest business trends?

This is has a very important feature if for example you are building Java Social Media applications that read information from the Internet or if you have constructed a Java application that retrieves information in Json Structure XML. I will show you how Data Services makes requests and interprets replies from a web service Data Source.

If you need background information on the first steps of my process, I have done a first post on how to use Data Services SDK libraries to construct an AWTableMetadata in a Java application, followed by the post where I explained how to access a JAVA application as a source of information using the WebService DataStore in SAP Data Services.

If you already read my previous blogs, lets jump into how to load and read Web Services Data Store in Data Integrator.

Step 1:

Open Data Services Designer. Go to the Data Store perspective and right click with the mouse and select New.

Step 2:

Set the name of the extractor and the URL where your web service WSDL is located (see my previous blog for reference).

Step 3:

Right Click on the “f(x)” symbol and select Import. Choose the functions from the webservice that you are going to use. In this example we select “getTableTweeetsEN” and “getTableTweeetsES”.

Special Note:

To access to these functions inside a transformation we have to use the function call Schema provided by Data Services. In this case the function getTableTweets_EN receive an input and returns a table (AWTableMetadata table). This return type comes in an especial nested form from our Web Service. We will have to resolve this nested schema doing a couple of transformations below.

 Step 4:

Select the input parameter for the functions; in this case it is a field from a table called “WS_Parameter”. We select that table as a Source table and our first item in our data flow.

Step 5:

Insert a transformation in the data flow as your second item. In this first query (Query1_EN). We create a SCHEMA called Schema 1, and assign the field came from the database “Parameter” as an attribute of this Schema.

Step 6:

Create a second transformation (Query2_EN). This query will be in charge of calling the web service with the input parameter using the Function Call procedure. Right click on the Schema Table called Query2 and select new Function Call.

Step 7:

Select the WS_ClaribaSMT dataSotre in the left panel, the right panel shows the functions that we imported to the Data Store. We select the first one getJsonTweet (For English Language) and click next.

Step 8:

We have to map the new function call Schema with the new Schema1. This is the structure used to call a Web Service in Data Services. In this case we are calling the function getJsonTweets_EN with a parameter nombre. Structure that matches our SHEMA1. Then click Finish.

The final result will contain the function call. You can add also an attribute below the function call. In this case we add “load_date” containing the sysdate representing the date of the load data.

Step 9:

The third query will be in charge of the recognition of the data returned by the Web service. In this case the Schema is in the left panel. To capture this on Data integrator we need to unlace this Schema until we get to the “return object” which contains the Data.

We click on the left panel above the getJsonTweetResponse and drag it into the right panel. Then we do right click on the getJsonTweetResponse from the right panel and select the option “Unnest”. This will cause the split between the schemas. We proceed to capture it in the next nested query.

Step 10:

We do the same procedure in the query 4, drag the getJsonTweetResponse to the right and unnest it. 

Step 11:

Query 5_EN contains the final result which be two variables that contains the header of the table plus the Load Date.

Step 12:

The last step depends on the implementation and the business rules. The table returned will have this format.

Conclusion

This method applies particularly if you are using function call schema and an array as return type for your web service. If your source is another thing different to an application the resolution of the web Service may vary. The method for mapping the final table is up to you and your business needs. A easy solution could be aggregate an ID to each row.

If you want to have more information please read my previous blogs or leave a comment below.