Selecting records based on a custom parameter

[beacon5]

[Crystal Reports 8.5]

Hi everybody,

I posted this on another site, but haven't gotten a response and really need some help. I decided to copy the post instead of putting a link to it.

I'm working on a report that will ask the user for a parameter, then select the appropriate records based on their choice. Here's my dilemma:

The database lists units for each program at our facility. For instance, Program 1 has Unit A, B, C and Program 2 has Unit D, E, etc. The database doesn't have a table for program, but that's what I want the parameter to be because it encompasses each of the accompanying units.

So, what I've done so far is create a parameter called prmProgram that displays a dropdown list of Program 1, Program 2, Program 3, etc. Once the user selects the desired program, I need to find a way to associate each of the related units with the program that was selected, so that only those records are displayed.

I'm fairly new to report writing, so any help (with detailed instructions) would be very much appreciated.

Thanks,
beacon

 

[njahnavi]

as per my undertanding you want to list the units related to that program.

suppose there are 3 units in the program A and the user selects the Program A then the report has to show the 3 units right...

first pull the tables that contain both units and Programs.
If they are in two tables then make a relation between them

and then at the crystal report side go to "Report tab" and then select "selection formulae" in that go to "record" and write a formula like {tablename.field} = {?prmprogram}

 

[beacon5]

Hi njahnavi,

You are close. The programs aren't listed on a table, which is where my problem is.

The database lists everything by units, but our facility typically refers to and likes to report on things by program.

I created the parameter and entered by own values since the programs aren't on a table. Once the parameter is passed, I need to figure out how to get the report to understand that Program A is actually Unit 1, Unit 2, and Unit 3 and to only select the records for those units.

I thought about using the unit for the parameter and allowing multiple values, but the person I'm writing the report for doesn't want to have to select multiple values.

Is there a way to set up a formula in the select expert that says If {?prmProgram} = "A" then select {tablename.field} = "Unit 1" AND {tablename.field} = "Unit 2" AND {tablename.field} = "Unit 3"?

 

[CR85user]

If {?prmProgram} = "A" then extractstring({table.unitid},' ','') in ['1','2','3'] else
If {?prmProgram} = "B" then extractstring({table.unitid},' ','') in ['4','5'] else
etc etc

I only used the extract string to save on your typing Unit... every time.

'J

CR8.5 / CRXI - Discovering the impossible

 

[beacon5]

Hi J,

Do I use what you typed in the Select Expert as a formula?

Also, the unit isn't actually going to be 'Unit' every time. I just used that to help explain my problem.

Would this be correct syntax if the units have fairly unique names?:
if {?prmProgram} = "A" then {table.unitid} in ['CAP', 'TAB', 'DIP'] else
if {?prmProgram} = "B" then {table.unitid} in ['FIT', 'MED']

Thanks,
beacon

 

[CR85user]

Hi Beacon,

This would be used in the record selection (I would suggest not using the select expert as it has always had a history of restricting the users ability to tweak the criteria to match what is needed).

Go to the 'report' menu then Selection -> Record

and then copy exactly what you used above.

This can also be used along with any other selection criteria, e.g.:

//Selection criteria
{table.date} in lastfullyear and {table.field} = 12 and
if {?prmProgram} = "A" then {table.unitid} in ['CAP', 'TAB', 'DIP'] else
if {?prmProgram} = "B" then {table.unitid} in ['FIT', 'MED'] else
if {?prmProgram} = "C" then {table.unitid} in ['ABC', 'DEF','GHI'] else
true


The 'true' part above is just to say if none are selected or the selected option doesn't match any of the defined programs then select all (It could just as easily be used to select none or invalid entries or however you wanted to apply it.

Good luck and holler if any issues in applying the above.

'J


CR8.5 / CRXI - Discovering the impossible

 

[beacon5]

Hi J,

That worked perfectly. Thanks for the tip about using record selection as opposed to the Select Expert too. I've had to teach myself everything I know about Crystal Reports, which isn't much, so I'm glad to learn something that I know will make my life a little easier.

Off the subject, but do you know of any must have resources for someone that's just starting out with Crystal Reports and SQL?

Thanks again,
beacon

 

[CR85user]

Make sure you install the help files and read up on all the different functions and their usage.

When you first start out using Crystal you find ways to acheive things and then later on can find a much more effecient method. So always be willing t ogo back to older reports and update them.

As a general rule of thumb it improves performance if you pass as much of the processing to the database as possible so that the work is done server side.

There are a whole bunch of FAQs here on Tek-Tips which cover some key points:

http://www.tek-tips.com/faq.cfm?pid=149

As well as this there are a few sites worth visiting to brush up on some routines:

http://www.crystalbook.com/crtcr/extras/10/AppA.pdf

(Although for Cr10 many functions are common)

https://www.sdn.sap.com/irj/boc/businessobjects-forums

(Obviously not as good as Tek-Tips )

http://www.crystalkeen.com/articles/index.htm

(A few articles worth reading on here)

Ken Hamady also shares some common formulas on his site:

http://kenhamady.com/formula.html

Also consider siging up to some newsletters for info and advise. Both Ken Hamady and Chealsea Tech provide regular newsletters of this sort and archives of previous posts, well worth a read if you get the time:

http://www.chelseatech.co.nz/pubs.htm

http://kenhamady.com/cru/

With regards the SQL side, using Crystal is a great way to learn.

Create your selection criteria and look at the SQL query within crystal (Database menu -> Show SQL Query..) and then add a sort order and compare again, change the selection criteria and see how that affects the query.

For the basics of what everything means then look at sites such as:

http://www.tizag.com/sqlTutorial/

Hope the above helps start you off.

The most important tip is to make use of this site. There are many very respected 'gurus' on this site who volunteer their time to help others get as much out of Crystal as possible.

Read up on other peoples posts also, as although you may not want to know how to 'collate sales figures' or 'Make a square peg fit into a round hole' you will be surprised at how many times another solution will help you solve another issue in future.

Good luck and thanks for posting detailed information as it is half the battle in solving any issues

'J

CR8.5 / CRXI - Discovering the impossible