CRXI Drop down Parameter pass to SQL Procedure 1

[CrystalQB]

Not sure if the subject was descriptive enough...sorry...but here's my dilema.

Working with Crystal XI
Datasource is SQL 2005 / Stored Proc

Fairly simple report with date parameter (user chooses month and year) built into SQL PROC

The user needs to be able to choose a Company from a drop down menu. I obviously can't pass that to the stored procedure because of it needing to be a dynamic list completely separate from PROC. So I tried setting up a statment in the Selection formula of the report itself...

IF ISNULL(?ParamCompany)
THEN qry.Company = qry.Company
ELSE qry.Company = ?ParamCompany

But I can't seem to figure out how this dynamic drop down list works in CRXI...I'm doing something wrong. Because when I try to execute the report it only gives me the two parameters from the PROC, then another parameter screen comes up with the already filled in with those PROC parameters THEN at the bottom the one from the report selection formula, and it has nothing in the drop down list except the "..."

ALSO, as you may have noticed in the Selection formula, I need to make an allowance for the possibility that if they leave the parameter blank / null it will bring up all the companies. What am I missing...can anyone point me in the direction I need to get this down?

I can do it all day long in CR10 and below, but I'm truly missing the boat on XI...I'm lost.

Any help is GREATLY appreciated!
Thank you,
CrystalQB

 

[lbass]

In CR XI the parameter cannot be left null or blank. You would ordinarily build in an option of "All" and then set up your record selection like:

(
IF {?ParamCompany} <> "All" then
qry.Company = ?ParamCompany else
if {?ParamCompany} = "All" then
true
)

I'm unclear where you are attempting to get the dynamic pick list from.

-LB

 

[CrystalQB]

yeah, I'm afraid my instructions have not been laid out very good, because I, too, do not know where the "drop down " options should be coming from. I'm working on that part now, from the requesters.

I'd LIKE it to come from a list of all the active companies...but there's no way to populate that from the PROC. So I guess it's just going to have to be a static list that gets updated on occasion. Not sure how else to get around that.

The record selection you provided me, makes sense, but where do I add in the "All"? Do I make an additional formula, or does it go in the Parameter itself?

Thanks,
CQB

 

[CrystalQB]

Figured out the All part...worked like a champ...thank you!!

So with that part figured out...is there truly no way to make my list dynamic if I want information that is not included in the results of the PROC?

The only thing I could come up with is to include the information I'm looking for within the results that the PROC displays. A LEFT oUTER JOIN within the query to bring back at least the code and name of all active companies (150 companies) and only the data for those that have activity for the given month and year. Is that a viable owrk around? Or too much out of the way?

What's your opinion?

And again thank you for the record selection tip!!

Thanks,
CQB

 

[lbass]

Ideally you would build it into the stored procedure. Try building the criteria into the from clause, e.g.,

left outer join Companies on
(
table.CompanyID = Companies.Company ID and
Companies.status = 'Active' and
Companies.date >= <date criteria>
)

But this won't give you the "All" option. You could do a separate command like this:

Select Companies.`Company ID`
From Companies
where <your criteria>
Union All
Select 'All'
From Companies

...to get the 'All' option. Don't link it to the SP. Then use it to populate your pick list, choosing the dynamic option.

-LB

 

[CrystalQB]

Okay...I created a Union witin the Proc to give an 'All' Option. It works just fine, no problem. I'm getting it all working fine except one part of it still hasn't been visited. And this could just be a bug I don't know about.

When I run the report, it asks for the Parameters from the PROC first, then asks, in a new window, for the report created parameter (which is now a dynamic list). Can I avoid the double parameter request? When I had a static list, it all came one one window the first go round...but only when it's dynamic does it pose this problem. Any ideas?

Thanks,
CQB

 

[lbass]

Can you create the parameter within the SP too? I think that's the only way to get the parameters in the same screen.

-LB

 

[CrystalQB]

I had already thought of that...and tried it, but if I create the parameter within the SP, then I will not be able to utilize the dynamic drop down list for the user to choose from.

So I guess it IS a bug, eh? Does that mean you cannot utilize the new feature of the dynamic parameter in the Enterprise environment? Because it seems to be effecting that too. No one else has any thoughts?

Thank you for all your help LB...much appreciated.

Sincerely,
CQB

 

[lbass]

I can't answer these questions. Sorry.

-LB