Hi,

Crystal reports newbie, and I'm a bit stuck, hoping there's an obvious answer to this.. :)

I have a query which uses a command to pull my data and which has a parameter accepting multiple values. I'd like users to be able to select these values rather than having to type themselves. The parameter is a 6 character 'scheme code', codes always begin with PP, the next 2 digits are e.g. 14 for season 2013/14, and then 01 to 09, e.g PP1401, PP1402.. are this year's codes. I want to set up this report to run for the next few years so don't want to make a static list, and I'm trying to find a way to make this list populate each time.

In Access, I've set my report to ask for the 'Season Code', this ends in 2 characters which equal characters 3 and 4 in my Scheme Code, e.g. Season Code of ST1314 means all associated Scheme Codes will be PP1401, PP1402 etc. I don't see how I can do that in Crystal though.

All of my codes are stored within a table in my database, so I've created another command which isn't linked to my initial one and doesn't have any fields used in the report which looks up the database and brings back all of these scheme codes, but when I try to create a dynamic parameter based on the results of this command I don't get any results.

So how can you look up a database for a dynamic list of parameter values? I found this thread on the SAP website - http://scn.sap.com/thread/3344125 - and someone has posted this info, but I've tried a few ways and it's not working for me. I'm using CR 2008 standalone, no server etc. Thank you in advance for any advice..

You can use Dynamic Parameters in Crystal Reports.

 

It's explained beautifully in the free booklet "How to Work with Crystal Reports in SAP Business One"

in the section on Selection Criteria Token. Just do a search and download the PDF.

 

You have to create your parameter fields in Field Explorer (Parameter Fields):

@DatePeriod,

 

@DataParam

 

Then you must edit the name @DataParam so that it reads as follows:

@Select internalSN from OINS JOIN .... WHERE....etc  ...

Write your SQL as you would normally would.

But note:

1.The SQL must not be too long, there's limited space.

2.There must be no space between @ and Select.

 

Then, you run your report in SAP B1 (Preview External Crystal Reports File)

And.. presto - You get the dropdown list from which you can select internalSN.

 

Hope it works

Regards

Leon

Hi,

Thanks, I've got that far though I just can't get my dynamic parameter to look up my database table or figure out how I could put in a formula that would give me the values I need in the drop down. I want report users to be able to select from a few scheme codes, which I'd like to either generate myself as they follow a pattern, but two digits change in them year on year and I can't see how to create them with a formula, or I'd like the parameter to look up a table in my database, I've got a bit of SQL to give the results I want but I can't put it directly into the parameter and I've put it into a command (not linked to any of my other tables/commands or used in the report) and used the command fields in the parameter but it comes up empty when I run my report.

I'm referencing this parameter within my main query in a command as I thought this would make the report faster than if it had to run and then was filtered at report level, so that's maybe the problem, but it just seems like something that you must be able to do, somehow.. :)

Jenny