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,

CONSTRAINT [PK_ImportedReportParameters] PRIMARY KEY CLUSTERED

(

[Parameter] ASC

)

)

GO

CREATE TABLE [dbo].[ReportParameters](

[Parameter] [varchar](50) NOT NULL,

[Reference] [varchar](50) NULL,

[Customer] [varchar](50) NULL,

[Value] as ISNULL (Customer,Reference)

CONSTRAINT [PK_ReportParameters] PRIMARY KEY CLUSTERED

(

[Parameter] ASC

)

)

truncate table ImportedReportParameters

BULK INSERT ImportedReportParameters

FROM 'C:\BAR\bin\ReportParameters.csv'

WITH ( FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

GO

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

DeploySharedDataset.rss

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

DeployDataset("ReportParameters")

End Sub

Public Sub DeployDataset(ByVal Dataset As String)

Dim definition As [Byte]() = Nothing

Dim warnings As Warning() = Nothing

 Try

   Dim stream As FileStream = File.OpenRead(reportPath + Dataset + ".rsd")

   definition = New [Byte](stream.Length - 1) {}

   stream.Read(definition, 0, CInt(stream.Length))

   stream.Close()

 Catch e As IOException

   Console.WriteLine(e.Message)

 End Try

 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

       Console.WriteLine(warning.Message)

     Next warning

   Else

     Console.WriteLine("Dataset: {0} published successfully with no warnings", Dataset)

   End If

 Catch e As Exception

   Console.WriteLine(e.Message)

 End Try

End Sub