[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

Crystal Report parameter

Can a formula field be used in creating a parameter?  When using the Field Explorer, Parameters, I don't see where formula fields are available.  For example, in a history record a status is fld number and an alpha value.  A formula has to be created to combine the field number (fld 20 and alpha in [xx.xy.xz].  History data need to be generated.
0
garyjgs
Asked:
garyjgs
2 Solutions
 
agandauCommented:
A formula can be used but not in a dynamically generated pick-list parameter.  If you need a pick-list, then I'm pretty sure you'll need to concatenate the two fields in the database.

You can use a cascading pick list.  In the parameters building dialog, if you set the type to Dynamic, and then specify more than 1 database column in the Value / Description / Parameter, then the runtime results will filter the second picklist based on the selection from the first.  If that helps any.
0
 
mlmccCommented:
Are you trying to generate the list of values?
If so you could use a formula to generate them.  You do it outside the parameter screen.

Are you trying to filter based on the parameter?

mlmcc
0
 
garyjgsAuthor Commented:
I have active records (employees) that are in one table and what I really need to review are the historical records (hrhistory) of those employees.  In order to obtain the hrhistory, the values are stored in 2 fields, such as, status.  In hrhistory, status is identifed by fld-nr = 17 and a-value in  [a1. a3, a5].  To get the status the formula is {fld-nr=17} and {a-value in [a1,a3,a5]}.  The hrhistory table has a begin date of when these status change.  I need by begin date and by ({fld-nr=17} and {a-value in [a1,a3,a5]})  get the histiorical status at the time of the begin date.  The begin date appears in the parameter drop down selection, but the formula field doesn't.  What I attempted to do is to say if the current employee field is = to the parameter (employee status) then parameter = formula field since I don't see the formula field in the parameter drop down list.
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
James0628Commented:
By "parameter drop down list", are you talking about the list of default values for a parameter?  That's how I think most of us would interpret it.

 I think maybe you're in the select expert and are trying to add a formula as a condition in the record selection.  If so, you can't add formulas that way.  They aren't shown in the "Select Expert" dropdown list.  Instead, you need to go to the formula and add that condition manually.  If you're in the "Select Expert", click on "Show Formula" and then "Formula Editor".

 In your first post, you talked about combining two fields.  Do you really need to combine them, or do you just need to test them?

 What do you need to test for, exactly?  fld-nr = 17 seems simple enough, but (a-value in  [a1, a3, a5]) is a bit vague.  Are a1, a3 and a5 string literals or fields or ???

 James
0
 
garyjgsAuthor Commented:
The hrhistory table stores values with the combination of fld-nr and a-value.  For example, my history status could be fld-nr=17 and a-value = either a1, a3, or a5.  In ths example my begin date (effective date) is 03-01-2009 and my status is a1 (full time employee).  The fld-nr in this example would be fld-nr=17 and a-value would be a1 out of the array of [a1,a3,a5].  If my status would change to part time employee on 04-01-2009, the fid-nr=17 and the a-value would be a3 from the array of [a1,a3,a5].  What I need to do is at what point in time is my status.  I need to be able to select from the beg-date what my status is as of 03-01-2009 which would be full time (fld-nr=17 and a-value = a1.  When my status changes on 04-01-2008, I need to be able to find the status of a3, (fld-nr-17 and a-vallue =a3).  The a1, a3, a5 values are strings.
0
 
James0628Commented:
> When my status changes on 04-01-2008, I need to be
 > able to find the status of a3, (fld-nr-17 and a-vallue =a3)

 Do you actually want to see that the status changed on 04/01, or is that just an example of how the status can change and you want to include both the a1status and the a3 status?

 If it's the latter, I think the basic problem is that you're trying to combine conditions in a formula and use that, and you really just need to add the conditions separately.

 In the "Select Expert", add a condition for fld-nr = 17.  Then add another condition for a-value "is one of" a1, a3, or a5.

 James
0
 
garyjgsAuthor Commented:
What I need to do is that from the Parameter Menu provide the end user of the report the capability of selecting whether the employee was Full Time (a1) for the beg-date of 03-01-2009 or was Part Time (a3) for the beg-date 04-01-2009 from the hrhistory table.  The end user wants to know the status of the employee for a date, 03-01-2009 or 04-01-2009, and be able to select Full Time (a1) or Part Time (a3).  There is the other table 'employee' which maintains the current status, emp-status would = a1 as of 03-01-2009.  When the emp-status is changed as of 04-01-2009, the emp-satus in employee table is updated with a3.  The hrhistory table is then updated with a beg-date 04-01-2009 with another entry of beg-date = 04-01-2009.  
0
 
James0628Commented:
> The end user wants to know the status of the employee for
 > a date, 03-01-2009 or 04-01-2009, and be able to select Full
 > Time (a1) or Part Time (a3).

 So, if the end user selects a beginning date of 03/01 or 04/01, you want to tell them that the status of an employee was a1 or a3, respectively, on those dates, so that they can select that status?  If so, what's the point?  You tell them that the status on 03/01 was a1, so they select a1, so they get the record for 03/01.  But they would have gotten that anyway.

 The other thing here is that you seem to be talking about the status for a single employee as of a certain date.  Is this report going to be run for just one employee at a time?

 I suspect that I still don't really get what you're trying to do.

 James
0
 
garyjgsAuthor Commented:
There are 5,000 employees and there is a need to go back historically and see what the status of each employee.  By determining the status, a count is made of the gender for reporting.  
0
 
James0628Commented:
So, you can run the report for a certain date and have it check the current and/or history tables and show the status of each employee as of that date.  I don't see the connection to getting different status values (a1, a3, etc.) in the list of values for a parameter.  If you want the user to be able to ask for the employees with a specific status as of a certain date, they could enter/select a1 or "Full time" in a parameter, but I don't see any need to try to preset the parameter values to contain only specific status codes.  It could just be a list of all of the possible codes and the user selects the one they want.

 James
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now