Solved

Read a Table and add those records into a existing parameter that was send from a SSRS report

Posted on 2016-11-29
11
54 Views
Last Modified: 2016-12-28
I need to read a Tablea, then add those records (10  varchar)  into a existing SSRS report parameter (@Temp) before I process that parameter in the Stored Procedure. The parameter will already have choices in it (from the SSRS report), I just need to add more choices to it.

Parameter @Temp already contains:  'Chilly,Luke Warm,Hotter'
Parameter @Temp already will contain when done:  'Chilly,Luke Warm,Hotter,Hot,Cold,Warm,Warmer'


Table =Tablea  Fld = Flda
Hot
Cold
Warm
Warmer
0
Comment
Question by:thayduck
11 Comments
 
LVL 12

Expert Comment

by:Arifhusen Ansari
ID: 41906778
Based on your explanation.

I guess that you want the values in your parameter from table.

If this is correct. Please follow the below process.

1) In normal case parameter has two filed value filed and label files. Value files is some what unique value label can be duplicate.
I have used example to bind FIrstname of user. So i took two column one for label and other for value files.

 Create the dataset in the report. This dataset will have the value from the table.
Let's say you want col1 data from tablea to be used.

SELECT top 10 BusinessEntityID,FirstName FROM Person.Person

Open in new window


Refer the screenshot. dataset.png
2) when your data set is ready. You only need to create a report parameter and configure the way shown below.

Create parameter

Go to Available value section and configure as show in figure. You can set value filed and label filed with the same column.

I just gave you the sample
parameter.png
Preview the report.

Preview.png
I have also attached the rdl file.

If you want to let your user to select more that one values in the parameter. Please check the muliple value check box for parameter.

Muliple.png
When you pass the value of this parameter in the stored procedure. You have to use

Join function of the ssrs.
eg. Join ( parameter.value,",")

In stored procedure just use split function to split the values and make a join.

You can find the code for split function in sql from web.



Hope it will help you.
ParameterFromDatabase.rdl
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41906816
Try..

Sample table

CREATE TABLE ReadMoreData
(
	Items VARCHAR(20)
)
GO

INSERT INTO ReadMoreData VALUES
 ('Chilly')
,('Luke Warm')
,('Hotter')
,('Hot')
,('Cold')
,('Warm')
,('Warmer')
GO

Open in new window



Script you need ... @param is your parameter from SSRS report


DECLARE @param AS VARCHAR(MAX) =  'Chilly,Luke Warm,Hotter'
SET @param = ''
SELECT @param = @param + SplittedString + ','
FROM 
(
	
		SELECT SplittedString FROM 
		(
			SELECT * FROM (
			SELECT  CAST('<A>'+ REPLACE(@param,',','</A><A>')+ '</A>' AS XML) Xmlcol ) a 
		) s
		CROSS apply
		(
			SELECT ProjectData.D.value('.', 'VARCHAR(15)') as SplittedString
			FROM s.xmlcol.nodes('A') as ProjectData(D)
		)a
		UNION
		SELECT Items FROM ReadMoreData
	
)p

SELECT @param = CASE WHEN CHARINDEX(',',para,LEN(para)-1) = LEN(para) THEN SUBSTRING(para,1,LEN(para)-1) ELSE para END
FROM
(
	SELECT CASE WHEN CHARINDEX(',',@param,0) = 1 THEN SUBSTRING(@param,2,LEN(@param)) ELSE @param END para
)y

PRINT @param

Open in new window



Output


Chilly,Cold,Hot,Hotter,Luke Warm,Warm,Warmer

Open in new window

0
 

Author Comment

by:thayduck
ID: 41907151
If @param, from the report, has   =     'Chilly,Luke Warm,Hotter'

and table has below 3 records in it,

then I want @param to =  'Chilly,Luke Warm,Hotter,Cold,Warm,Warmer'    when all is done.

Don't do this in your code            SET @param = ''   then execute and you see that it is not quite what I want.
You get    Chilly,Luke Warm,HotterChilly,Cold,Hotter,Luke Warm,Warm,Warmer

But, you are close.


INSERT INTO #ReadMoreData VALUES
 
('Cold')
,('Warm')
,('Warmer')
GO
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41907196
Not getting , explain more..
0
 

Author Comment

by:thayduck
ID: 41907213
The parm from the report has this:  'Chilly,Luke Warm,Hotter'

The table has this : Cold  Warm  Warmer

Now, I want your code to add Cold  Warm   Warmer  to the end of report parm so the report parm looks like :

 'Chilly,Luke Warm,Hotter,Cold,Warm,Warmer'

Why do you do this :   SET @param = ''  
By doing that, I will lose whatever came in from the report.


Also, do you work for a railroad ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41907220
Try..

DECLARE @param AS VARCHAR(MAX) =  'Chilly,Luke Warm,Hotter,Pawan'

DECLARE @Newparams AS VARCHAR(MAX) =  ''

SELECT @Newparams = @Newparams + SplittedString + ','
FROM 
(
	
		SELECT SplittedString FROM 
		(
			SELECT * FROM (
			SELECT  CAST('<A>'+ REPLACE(@param,',','</A><A>')+ '</A>' AS XML) Xmlcol ) a 
		) s
		CROSS apply
		(
			SELECT ProjectData.D.value('.', 'VARCHAR(15)') as SplittedString
			FROM s.xmlcol.nodes('A') as ProjectData(D)
		)a
		UNION
		SELECT Items FROM ReadMoreData
	
)p

SELECT @Newparams = CASE WHEN CHARINDEX(',',para,LEN(para)-1) = LEN(para) THEN SUBSTRING(para,1,LEN(para)-1) ELSE para END
FROM
(
	SELECT CASE WHEN CHARINDEX(',',@Newparams,0) = 1 THEN SUBSTRING(@Newparams,2,LEN(@param)) ELSE @Newparams END para
)y

PRINT @Newparams

Open in new window

0
 

Author Comment

by:thayduck
ID: 41907246
(3 row(s) affected)
Chilly,Cold,Hotter,Luke Warm,Pawan,Warm,Warmer

Looks great except for 1 more thing,

Can you insert a ' at the beginning and end of parm results, like below.

'Chilly,Cold,Hotter,Luke Warm,Pawan,Warm,Warmer'
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41907267
Try,

DECLARE @param AS VARCHAR(MAX) =  'Chilly,Luke Warm,Hotter,Pawan'

DECLARE @Newparams AS VARCHAR(MAX) =  ''

SELECT @Newparams = @Newparams + SplittedString + ','
FROM 
(
	
		SELECT SplittedString FROM 
		(
			SELECT * FROM (
			SELECT  CAST('<A>'+ REPLACE(@param,',','</A><A>')+ '</A>' AS XML) Xmlcol ) a 
		) s
		CROSS apply
		(
			SELECT ProjectData.D.value('.', 'VARCHAR(15)') as SplittedString
			FROM s.xmlcol.nodes('A') as ProjectData(D)
		)a
		UNION
		SELECT Items FROM ReadMoreData
	
)p

SELECT @Newparams = CASE WHEN CHARINDEX(',',para,LEN(para)-1) = LEN(para) THEN SUBSTRING(para,1,LEN(para)-1) ELSE para END
FROM
(
	SELECT CASE WHEN CHARINDEX(',',@Newparams,0) = 1 THEN SUBSTRING(@Newparams,2,LEN(@param)) ELSE @Newparams END para
)y

SET @Newparams = '''' +  @Newparams + ''''

PRINT @Newparams

Open in new window


O/p

'Chilly,Cold,Hot,Hotter,Luke Warm,Pawan,Warm,Warmer'
0
 

Author Comment

by:thayduck
ID: 41907324
Works great.

By you getting this to work allows me to close the other question I had open about the Oracle OR statement.
I will use this code instead of the Oracle OR statement to get the procedure to do what I want.

Thanks for all your help and fast responses.
0
 

Author Closing Comment

by:thayduck
ID: 41907330
Thanks for all your help and fast responses.
Your solution does exactly what I need it to do.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41907340
You might want to consider using the FOR XML PATH process (lots of examples on line) to create a single string from your tableA then concatenate that string to your parameter.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question