SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2008 set defaut value on paramter


SSRS 2008 set defaut value on paramter

Author
Message
wendy elizabeth
wendy elizabeth
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3468 Visits: 824
In a SSRS 2008 r2 existing report, I am trying to add a new paramerter to the report called @GRADE. Currently the
report runs based upon @Course. Now I want to run the report based upon if the @Course or @GRADE parameter
is selected. Depending which parameter is selected, I want to generate the report in that manner. The following is
a small snapshot of the code in the dataset.

IF @Course IS NOT NULL
WITH Ctudent AS (

SELECT
BCourseSK


FROM
DataStore.dbo.vwBr
WHERE
BCourseSK = @Course

)
ELSE IF @GRADE IS NOT NULL

WITH Btudent AS (

SELECT [grade]


FROM
[DataStore].[dbo].[vwBr]

WHERE
grade = @GRADE


)

Basically either @Course or @GRADE parameter is selected but not both. For this to occur,
I have set the default value for these paramters to be null. From what I have
seen on the internet, setting the default paramter value to null is the same as setting the parameter as
an optional value.

My problem is the way I set the default parameter values to NULL. The query will not pick any values
when the report is executed.
(When run the sql in SSIS manager, the sql is working).

Thus can you tell me what you recommend I do so that the query runs and picks records?
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 1649
Assuming that the query you have written works correctly when @Course or @Grade are specified then :

1. Create the parameter and make sure to allow nulls
2. Add an available value as (NULL) either within the query (if using a dataset) or hard-coded
3. Set the default to (NULL)

I have reproduced your requirement using the process above.

Fitz
wendy elizabeth
wendy elizabeth
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3468 Visits: 824
Thanks for your response! I have a few additional questions to ask you about your comment,
"1. Create the parameter and make sure to allow nulls
2. Add an available value as (NULL) either within the query (if using a dataset) or hard-coded" which are:

a. In SSRS how do you make certain that the SSRS report allows for null values?
b. What benefit do I get by allowing NULL to be an available value? How do you specify that NULL is an available value?
c. If I specify NULL as an available value, do I need to specify other values as being available?
d. Just for my knowledge, I would like to know how to specify NULL is an available value in code.
Mark Fitzgerald-331224
Mark Fitzgerald-331224
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4381 Visits: 1649
wendy elizabeth (10/16/2013)
Thanks for your response! I have a few additional questions to ask you about your comment,
"1. Create the parameter and make sure to allow nulls
2. Add an available value as (NULL) either within the query (if using a dataset) or hard-coded" which are:

a. In SSRS how do you make certain that the SSRS report allows for null values?
b. What benefit do I get by allowing NULL to be an available value? How do you specify that NULL is an available value?
c. If I specify NULL as an available value, do I need to specify other values as being available?
d. Just for my knowledge, I would like to know how to specify NULL is an available value in code.


General comments:
When using a parameter it is usual to help the user by having a query to show the possible choices. To do this you would normally add a dataset to the SSRS report that will have something like :

SELECT DISTINCT CourseName, CourseCode
FROM dbo.CourseList



To add the NULL (or unspecified) option as shown below. The CourseName in this example will be shown to the end user. The CourseCode is used for the value.

SELECT DISTINCT CourseName, CourseCode
FROM dbo.CourseList
UNION
SELECT 'NULL',-1



You can use this in your dataset query as below. This would show the delegates for a specific course or all delegates if the NULL option (meaning unspecified) was chosen.

SELECT *
FROM dbo.DelegatesList
WHERE CourseCode = @CourseCode OR (@CourseCode = -1)



Answers to your questions:
a. In SSRS how do you make certain that the SSRS report allows for null values?
The parameter has an option to allow nulls on the first tab of the parameter settings dialog box. Check (tick) this to allow nulls.

b. What benefit do I get by allowing NULL to be an available value? How do you specify that NULL is an available value?
If you want the end users to be allowed to select NULL from a list then you must have it in the allowable choices list. Why would you need it is down to your requirement for the end users to either select a course or a grade i.e. one of those should be NULL.

c. If I specify NULL as an available value, do I need to specify other values as being available?
Yes see general comments above.

d. Just for my knowledge, I would like to know how to specify NULL is an available value in code.[/quote]
See general comments above.

From your general requirement from the first post, they query could be rewritten to include both @Grade and @Course as below. This would cover the cases :
a) return all delegates for a course regardless of grade
b) return all delegates who attained a grade regardless of course
c) return all delegates who attained a grade in a selected course


SELECT *
FROM dbo.Delegate
WHERE
(CourseCode = @CourseCode OR @CourseCode = -1)
AND (Grade = @Grade OR @Grade = -1)



Fitz
wendy elizabeth
wendy elizabeth
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3468 Visits: 824
Thank you very much! Your answer was very helpful!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search