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

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
thayduckProgrammer AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
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
Pawan KumarDatabase ExpertCommented:
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
thayduckProgrammer AnalystAuthor Commented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Pawan KumarDatabase ExpertCommented:
Not getting , explain more..
0
thayduckProgrammer AnalystAuthor Commented:
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
Pawan KumarDatabase ExpertCommented:
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
thayduckProgrammer AnalystAuthor Commented:
(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
Pawan KumarDatabase ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thayduckProgrammer AnalystAuthor Commented:
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
thayduckProgrammer AnalystAuthor Commented:
Thanks for all your help and fast responses.
Your solution does exactly what I need it to do.
0
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.