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
48 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 23

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
 
LVL 23

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 23

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 23

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

947 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now