Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: SQL Parameter Post Reply Post New Topic
Author Message
ekendricks
Newbie
Newbie


Joined: 03 Jan 2010
Location: United States
Online Status: Offline
Posts: 5
Quote ekendricks Replybullet Topic: SQL Parameter
     Posted: 12 Jan 2010 at 2:59pm
I'm using SQL 2005 and CR 2008 for report design.  The report server is CR 9, which is causing problems with optional parameters, and parameters in general.  The simple solution to my problem would be to use one platform, but not being the decision maker, I'm tasked with getting around the issues.
 
I have a SP with 5 Parameters - @StartDate, @EndDate, @Company, @Division and @Office.  StartDate and EndDate are mandatory parameters. What I'm wanting to do is use one SP, and create 4 or more reports, with each report using @StartDate, @EndDate, and one of the other three parameters.
 
Problem:  Using the Report Creation Wizard, selecting the SP, I get a prompt to enter values for all 5 Parameters. I select "Set to Null" and continue.  In the field explorer, I cannot delete the unwanted parameters, and the version incompatibility, won't pass null to the SP. 
 
Does anyone know of a way to prevent the Creation Wizard from attaching to the SP Parameters, with code in the SP or something?  That way I could work with whatever parameters I choose.
 
Also, the "Optional Parameter" option is not accessible when using a SP, static list of values, only when using a view or table.  When I try using "Dynamic List Of Values", the Optional Parameter is available, but the min-max value is not, and min - max are required for me to run the CR2008 report on CR 9 server.  Is there a way to enable "Optional Parameter" when using a SP AND Static List of Values, with code in the SP or Record Formula?
 
Thanks in advance for any and all assistance.
 
Ernest
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 14 Jan 2010 at 6:18am
As far as I know, when you attach to an sp, all parameters will be requested.  It's been too long, so I don't know if CR9 has a Set to Null, and it surprises me that the versions aren't passing the nulls. 
 
Since you want 1 stored proc to drive several reports, what I would try (hopefully the powers that be will approve) would be to put a hint to the user / populate the parameter with a default value, like NA.  Then in your sp you could either code to skip the section or to change the parameter to a null.
 
It is this sort of situation, along with the inflexiblity of CR parameter options that had my company move to custom wizards, which allow us to display and tailor the parameter options based on anything that we want.
 
HTH
IP IP Logged
ekendricks
Newbie
Newbie


Joined: 03 Jan 2010
Location: United States
Online Status: Offline
Posts: 5
Quote ekendricks Replybullet Posted: 14 Jan 2010 at 5:16pm

Thanks for the reply, your insight is very much appreciated.

ELK
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.