Specifying Parameter Default value as NULL -- sql server reporting services

pmud
11/2/2005 7:59:06 AM

Hi Robert,

I tried =Nothing , it didnt work. My problem is that I have a report with a
matrix with a row and column called Carrier and Month respectively. This
report has around 10 parameters and allows the users to view the report based
on one or all of the parameters of this report. Now when the data cell of
the matrix is clicked, it jumps to the subreport.

Now the problem is that when the user doesnt select any of the parameters &
ets thier value remain as null ( so that they show data for all ratger than a
specific parameter) , then when it jumps to the details report, the report
doesnt show up 'coz the paramater is sghown as blank. What I want is, that if
the user selects NULL, for any or all parametres, then in the sub report, it
should atuomatically take that parameter to show all values. How can this be
done? Below is the sample query I use in my details report:

SELECT ORDER_NO, ORDER_DATE, BILL_TYPE, PORT_REQUIRED, SALES_METHOD,
CONTRACT_LENGTH, CARRIER_CODE
FROM IRUS_INVOICES
WHERE (CARRIER_CODE = @carrier) AND (MONTH(ORDER_DATE) = @orderMonth)
AND (BILL_TYPE = @billType) OR
(CARRIER_CODE = @carrier) AND (MONTH(ORDER_DATE) =
@orderMonth) AND (@billType IS NULL)

Here the parameter Bill_Type is passed from the main matrix report. But if
the user chooses Null as the parameter in the main report, then this details
report, doesnt show any data . When I check the box next to Bill_Type ( in
details repotr), then it shows data. How can I modify my query so that if
user just selects null for any or all parameters in main report, then the
details report automatically shows the data with that parameter as null i.e
showing all data irrespective of that parameter.

Please help.
Thanks
--
pmud

[quoted text, click to view]

Chris Hastings
11/2/2005 12:30:25 PM

Hi pmud,
I think you just have a problem with order of operations in the query, try

[quoted text, click to view]

I hope that helps!
Chris

[quoted text, click to view]

pmud
11/2/2005 2:39:06 PM

Hi Chris,

Thats what I had done while wrting the query but the query designer
automatically restructured it to the following even though I had added
paranthesis in appropriate places.

Any other ideas?

Thanks
--
pmud

[quoted text, click to view]

pmud
11/2/2005 3:46:05 PM

Hi chris,

I have already tried this and I get the "Syntax error or access violation ".
I cant seem to find the error.

IF @billType is NULL
BEGIN SELECT ORDER_NO, ORDER_DATE, BILL_TYPE, PORT_REQUIRED,
SALES_METHOD, CONTRACT_LENGTH, CARRIER_CODE
FROM IRUS_INVOICES
WHERE (CARRIER_CODE = @carrier) AND (MONTH(ORDER_DATE) =
@orderMonth) END
ELSE BEGIN
SELECT ORDER_NO, ORDER_DATE, BILL_TYPE,
PORT_REQUIRED, SALES_METHOD, CONTRACT_LENGTH, CARRIER_CODE
FROM IRUS_INVOICES
WHERE (CARRIER_CODE = @carrier) AND
(MONTH(ORDER_DATE) = @orderMonth) AND (BILL_TYPE = @billType)
END

My second concern is that, I need to pass one or more of the 10 parameters
available to the user. So making the combinations of "If else" statements
will be almost impossible, since at a time any number of parameters the user
can select or leave any number of them as null.

So, I was wondering if a case statement will help. But how do I structure
that?

Thanks
--
pmud

[quoted text, click to view]

Chris Hastings
11/2/2005 4:49:54 PM

That's craziness! Sometimes I wonder about that query designer...
You could try...

IF @billType IS NULL
BEGIN
SELECT ORDER_NO, ORDER_DATE, BILL_TYPE, PORT_REQUIRED, SALES_METHOD,
CONTRACT_LENGTH, CARRIER_CODE
FROM IRUS_INVOICES
WHERE (CARRIER_CODE = @carrier) AND (MONTH(ORDER_DATE) =
@orderMonth)
END
ELSE
BEGIN
SELECT ORDER_NO, ORDER_DATE, BILL_TYPE, PORT_REQUIRED, SALES_METHOD,
CONTRACT_LENGTH, CARRIER_CODE
FROM IRUS_INVOICES
WHERE (CARRIER_CODE = @carrier) AND (MONTH(ORDER_DATE) =
@orderMonth)
AND (BILL_TYPE = @billType)
END

It seems like there should be a better way, but I'm not sure what it is.
I hope that works!
Chris

[quoted text, click to view]

Chris Hastings
11/2/2005 11:49:47 PM

Stupid designer!
I went in to design a report and tried what you are doing and I get the same
error.
a couple of things, are you using SQL for the data? If so, we can make a
stored procedure where you pass all of the info in as parameters and we can
do whatever we want to there and get away from the graphical design tool.
Or you can shut it off the graphical design porting in SQL reports.
A stored proc is probably the best. It is more efficient and it is easy to
call from reports.
Let me know if you want any assistance with that.
Chris

[quoted text, click to view]

pmud
11/3/2005 7:45:09 AM

Yes Chris. You are right. Stored Procedure will be a good idea. I will try
that and let you know if that solves my problem.

Thanks
--
pmud

[quoted text, click to view]

pmud
11/8/2005 9:00:04 AM

Thanks Chris. the stored procedure worked for me. :)
--
pmud

[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 about sql server reporting services

sql server reporting services : Specifying Parameter Default value as NULL