Complex Record selection formula help
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20494
Printed Date: 05 May 2024 at 4:29am
Topic: Complex Record selection formula help
Posted By: alzniagara
Subject: Complex Record selection formula help
Date Posted: 19 Feb 2014 at 8:57am
|
Hello,
I am trying to create an active client list for the workers in my organization. I am having a very hard time due to how the data is stored in the database. I am using an ODBC connection and have included 2 tables in my report, linked as below: 
A = active, W = waiting, T = terminated Essentially every new status line in the same department cancels out the status before it. I want a client name to display on the report IF the most recent STATUS in a specific DEPARTMENT = 'A' (active). So using the sample above, client 898 is currently active in 2 departments (FS and TX) and is currently terminated from the INT department. I have tried a bazillion ways to select and/or filter the report and I still run into the same issue. If I am trying to show a list of clients currently active in the "FS" department, because the client had a status of A in the INT department at some point in the past they are still showing up on my list when I use a formula like: ({department}="FS" and {status}="A") (It's like Crystal is ignoring the 'and' in all my fomulas) However, I guess what I really need is something like: (most recent {department="FS"} and {status}="A") I hope this is clear and someone out there can help end my frustration!! Thanks! |
Replies:
Posted By: hello
Date Posted: 19 Feb 2014 at 9:59am
|
Do you have a date field in the table that can be referenced?
The status shouldn't change unless the record date is newer. |
Posted By: alzniagara
Date Posted: 19 Feb 2014 at 10:05am
| Yes, sorry I accidentally removed the image of the tables I included! |
Posted By: hello
Date Posted: 19 Feb 2014 at 12:01pm
|
Group by department, then group by date. Use the Select Expert to filter out all status codes that are not "A". Create summaries for all your fields using maximum/sum values. Then suppress all the detail and group sections except the one that holds the summaries. |
Posted By: alzniagara
Date Posted: 20 Feb 2014 at 4:04am
|
Hi - I'm not sure exactly what you mean by creating summaries for all my fields (I have a lot in the report!). What I did was create a maximum summary for status date.
I actually need the list to show all clients who have a current status of "A" OR "W" in department "CG" OR "FS". The way we track clients, every person with a status of "A" will have an older status of "W", so they are showing twice in each department group - which I was able to eliminate by suppressing if {CLIENT_NUMBER} = Previous({CLIENT_NUMBER}). I think I have the data showing I need, but how do I sort the group alphabetically by client name? When I try to use a formula as the group sort order, the only fields it gives me access to are parameter fields? |
Posted By: alzniagara
Date Posted: 20 Feb 2014 at 8:19am
|
Hi again,
It turns out I am still getting incorrect data showing on the report. Here is how I have currently grouped things (Group 1 is last name in order to sort the report alphabetically): 
And the Select Expert Formula is this: {C3CLSUP.SUPERVISOR1} = {?Counsellor Initials} and {C3CLSTAT.STATUS} in ["A", "W"] and {C3CLSTAT.DEPT_CODE} in ["CG", "FS"] My problem is that records are pulling if they have a status of "A" in any department instead of only in "CG" and "FS". If I could edit the select formula to do this: {C3CLSUP.SUPERVISOR1} = {?Counsellor Initials} and ({C3CLSTAT.STATUS} in ["A", "W"] and {C3CLSTAT.DEPT_CODE} in ["CG", "FS"]) then I think I would get the data I wanted. But if I manually add the brackets to the formula Crystal just strips them away. Thanks for your help! |
Posted By: hello
Date Posted: 20 Feb 2014 at 9:08am
|
You may have to create a sub-report to first filter the "A" status, then pass the results to the main report that will filter the "W" status.
I have never worked with sub-reports, but someone here may have. |
Posted By: Gurbs
Date Posted: 25 Feb 2014 at 1:05am
|
I haven't read the entire thread, but from your last post, I got an idea that might work.
You could create a simple formula that you can use in your select statement. Say you call it {@Pass}. Create a formula like: if {C3CLSTAT.DEPT_CODE} in ["CG", "FS"] and {C3CLSTAT.STATUS} in ["A", "W"] then 'TRUE' else 'FALSE' In your select statement, you then strip out the {C3CLSTAT.STATUS} in ["A", "W"] and {C3CLSTAT.DEPT_CODE} in ["CG", "FS"] part, and simply say: {@Pass} = 'TRUE' |
Posted By: DBlank
Date Posted: 25 Feb 2014 at 2:41am
|
I think this will work
group on client id and then group on department
in you select statmetn use
C3CLSUP.SUPERVISOR1} = {?Counsellor Initials} and
{C3CLSTAT.DEPT_CODE} in ["CG", "FS"] you now need to apply a group condition
create a flag formula
if statusdate=max(statusdate,department) and status in ["A","W"] then 1
sum this at the group 2 level
If you get a sum>0 then then it meets your conditions
use that a s a group select
SUM(@flag,department)>0
|
Posted By: alzniagara
Date Posted: 25 Feb 2014 at 5:05am
|
Thank you DBlank,
I can see how this would work, because if I put the @flag formula in my detail section the correct clients have a "1". However, I'm stuck at this point: "sum this at the group 2 level". When I try to insert a summary I can't use the @flag field? And when I use the sum(@flag,department)>0 in the group select formula I receive an error saying the @flag can't be summarized. Thanks! |
Posted By: DBlank
Date Posted: 25 Feb 2014 at 7:48am
|
Yep. Technically this is trying to use summary in a summary wich is not allowed.
Instead of the group select you can use a suppress statment to hide all the records that do not meet your criteria. This menas all of your display needs to be in the footers.
Create a Running Total (you can also use shared variable fomrula if you prefer)
Name=HideFlag (or whatever)
Field to Summarize=clientid
type= count
evaluate=use a formula
table.statusdate=max(table.statusdate,table.department) and table.status in ["A","W"]
Reset= on a group (group 2 department)
palce in group 2 footer.
You can now use this value to suppress the footers where #HideFlag=0
|
Posted By: alzniagara
Date Posted: 19 Mar 2014 at 5:20am
| Sorry the late reply - but wanted to thank you so much - this last suggestion worked like a charm!! |