Not Logged In

sspyrs 0.3

SSPYRS

The SSPYRS (SQL Server Python Reporting Services) library is a lightweight interface for interacting with and retrieving data from SSRS reports. The core functionality of the library is straightforward. Perform authentication to an SSRS server, initialize a session, and then retrieve the report data from that session. Report data can be interacted with via raw XML, but has predefined methods to organize it into Pandas DataFrame objects.

The SSPYRS library works primarily from the XML export functionality of SSRS. However, this neither XML nor CSV exports are provided in the free express versions of SQL Server (they are available within the currently free developer editions of SQL Server 2017). The library does include direct download functions for the Excel export included in the express version, however it will not read the data directly into memory.

SSPYRS has been validated to work with SSRS 2008 R2, SSRS 2014, and SSRS 2016, SSRS 2017, and PowerBI Server 2017 under most server settings.

To install SSPYRS, execute in console:

pip install sspyrs

Usage and Documentation

Report Objects

If passing parameters to the report, they can be passed as a dictionary as an argument called ‘parameters’. Note that parameters must use the actual parameter names designated within the rdl file. Parameters with defaults do not need to be specified unless desired. An example of valid parameters would be:

Retrieving Data

Raw XML Data

To retrieve the raw XML from the report, use the rawdata() method:

rpt_xml = myrpt.rawdata()

The resulting variable will be a dictionary with all report data elements. This will include some report metadata in addition to the XML formatted data elements from the report. Note that some of the XML tags and headings may appear differently than their corresponding report attributes. This is due to the fact that the XML does not include any XML object labels, only their names, which must be unique across the entire .rdl file, not just within an element. For example, in a report with 2 tables which share column names between them, the first table or data object will have the normal column names appended with an “@” (e.g. “@ID”,”@Val”), while the second table will have column names like “@ID2”, “@Val2”. The tabledata() method strips the “@” and numbers out, but the rawdata() method leaves them be.

Tabular Data

To quickly organize the raw XML into a tabular format, use the tabledata() method:

rpt_tables = myrpt.tabledata()

The resulting variable will be a dictionary of Pandas DataFrames, whose keys in the dictionary correspond to the data object names within the .rdl file. This method also attempts some limited data parsing for number and date columns.

Exporting Data

Default Download

When working with versions that allow XML exports, the report data can be directly exported to a few convenient formats using the download() method:

rpt_downresults = myrpt.download(type='CSV')

The resulting variable lists out the data objects which were downloaded and written to files. Currently available exports include CSV, JSON, and Excel. The default download file type is CSV. For CSV and JSON, a file will be created for each data object, named by its dictionary key from the tabledata() results. For Excel, a single file with multiple tabs is created.

Direct Download

When working with versions of SSRS which do not allow XML data exports (typically because the feature is not included in express editions), the data can be exported directly to any of the available export types (on express editions this usually includes Excel, Word, and PDF) using the directdown() method. The direct download can be called:

These functions will create a file called ‘myfile.csv’, or whatever extension is specified, in the current working directory. The directdown method supports all available SSRS export formats as of SSRS 2017. If there is a report containing complicated formatting to the point that the built in rawdata() and tabledata() methods are impractical, using the directdown() method and parsing the results is a viable alternative.

Also, worth noting is the Excel export from directdown() preserves SSRS formatting and reads natively into Python via pandas. If preserving data formats is important, exporting to Excel via directdown() and reading the resulting file back into Python is the preferred solution.