Parameter's List of Values - Query

[marcela79]

using Crystal XI.

I have a report where I need to limit the list of values for a parameter based on a table. Is this possible?

The report looks something like this:

WO Desc EQ EQLoc (each representing a column)

EQLoc is also a parameter, the user can select the Work orders based on the Equipment's Location.

When the user is making the selection, I want those selections to be fed from a completeley different table, with a filter applied to it. The table in question holds equipment and locations, I only want to show those objects of type 'L'.

If you are familiar with COGNOS, this would be accomplished by creating another query and applying the filter in question and linking it to the parameter...

Thank you in advance for any help you can provide.

 

[synapsevampire]

The exact same method applies here.

Use Add Command under your data source to paste in the appropriate query to extract the values, and then base the parameter on that query.

The report can be based on tables or a different query, it doesn't have to have anything to do with your parameters query, though it can if you'd like.

-k

 

[marcela79]

Thank you synapsevampire. I've tried that but then Crystal gets rid of the SQL functions I'm using in the report...

 

[lbass]

I know what you mean. I think your options are to create a static parameter list based on imported values filtered in another report and continue using SQL expressions in the body your report, or use a command to populate the list of values and also use a command as your datasource in order to incorporate the SQL functions.

-LB

 

[synapsevampire]

This shouldn't alter SQL Expressions.

Post specifics, how many data sources do you have now, how were they developed, etc.

-

 

[lbass]

Adding a command to a report in XI R1 makes SQL expressions unavailable in the field explorer.

-LB

 

[marcela79]

Hi lbass,
How do I create a static parameter list based on imported values filtered in another report?

 

[lbass]

Create a blank report that only has your location field (if that's what should be in your parameter list of values) with a record selection formula that limits it to the type "L" value. Then export this list as tab-separated text and I usually choose "application" so that it automatically opens in wordpad. Then do a search and replace choosing a quote for the "find" value and leaving replace blank. Then save the file in a place you can easily locate it.

In your original report, go into the parameter setup screen and choose actions->import and then select your text file.

If the desired list of values changes frequently, you have to update the list by rerunning the list report, exporting, and re-importing.

-LB

 

[marcela79]

Thank you very much for sharing your expertise!

 

[synapsevampire]

You're overcomplicating this.

That is a bug, it eliminates the SQL Expression as an option, however you're mistaken, you can still use SQL Expressions, you just can't add them once you add in the Command Object.

Use the tables or Views, add in your SQL Expressions, then add in the Command Object. It preserves the SQL Expressions, I just tested.

-k

 

[marcela79]

I just tried it. I added my SQL Expressions, then added in the Command Object:

select code from equipment_table where type = 'L'

It gave the the following message after I clicked OK in the Link tab:

More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed.

After I click OK, it deletes all the SQL Expressions from my report.

 

[synapsevampire]

It doesn't delete it here, it deletes the SQL Expression as an option, if you've already placed or referenced the SQL Expression it preserves it.

Try again and verify, I think that you're mistaken.

Again, it does NOT eliminate the SQL Expression, it simply disallows you from further creation, enhancement or placement of them.

I tested using 2 different data sources, SQL Server and MS Access.

Make sure that you do NOT link the Command to your tables (you may have to eliminate the links depending upon your default configuration), and ignore the warnings, they are NOT errors.

-k

 

[lbass]

SV,

I know that you can use SQL expressions if you add a command later, but if I recall correctly, I stopped doing this when I had reports later fail. Perhaps I misinterpreted the cause of the failure to be due to the command/sql expression combination. Have you used both in combination over time so that you feel confident that this approach will work over the long term?

I guess I'm having a little trouble with the logic that the report eliminates the possibility of any new SQL expressions or editing of current ones, but that existing ones are okay.

-LB

 

[synapsevampire]

LB: Consider that as soon as you use a Command Object, then the menu is disabled for SQL Expressions, that's the extent of it.

If you have specific failures, please send one along as an example to me and I'll analyze it for you.

As you know, there are many reasons that a report might fail...

As to your quetion, it feels much as your response..."if I recall correctly...". How long or how many executions would you consider acceptable before it is LB safe?

I have reports in production using this method that have been there for over a year, is that acceptable?

What if they fail tomorrow for some unclear reason, will you then assume that it's the SQL Expression again?

You'll see plenty of posts from me stating that there seems to be voodoo at play (my playful way of saying I've no frickin' idea of why something ocurred), but I will not tell clients that something does not work when I cannot verify it.

Please reconsider your approach and test again.

If you need a second pair of eyes to verify results, I'll assist how I might.

-k

 

[marcela79]

synapsevampire,

I just tried it again, and this time I deleted all of the links.

It comes back with the same msg:

More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed.

As soon as I click OK to the msg, it deletes my SQL expressions...

 

[synapsevampire]

Please, try to be descriptive.

WHere is the SQL Expression placed, on the report canvas?

Or are you just repeating what LB and I have been communicating, that the SQL Expression is no longer available in the Field Explorer.

btw, that message should always appear.

-k