Using CallScripter Reporting with SSRS
Feature available Version 4.5.29+
Overview
This feature allows CallScripter Reports to be imported as a dataset in SSRS allowing users to utilise the additional functionality of SQL Server Reporting Services.
A system stored procedure can be created and called to populate an SSRS dataset on which a straight data extract can be run, or more complex aggregate functions preformed.
System Requirements
This feature is a legacy feature and has compatibility issues with current Microsoft environments. It is compatible with SQL Server 2008R2 and below, but not SQL Server 2012 and above. It requires a version of MS SQL that supports CLR 2.0 , with Microsoft's latest platforms only supporting CLR 4.0. If you currently use or wish to discuss these requirements please contact your CallScripter support vendor.
Deployment & Usage
The following instructions detail the process for adding the Stored Procedure and creating the dataset within SSRS.
Step 1:
Create Stored Procedures and grant permissions in the CallScripter database using the following script:
create assembly SQLReportWriter
from 'C:\Program Files\Callscripter\CallScripter\CallScripter Data\Reports\SQLReporting\SQLReportWriter.dll'
with PERMISSION_SET = unsafe
go
create procedure RPT4_GetReportOutput(
@serverName nvarchar(max),
@dbName nvarchar(max),
@reportID int,
@startTime datetime,
@endTime datetime,
@outboundCampaign int,
@outboundRecords int
)
as external name SQLReportWriter.[SQLReportWriter.SQLReportHelper].[RunReport]
go
drop procedure RPT4_GetReportOutputWrapper
go
create procedure RPT4_GetReportOutputWrapper(
@serverName nvarchar(max),
@dbName nvarchar(max),
@reportID int,
@startTime datetime,
@endTime datetime,
@outboundCampaign int,
@outboundRecords int
)
as exec RPT4_GetReportOutput @serverName ,@dbName ,@reportID ,@startTime ,@endTime , @outboundCampaign ,@outboundRecords
go
GRANT EXECUTE
ON OBJECT::[dbo].[RPT4_GetReportOutput] TO [CALLSCRIPTER]
AS [dbo];
GRANT EXECUTE
ON OBJECT::[dbo].[RPT4_GetReportOutputWrapper] TO [CALLSCRIPTER]
AS [dbo];
Step 2:
Create the CallScripter report you wish to use in the Report Designer. The fields selected in this will determine the fields in the SSRS Dataset.
Step 3:
Create a new SSRS Reporting Project, and use the CallScripter database as the data source. Create a new blank Report in the project.
Step 4:
In your SSRS Reporting Project create Dataset which calls the stored procedure 'RPT4_GetReportOutputWrapper'.
When this stored procedure is run, a dataset is created which matches the format as specified in the report designer.
If you are using forms authentication, you may need to pass the connection string in the SP variables using the the following entries: @serverName = '__manual__' and @dbName = N'Data Source=<MYDATABASE>;Initial Catalog=<CSDBINSTANCE>;User Id=<user>;Password=<user>;'.
Step 5:
Once the dataset is created, the fields in the CallScripter report map to the available dataset fields. To produce a simple table report, add each of these fields to a column in SSRS.
______________
Additional Information:
Further details on SSRS reporting with some tutorials can be found using the following links:
Internet links to MSDN SSRS Tutorials
