Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Complex Record selection formula help Post Reply Post New Topic
Page  of 2 Next >>
Author Message
alzniagara
Newbie
Newbie
Avatar

Joined: 30 Jan 2014
Location: Canada
Online Status: Offline
Posts: 9
Quote alzniagara Replybullet Topic: Complex Record selection formula help
    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!

Edited by alzniagara - 19 Feb 2014 at 10:05am
IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet 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.

IP IP Logged
alzniagara
Newbie
Newbie
Avatar

Joined: 30 Jan 2014
Location: Canada
Online Status: Offline
Posts: 9
Quote alzniagara Replybullet Posted: 19 Feb 2014 at 10:05am
Yes, sorry I accidentally removed the image of the tables I included!
IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet 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.





Edited by hello - 19 Feb 2014 at 2:00pm
IP IP Logged
alzniagara
Newbie
Newbie
Avatar

Joined: 30 Jan 2014
Location: Canada
Online Status: Offline
Posts: 9
Quote alzniagara Replybullet 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?
IP IP Logged
alzniagara
Newbie
Newbie
Avatar

Joined: 30 Jan 2014
Location: Canada
Online Status: Offline
Posts: 9
Quote alzniagara Replybullet 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!

Edited by alzniagara - 20 Feb 2014 at 8:21am
IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet 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.
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet 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'
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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
IP IP Logged
alzniagara
Newbie
Newbie
Avatar

Joined: 30 Jan 2014
Location: Canada
Online Status: Offline
Posts: 9
Quote alzniagara Replybullet 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!
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.