November 26, 2008 at 4:11 am
Hi!
I have successfully managed to set up a dynamic parameter list with a MS Access 2003 database in SQL Reporting Services 2005, noticing that you can not use named parameters in Access.
The dynamic parameter query list looks like:
[font="Courier New"]SELECT Distinct Department
FROM tblData [/font]
and the corresponding parameterized dataset query looks like:
[font="Courier New"]select * from tblData
where parameter=?[/font]
I am trying to add 'All Items' to a dynamic parameter list but have run in to problems with setting up the query for parameterized data set.
The parameter list query looks like (works fine):
[font="Courier New"]SELECT 'All Items' As Department from tblData
Union
SELECT Distinct Department
FROM tblData[/font]
I have tried to use the following parameterized data set query:
[font="Courier New"]select * from tblData
where parameter=? OR ?='All Items'[/font]
but this results in two parameters, who both has to be set to desired value to get the report to work properly.
How do I do to get All Items to work with one parameter when I can not use named parameters with MS Access database? Sadly, the database has to be in MS Access (I have no problem getting this to work with a SQLServer 2005 database).
Any suggestions are appreciated.
Regards,
Bo Lausten-Thomsen
December 1, 2008 at 5:52 am
You need to code in your Null value to your Main Sql like this
select * from tblData
where((? is null) or (parameter=?))
Then if you pass in a Null value to ? it is ignored and that will return all items
December 1, 2008 at 6:44 am
Thanks a lot Brunm
Today I am traveling so I can not try but I will do it first thing tomorrow an let you know my progress.
/Bo
December 1, 2008 at 10:09 am
Also in the Dataset properties you can assign the same report parameter to both the query parameters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2008 at 2:09 am
Thank you Brunm and Jack for your answers!
With Jacks solution it works like a charm.
I also tried with Brunms solution but I could not get it to work properly. I only want the user to se one parameter, the one with the values from the dynamic parameter list. Therefore I set the default value of the other parameter to Null which led to all Items being showed no matter what value I choose from the parameter list. Maybe I did something wrong here...
So, thanks again for your answers, it is working.
/Bo
December 2, 2008 at 6:16 am
Great, glad I could help.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 2, 2008 at 6:29 am
This is what i was suggesting, i do this with all my parameters that i use in SSRS report as it allows me ignore criteria/parameters if i want to
Your Parameter is populated by the dataset
SELECT NULL AS ID , 'All Items' As Department from tblData
Union
SELECT Distinct Department AS ID , Department AS Department
FROM tblData
Which would feed into a Parameter called @Department_id (as value and Label)
Then your Main Sql would be
select * from tblData
where ((@Department_id is null) or (Department_id = @Department_id))
Hence when a user selects 'All items' Null will be passed into @Department_id which will trigger the left hand side of the where clause
December 2, 2008 at 7:49 am
Your suggestion will work nice with when the database is in SQL Server but when the database is in MS Access you can not use namned varible (@Department_id ), you have to use ?.
This results in two parameters not in one as if you use SQL Server:crying:. And as you only want to show one parameter to the user (the user should not have to enter value for two parameters), you have to point both parameters in the main SQL to the same value, as Jack suggested.
/Bo
April 23, 2009 at 3:02 am
brunm (12/2/2008)
This is what i was suggesting, i do this with all my parameters that i use in SSRS report as it allows me ignore criteria/parameters if i want toYour Parameter is populated by the dataset
SELECT NULL AS ID , 'All Items' As Department from tblData
Union
SELECT Distinct Department AS ID , Department AS Department
FROM tblData
Which would feed into a Parameter called @Department_id (as value and Label)
Then your Main Sql would be
select * from tblData
where ((@Department_id is null) or (Department_id = @Department_id))
Hence when a user selects 'All items' Null will be passed into @Department_id which will trigger the left hand side of the where clause
I have the above working fine in my report, but I have an additional problem.
My parameter list look like this:
All Terminal Types
Fleet
Mini M
Sat-B
Sat-C
But I know I have a few records (8 out of 20-30,000) which have no Terminal Type. In the SQL database they appear to be NULL. I don't seem to have any way to get them to return as an option.
Can anyone help?
April 23, 2009 at 3:20 am
You will have to convert the Nulls to something else
ISNULL(TBL.FIELD.VALUE,'No Value')
Eg
where ((@Department_id is null) or (ISNULL(Department_id, 'No Value') = @Department_id))
Please note the difference between setting Null Data Values to something else ('No Value') and passing in a Null Parameter which is handled by the SQL above.
Your parameter List will then have an extra entry
No Value
All Terminal Types
Fleet
Mini M
Sat-B
Sat-C
April 23, 2009 at 4:19 am
Thanks for you reply brunm.
I'm not 100% with you, mostly because I'm still new to this...
At the moment, my parameter dataset query looks like this:
SELECT DISTINCT TERM_TYPE, TERM_TYPE as Terminal
FROM TankerPacific_Final_Reporting_View
UNION
SELECT NULL AS TERM_TYPE, 'All Terminal Types' as Terminal
FROM TankerPacific_Final_Reporting_View
and my main query looks like this:
SELECT TankerPacific_Final_Reporting_View.*
FROM TankerPacific_Final_Reporting_View
WHERE (TERM_TYPE = @TType) OR
(@TType IS NULL)
What am I adding to which bit?
April 23, 2009 at 4:33 am
Your Sql changes to
SELECT DISTINCT TERM_TYPE, TERM_TYPE as Terminal
FROM TankerPacific_Final_Reporting_View
UNION
SELECT NULL AS TERM_TYPE, 'All Terminal Types' as Terminal
Union
SELECT 'No Value' AS TERM_TYPE, 'No Value' as Terminal
and
SELECT TankerPacific_Final_Reporting_View.*
FROM TankerPacific_Final_Reporting_View
WHERE (ISNULL(TERM_TYPE, 'No Value') = @TType) OR (@TType IS NULL)
April 23, 2009 at 4:40 am
Many thanks brunm, that worked perfectly!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply