Wrox Programmer Forums
|
BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7
This is the forum to discuss the Wrox book Professional SQL ServerReporting Services by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney; ISBN: 9780764568787
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 8th, 2004, 11:21 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default optional parameters in SP

I have a store procedure accepting 3 parameters (year, month, date), the "date" is optional, inside the store procedure, I will detect if the user enter the "date" parameter or not, if entered, it returns a daily sales report, if not, it returns a monthly sales report. The store procedure works fine in query analyzer, and the daily sales report (when all three parameter entered) works fine, but I get nothing return when I ignore the "date" parameter. I configured the "date" as "allowing null". Would some give me some advise how I get this work? I don't want to create two different reports (monthly, daily). THanks.

 
Old July 12th, 2004, 09:26 AM
Authorized User
 
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I had to guess the problem is in the stored procedure.

When you set the input parameters how did you declare the date? Did you set a default value for it?

Sandy Murdock MCP
 
Old July 12th, 2004, 10:38 AM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I did, I set it to "null". The procedure is fine. I tested it in query analyzer. The problem is the report service did not pass in a "null" value for the "date" parameter when I did not choose a "date" value in the "date" drop down box. I end up used a union query to partially fix the problem. I used :
select 0
union
select dd from ......

so, when the date drop down box will show data like this : 0,1,2,3,.... I changed my store procedure to detect the "0", when it is "0", I return the monthly report, when it is not 0 I return the daily report. Now I need to convert the "0" into something user like to see, like "All", so the date drop down will show up like "All, 1,2,3...." Is there a way in the reporting designer to set teh value of the drop down data different from the display value? When I have the "all" display, I want it to pass in "0" as the value.

 
Old July 12th, 2004, 11:38 AM
Authorized User
 
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Assumption:

Your dates are in a dropdownlist
Your dates are integers for the month (IE: 1 = january)
Your values are not coming from a db but were set using the parameters property of the report.

IF all these assumptions are correct, then you can open the report in visual Studio (design view). Right click on the vacant space around the report. Select Parameters.

For available values you would have non-queried checked. Currently for the values you would have (since the value seems to equal the text) an integer under the label and an integer under the value.

Change the Label to be the text string you want. Leave the value alone.

If that isn't how you did it, but you are not going to change the way the date works (and my assumption about there being 12 values, one for each month, is correct) then you may want to consider doing it this way.

My assumptions could be way off.

Hope that helps.

Sandy Murdock MCP
 
Old July 12th, 2004, 01:20 PM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your response.
The first two assumptions in your response were right, but the third one is not. I am only filling the date data that has order. For example, if there are only three days (3,5,20) has orders in March, the date drop down will only has "All, 3, 5, 20". so it is a "from query" parameter. I used a query to return the dates:

select "0"
union
select date from order where it has order


What is confusing me is, when it is a "from query" parameter, there is no way to make the label field different from the value field. I want be able to use a expression: if data return ==0, then display "All" . Is there a way to do that?

 
Old July 12th, 2004, 01:22 PM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

"expression" is not an option in thew label field drop down in the parameter dialog box.

 
Old July 13th, 2004, 09:10 AM
Authorized User
 
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default

How about this...

select 0 as value, 'All' as Name
union
select dd as value, cast(dd as char(3)) as Name....

This will put the phrase 'All' beside 0, and the numbers beside numbers for the rest.

You can then assign name as your label and value for your value




Sandy Murdock MCP
 
Old July 13th, 2004, 03:28 PM
Authorized User
 
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much! Your suggestion works perfect!






Similar Threads
Thread Thread Starter Forum Replies Last Post
Alternative to optional parameters in C# asptwodev BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 5 November 12th, 2007 03:47 AM
Passing Parameters to Subreports containing SP ms1234 Crystal Reports 0 May 18th, 2007 11:46 AM
Are Optional Parameters supported in Access 2000? howardb1 Access VBA 2 April 26th, 2006 10:01 AM
Viewing List of Parameters SP sidneyfuerte SQL Server 2000 1 September 12th, 2003 02:17 PM
Optional Stored Proc Parameters? VBAHole22 SQL Server 2000 3 August 13th, 2003 11:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.