Home > Home, MSDN, SSRS > SSRS: Report Parameters Selected Values To Insert Into Table

SSRS: Report Parameters Selected Values To Insert Into Table

Hi Guys, I got this question from MSDN, Original question link here

Question in brief:-

We have a requirement, We want to insert seleted parameters values into custom builted table ,after execution of a report.

How can we insert Selected report parameters into a table.

Note: Report Server database has tables which capture parameter values, easily one can query the tables. However on production report server we developers don’t have access to report server database

Lets start report creation, below things are required to implement:-

  1. Report RDL
  2. At least 2 parameters in the report
  3. If above are 2 parameters then 2 additional parameters, if report has 3 parameters then 3 additional parameters
  4. Custom table created physically in the database

lets create Report first:-

Create basic report, then add parameters like I did.

Create one parameter Initial

Create second parameter Employee  as multivalue

Create DataSet1 using below SQL query :-

SELECT ‘A’ intital

UNION

SELECT ‘B’ intital

UNION

SELECT ‘C’ intital

 

Assign available values to first parameter Initial using  query DataSet1 as shown in below:-

1.png

 

Create DataSet2 using below SQL query :-

 

SELECT * FROM

(

SELECT ‘A’ intital,’Annik’ FirstName

UNION

SELECT ‘A’ intital,’Andrew’ FirstName

UNION

SELECT ‘B’ intital,’Bailey’ FirstName

UNION

SELECT ‘B’ intital ,’Ben’ FirstName

UNION

SELECT ‘C’ intital,’Chris’ FirstName

UNION

SELECT ‘C’ intital,’Charles’ FirstName

) T

where T.intital =@Initial

It is cascaded with first parameter.

Assign available values to Second parameter Initial using  query DataSet2 as shown in below:-

1

Create two more parameters as name

  • InitialSelectedvalues
  • EmployeeSelectedValues

Set default value for both the parameters as shown below:-

InitialSelectedvalues  :- Default value as Parameter Initial

1

 

EmployeeSelectedValues : Default value as Employee  with Join as its multi value parameter

1

Expression is like :=join(Parameters!Employee.Value,”,”)

 

Now time to create physical table in data base using below script:-

create table parametertable
(
parameterName NVarchar(250),
PrameterValue NVarchar(max),
Createdon datetime default getdate()
)

ParameterName Field to hold the name of parameter

PrameterValue field to hold the values selected in the parameters

Createdon is like inserted date of data to track the records when the report inserted.

 

Once the table created , go the report designer and create one more dataset as DataSet3 with below SQL query:-

insert into parametertable
(
parameterName
,PrameterValue
)

Select ‘Initail’, @InitialSelectedvalues
UNION
Select ‘Employee’, @EmployeeSelectedValues

Run the report,  Report look like below:-

1

Check the table data , data look like below:-

1

As we selected B in first parameter and Bailey and Ben selected in second parameter, data is inserted in the table.

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

 

Advertisements
Categories: Home, MSDN, SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: