Promise based api for MSSQL Reporting Services with ntlm and basic security
npm install mssql-ssrs
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)
`js
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
`js
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):
`js
var url = 'http(s)://:/ReportServer_',
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
`js
// 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
`js
// 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
`js
var { ReportService } = require('mssql-ssrs');
var reportService = new ReportService();
await reportService.start(url/Path/serverConfig, soapConfig [, options] [, security]);
`
$3
`js
var client = reportService.getClient();
or
reportService.client['functionName']()
`
$3
`js
var description = reportService.getDescription();
`
$3
List all children down from current specified folder, if recursive is used it will go down into all folders
`js
var reportList = await reportService.listChildren(reportPath[, isRcursive]);
`
$3
`js
var params = await reportService.getReportParams(reportPath[, forRendering]);
`
$3
`js
var params = await reportService.updateReportParams(reportPath, params[, formatParams]);
`
$3
For all DataSourceDefinition properties use microsoft documentation
`js
var status = await reportService.testDataSourceConnection(userName, password, dataSourceDefinition)
`
Example for dataSourceDefinition:
`js
DataSourceDefinition: {
Extension: 'SQL',
ConnectString: 'Data Source=\\;Initial Catalog='
}
`
$3
If properties are given, all report properties are returned. Report custom properties are not available
`js
var properties = ['Hidden', 'Description'];
// or
var properties = [{ Name: 'Hidden' }, { Name: 'Description' }];
var properties = await reportService.getProperties(reportPath[, properties])
`
$3
`js
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)
`
$3
`js
var jobs = await reportService.listJobs()
`
$3
`js
await reportService.cancelJob(jobId)
`
$3
`js
var rdl = await reportService.getItemDefinition(reportPath)
`
$3
`js
await reportService.createFolder(folderName, path)
`
$3
`js
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
$3
Mostly as above but definition property is a ReportDefinition object
`js
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
`
$3
`js
await reportService.deleteItem(path)
`
$3
Usually used for creating images
`js
var resurce = await reportService.createResource(name, path, fileContents, overwrite, mimeType);
`
$3
`js
var references = await reportService.getItemDataSources(itemPath);
`
$3
`js
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.
$3
`js
var references = await reportService.getItemReferences(itemPath, referenceType);
`
- itemPath: path of the report including the file name
- referenceType: 'DataSource'|'DataSet'...
$3
`js
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
$3
- list of all reporting execution methods and options
- not all methods where implemented
`js
var { ReportExecution } = require('mssql-ssrs');
var reportExecution = new ReportExecution();
await reportExecution.start(url/Path/serverConfig, soapConfig [, options] [, security]);
`
Using client soap directly
`js
var client = reportExecution.getClient();
or
reportExecution.client['functionName']()
`
$3
`js
var description = reportExecution.getDescription()
`
$3
`js
var extensions = await reportExecution.listRenderingExtensions()
`
$3
`js
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:
`js
{
"Extension": "pdf",
"MimeType": "application/pdf",
"Result:" "", // base64 string, this is the pdf
"StreamIds": null
}
`
Report Execution via Url
$3
No need to use start function (it does not exist)
`js
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
`js
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
`js
{
data: Buffer,
status: ...,
statusText: ...,
headers: ...,
config: ...,
request: ...
}
`
Report Manager
`js
var { ReportManager } = require('mssql-ssrs');
var ssrs = new ReportManager();
await ssrs.start(url/Path/serverConfig, soapConfig [, options] [, security]);
`
$3
`js
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
$3
Get report list from cache, if path is not found in cache it will be download and cached
`js
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
$3
`js
await ssrs.cacheReportList(reportPath[, keepHidden])
`
$3
`js
await ssrs.clearCache()
`
$3
Report Builder only installs from ie/edge
`js
var link = await ssrs.reportBuilder(reportPath)
`
$3
Create a copy of a specified report in the same folder and return new report
`js
var newReport = await ssrs.createReportCopy(reportPath, options)
`
Inspired from Report Loader
$3
Download list of all items down from specified path, can also be used for 1 specific report
`js
var fileList = await ssrs.download(reportPath)
`
- reportPath: string|Array of strings path for base folders in report service from where to create definitions.
$3
`js
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)
$3
Upload items (report/datasource/image) or entire folder structure to reporting services
`js
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
$3
Read file directory and upload reports
`js
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=\; initial catalog=',
UserName: '',
Password: ''
},
mySecondDataSourceName: {
WindowsCredentials: true,
ConnectString: 'data source=\; initial catalog=',
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
$3
Creates soap clients (used for creating reportService and reportExecution client)
$3
types of soap security
`js
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
});
`
`js
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