Inserting the average numbers of helpdesk calls in a cross tab.

[danstaunton]

Hi,

I need to build a crosstab report that displays the average number of helpdesk calls per hour for each day. My Cross Tab is correctly displaying the hour in the rows section (00:00 - 23:00) the days (mon - sun) in the column section.

The only way I can calculate the total number of calls is by doing a distinct count on my call reference numbers. So I cannot use the average option in summary section as it just gives me very large figures.

Is there a way around this?

 

[HowardHammerman]

I am wondering why you need to do a distinct count? Are there more than one record per call? If so you will need to find a way of eliminating the dups. You may have to create a temporary table or a stored proceedure outside of Crystal.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Madawc]

Why are there multiple records for the same call reference numbers?

Have you tried grouping by call reference numbers? Then you could do a summary total based on that group.

I'm not sure if you could use that in a crosstab. If you can't, you could do a subreprot which groups differently and just shows totals.

This is based on Crystal 11. It always helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.



Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[danstaunton]

I need to do disctinct count because my call reference is actually a number.

Can you think of any other ways I can do this in Crystal? I really do not want to add any tables in my database.

 

[HowardHammerman]

You can group on a field that is a number. You only need distinct count when there are multiple records with the same key value.

You might have to do a manual cross tab. I believe that there is one or more FAQs on this topic.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[danstaunton]

Hi,

I've started to look at the manual crosstab. I've already configured it using this guide -

http://www.tek-tips.com/faqs.cfm?fid=1219

I have no problems getting the distinct count of calls per hour per day, it's the calculating the average that I have the problem with.

 

[lbass]

What version of CR are you using?

-LB

 

[danstaunton]

XI Release 2

 

[HowardHammerman]

Just create a second distinct count that resets on the day rather than the hour. I think in your case you need a running total reset "never" with an accumulation formula that is true when the current date is equal to the column date.

The divide one by the other.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[danstaunton]

Hi,

The day names are put in manually as text objects. I did this after following the guide on creating a manual crosstab. I'm not sure how to create an accumulation formula can you please explain how I do this?

Thanks

 

[lbass]

You should have mentioned that this is a manual crosstab. Since you seem to be looking for an average in each cell (dayofweek x hour), what is this an average of? Do you have distinctcounts for each of several weeks in an inner group that you then want averaged?

-LB