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
59 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 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