Sunday, February 22, 2015

How to handle parameters specified by end user for SSRS reporting

For customized report for each customer, the parameters are different. It is requested to provide an easy way for customers to specify their parameters, and be able to set defaults based on initial recommendations

Design considerations
1) Choose Excel as interface for customer to input their data;
2) The excel template must be provided by vendor with default value
3) Only Customer column is editable, also with data validation
4) The excel template should be extendable to cover all parameters
5) Need to turn excel spreadsheet into csv file for SQL to load. This will work even Excel is not setup on server; also technically csv is much more stable to be loaded into database.

Solution overview

1) The initial report parameters with default value can be used for initial setup  
2) Changed report parameter in Excel can be loaded anytime
3) Report will take parameters specified by customer, and default parameter will be taken only if not specified by customer

Load data into table - BULK INSERT

CREATE TABLE [dbo].[ImportedReportParameters](
[Parameter] [varchar](50) NOT NULL,
[Reference] [varchar](50) NULL,
[Customer] [varchar](50) NULL,
[Parameter] ASC

CREATE TABLE [dbo].[ReportParameters](
[Parameter] [varchar](50) NOT NULL,
[Reference] [varchar](50) NULL,
[Customer] [varchar](50) NULL,
[Value] as ISNULL (Customer,Reference)
[Parameter] ASC

truncate table ImportedReportParameters

BULK INSERT ImportedReportParameters
FROM 'C:\BAR\bin\ReportParameters.csv'
delete from ImportedReportParameters where Parameter ='Parameter'

insert into ReportParameters (Parameter,Reference,Customer)
select I.Parameter,I.Reference,I.Customer
from ImportedReportParameters I
left join ReportParameters R on (I.Parameter = R.Parameter)
where R.Parameter is null

update  R set Reference = I.Reference, Customer = I.Customer
from ImportedReportParameters I
inner join ReportParamet

Create shared dataset and then use lookup

Sample below to specify target
=CInt(Lookup("Target",Fields!Parameter.Value, Fields!Value.Value,"ReportParameters"))/100

Load shared dataset to report server with RS.exe

rs -i c:\BAR\bin\DeploySharedDataset.rss -s http://localhost/ReportServer  -e Mgmt2010

Dim parentFolder As String = "folderName"
Dim reportParentPath As String = "/ folderName "
Dim reportPath As String = "C:\BAR\Report\ folderName \"

Public Sub Main()
'Initialize the reporting services credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
' Pass our datasets name to the deploy report function
End Sub

Public Sub DeployDataset(ByVal Dataset As String)
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
   Dim stream As FileStream = File.OpenRead(reportPath + Dataset + ".rsd")
   definition = New [Byte](stream.Length - 1) {}
   stream.Read(definition, 0, CInt(stream.Length))
 Catch e As IOException
 End Try
   Dim item As CatalogItem
   item = rs.CreateCatalogItem("DataSet", Dataset, reportParentPath, True, definition, Nothing, warnings)
   If Not (warnings Is Nothing) Then
     Dim warning As Warning
     For Each warning In warnings
     Next warning
     Console.WriteLine("Dataset: {0} published successfully with no warnings", Dataset)
   End If
 Catch e As Exception
 End Try

End Sub

No comments:

Post a Comment