ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Parameter for user-defined item parameters
http://www.istechforum.com/YaBB.pl?num=1264013058

Message started by David Waldmann on 01/20/10 at 11:44:18

Title: Parameter for user-defined item parameters
Post by David Waldmann on 01/20/10 at 11:44:18

I am trying to modify a report that I use to replenish stock. There are two levels to my selection criteria, but they could be defined as one.

Here is a sample of my item numbers:
100AR
100CR
104AR
104CR
120AR
120CR
122AR
122CR

First, we make WOs by the group 100, 104, 120 and 122 (one prefix for each group)
However, we also group by 100 & 120 (bold), and 104 & 122 (italic).

The way I currently have it set up I can enter a selection criteria based on the first grouping, i.e. One Of: 100, 120. This works, but I want to set it up so that whoever is doing this doesn't need to know which sub-groups go together, and they can just pick "Bold" versus "Italic" and get all the applicable ones.

And I'd like to set it up as a Parameter, to make it more user-friendly, but I can't see how to make a Parameter refer to a group of things (either by referring to an existing formula, or creating one as part of the Parameter). Is this possible?

If I HAVE to I can use some non-used field in the Item info, but I'd really like to use what I already have.

Title: Re: Parameter for user-defined item parameters
Post by dameng on 01/21/10 at 16:28:36

one of two ways:
1) use the "LIKE" record selection, so the user puts in only the 120, which will be select records like "120*" or use the ONE OF and the user puts in 100* and 120*
or
if you want a drop down list of selectible values,
2) create a view that returns the left 3 char of the Item code, and include this view in your report linked appropriately.

couple thoughts at least.

Title: Re: Parameter for user-defined item parameters
Post by David Waldmann on 01/22/10 at 05:20:55

Dave - thanks for your thoughts.

However, I believe that only gives me the functionality I already have. I want them to be able to choose (based on my example) "Bold" which would select 100* and 120*, or "Italic" which would return 104* and 122*. I don't want them to have to know that they need to select 100* & 120* - they don't know which ones go together - only that we need to run Bold or Italic...

Title: Re: Parameter for user-defined item parameters
Post by GasGiant on 01/22/10 at 06:26:57

the SQL "IN" keyword let's you enter a list, so the Bold query would be something like "SELECT * FROM INVTXN WHERE mtit_code IN('100AR','100CR','120AR','120CR')" and the Italics query would grab the others. the "LIKE" matches would not work for the italics list, but "LIKE '__0_R'" would work for the bold group. So would "LIKE '%0%'", but it would likely pick up a lot of other items that have zeroes anywhere between the first and last character.

Title: Re: Parameter for user-defined item parameters
Post by David Waldmann on 01/22/10 at 09:18:59

Colin, I think I know what you're driving at, but I'm not sure how I can make the selection criteria user-selectable unless I make a separate report for every group (which I had never thought of doing until right now - that would work, but be kind of clunky). I want to be able to define several selection criteria (as you suggested, SELECT IN works and is what I've been using so far for testing), and then enable the user to pick which criteria to use. Ideally, they would be able to select one or more of the criteria.

I was envisioning a Parameter, but I can't see how to make the selection criteria use that. Yes, you can use Parameters in the selection criteria, but the Parameter doesn't seem to be able to translate as a "selection smart" entry. For instance, if I could make a Parameter and prefill it with values of SELECT FROM....IN(100AR,100CR,etc), SELECT FROM....IN(104AR,104CR,etc), labeled with the proper descriptions, I would get what I'm looking for.

BTW, the example I gave is not complete, so the LIKE match wouldn't work, as there are too many variables in the item number format. I need to be able to enter a list.

Maybe I'm asking for too much...?

Title: Re: Parameter for user-defined item parameters
Post by dameng on 01/23/10 at 10:15:12

dave,
your Parameter could be a text field with two choices, "BOLD" , "ITALICS".
Then you create a record selection formula, whereas, IF ?choose = "BOLD" then Item code IN (,,,,,,) or like, ELSE IF ?choose = "ITALICS" Then Item code IN or like....

if that doesn't work for you, there is a 3rd party plug in for Crystal Reports, that adds to the Function Library, it's called CUT LIGHT. it allows you to have your Report run an SQL command back to the database. it's pretty slick.

Title: Re: Parameter for user-defined item parameters
Post by David Waldmann on 01/24/10 at 12:50:22

Dave,
It sounds like that should work. Will give it a try.
Thanks!