You want to build a Klip using data from a report built with Microsoft's SQL Server Reporting Services platform that retrieves data from the database (Microsoft Sharepoint, SQL Server, Oracle, DB2, SAP, Essbase, etc.)
Klipfolio Dashboard's Engines.ReportingServices API can be used to display the contents of reports created with Microsoft's SQL Server Reporting Services.
In this article, you will find the following sections:|
Table of Contents
|
Klipfolio Dashboard's APIs provide methods to access the Report Server web service; it does not install any drivers or tools for SSRS itself.
Refer to Microsoft's documentation on how to configure the SQL Server, such as:
http://msdn.microsoft.com/en-us/library/ms159261.aspx
http://technet.microsoft.com/en-us/library/bb630449.aspx
Download the sample Klip
See below to find out how to update this Klip in order to run it on your machine.
The general steps for developing a Reporting Services Klip are:
1. Specify the required information about your server in the <klipscript>, which will return the data in XML format.
2. Write the <style> section that matches tags in the returned XML data. Klipfolio Dashboard uses this information to display the data in your Klip. The tags you use here are case-sensitive.
Note: Your stylesheet must match your own data.
The easiest way to start is to download the Sample Reporting Services Klip and update its script and <style> blocks according to the returned data.
Here is a breakdown of the important sections of the klipscript. For the entire script, refer to the sample Klip mentioned above.
In the onLoad() function, which is executed when the Klip is loaded, create a session and load the report:
function onLoad ()
{
// Create the session with the server name and port combined (default port is 80)
// Change the URL to your web server's. Keep the quotation marks.
g_service = Engines.ReportingServices.createService ("http://webservername:80");
// [Optional] Specify whether or not to use SSL/TLS for transport layer security (default is false).
// g_service.secure = true;
// [Optional] Specify a different report server virtual directory (default is "ReportServer"). Keep the quotation marks.
// g_service.setVirtualDirectory ("ReportServer");
// Load a report based on the report's deployed project path.
// Change the projectPath according to your server setup. Keep the quotation marks.
g_report = g_service.loadReport ("projectPath");
}1. First, you create a session by specifying the server name. The port is 80 by default, so you may omit this unless you have specified another value for your server, i.e. "http://webservername" is the same as "http://webservername:80".
2. If desired, you may set the use of SSL/TLS and specify a virtual directory. In the sample Klip, these lines are commented out on the assumption that the default settings are being used. Otherwise, comment in (i.e. take out //) the relevant lines and specify parameters according to your own server setup.
3. Specify the project path ("projectPath") according to your project setting, e.g. "AdventureWorks Sample Reports/Territory Sales Drilldown". If you are using backslashes (\) anywhere in the script, be sure to escape each with an additional backslash. For example, if you want to point to "C:\temp\folder", it should be written as "C:\\temp\\folder".
4. You will want to run the report and get the XML formatted data back whenever the Klip is refreshed. You do this in the onRefresh() function:
function onRefresh ()
{
// Run the report
if (g_report.run ())
{
// Process the resulting XML
Engines.Data.process (g_report.getXML ());
// Output the returned XML data in Debug Window.
traceln( (g_report.getXML ()) );
return true;
}
else
{
// Output error message in Debug Window
traceln (g_service.getLastError ());
}
return false;
}You don't need to change anything in the onRefresh() function. However, note the use of traceln( (g_report.getXML() );. If the report is run successfully, you will see your report in XML format output into the Debug Window.
5. You must update the <style> block to match the data returned in the above step. The Klip will take the returned XML data and refer to the settings in the <style> block to find out how the data should be displayed.
Look for repeating blocks of data that make up each row of data, which is called an "item" in a Klip. The <style> definition should follow the syntax discussed in this article.
In a web browser, can you access the Web Service URL and the virtual directory you specified in the onLoad() function? If you can't, the Klip can't, either.
For example, if your URL and virtual directory were "http://localhost:8080" and "ReportServer", respectively, go to http://localhost:8080/ReportServer in a browser.
If you can't get to your report in the browser, check your SQL Server's Web Services setup. (This is not part of the Klipfolio Dashboard installation.)
Make sure you don't have any typos in the parameters in the script. It's best to assume that the paths are case-sensitive.
This means that the Klip has been able to refresh itself successfully, i.e. the report has been run and the XML data from the server has been returned. However, the Klip doesn't know how to display the data because the <style> block isn't written correctly.
To update the <style> block, open the Debug Window to see the XML output, which is what the Klip is trying to display. Then, follow the instructions on how to extract data from inside an XML tag to write the <style> block.
