January 31, 2017 at 10:16 pm
Hello,
I am trying to implement some cascading parameters. The issue I have is that I have several environments (test, prod, etc) in two sites. This issue is that in the database environment can have the same ID and Name etc. the only thing that can make it unique is a combination of Environment ID and Site ID. Currently when it filters, I can choose TEST from one site, and as I go to make a choice from the next box it defaults to the test environment in the other site. This only happens if I have previously chosen both sites (multi choice), if I only choose one or the other, the choice of environment works correctly in that it does not change itself to the environment of the other site.
Can I assign several values to one Parameter rather can just using only the ID, which is causing me the issues? OR would I have to create several hidden parameters , that I will need to add to the filter (so 3 filters if 3 parameters are created), on the dataset?
I hope I have made some sense here!
I have googled extensivley but I do not get anything that remotely resembles my situation. Thank you for any help.
Regards,
D.
February 7, 2017 at 6:27 am
Hey Duran,
I may not be understanding your situation correctly, but it sounds like XML would solve your problem. I would have a procedure feed your parameter. The procedure would return 2 columns: ID and Value (or whatever you want to call them). I would return XML fragments in the ID column (something like <Environment="TEST" ID="123"/>...). Then, when a user selects one or more values, a comma delimited string (containing all of the XML fragments selected) is sent into another procedure. That procedure can easily shred the XML so you can use it.
Make sense?
February 7, 2017 at 7:11 am
Yes, you can pass multiple values for a parameter in SSRS. I've assume you have already configured your report to allow to submit multiple parameters, so I'm going to straight to the side of your SQL statement.
When you pass multiple parameters using SSRS, it'll the values in a delimited string. Thus, for example, if you're user selected 3 colours for the parameter Colour, it would pass 'blue, green, yellow'. Obviously none of your items will have a colour of 'blue, green, yellow', so you need to extract each colour out. SQL Server 2016 has a method of doing this already, however, I don't know what version you are using. I'm therefore going to use the Delimited8K splitter, which you can find in the article Tally OH! An Improved SQL 8K “CSV Splitter” Function.
Once you've added that function to your server in your preferred location (such as the database you're running the query on, or a central functions repository database), you would then need to update your SP/SELECT statement to make use of it. For a simplified example:SELECT *
FROM dbo.Balls B
WHERE B.Shape = 'Sphere'
AND B.Inflated = 1
AND B.Colour IN (SELECT S.Item FROM SharedFunctions.dbo.DelimitedSplit8K(@Colour, ',') S);
Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply