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
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 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