SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Adding an active picklist
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mikebird
Aged Yak Warrior

United Kingdom
529 Posts

Posted - 02/07/2013 :  15:41:34  Show Profile  Reply with Quote
to an established matrix
to filter on a provided distinct dataset
How do I make a parameter for that?

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/26/2013 :  23:41:32  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/26/2013 :  23:58:30  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 02/27/2013 00:04:06
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  00:06:56  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  00:09:28  Show Profile  Reply with Quote
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/

Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/27/2013 :  00:33:18  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  00:41:28  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 02/27/2013 00:42:03
Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/27/2013 :  00:53:43  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  01:03:49  Show Profile  Reply with Quote
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/

Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/27/2013 :  04:11:28  Show Profile  Reply with Quote
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...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000