sql server reporting services: Select all records if parameter left blank -- sql server reporting services

You're in the

sql server reporting services

group:

Select all records if parameter left blank

Bruce Lawrence
9/20/2007 12:00:00 AM

sql server reporting services: I've got a report that prompts the user for a few inputs through
parameters.

One of the fields is "Shift". The values are 1,2 or 3 and the user
types what they want. Its not a drop down.

What I would like to do is select all shifts if the user leaves it
blank or selects "All" or whatever. I've searched other solutions
here but keep running into a problem with "All" not being a valid
option type.

Should I add a new parameter or modify the dataset. What should I put
in there? The data is coming from 3 different tables so forgive the
cumbersome query.

SELECT dbo.tblDetail.Asset, dbo.tblDetail.OperDate,
dbo.tblDTCodes.Reason, dbo.tblDTCodes.Code,
SUM(dbo.tblDTEvents.Duration) AS SumDur,
dbo.tblDetail.Shift
FROM dbo.tblDTEvents INNER JOIN
dbo.tblDetail ON dbo.tblDTEvents.RecordID =
dbo.tblDetail.RecordID INNER JOIN
dbo.tblDTCodes ON dbo.tblDTEvents.Code =
dbo.tblDTCodes.Code
WHERE (dbo.tblDetail.Shift = @Shift) AND (dbo.tblDetail.Asset =
@Asset) AND (dbo.tblDetail.OperDate = @Date)
GROUP BY dbo.tblDetail.Asset, dbo.tblDetail.OperDate,
dbo.tblDTCodes.Reason, dbo.tblDTCodes.Code, dbo.tblDetail.Shift
ORDER BY SUM(dbo.tblDTEvents.Duration) DESC

Re: Select all records if parameter left blank

midas
9/20/2007 12:19:37 PM

Assuming you are using 2005...

For the parameter @Shift check the "multi-value" checkbox.
Set the available values to non-queries and set the label/value as
1/1, 2/2, 3/3
Change your query to read dbo.tblDetailShift IN (@shift)
This will create a drop down box and they can choose which shifts they
want to include.
Do not allow "blank" or "null"

Re: Select all records if parameter left blank

RMJCS
9/20/2007 7:57:50 PM

I'm making some assumptions about your setup, but how about tweaking
the where clause to something like;

(@Shift is null or dbo.tblDetail.Shift = @Shift)

You might need to make sure that @shift is a numeric parameter, not
character, so that 'blank' means 'null'.

Regards, Rhys

[quoted text, click to view]

Re: Select all records if parameter left blank

Bruce Lawrence
9/21/2007 3:28:51 PM

[quoted text, click to view]

Thanks for the replies.

The I tried the @Shift Is Null and it doesn't return any errors but it
also doesn't return any records.

Re: Select all records if parameter left blank

Tom Garth
9/22/2007 11:54:01 AM

Then you propably want (@Shift = '' or dbo.tblDetail.Shift = @Shift)
--
Tom Garth

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn