Select Statement

[Butlertl]

Crystal 10 or 11

I have a list of customers with Remedy Ticekts...I need to select only those tickets where the customer opened multiple tickets during one day....and I need to search for a past 12 month period. Any ideas? Thanks to all.

Terry

 

[kskid]

Sounds like a classroom assignment.

 

[Butlertl]

Wish it was..........the government asked the question yesterday and now we are jumping thru hoops.

 

[raja2008]

How about this.

Group the tickets first based on Each day.
Then group based on the Requester ID.
Now in the second group have a supress formula to supress where the count <=1.
Hope this helps.

 

[CR85user]

As per Raja's suggestion but instead you could use the detail section and suppress based on maximum(count({table.dategroup},{customer.id})) < 2

'J

CR8.5 / CRXI - Discovering the impossible

 

[Butlertl]

This is what I think you are suggesting....

maximum(count({OAO_HDSP_Support.Create Date},{OAO_HDSP_Support.Customer Last Name})) < 2

My first group is based on "Create Date" and the second group on "Customer Last Name"....

What is the TABLE.DATAGROUP?

Thanks for your suggestions.

 

[raja2008]

CR85user mentioned DateGroup just like you set it up.
You just read it wrong.
I do that too.

 

[Butlertl]

Understand....

To suppress, I enterd the SECTION EXPERT and then used SUPPRESS BLANK SECTION...and entered the formula....I was getting an eeror that the formula was wrong. I was using it in CR 10

 

[lbass]

First, set up a record selection formula that limits the date field:

{table.date} in dateadd("m",-12, currentdate-day(currentdate)+1) to currentdate-day(currentdate)

Insert a group#1 on customer and then a group#2 on date (on change of day)--they must be in this order. Then go to report->selection formula->GROUP and enter:

distinctcount({table.ticketID},{table.date}) > 1

-LB

 

[Butlertl]

Tried as you suggested and got an error with the >1

Tried this one:
DistinctCount({OAO_HDSP_Support.Customer Last Name}, {OAO_HDSP_Support.Create Date}, "daily" and I get a message that it must match a group. My 1st Group is: OAO_HDSP_Support.Customer Last Name
and my second group is:
{OAO_HDSP_Support.Create Date}

 

[lbass]

You should not be counting the name, but instead, the ticket ID or similar field. Please go to the group expert (report->group expert) and check that you have set the date to print on change of day. You shouldn't have to specify that in your formula though. Please then show the exact group selection formula you are using and identify the specific error message.

-LB

 

[Butlertl]

It prints the date on every change of DAY.....

Report>Group Expert:
#1 Customer Last Name
#2 Create Date

Report>Selection Formulas>Group:
distinctcount({OAO_HDSP_Support.Support ID},{OAO_HDSP_Support.Create Date}) > 1

ERROR: "There must be a group that matches this field."

Thanks Again

 

[lbass]

That means that you don't have a group on that field or that the date condition needs to be added. So I wonder whether you might have a create date from a different table that you are grouping on? It sounds like you have already tried:

distinctcount({OAO_HDSP_Support.Support ID},{OAO_HDSP_Support.Create Date},"daily") > 1

...and you still get the group error? Then it has to be that you didn't group on that field. Could you have converted the field to a formula and then grouped on that or something?

-LB

 

[Butlertl]

I'll start over insure my process is correct.

Thanks

 

[Butlertl]

Started from scratch...followed your process and it owrks....I agree...it must have been something with the Create Date.

Thanks again for your help.