In Chapter 8, Dashboard Data Connectivity, we discussed a number of data connectivity options for dashboards in SAP BusinessObjects Dashboards. In addition to these standard data connections, Centigon Solutions has developed an add-on that allows us to use Comma Separated Value (CSV) files to grab data from. This recipe will give you a quick walkthrough of the features of this add-on.
Go to the Centigon Solutions website (http://www.centigonsolutions.com/) and browse for CSV Connector. Download the free trial and install it with the Add-On Manager. You also need a sample CSV file to use in this recipe.
- Open a new SAP BusinessObjects Dashboards file and go to the spreadsheet area. Enter the location of the CSV file in cell C1.
- Open the Data Manager and click on the Add button. Under Add-On connection, you will see the CSV Connector. Select it.
- Give this connection a name. Bind the CSV Data URL field to cell C1.
- Also, check the Delimiter setting. This should match the format of your CSV file.
- Go to the Data Preview tab and click on the Preview Data button. The data from the CSV file will now be shown. Note that the number of columns and rows is displayed as well.
- Go back to the Definition tab and bind the Data Destination field to a range of cells that match the format, as shown in the Data Preview tab.
- Now add a filter on the dataset in the CSV file. Bind the Filter Columns field to a range of cells with the same number of columns as the data destination range. Close the Data Manager.
- You can now set up a Combo Box component or another Selector component to change the values of these cells. Use an asterisk (*) as a wildcard to show all records in a column.
- If you want to preview your dashboard, do not forget to select the Refresh On Load option in the Usage tab of the CSV Connector connection in the Data Manager.
The CSV Connector add-on from Centigon Solutions is an ideal data connector to handle input from CSV files if you do not have the option to use server data providers such as QaaWS or SAP BW. Especially for smaller enterprises, this is a very good solution to provide dashboards with fresh data. In addition, when comparing a CSV to an XLS data source, CSV files have no row limit, whereas XLS files are restricted to 1 million rows.
A nice feature of this component is the ability to preview the data in the data manager. This will help you to set up the component without having to preview the dashboard every time to check out how the data will be loaded in the spreadsheet.
In this recipe, we showed how to filter the dataset of the CSV file. You can use this option to load only the data you will actually need, which will improve the performance of the dashboard. Furthermore, in the Data Output Definition tab of this add-on in the Data Manager, you can define which columns and rows should be loaded.
Besides displaying the data from a CSV file and filtering the values, the CSV Connector add-on can also do some calculations as described in the following steps:
- Add another row above the Data Destination area. There are four calculation values. Enter a calculation value for each column as follows:
- Column 1: Lists the values separated by a comma
- Column 2: Counts the number of rows
- Column 3: Calculates the sum of values
- Column 4: Calculates the average of values
- Next, enter a single-value index that indicates which column the calculations are applied on.
- Open the Data Manager and bind the Column Calculations field and the Column Calculation Index field to the corresponding cells you just entered in the spreadsheet.
- Preview the dashboard to try this functionality.
The CSV Connector add-on also includes a component that is able to export a data range from a dashboard to a CSV file or to the clipboard. To learn more about this feature, visit the Centigon Solutions website at http://www.centigonsolutions.com/.