Adding an active picklist - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Adding an active picklist

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2013-02-07 : 15:41:34

to an established matrix
to filter on a provided distinct dataset
How do I make a parameter for that?

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-26 : 23:41:32

Hi Mike,
Can you give me a bit more info?

In Simple terms, what does you current dataset contain?
Are you adding another dataset to filter the current dataset (ie. was that your plan)
What will the parameter show?

I'll jump ahead a little now just in case it gets you somewhere (plus it's 4am and the wife will be annoyed if I wake her now!)

I have built reports that use datasets to act as filters but depending on what you want the output to look like there are many ways to do it.

As an example:
I had a report that could show data for a single country or by continent.
The countries and continents where all in a single table such as


RegionID RegionName  ParentID
       1 Global      NULL
       2 West Europe 1
       3 France      2
       4 Germany     2
       5 UK          2

Data in another simple table


RegionID ProductID Qty Value
       3         1  10   100
       3         2  20   120
       4         1  30   300
       4         3  40   160
       5         3  50   200
       5         7  60    40

Create a basic query to get my data such as (call it dsMainData)


SELECT * FROM myTable WHERE RegionID IN (@RegionID)

Create a DataSet to contain the list of countries to pass to the query. If it was a single region, e.g. UK then just UK would be passed, if it was a continent then all children would be passed.
The query for this dataset (let's call it dsRegionList) would be


SELECT * FROM Regions
WHERE RegionID = @SelectedRegionID
OR ParentID  = @SelectedRegionID

Now create a DataSet with a plain list of regions (this will be presented to the user)
Lets call it dsSelectedRegion and the query would be simply


SELECT * FROM Regions

Look in the parameters tree in the report data pane.
By now, most of your required parameters will have been automatically created when you created the datasets because we specified @parameters in the query, if not just create them.

Go to your SelectedRegionID parameter and set the available values to your query dsSelectedRegion using RegionID as the value and RegionName as the label
Now go to your RegionID parameter and set 'Allow multiple values' to True, then set the available values to you query dsRegionList using RegionID as the value and RegionName as the label, do the same in default values using RegionID as the value.

Now that should be it..... just create your report based on dsMaindata.

If you get any issues with the order of parameters, you have to move them maually in the RDL but if you followed above they should be in the right order.

How it works.
First the user chooses from the list of regions. Lets say they choose West Europe..
The dataset dsRegionsList will be automatically updated to contain West Europe, UK, Germany and France..
Our RegionID parameter (which you can set to hidden once it works) now will show our four regions (west europe contains no data so it does not matter in this case that we included it)..
The dsMainData dataset will now be updated and return records matching any of our 4 regions as the query uses an IN clause "WHERE RegionID IN (@RegionID)".

Well I did all that from memory so it may not be 100% but it might be what you are looking for, if not just give me more details and I'll get back to you when I can.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-26 : 23:58:30

i dont think it will work as intended

when you select multivalue from report it will send value as a comma separated list
ie in your above example values will be sent as 2,3,4,5 for region

then when you use it in your sql it will be like below illustration



declare @regionID varchar(1000)

set @regionID='2,3,4,5'
declare @test table
(
RegionID int,
ProductID int,
Qty int,
Value int
)
insert @test 
values (3,         1,  10,   100),
       (3,         2,  20,   120),
       (4,         1,  30,   300),
       (4,         3,  40,   160),
       (5,         3,  50,   200),
       (5,         7,  60,    40)

	   SELECT * FROm @test where RegionID IN (@RegionID)


output
-------------------------------

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '2,3,4,5' to data type int.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 00:06:56

If you want to implement multivalued filter in sql query you need to parse out individual values and then use it for filtering

you can use variety of method for parsing like using UDF,XML etc. My preffered method is to use XML as given below for your scenario




declare @regionID varchar(1000)

set @regionID='2,34,5'
declare @test table
(
RegionID int,
ProductID int,
Qty int,
Value int
)
insert @test 
values (3,         1,  10,   100),
       (3,         2,  20,   120),
       (4,         1,  30,   300),
       (4,         3,  40,   160),
       (5,         3,  50,   200),
       (5,         7,  60,    40)

	   SELECT * FROm @test 
	   WHERE CAST('<Root><Node>' + REPLACE(@RegionID,',','</Node><Node>') + '</Node></Root>' AS xml).exist('/Root/Node[.=sql:column("RegionID")]') =1

output
---------------------------------------------------------
RegionID	ProductID	Qty	Value
---------------------------------------------------------
3	        1	        10	100
3	        2	        20	120
4	        1	        30	300
4	        3	        40	160
5	        3	        50	200
5	        7	        60	40

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 00:09:28

another way is use string pattern comparison but it may not perform well for large datasets


declare @regionID varchar(1000)

set @regionID='2,34,5'
declare @test table
(
RegionID int,
ProductID int,
Qty int,
Value int
)
insert @test 
values (3,         1,  10,   100),
       (3,         2,  20,   120),
       (4,         1,  30,   300),
       (4,         3,  40,   160),
       (5,         3,  50,   200),
       (5,         7,  60,    40)

	   SELECT * FROm @test 
	   WHERE ',' + @RegionID + ',' LIKE '%,' + CAST(RegionID AS varchar(10)) + ',%'

output
---------------------------------------------------------
RegionID	ProductID	Qty	Value
---------------------------------------------------------
3	        1	        10	100
3	        2	        20	120
4	        1	        30	300
4	        3	        40	160
5	        3	        50	200
5	        7	        60	40

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 00:33:18

Trust me guys, it does work. I've been doing this exact process for years. As long as you don't call stored procedures in your datasets, SSRS will generate dynamic SQL and inject you multivalue list into your IN(@param) correctly. Run a trace on the server while you execute the report and you'll see it in action.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 00:41:28

quote:
Originally posted by Alan Schofield

Trust me guys, it does work. I've been doing this exact process for years. As long as you don't call stored procedures in your datasets, SSRS will generate dynamic SQL and inject you multivalue list into your IN(@param) correctly. Run a trace on the server while you execute the report and you'll see it in action.

We never put queries directly in SSRS. Its not a good practice as using stored procedures will ensure execution plans being cached and reused. Also putting queries in SSRS will require opening and modifying reports each time when some tweaks have to be done at data side which doesnt affect metadata of the datasets.Keeping them in SPs ensure you just need to change the SP and does not touch report at all.

Can you tell how SSRS made query dynamic? I dont think it will make it dynamic by itself. Were you using some wizard for query creation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 00:53:43

Give me a few hours to sleep as I've been awake for 22 hours! when I get back to my pc I'll post some examples in a new thread as we've taken this one off topic a little.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-27 : 01:03:49

Ok..No problem
I've found it myself

Apparently it works fine when you create the query inline in SSRS using query editor wizard.
However it will not work well for multi valued parameters when you use sql procedures or directly type in your query using generic editor.
In latter case you need to rewrite logic using any of the parsing techniques I illustrated in earlier threads to get it working fine for multi parameter conditions.
I've checked this in SSRS 2005 and 2008 R2 and functionality is consistent in both the versions.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 04:11:28

Hi visakh16,

I agree it does not work with SP's but it does work perfectly if you type your query into the built-in editor as long as you remember the golden SSRS rule that parameter names are case sensitive. I never use any of the wizards as they tend to create a mess that needs tidying up afterwards anyway.

I totally agree with your comments regarding SP's and why they are generally better, I've been using SQL Server for 15 years now it's by far the most secure way of doing things but what usually happens when I create a report solution is I'll end up writing maybe 15 or 20 reports, each of which are highly parametrised (to reduce the actual number of report designs) and none of which could share any significant amount of code anyway so I can either write 15 SP's or put the same queries in 15 reports without worrying about handling multi-value parameters.

Basically it's a bit of a trade off, performance/flexibility.

OK, I think we should stop here and open a new thread if we want to discuss this more...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources