Crystal XI - Filter out data from a report 1

[ReillyC]

I have a report where I list the employees and the number of requests they have submitted. What I need to do is just display the employees that have a request in 2005 plus their running totals. How can I filter that out?

Current Results
Earliest
Name Request date Running total State Request #
Smith 01-01-1998 1 NJ 263
Smith 01-01-2005 2 NY 467

Slat 01-01-1998 1 NJ 002
Slat 12-06-2005 2 NJ 101


Pack 11-01-1994 2 NJ 326
Pack 01-01-1984 1 NY 659
Pack 11-01-2005 3 NJ 859


Peter 1/1/1994 1 NY 596


Peter in the above sample would be removed because she doesn't have a request in 2005.

Desired Results:
Earliest
Name Request date Running total State Request #
Smith 01-01-1998 1 NJ 263
Smith 01-01-2005 2 NY 467

Slat 01-01-1998 1 NJ 002
Slat 12-06-2005 2 NJ 101


Pack 11-01-1994 2 NJ 326
Pack 01-01-1984 1 NY 659
Pack 11-01-2005 3 NJ 659

 

[lbass]

Go to report->selection formula->GROUP and enter:

maximum({table.earliestrequestdate},{table.name}) >= date(2005,1,1)

-LB

 

[ReillyC]

It didn't work.
I have 4 groups on this report

1. Employee id
2. Employee name
3. Request #
4. Request date

I placed all the data in the Request # group header. Also in the group header I have the running total. The running total is in date order.

 

[lbass]

It still should work. Instead of saying something doesn't work, you should explain in what way it doesn't work or show a sample of the results so we can assess what the problem may be.

-LB

 

[ReillyC]

Ok now I see what's happening.


Currently I'm getting every employee that has submitted a request less than or equal to 2005. See Peter in the below sample. She should be removed because she doesn't have a request in 2005. I need to include employees who have submitted a request in 2005 but I also have to display their running total to date.
Everyone must have a 2005 request submission.

Current Results
Earliest
Name Request date Running total State Request #
Smith 01-01-1998 1 NJ 263
Smith 01-01-2005 2 NY 467

Slat 01-01-1998 1 NJ 002
Slat 12-06-2005 2 NJ 101


Pack 11-01-1994 2 NJ 326
Pack 01-01-1984 1 NY 659
Pack 11-01-2005 3 NJ 859


Peter 1/1/1994 1 NY 596


Everyone must have a 2005 request submission. See below

Desired Results:
Earliest
Name Request date Running total State Request #
Smith 01-01-1998 1 NJ 263
Smith 01-01-2005 2 NY 467

Slat 01-01-1998 1 NJ 002
Slat 12-06-2005 2 NJ 101


Pack 11-01-1994 2 NJ 326
Pack 01-01-1984 1 NY 659
Pack 11-01-2005 3 NJ 659

Cheers
R

 

[lbass]

You have not explained in what way my suggestion for using the group selection formula requiring the maximum date to be greater than 7-1-2005 did not work.

-LB

 

[ReillyC]

After reviewing my reportI realize my data isn't quite right to begin with. I need help correcting it.

I need to get the earliest request date and the corresponding country listed in the request id. Right now I'm getting the earliest request date by not the corresponding country that goes with it.

This is the raw data
RequestID = 1000
State Request Date Employee name Request Number
NY = 12/31/2002 Rey 23333
NJ = 01/05/2001 Rey 96333
PA = 01/15/1999 Rey 50639
NY = 12/31/2002 Smith 789333
NJ = 01/05/2001 Smith 629933
PA = 01/15/1999 Smith 56963

RequestID = 1001
State Request Date Employee name Request Number
CA = 11/15/2002 Lang 69633
NJ = 09/15/2001 Lang 89663
NY = 01/05/1987 Lang 59633
CA = 11/15/2002 Peter 16933
NJ = 09/15/2001 Peter 02389
NY = 01/05/1987 Peter 56933

In my selection criteria I only need two states NY and NJ but from that I need to select the earliest request date of the two.
Desired Crireria

State Request Date Employee name Request Number request ID
NJ = 01/05/2001 Smith 629933 1000
NJ = 01/05/2001 Rey 96333 1000
NY = 01/05/1987 Lang 59633 1001
NY = 01/05/1987 Peter 56933 1001
I've grouped by report by:

Table.Employeeid
Table.Requestid
Table.State

I placed my fields in the state group footer including a field where I request the earliest request date of the request id using the following formula MINIMUM({Table.Requestdate},{tablerequestid }). This works but I'm not getting the corresponding state and request number.

 

[lbass]

First you should go to report->selection formula->RECORD and enter:

{table.state} in ["NJ","NY"]

Then go to report->selection formula->GROUP and enter:

{Table.Requestdate} = minimum({Table.Requestdate},{tablerequestid })

-LB

 

[ReillyC]

This formula worked{Table.Requestdate} = minimum({Table.Requestdate},{tablerequestid }). Hooray.

I have 2 other issues.

I need to place the records in date order so I can place a counter for each record. In order to accomplish this I selected group sort expert then I selected the min request date on the tablerequestid.


State Request Date Employee name Request Number request ID Running Total

NY = 01/05/1987 Smith 59633 1001 1
NJ = 01/05/2001 Smith 629933 1000 2
NY = 01/05/1987 Peter 56933 1001 1
NJ = 01/05/2001 Rey 96333 1000 1
NJ = 01/05/2005 Rey 97333 3333 2
NJ = 01/15/2005 Lit 98333 7373 1



Then I need to display all employees that have a 2005 request along with their running total YTD. This is where I have trouble. How do I accomplish this?

State Request Date Employee name Request Number request ID Running Total

NJ = 01/05/2001 Rey 96333 1000 1
NJ = 01/05/2005 Rey 97333 3333 2
NJ = 01/15/2005 Lit 98333 7373 1

 

[lbass]

I forgot about your earlier requirement. I think you should go back to the beginning because you basically have two subselects here. Go to database->database expert->your datasource->add command and enter:

select minimum(table.`request date`), table.`request ID`
from `table`table
where table.`state` in ('NY','NJ')
group by table.`request ID`

Link this to the main table on request ID and on request date with an equal join, and go into the link options for both and check "enforce both".

Add the state criteria into the record selection formula of the main report. Now ONLY the earliest date per request ID should show up in the report. Now you can use a GROUP selection formula like:

maximum({table.request date},{table.employeeID}) >= date(2005,1,1)

-LB

 

[ReillyC]

OMG so close!
I originally placed by data in the group header of the state but I had to change it to the group header of request id. The reason for the move was because Crystal ignored the min date requirement and continued to display both states (NY and NJ). I needed to display the earliest grant date of the states.
Now that I've moved it to the group header of request id, it works. Only now the counter doesn't work because I cannot put the dates in order. Since I placed the
{Table.Requestdate} = minimum({Table.Requestdate},{tablerequestid }) in the group selection the
How can I get the dates in order so the counter will be correct?
Current Results

NJ = 01/05/2001 Rey 96333 1000 3
NJ = 01/05/2005 Rey 97333 3333 2
NJ = 01/15/1998 Rey 57896 7373 1


Desired Results
NJ = 01/15/1998 Rey 57896 7373 1
NJ = 01/05/2001 Rey 96333 1000 2
NJ = 01/05/2005 Rey 97333 3333 3

Thanks
R

 

[ReillyC]

I got it sort properly but this formula maximum({table.request date},{table.employeeID}) >= date(2005,1,1)only suppresses records that contain 2005. I need to suppress the inventor and his or her requests if the year of all the requests doesn't contain 2005.


NJ = 01/05/2001 Rey 96333 1000 3
NJ = 01/15/1998 Rey 57896 7373 1


NJ = 01/15/1998 Riley 57896 7373 1
NJ = 01/05/2001 Riley 96333 1000 2
NJ = 01/05/2005 Riley 97333 3333 3

Rey wouldn't display because it does not contains 2005 as the earliest request date.

 

[lbass]

I don't know what you have done. It's not clear to me whether you followed the suggestion in my last post. I'm lost.

-LB

 

[ReillyC]

I implemented your suggestion to add the command and then place the formula maximum({table.request date},{table.employeeID}) >= date(2005,1,1) in the group selection.

It almost works. It leaves too much data behind.

For example,
Current Data
There 4 employees but only two have request dates in 2005 ( the earliest request date in the request id group)
State Request Date Employee name Request Number Request ID Running Total

NY = 01/05/1987 Smith 59633 1001 1
NJ = 01/05/2001 Smith 629933 1000 2

NY = 01/05/1987 Peter 56933 1001 1

NY = 01/05/1988 Rey 97833 0089 1
NJ = 01/05/2001 Rey 96333 1000 2
NJ = 01/05/2005 Rey 97333 3333 3

NJ = 01/15/2005 Lit 98333 7373 1

Desired Results
State Request Date Employee name Request Number Request ID Running Total

NY = 01/05/1988 Rey 97833 0089 1
NJ = 01/05/2001 Rey 96333 1000 2
NJ = 01/05/2005 Rey 97333 3333 3

NJ = 01/15/2005 Lit 98333 7373 1

 

[lbass]

What is your group structure now? Do you have more than a group on employee name?

-LB

 

[ReillyC]

My group stucture is the following:
Employee Index Suppressed
Employee Last Name Suppressed
Request ID data appears here
Request date Supprssed

 

[lbass]

This should work as is. If you unsuppress the request date group, you should see only one date per Request ID. Is that the case? If not, make sure you have done the linking on the command and that you have set "enforce both" for the join.

-LB

 

[ReillyC]

I forgot. I couldn't enforce both. I received the following error.
Cannot determine the queries necessary to get data for this report.
Details: This query cannot be performed. The link order requires many to one query configuration which is not supported. Please rearrange the links.