Report format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a report with the following format:

Requests Total time spent Avg time spent Pending tickets Late
tickets % from business line

The report runs off the following query:

SELECT [New London Helpdesk].[ID], COUNT([New London
Helpdesk].[ID]) AS CountofID, [New London Helpdesk].[Date Received], [New
London Helpdesk].[Request Category], [New London Helpdesk].[Time to
Completion (hrs)], [New London Helpdesk].[Late Status]
FROM [New London Helpdesk]
WHERE [New London Helpdesk].[Date Received] Between [Enter
Start Date] And [Enter End Date]
GROUP BY [New London Helpdesk].[ID], [New London
Helpdesk].[Date Received], [New London Helpdesk].[Request Category], [New
London Helpdesk].[Time to Completion (hrs)], [New London Helpdesk].[Late
Status];

My problem is that the data in the report is accurate, but the formatting is
incorrect. This report should be a single line on a single page, instead
the same line is repeated on multiple pages.

Please advise.
 
If you open your report in design view, does it have group
headers or group footers that may have page breaks set?
It sounds like you don't want any. You probably just need
your headings in the page header, and all your data fields
in the detail section of the report.
 
I would suggest that you need to drop some fields from your query and use
aggregate functions on other. You are going to get one line for each
combination of ID, Date Received, Category, etc..

Perhaps you need something more like the following. Assumptions

Requests: Each request is defined by the field ID
Total Time: is the sum of time to completion (hrs) and is a numeric field not a
date field
Late tickets: is a count of Late Status and Late Status is a Yes/No field

I don't know how you define Pending Tickets, so I can't help you there. And also
Business line again defeats me as to its meaning.



SELECT
COUNT([New London Helpdesk].[ID]) AS CountofID,
SUM([New London Helpdesk].[Time to Completion (hrs)]) as TotalHours
Avg([New London Helpdesk].[Time to Completion (hrs)]) as AvgHrs,
Abs(Sum([New London Helpdesk].[Late Status])) as CountLate
FROM [New London Helpdesk]
WHERE [New London Helpdesk].[Date Received]
Between [Enter Start Date] And [Enter End Date]

Note that this particular query has no Group By Clause. If you wanted to group
by Request Category to get a total for each of Category you would need to make
the following change.

SELECT [New London Helpdesk].[Request Category],
COUNT([New London Helpdesk].[ID]) AS CountofID,
SUM([New London Helpdesk].[Time to Completion (hrs)]) as TotalHours
Avg([New London Helpdesk].[Time to Completion (hrs)]) as AvgHrs,
Abs(Sum([New London Helpdesk].[Late Status])) as CountLate
FROM [New London Helpdesk]
WHERE [New London Helpdesk].[Date Received]
Between [Enter Start Date] And [Enter End Date]
Group By [New London Helpdesk].[Request Category]

Hope this helps get you started.
 
Back
Top