Select Expert - Saved Data-
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8825
Printed Date: 06 May 2024 at 1:18pm
Topic: Select Expert - Saved Data-
Posted By: bowja
Subject: Select Expert - Saved Data-
Date Posted: 07 Jan 2010 at 2:36am
|
Hi,
I want to show all records which meet my select expert criteria: {JobOrder.JobType} = "Contracting" and ({JobOrder.EndDate} in WeekToDateFromSun or {JobOrder.StartDate} in WeekToDateFromSun or {JobOrder.FilledDate} in WeekToDateFromSun or {JobOrder.OrderDate}in WeekToDateFromSun) NB. There are 164 I then want the report to indicate which of those records have "Order Taken" or "Order Taken - Electrical" in the Description field from the 2nd table. NB. There are 157 I do not want this to limit the total results. (1 if comment exists and 0 if not would be great) When I use Select expert with the following formula it only returns the 157 with the comment  .
{JobComments.Description} in ["Order Taken", "Order Taken - Electrical"] I want something like the below to happen: if({JobComments.Description} in ["Order Taken", "Order Taken - PRS"])exists then "yes" else "no" i.e. 164 results 157 with Yes in the field and 7 with No in the field. Any help would be greatly appreciated. ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Replies:
Posted By: hilfy
Date Posted: 07 Jan 2010 at 10:25am
|
In the Select expert, you'll have to manually modify the formula to be something like this:
{JobOrder.JobType} = "Contracting" and
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: bowja
Date Posted: 08 Jan 2010 at 3:01am
|
Thank you very much for your prompt response however I don't think I was clear enough previously.
There are many other "JobComments.Description"'s i.e. "Phone call", "Quote" etc If I use the formula suggested it will bring up only the results which have no "JobComments.Description" or one of "Order Taken", "Order Taken - Electrical". What I want is every one of the results which meet the initial criteria: {JobOrder.JobType} = "Contracting" and ({JobOrder.EndDate} in WeekToDateFromSun or {JobOrder.StartDate} in WeekToDateFromSun or {JobOrder.FilledDate} in WeekToDateFromSun or {JobOrder.OrderDate}in WeekToDateFromSun) And in a field on that line it to indicate if either of the comments "Order Taken", "Order Taken - Electrical" is there. If the comments aren't there I would like the result to still show up but with an indication that neither of the comments are there (this could be a blank field or a no). I hope this is a little clearer about the data I am working with. ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Posted By: hilfy
Date Posted: 08 Jan 2010 at 8:19am
|
I think I see now...
Set your link to JobComments to a left outer join so that you include all records, even if there is no corresponding comment.
If there can be only one comment for a job, then this is a simple formula:
If IsNull({JobComments.Description} then 'No'
else if {JobComments.Description} in ["Order Taken", "Order Taken - Electrical"] then 'Yes'
else 'No'
However, if there can be multiple comments for a job and you want to know if any of them have the values you're looking for this is a little more complicated.
1. Group on some field that identifies a distinct job. For this example I'll call it {JobOrder.ID}.
2. Create a formula that looks like this:
3. Use this formula in your report. It may need to go in the group footer for the job so you may need to rearrange your data accordingly.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: bowja
Date Posted: 09 Jan 2010 at 11:21pm
|
Thanks again your answer seems to be what I am intending however I cant seem to get it to work.
I am putting the formula into Saved Data Selection Formula of the JobComments.Description field (is this right?). When I try to confirm it or check the formula I get the error message: "This function can not be used as it must be evaluated later" on the following section: PreviousIsNull({JobOrder.JobOrderNo}) If I try putting the formula in Select Expert Group it says "A number is required here" and the cursor is at the end after comments. Also when you say IT may need to go in the group footer do you mean the description field or formula? Thanks again for taking the time to help me with this ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Posted By: hilfy
Date Posted: 11 Jan 2010 at 6:40pm
|
No, neither of those places. I went back a re-read your original post response above and I see that you want to show all data that meets your criteria whether or not they have the descriptions you're looking for and you want to indicate for each whether they have the comments you're looking for, correct?
Just create the formula as a formula, this will be your yes/no indicator. Put all of the information about the job order in one or more group header sections. Drag this new formula onto the report whereever you need to indicate whether the selected commment(s) exist. NOTE: you may need to add "WhilePrintingRecords;" as the first line in the formula to get it to work correctly, but try without it first.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: bowja
Date Posted: 17 Jan 2010 at 4:21am
|
Still struggling so thought I would start again and try to be more clear.
I am working in FastTrack which has a number of Tables. The ones I need for this part are Job Order and Job Order Comments. What I am trying to do: I am creating a report which needs to show all records which meet my Job.Order selection criteria: {JobOrder.PositionType} = "Temp" and ({JobOrder.OrderDate} in Last7Days or {JobOrder.FilledDate} in Last7Days or {JobOrder.StartDate} in Last7Days or {JobOrder.EndDate} in Last7Days) i.e. OwnerStaffID JobOrderNo OfficeCode JobStatus JobType CommissionType KATH 200,037,625 100 Filled Firm Order Commission 1 JAMESM 200,037,562 100 Filled Firm Order Commission 1 JAMESM 200,037,082 100 Closed Firm Order Commission 1 JAMESM 200,037,549 100 Filled Firm Order Commission 1 CHRISTI 200,037,530 200 Filled Firm Order Commission 1 Say there are 100 results. I can do this successfully. The field "Job.Order.OrderNumber" is unique to each of the 100 records. What I want to do is integrate the Job Order Comments table to return my 100 results (and only those 100 results) with two new fields which say yes or no dependent on if the JobOrderComments table has the comments "Order Taken", "Order Taken PRS" etc and/or "Order Filled", "Order Filled Fenton" for each of the JobOrder results ie each JobOrder.OrderNumber. NB for every Job Order there are a number of corresponding comments i.e. lots of comments for each JobOrder.OrderNumber. So if I simply add the field "description" I get the following: OwnerStaffID JobOrderNo OfficeCode JobStatus CommissionType Description FENTON 200,027,865 300 Filled Commission 1 Order Taken FENTON 200,027,865 300 Filled Commission 1 Purchase Order FENTON 200,027,865 300 Filled Commission 1 Order Confirmed FENTON 200,027,865 300 Filled Commission 1 Order Confirmed FENTON 200,027,865 300 Filled Commission 1 Order Confirmed FENTON 200,027,865 300 Filled Commission 1 Order Confirmed FENTON 200,027,865 300 Filled Commission 1 Rate Authorisation- FENTON 200,027,865 300 Filled Commission 1 QC Closing Note FENTON 200,027,865 300 Filled Commission 1 Rate - Authorisation FENTON 200,029,211 300 Closed Commission 1 Order Taken FENTON 200,029,211 300 Closed Commission 1 Email/Post FENTON 200,029,211 300 Closed Commission 1 3. Resume only FENTON 200,029,211 300 Closed Commission 1 3. Resume only FENTON 200,029,211 300 Closed Commission 1 3. Resume only FENTON 200,029,211 300 Closed Commission 1 Order Confirmed - Fenton FENTON 200,029,211 300 Closed Commission 1 Job Order update FENTON 200,029,211 300 Closed Commission 1 Job Order update FENTON 200,029,211 300 Closed Commission 1 Order Confirmed - Fenton FENTON 200,029,211 300 Closed Commission 1 Job Order update FENTON 200,029,211 300 Closed Commission 1 QC Closing Note Visual what I want would look something like this: OwnerStaffID JobOrderNo OfficeCode JobStatus CommissionType Order Taken Comment Present Order Confirmed Comment Present FENTON 200,027,865 300 Filled Commission 1 Yes Yes FENTON 200,029,211 300 Closed Commission 1 Yes Yes VICKI 200,032,866 200 Closed Commission 1 Yes Yes AARON 200,033,741 200 Closed Commission 1 No Yes AARON 200,034,131 200 Closed Commission 1 Yes Yes DEBBIE 200,034,336 200 Closed Commission 1 Yes Yes TRA. 200,034,788 300 Closed Commission 1 Yes No VICKI 200,034,840 200 Filled Commission 1 Yes Yes FENTON 200,035,088 300 Closed Commission 1 Yes Yes SALLY 200,035,483 200 Cancelled None No Yes BENNY 200,035,956 300 F-Competitor None Yes Yes KATH 200,035,960 100 Submitted None Yes Yes PEOPLE 200,036,025 300 Filled Commission 1 Yes Yes BENNY 200,036,066 300 F-Competitor None Yes Yes VICKI 200,036,168 200 Cancelled Commission 1 Yes Yes TRA. 200,036,233 300 Filled Commission 1 No No FENTON 200,036,276 300 Filled Commission 1 Yes Yes ELIZA 200,036,294 100 Cancelled None Yes No JAMESM 200,036,321 100 Filled Commission 1 Yes No CHRISTI 200,036,358 200 Filled Commission 1 Yes Yes CHRISTI 200,036,376 200 F-Competitor None Yes No NETWORK 200,036,445 300 Closed Commission 1 No No FENTON 200,036,457 300 Closed Commission 1 Yes Yes KYML 200,036,560 200 Closed Commission 1 Yes Yes JAMESM 200,036,730 100 Closed Commission 1 Yes Yes SALLY 200,036,742 200 Submitted None Yes Yes GAVIN 200,036,753 200 Filled Commission 1 Yes No KYML 200,036,787 200 Submitted Commission 1 No Yes DEBBIE 200,036,792 200 Closed Commission 1 Yes No ELIZA 200,036,820 100 Filled None Yes Yes KATH 200,036,837 100 Filled Commission 1 Yes Yes FENTON 200,036,855 300 Filled Commission 1 Yes Yes KYML 200,036,874 200 Closed Commission 1 Yes Yes VICKI 200,036,890 200 Filled Commission 1 Yes Yes ELIZA 200,036,940 200 Closed Commission 1 No No Returning my 100 records and indicated if there is a comment in the record in the database. ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Posted By: hilfy
Date Posted: 19 Jan 2010 at 6:27am
|
Ok, you're going to have to do group on JobOrderNo to get this right so that you don't have multiple records for any order. Put your data in the group FOOTER. Then create two formulas, one for Order Taken comments and one for Order Filled comments. They will look like this: StringVar OrderTaken; You'll do the same sort of thing for Order Filled. What this formula does is every time a job id changes, it initializes the value to 'No'. Then, if a comments record exists that has the string you're looking for, it sets the value to 'Yes'. The last line (without a semi-colon!) will display the value of the variable when you drag the formula to the report. By putting your data in the group footer, you give this formula a chance to look at all of the comments records to come up with the correct Yes or No value for the job id. These are regular formulas that you drag onto the report, they are NOT part of the group or record selection criteria.
Make sense?
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: bowja
Date Posted: 20 Jan 2010 at 2:01am
|
Thank you for you patience hilfy!
I think I have managed the above and that I am finally getting somewhere...... I inserted the above formula in the Formula Fields in Field Explorer. I then dragged it to the end of the data in the group footer (where i want the yes/no to appear). The results however are still not correct. There are 253 results that should and do come up. There are 245 Job Orders with the "Order Taken" description in the comments(checked using excel and exporting all comments). However 20 do not have a "yes" or "no" (and they all have the comment description "Order Taken" except 1). Also 140 come up with "no" (most of these do have the comment) and 93 come up with "yes". The 8 records missing the comment either have "yes","no" or nothing in the formula field.  ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Posted By: hilfy
Date Posted: 20 Jan 2010 at 6:23am
|
Please copy and paste your formula into a post here so that I can see it. Also, what type of section did you put the formula in?
Thanks!
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: bowja
Date Posted: 20 Jan 2010 at 1:32pm
|
This is the formula:
StringVar OrderTaken; if PreviousIsNull({JobOrder.JobOrderNo}) or {JobOrder.JobOrderNo} <> Previous({JobOrder.JobOrderNo}) then OrderTaken := 'No'; if InStr(UpperCase({JobComments.Description}), 'ORDER TAKEN') > 0 then OrderTaken := 'Yes'; OrderTaken I created this in Formula Fields in Field Explorer and then dragged the Field into the Group Footer #1 with the other data. Below is a cross section of some of the data that the report produces OwnerStaffID JobOrderNo OfficeCode OfficeCode JobStatus OrdJobType OrderTaken DEBBIE 200,035,658 200 200 Closed Firm Order AARON 200,035,953 200 200 Closed Extended KATH 200,035,990 100 100 Filled Extended PEOPLE 200,036,025 200 300 Filled Firm Order JAMESM 200,036,085 100 100 Closed Extended CHRISTI 200,036,132 200 200 F-Competitor Firm Order ELIZA 200,036,294 100 100 Cancelled Firm Order No JAMESM 200,036,321 100 100 Closed Extended No CHRISTI 200,036,358 200 200 Filled Firm Order No CHRISTI 200,036,376 200 200 F-Competitor Firm Order No KYML 200,036,560 200 200 Closed Firm Order No JAMESM 200,037,774 100 100 Filled Payrolled Yes JAMESM 200,037,775 100 100 Filled Payrolled Yes JAMESM 200,037,776 100 100 Filled Payrolled Yes JAMESM 200,037,777 100 100 Filled Payrolled Yes SALLY 200,037,779 200 300 Submitted Firm Order Yes JAMESM 200,037,780 100 100 Closed Contingent No ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Posted By: bowja
Date Posted: 01 Feb 2010 at 6:18pm
|
Hi again, Thank you very much for all of your help however as the report was throwing up some very odd results using one formula field I tried a new technique which worked. I put the description comment in the details section, then put a custom field in with the formula: if description = "order taken" then 1 else 0. Then I created a summary field in the group footer to sum up the custom field, resetting for each new group. I also put the Job No. in the group footer
By suppressing the details I am left with the Job No. and a "0" if no Order taken comment of > 0 if there is an order taken comment. ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |