Report to Count Tickets by Day from Range of Dates

[Former Member]

Hello Everyone,

I have been working to generate a number or reports to count the number of tickets closed by different metrics but I am having an issue with the latest request I have received. They want to have a report generated that shows the number of tickets closed each day by a list of technicians during a range of days. I have been able to pull the data and can break it down into periods of weeks but I cannot seem to get it break this down to the day level for the report. In the image below you can see on the left an example of the data I can pull and on the right is what I want it to look like. I also have a list of the field names that are related below that.

Ticket Number: Request Number

Technician Name: Request Assignee Combo Name

Ticket Open Date: Request Open Date

To achieve the week breakdown I have a formula of =Week([Request Open Date])

Date Range is selected on refresh of the report by user input.

This is what I see as the most relevant parts of the information. I may be missing something obvious here so I apologize if I am missing something, I can add the notes for that. I would appreciate any help I can get with this.

Thank you for your time and advise,

Anthony

[abhilash_kumar · Accepted solution]

Hi Anthony,

This does not look like a Crystal Report to me.

Are you trying to do this in WebI?

-Abhilash

[Former Member · Accepted solution]

Hello Abhilash,

Yes, I am using Webi. This is a Crystal report in Webi. Did this need to be posted to a different group?

-Anthony

[abhilash_kumar · Accepted solution]

You should post here:

-Abhilash

[Former Member · Accepted solution]

Can I move this over there or do I have to recreate the posting?

[Former Member · Accepted solution]

I have recreated the discussion in the correct location. Thanks Abhilash for pointing me in the correct direction.

[Former Member · Accepted solution]

Hi Anthony,

Have you tried creating the crosstab with Request Open date in columns, Request Assignee Combo Name in rows and no. of tickets(count(request number)) in body.

This should give you the expected output.

Regards

Sri Harsha

[Former Member · Accepted solution]

Hello SriHarsha,

I have tried that in the past and it results in it creating a separate column for each ticket created at each date stamp which results in each ticket creating a column rather than a sum of the tickets for each person on the day specified.

Thanks for the ideas though,

-Anthony

[Former Member · Accepted solution]

If the Date is in timestamp format, convert the same to the format without timestamp and use that converted date in crosstab column.

FormatDate([date_object];"MM/dd/yyyy")

This would give you one column for each day instead of creating a column for each timestamp.

[Former Member · Accepted solution]

Hello SriHarsha,

Thank you for that bit. I completely overlook reformatting the time stamp that was being used. That makes everything line up how I wanted. With that I can finish the report.

[DellSC · Accepted solution]

Even easier, if you go to the group options (right-click on the column header, go to "Column Options" and then "Group Options") for the dates in the crosstab, you can set "The row will be printed for:" to "Each Day".

These are options that are available for any grouping on a datetime field - whether it's in a crosstab or just in the report.

-Dell

[Former Member · Accepted solution]

Hello Dell,

I think because I actually have a Webi system and am not working directly from Crystal reports I don't have that option available. When I right-Click I only see the below options.

This is the menu you are referring to correct?

Thanks for the ideas,

Anthony

[DellSC · Accepted solution]

Ahh...I didn't read the rest of the thread and assumed that since you posted in the Crystal space, that's what you were working with....

Sorry!

-Dell

[Former Member · Accepted solution]

No worries Dell, my fault for posting in the wrong area. Good to know the crystal report side has an easier to use functional logic though. Thanks for the information.

-Anthony