get the wright value in group header or footer 2

[nilsbinnemans]

Hi,

I'm using CR developer 2011 (version 14)

I have a database view with query information. I have made a group for each query number. In details I have the following fields:
{Date}// The date a change has been made
{user}// who made the change
{Status}// Query status [OPEN, ANSWERED, REISSUE, CLOSED]

So for one query I could have several detail lines like:

{Date} - {User} - {status}
01/01/2013 10:00 - user1 - OPEN
02/01/2013 10:30 - user2 - ANSWERED
03/01/2013 10:00 - user1 - CLOSED


I would like to be able to filter out al queries with a certain status. But when I use my selection-expert and say {status}<>"CLOSED" it would still show me the first 2 lines of the query above.
So what I would like is actually a formula that gives me the LATEST status (with the heighest date) of the query.

thanks for any help.

Nils

 

[MCuthill]

Nils,

I am assuming there is a Call ID, or something grouping the above detail rows (as you will need a group to look for the maximum of, else you wish to see the status of only the most recent detail row queried).

Either way, I beleive the solution to be something like:
{@MAX_Status}

[blue]IF[/blue] {Table.Date} = [blue]MAX[/blue]({Table.Date},{Table.YourGroupField}) [blue]THEN[/blue] {Table.Date} [blue]ELSE DATE[/blue](1900,01,01)

You should be able to then return the Max of this field. (I think)


If you cannot return a Max off this formula field, then a RunningTotal evaluation of sorts should work (assuming you are okay with the result displaying the footer for the (assumed) Call ID group).
In (assumed) Group Header:
{@RESET_MaxStatus}

[blue]WhilePrintingRecords[/blue];
[blue]Shared StringVar[/blue] MaxStatus:="";

(this empties the variable, at the beginning of each group)

In details:
{@SET_MaxStatus}

[blue]Shared StringVar[/blue] MaxStatus;
[blue]IF[/blue] {Table.Date} = [blue]MAX[/blue]({Table.Date},{Table.YourGroupField}) [blue]THEN[/blue] MaxStatus:={Table.Status} [blue]ELSE[/blue] "";

In (assumed) Group Footer:
{@DISPLAY_MaxStatus}

[blue]Shared StringVar[/blue] MaxStatus;

Note: [blue]MAX[/blue]() may be [blue]MAXIMUM[/blue](), I do not have Crystal in front of me today.

Hope this helps, cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You could use a group selection formula (report->selection formula->GROUP) like the following:

{table.date} = maximum({table.date},{table.groupfield}) and
{table.status} <> "Closed"

This would show the most recent record for those groups where the most recent status is not closed.

-LB

 

[nilsbinnemans]

Hi,

Thanks both for the reply.

MCuthill: it seems that I'm indeed getting the Maximum {Table.Date} in the group footerbut not the corresponding Status.

Lbass: I was getting there using your formula. However an other problem was raised.
The report is to be viewed by the person(s) who OPENED a query. They want to view which queries they have opened are still not closed.
When I'm using your formula only the last {status} (and with it {user}) are shown. Is there a way to show the {Table.User} wherefore {Table.status}= OPEN

thanks

 

[MCuthill]

G'day Nils,

Oops. Yes, that first approach returns the (Maximum) Date, not the Status. My apologies, I got mixed up in my posting. The second approach (the 3-formula method) posted above should return the Status attached to the most recent date, as you indicated in your Original Post.

Please Note: You won't be able to create a Record Selection on this field though to filter out those where the most recent Status is Closed (assuming this is a requirement). If your requirement is to not list any Call ID where there is a Closed present in the group, please advise (I am reading a little bit "between the lines" on your first post, this may not be the case at all)

Please advise should the second method not return the results you seek or if you have any troubles when implementing.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[IanWaterman]

Another possibility is to creaet a view on the database. Query for view would be something like

select UserId, CallID,
Max(Case when Status = 'CLOSED' then 1 else 0 end) Closed
from Callogtable
group by UserId, CallID
having Max(Case when Status = 'CLOSED' then 1 else 0 end) = 0

This would return a distinct list of open calls and user ids, if more than one user can be on a call then remove UserId from query.

This view can beadded to report and then only open calls will be included.

Ian

 

[lbass]

Create these formulas:

//{@openuser}:
if {table.status} = "Open" then
{table.user}

//{@closed}:
if {table.status} = "Closed" then
1

Then change the group selection formula to:

sum({@closed},{table.querynumber})=0

This will show only open queries. Then right click on {@openuser} (place it in the detail section first) and insert a maximum on it at the querynumber group level. Then go to report->group sort and select the maximum of {@openuser} at the querynumber level as your group sort field. This will sort the query groups by openuser, so they are clustered together by openuser.

-LB

 

[nilsbinnemans]

Thanks MCuthill,

I tried your second approach another time and it seems that I did something wrong the first time as it was working fine. (Only MAX should be MAXIMUM)
I did the same to become the first user (MINIMUM).

greets