Counting number of closed tickets for each month of the year

[bluem100]

Hi All,

I am creating a crystal report that would
count the number of service tickets closed
for each month of the year.
In my report I placed three columns:
Column1, Column2, and Column3. The title of
the column will be displayed based on the
user feed to a prompt. For example if the
user wants to see tickets closed in First
Quarter of the year. The Columns titles
will change to January, February, and
March.
Now I would like to count the numbers of
closed tickets based on the month that is
shown in each column. I looked in crystal
report to see if there is a function will do
that for me, but I could no do so. Can
anyone help me with this……..
Can this be done using Running Total?


Thanks,
J

 

[Turkbear]

Hi,
Far too little info:
What version of Crystal?
What is the data source?
What actual fields are in the data?
What type of data is in the fields?


The more we know, the more we can help..

 

[bdreed35]

You can use a running total if you like, but I like the flexibility of using a formula.

Create this formula, place it in the details section, and suppress it:
@Closed_Tickets

if {table.field} = "Closed" then
    1
else
    0

Now you can create a summary based off this formula at any level you need.
Right click the formula in the details section, and choose
Choose Insert , Summary.
The field should be your formula.
THe type of Summary should be Sum.
The group should be your date group.

~Brian

 

[lbass]

It looks like a crosstab would be the best approach. You would use {table.date} for the column field and then choose "Group Options" and set it to change "monthly". Depending on how the {table.closed} field is setup, you could either add it as the summary field (if it already returns a 1 if true) or you could use a formula like Brian's that could be summed.

To get the user-defined months, set up a date range parameter and in the record selection formula use:

{table.date} = {?date}

This will then limit the months that appear in the crosstab.

-LB

 

[bluem100]

I am using Crystal Report 8.0, the data source is ServiceCenter ODBC.

Here are the fields;
1) OrderNumber (example of this field is PA19999)
2) ClosedTime (this is DateTime formate)
3)Column1Title, Column2Title, Column3Title (this will show the month name of the column depending on what the user input (1st, 2nd, 3rd, or 4th quarter. For example if the user wants to see date for 1st quarter, the column title will be shown as "January" for Column1Title, "February" for Column1Title, and "March" for Column3Title.
4) Column1Total, Column2Total, and Column3Total - These coulmns will display the number of order closed during a month based on the Column Title fields. For example if Column1Title is "January", the Column1Total will show number of orders closed during the month of January. if Column2Title is "February", the Column2Total will show number of closed orders for February.

I am unable to translate the above into a code so that the calculation are done correctly.
Once i get the code to show correct count for each month, I guess i will be able to use in the running total using the insert summary,...etc in my group footer.

Thanks,
J

 

[bluem100]

You can say that my main problem is getting the report to count orders closed for each month.

 

[lbass]

Try inserting a crosstab, and use {table.closedtime} as your column field. Then choose "Group Options" and set it to change "monthly". Then add {table.closedtime} as the summary field, and choose count as the summary operation.

-LB

 

[synapsevampire]

Or if you don't want a cross-tab, rather summary data at the month group level, insert the date field as a gorup, and select group options->Month

Now you can right click any field in the details and select insert->summary->count, you can also designate for all gorups and a grand total.

-k

 

[bluem100]

what formula or function i can use to indicate that an order has been closed in January, Feburary, March,...etc.

 

[lbass]

I think you just need to use {table.closeddate} as your column field, with group options set to display "monthly", assuming you are using a crosstab. If you are not using a crosstab, then you could group on {table.closeddate} and set it for "on change of month."

-LB