Open tickets with closed assignments only

[gennaroalpha7]

Hi -

This is a continuation of thread thread767-1733072 - I would like some help from more experienced Crystal Developers please.

I have 4 fields on my report. They are CallLog.CallID, CallLog.RecvdDate, CallLog.CallStatus, and Asgnmnt.AssignStatus

I would like the report to pull Open Tickets that only have "Closed Assignments", but its pulling tickets with Active Assignments too.

I have grouped on CallLog.CallStatus and Asgnmnt.AssignStatus and added this code, {Asgnmnt.AssignStatus} <> "Active", to Report > Selection Formulas > Group and added this code, Date ({CallLog.RecvdDate}) in Lastfullmonth and {CallLog.CallStatus} = "Open", to the Report > Selection Formulas > Record.

Is there something I have to do to make this work or something I am missing?

Thx in advance for your help.

G.

 

[briangriffin]

The easiest way would be to create a command object, if you're familiar with them. It would be something like:

select CallID from Asgnmnt where AssignStatus = 'Closed'

The command works the same as a table, so if you inner join that to the CallLog table you already are using, it should restrict to only those calls with a Closed assignment record but also include all the other records.

You'll have to remove {CallLog.CallStatus} = "Open" from your selection criteria. Also, it seems that you would need to group on CallID.

 

[gennaroalpha7]

Hi Brian -

I tried to make a Commnand like you suggested but it didn't take - gave an error "Couldn't retrieve the Data"

I found an old report that is similiar to what I want but it reports on tickets that have "No" assignments. I think it excludes tickets that have "Closed Assignments" too. Can this report be changed to fill my requirement?

Please click the link to view the report....

https://www.dropbox.com/s/0eavkajzawmrzgx/Tickets with No Assignments_v0.1.rpt

I would like the report to pull "Open" Tickets that have "Closed Assignments" and "No Active Assignments".

Thx.

G.

 

[briangriffin]

The saved data got dumped from your Dropbox report, but here's what I would do next:

Create a formula called {@Active}:

if {Asgnmnt.AssignStatus} = "Active" then 1 else 0

Put that in the details section. Now create a sum of that formula field and put it in the group 2 footer (you'll need to create a group on CallID - you can hide the GH and GF if you want).

So the sum of that field for any ticket with no active assignments will be 0. In the Group Select Expert, filter on tickets where the sum of the formula is 0. That should drop any ticket with at least one active assignment. You can delete or suppress the formula field once you're sure everything is working properly.

If you go this route be aware that group selection is really group hiding - you'll see that your record count doesn't change. This means that any totaling you do using the Crystal summary functions will be wrong, since they include the hidden records. You'll have to create manual running totals (this is another reason why the command object approach is better).

 

[gennaroalpha7]

Thank you - I will try this and probably have some questions. If you will please.

G.

 

[gennaroalpha7]

Hi -

In the post above of 20 Jun 14 10:46 I followed the suggestion up until the group selection filter, the records disappeared when I set a group selection as suggested - I removed it. Why did the records disappear?

I would like to set a date range to pull the records from. Currently there is a receive date formula but I would like to make that a regular CallLog.RecvdDate field to set a date range. Can it be changed to a regular field instead of a formula?

I would also like to remove the (Graph, Details, & Customer Type) Parameter fields without ruining the report. And when the Customer Type parameter field is removed - how do you change the record selection to reflect that?

Pleaes click link to view...

https://www.dropbox.com/s/ikkk47habff2uzn/Tickets with No Assignments_v0.2.rpt

Thx.

G.

 

[briangriffin]

I would suggest getting the selection issue resolved, then work on the parameters.

My best guess is that you are doing group selection on the wrong summary field. It should look something like this (assuming the group is CallID):

sum({@Active},{CallID}) > 0

Can you see the sum of {@Active} in the CallID group footer? If so, do you see zeros for the groups with no active assignments and values greater than 0 for the others?

 

[gennaroalpha7]

I added a summary in the GF, but I had to change it to "Count" because if it was left at Sum there would be nothing shown on the report.

And when I add, sum({@Active},{CallID}) > 0, to the Group Selection, it highlights [highlight #FCE94F]{CallID}[/highlight] and says "This field name is not known". When I change it to the correct field CallLog.CallID(string) then everything on the report disappears.

Here are the steps I have completed.
1. Create a formula called {@Active}: if {Asgnmnt.AssignStatus} = "Active" then 1 else 0
2. Added it the details section
3. Grouped on CallLog.CallID
4. Created a summary count of, @Active, formula field and the location is the group 1 footer; only have GF 1 now.

Thx.

G.

 

[gennaroalpha7]

When I created this, if {Asgnmnt.AssignStatus} = "Active" then 1 else 0, formula and installed it in the details section there is no 0 or 1. it only shows 0 in the GF1 count.

Thx.

 

[briangriffin]

Can you please post the latest version to dropbox?

 

[gennaroalpha7]

Thanks for reviewing...

https://www.dropbox.com/s/ikkk47habff2uzn/Tickets with No Assignments_v0.2.rpt

 

[briangriffin]

Your report didn't come through with saved data, but I was able to change the summary field from count to sum. Regardless, the first thing you need to do is have the formula in the detail section returning 0 or 1 based on the assignment status. If you can see that working (and you can hide or delete the formula later) then the rest should fall into place.

Here's a sample report that identifies any customer with an order over $10k. It uses the XTreme sample database, which you can download if it's not already on your workstation. The method is the same as what you are trying to do.

https://www.dropbox.com/s/oof7c62qk32616y/Orders Over 10k.rpt

Note the record count and order total. Then delete the group selection criteria and you'll see that those figures don't change. Group record selection is really just group hiding, so if you're going to be doing any other summaries you'll have to create the formulas manually.

 

[gennaroalpha7]

The formula in the detail section is: if {Asgnmnt.AssignStatus} = "Active" then 1 else 0 and I called @Active. Is that correct?

The Group Select Expert is what I dont understand? Filter on tickets where the sum of the formula is 0 - how do I do this?

The report that you viewed - is that correct? Except for the Group Select Expert formula that I ask about above.

Thx.

G/

 

[gennaroalpha7]

Hi -

As of now, the report pulls up Open tickets with No Assignments at all, but if an Open ticket has a Closed Assignment with No Active Assignment it will not be on the report.

Currently the code in the record selection to do this is:

(isnull({Asgnmnt.HEATSeq}) or {Asgnmnt.HEATSeq} = 0 or {Asgnmnt.HEATSeq} = 1)

and
{CallLog.CallStatus} <> "Closed"

and
{CallLog.RecvdDate} in "2013-06-01" to "2014-06-30"


How can the code be changed to pull up Open Tickets with No Active Assignments - tickets can have Closed Assignments.


The (Type: number) information in the Asgnmnt.HEATSeq table is numbers, like: 1.105.636.808, 1.106.170.607, 1.106.323.977 etc...

And if I add {Asgnmnt.AssignStatus} <> "Closed" all the tickets/records disappear.

Thx.

G.

 

[briangriffin]

I would comment out this line in the record selection for now (you can add it back in later after everything else is working):

(isnull({Asgnmnt.HEATSeq}) or {Asgnmnt.HEATSeq} = 0 or {Asgnmnt.HEATSeq} = 1)

Next, for group selection to work this formula has to be in the detail section and has to be returning 0 or 1. I would put the AssignStatus field in the details section temporarily just for a visual confirmation that the formula is working.

if {Asgnmnt.AssignStatus} = "Active" then 1 else 0

Once that is working put the sum in the group footer. If you take a look at the sample report you'll see how it all comes together once the summary is in place.

 

[gennaroalpha7]

Sorry, this is what is currently in the Record Selection....

(isnull({Asgnmnt.HEATSeq}) or {Asgnmnt.HEATSeq} = 0)

Is this what you want commented out or taken out, and will add back later? Can you please be more specific with your instructions. Thanks.

Thx.

G.

 

[briangriffin]

Yes, take that line out. The date range and closed ticket restrictions in select expert are not causing your problem so you can leave them in.

Basically you want to have the report return all open tickets for the date range. Get the formula in the details to return 1 or 0 based on assignment status and at that point you'll know you're ready to start restricting the tickets to what you really want to see.

I think it's important to be able to visually confirm what is happening before starting to filter anything.

 

[gennaroalpha7]

I have taken that line out and the date range too. So all the tickets come in. I placed the formula, if {Asgnmnt.AssignStatus} = "Active" then 1 else 0, @Active in the details section and its working but not accurately.

Somet tickets have multiply assignments, some closed and some Active. Some tickets have as much as 20 assignments, some have only 1 or 2. So the formula @Active is getting confused I think. The sum summary in the footer is working too, but counting duplicate tickets. For example, 746348 has two Active assignments and has a "0" next to it, it should be a 1

Thx.

G.

 

[gennaroalpha7]

Hi, here's the latest version. Please review. Thanks.

G.

https://www.dropbox.com/s/nyv9zvhm8jlnfkw/Tickets with No Assignments_v0.3_Development.rpt

 

[gennaroalpha7]

Here is a screen shot incase the data went missing...

https://www.dropbox.com/s/cnp62fev56hhdhu/Tickets with No Assignments.PNG

Thx.

G.