mssql-ssrs

mssql-ssrs

Promise based api for MSSQL reporting services

Table of contents

• Install
• Usage
  • Url/serverConfig/path
  • Soap config
  • Report service options
  • Security
• Report Service
  • report service client
  • client description
  • list children
  • get parameters for specific report
  • update parameters for specifig report
  • test data source connection
  • get report properties
  • set report properties
  • list all running jobs
  • cancel running job
  • get item definition
  • create folder
  • create data source
  • create report
  • delete item
  • get item datasources
  • set item datasources
  • get item references
  • set item references
  • create resource
• Report Execution
  • get report execution client
  • get client description
  • list available rendering extensions
  • run report
• Report Execution with Url
  • run report
• Report manager
  • get report list
  • cache report list
  • create a copy of a report
  • create link for report builder for specified report
  • clear cached reports
  • read reports folder
  • upload reports
  • download reports
  • fix data source reference
• Soap
  • create client
  • security
• Contributors

Install

Install with npm:

  npm install mssql-ssrs

Usage

MSSQL has 2 parts for reporting services:

• report service for report management (create, search...)
• report execution for report rendering (executing report)

To start using reporting services we need to connect to the server first:

start both services (reportService, reportExecution)

var { ReportManager } = require('mssql-ssrs');

var ssrs = new ReportManager([cacheReports]);
await ssrs.start(url/path/serverConfig, soapConfig [, options] [, security]);

const list = await ssrs.reportService.listChildren(reportPath);
const report = await ssrs.reportExecution.getReport(reportPath, fileType, parameters);
...

or start them separately

var { ReportService, ReportExecution } = require('mssql-ssrs');

var rs = new ReportService();
await rs.start(url/Path/serverConfig, soapConfig [, options] [, security]);

var re = new ReportExecution();
await re.start(url/Path/serverConfig, soapConfig [, options] [, security]);

NOTE: Report Execution via Url does not have or require start

Url/serverConfig/path

The url/serverConfig/path argument accepts a string url, config object or a system file path (the file path option must contain a valid ssrs wsdl file from reporting services):

var url = 'http(s)://<serverName>:<port>/ReportServer_<sqlInstance>',
var serverConfig = {
    server: 'serverName',
    instance: 'serverInstance',
    isHttps: false, // optional, default: false
    port: 80, // optional, default: 80
};

Soap Config

soapConfig, can include directly on config object or on config.wsdl_options the folowing properties for ssrs connection:

• username: '', (required)
• password: '', (required)
• workstation: '', (optional)
• domain: '', (optional)

Report Service Options

• rootFolder: base folder added to reportPath parameters, default: '/'
• useRs2012: specify witch version of wsdl should client use (2010/2012), default: false (2010)
• cache: specify whether to cache report list, default false
  • by default hidden reports are not kept
• cacheOnStart: specify whether to cache all reports when starting report services, default false

Report Manager

• cacheReports can also be set directly when instatiating ReportManager
  • new ReportManager(true/false) - default false
  • same as cache option on start
• cacheOnStart option is stil needed if all reports should be cached at start

Security

More information on types of security see soap security

Defaults to NTLM security so no extra steps needed, just start

• NTLM security, more details here Usage

    // ex:
    await ssrs.start(url, { username: username, password: password });
    
    // start everything
    await ssrs.start(url/Path/serverConfig, soapConfig [, options] [, security]);
    // or start separately
    var rs = new ReportService();
    await rs.start(url/Path/serverConfig, soapConfig [, options] [, security]);
    var re = new ReportExecution();
    await re.start(url/Path/serverConfig, soapConfig [, options] [, security]);

• basic security and others

    // added in the same way for any other security type you use
    // instanciating security type can differ

    var auth = { username: username, password: password };
    await ssrs.start(url, auth, null, 'basic');
    
    // or

    var wsdl_headers = {};
    var security = new ssrs.soap.security.BasicAuthSecurity(auth.username, auth.password);
    security.addHeaders(wsdl_headers); // add authorization

    await ssrs.start(url, { wsdl_headers: wsdl_headers }, null, security);

Report Service

• list of all reporting services methods and options
• not all methods where implemented

var { ReportService } = require('mssql-ssrs');
var reportService = new ReportService();

await reportService.start(url/Path/serverConfig, soapConfig [, options] [, security]);

Report service client

var client = reportService.getClient();
or
reportService.client['functionName']()

Client description

var description = reportService.getDescription();

List children

List all children down from current specified folder, if recursive is used it will go down into all folders

var reportList = await reportService.listChildren(reportPath[, isRcursive]);

Get parameters for specific report

var params = await reportService.getReportParams(reportPath[, forRendering]);

Update parameters for specifig report

var params = await reportService.updateReportParams(reportPath, params[, formatParams]);

Testing data source connection

For all DataSourceDefinition properties use microsoft documentation

var status = await reportService.testDataSourceConnection(userName, password, dataSourceDefinition)

Example for dataSourceDefinition:

DataSourceDefinition: {
  Extension: 'SQL',
  ConnectString: 'Data Source=<server>\\<instance>;Initial Catalog=<DbName>'
}

Get report properties

If properties are given, all report properties are returned. Report custom properties are not available

var properties = ['Hidden', 'Description'];
// or
var properties = [{ Name: 'Hidden' }, { Name: 'Description' }];
var properties = await reportService.getProperties(reportPath[, properties])

Set report properties

var properties = { Hidden: true, Description: 'my description' };
// or
var properties = [
  { Name: 'Hidden', Value: true }, 
  { Name: 'Description', Value: 'my description' }
];
var properties = await reportService.setProperties(reportPath, properties)

List all running jobs

var jobs = await reportService.listJobs()

Cancel running job

await reportService.cancelJob(jobId)

Get item definition

var rdl = await reportService.getItemDefinition(reportPath)

Create folder

await reportService.createFolder(folderName, path)

Create data source

var dataSource = await reportService.createDataSource(dataSourceName, folderPath, overwrite, definition, description, isHidden)

Create data source

• dataSourceName: The name for the data source including the file name and, in SharePoint mode, the extension (.rsds).
• folderPath: The fully qualified URL for the parent folder that will contain the data source.
• overwrite: default false, indicates whether an existing data source with the same name in the location specified should be overwritten.
• definition: A DataSourceDefinition object that describes the connection properties for the data source.
• description: report description
• isHidden: hide report in ssrs

Data Source Definition

• ConnectString: 'data source=server\instance; initial catalog=databaseName'
• UseOriginalConnectString: data source should revert to the original connection string
• OriginalConnectStringExpressionBased: indicates whether the original connection string for the data source was expression-based.
• Extension: SQL, OLEDB, ODBC, or a custom
• Enabled: enable/disable datasource
• EnabledSpecified: true if the Enabled property should be omitted from the Web service call; otherwise, false. The default is false.
• CredentialRetrieval: Prompt, Store, Integrated, None
• WindowsCredentials: indicates whether the report server passes user-provided or stored credentials as Windows credentials when it connects to a data source.
• ImpersonateUser: indicates whether the report server tries to impersonate a user by using stored credentials.
• ImpersonateUserSpecified: true if the ImpersonateUser property should be omitted from the Web service call; otherwise, false. The default is false.
• Prompt: prompt that the report server displays to the user when it prompts for credentials.
• UserName: auth
• Password: auth

Create report

Mostly as above but definition property is a ReportDefinition object

var report = await reportService.createReport(reportName, folderPath, overwrite, definition, description, isHidden)
- `reportName`: report name
- `folderPath`: report folder destination
- `overwrite`: overwrite if already exists
- `definition`: report definition xml string (will be automaticaly converted to base64)
- `description`: report description
- `isHidden`: report manager property hidden

Delete item

await reportService.deleteItem(path)

Create resource

Usually used for creating images

var resurce = await reportService.createResource(name, path, fileContents, overwrite, mimeType);

Get item data sources

var references = await reportService.getItemDataSources(itemPath);

Set item data sources

var dataSources = { dataSourceName: 'dataSourcesNewReferencePath' });
var references = await reportService.setItemDataSources(itemPath, dataSources);

• itemPath: path of the report including the file name
• dataSources: object of dataSourceName: newValue type.

Get item references

var references = await reportService.getItemReferences(itemPath, referenceType);

• itemPath: path of the report including the file name
• referenceType: 'DataSource'|'DataSet'...

Set item references

var refs = { 'DataSourceName': '/path/DataSourceName' };
var refs = [{ Name: 'DataSourceName': Reference: '/path/DataSourceName' }];
var references = await reportService.setItemReferences(itemPath, refs);

• itemPath: path of the report including the file name
• refs: array of objects with name and reference paths

Report Execution

Get report execution client

• list of all reporting execution methods and options
• not all methods where implemented

var { ReportExecution } = require('mssql-ssrs');
var reportExecution = new ReportExecution();

await reportExecution.start(url/Path/serverConfig, soapConfig [, options] [, security]);

Using client soap directly

var client = reportExecution.getClient();
or 
reportExecution.client['functionName']()

Get client description

var description = reportExecution.getDescription()

List available rendering extensions

var extensions = await reportExecution.listRenderingExtensions()

Run report

var reportPath = '/Folder/ReportName';
var fileType = 'pdf';
var parameters = { 
  parameterName1: 1,
  parameterName2: false,
  parameterName3: 'parameterValue', 
  multiValue: ['value1', 'value2']
};
//or
var parameters = [
  { Name: 'parameterName1', Value: 1 },
  { Name: 'parameterName2', Value: false },
  { Name: 'parameterName3', Value: 'parameterValue' },
  { Name: 'multiValue', Value: ['value1', 'value2'] }
]
var report = await reportExecution.getReport(reportPath, fileType, parameters)

• parameters can be an object with name, value atributes or instance of ReportParameterInfo objects NOTE: HTML render will automatically get associated image streams from the report server in base64 inside the html

report result:

  {
    "Extension": "pdf",
    "MimeType": "application/pdf",
    "Result:" "", // base64 string, this is the pdf
    "StreamIds": null
  }

Report Execution via Url

Run report (with url)

No need to use start function (it does not exist)

var { ReportExecutionUrl } = require('mssql-ssrs');

var auth = {
  username: 'userName',
  password: 'password',
  workstation: '', // optional
  domain: '' // optional
};
var re = new ReportExecutionUrl(url/path/serverConfig, auth[, options][, axiosConfig]);

• options: optional
  • rootFolder: the folder to look into for reports
• axiosConfig: config for axios instance

var report = await re.getReport(reportPath, fileType, parameters, axiosConfig)

• reportPath: path to the report
• fileType: the report file tipe of file extension
• parameters can be an object with { name: value } properties or instance of ReportParameterInfo objects
• axiosConfig: local axios config for overriding defaults per request

returned result is an axios response schema

{
  data: Buffer,
  status: ...,
  statusText: ...,
  headers: ...,
  config: ...,
  request: ...
}

Report Manager

var { ReportManager } = require('mssql-ssrs');
var ssrs = new ReportManager();

await ssrs.start(url/Path/serverConfig, soapConfig [, options] [, security]);

Fix Data Source Reference

var references = await ssrs.fixDataSourceReference(reportPath, dataSourcePath[, logger]);

• reportPath: path to reports

• dataSourcePath: path to data source

• log: boolean, outputs to console or

• log: object

  • log: function for normal log messages
  • warn: function for log warrning/error messages

Get report list

Get report list from cache, if path is not found in cache it will be download and cached

var reportList = await ssrs.getReportList(reportPath [, forceRefresh])

• if reportPath is not present of is the same as rootFolder for reports entire cache is returned
• forceRefresh force a recache, if reportPath is not present rootFolder is used

Cache report list

await ssrs.cacheReportList(reportPath[, keepHidden])

Clear cached reports

await ssrs.clearCache()

Create report builder link for specified report

Report Builder only installs from ie/edge

var link = await ssrs.reportBuilder(reportPath)

Create a copy of a report

Create a copy of a specified report in the same folder and return new report

var newReport = await ssrs.createReportCopy(reportPath, options)

Inspired from Report Loader

Download reports

Download list of all items down from specified path, can also be used for 1 specific report

var fileList = await ssrs.download(reportPath)

• reportPath: string|Array of strings path for base folders in report service from where to create definitions.

Read reports folder

var result = await ssrs.readFiles(filePath, exclude, noDefinitions);

• filePath: path to folder to read
• exclude: array of strings to exclude specified files paths, names or extensions
• noDefinitions: does not read file content(definition)

Upload reports

Upload items (report/datasource/image) or entire folder structure to reporting services

var warrnings = await ssrs.upload(filePath, reportPath, options)

• filePath: root folder path where to read files
• reportPath: report path where to upload files
• options for upload and uploadFiles are the same

Upload reports files

Read file directory and upload reports

var warrnings = await ssrs.uploadFiles(filePath [, reportPath] [, options]);

var warrnings = await ssrs.uploadFiles('.path/to/root/directory', '/newReportFolderName', {
  overwrite: false,
  keepDataSource: true, // keep existing datasources
  deleteExistingItems: false,
  fixDataSourceReference: false,
  exclude: ['folderName', '.extension', '/path/to/file.rdl'],
  include: { folders: [], dataSources: [], reports: [] },
  dataSourceOptions: {
    myDataSourceName: {
      ConnectString: 'data source=<server>\<instance>; initial catalog=<dbName>',
      UserName: '',
      Password: ''
    },
    mySecondDataSourceName: {
      WindowsCredentials: true,
      ConnectString: 'data source=<server>\<instance>; initial catalog=<dbName>',
      UserName: '',
      Password: ''
    }
  },
  logger: true || {
    log: function (msg) { console.log(msg) },
    warn: function (msg) { console.warn(msg) }
  }
}});

• filePath: root folder from where to read files
• reportPath: report path where to upload, if not specified last folder name from filePath is used
• options: additional properties object, optional
  • exclude: array of strings to exclude specified files paths, names or extensions
  • overwrite: overrites reports and datasources on upload, default true
  • deleteExistingItems: delete items before upload, default false
  • keepDataSource: do not delete existing datasources, default false
  • fixDataSourceReference: fix uploaded reports datasource references with uploaded datasources, default true
  • dataSourceOptions: each dataSourceName and its connection properties
    • dataSourceName:
      • connectstring: connection string for data source
      • userName: userName for data source
      • password: password for data source
      • name, prompt, security, extension type is determined from the .rds and dataSourceOptions file
  • logger: boolean, outputs to console
  • logger: object
    • log: log messages function
    • warn: log warrning/error messages function

soap

Create client

Creates soap clients (used for creating reportService and reportExecution client)

Security

types of soap security

const ssrs = require('mssql-ssrs')
var customSecurity = await ssrs.soap.security.BasicAuthSecurity('username', 'password');
var customSecurity = await ssrs.soap.security.NTLMSecurity('username', 'password', 'domain', 'workstation');
var customSecurity = await ssrs.soap.security.NTLMSecurity({
  username: username,
  password: password,
  domain: domain,
  workstation: workstation
});

const { soap, SsrsSoap } = require('mssql-ssrs')
 
const ssrs = new SsrsSoap([url][, options])
const client = await ssrs.createClient(url, config[, security])

• url: url/serverConfig/path
• config: { username:'', password:'', domain: '', workstation: '', ...otherOptions }
• security: 'ntlm' | 'basic' | customSecurity