Crystal Reports - Chart with past 12 month totals

building reports crystal reports
1

I have a report I was asked to create. I am new at creating reports.

The report will include the Call Type field from out ticketing software. I included this field in the report and a count of each call type. In the select expert, I am able to select RecvdDate and choose whatever timeframe is needed.

I was also asked to include a chart on the bottom of the report to show totals of call types for the past 12 months. I have been researching for a few days now and can’t seem to find an answer. From what I have been reading, I would need to use a formula, but all of the suggested formulas give me an error.

Any help would be appreciated. If more info is needed, please let me know.

Thanks,

Tom

td138 (BOB member since 2015-12-15)

2

send the example of formula which is giving error for easy debug… what is the error message coming… based on that you can easily solve the problem. most of the cases it is a casting problem character to number and number to character…

surya.g :india: (BOB member since 2009-11-24)

3

I tried this formula:

dateTimeVar startmonth12 := DateAdd (“m”,-12,CurrentDate)-Day (DateAdd(“m”,-12,CurrentDate)) +1;

dateTimeVar endmonth12 := DateAdd (“m”,-11,CurrentDate)-Day (DateAdd(“m”,-11,CurrentDate));

and I got the following error: a Boolean is required here

Like I said earlier, I’m not familiar with formulas.

Thanks for any help you can provide.

td138 (BOB member since 2015-12-15)

4

Are you putting the formula in the selection criteria area? But I am confused by what you are trying to accomplish in the formula. This is what I am reading for the first line. Get the date from a year ago today and subtract the day of that month a year ago then add one (day). If you are trying to get the first of the month a year ago, there are other formulas that are easier to read (I would need to do a search to find them).

kevlray :us: (BOB member since 2010-06-23)

5

I am trying to create a chart that shows totals from the past 12 months.

I was looking up formulas and came across that one which said that it should do what I needed.

td138 (BOB member since 2015-12-15)

6

Again, did you put the lines

dateTimeVar startmonth12 := DateAdd (“m”,-12,CurrentDate)-Day (DateAdd(“m”,-12,CurrentDate)) +1;

dateTimeVar endmonth12 := DateAdd (“m”,-11,CurrentDate)-Day (DateAdd(“m”,-11,CurrentDate));

in the selection criteria formula or did you make a formula field?

A quick check in Crystal Reports. The two lines give these values for today.

12/1/2014 12:00:00AM
12/31/2014 12:00:00AM

So they return a full month a year ago.

kevlray :us: (BOB member since 2010-06-23)

7

I tried to add it in the Group Selection formula

td138 (BOB member since 2015-12-15)

8

I was able to get the correct totals in the report itself. It turns out that I had one of the groups duplicated in the report which was giving me the wrong totals.

The only issue I have is with the chart. I was able to add the chart. But I’m not sure how to have the chart display the past 12 months of totals.

td138 (BOB member since 2015-12-15)

9

You would definitely get an error if you put those lines in the group selection since it is looking for a true/false result to filter records.

So are you filtering the data now for the last 12 months? If not, you may need a sub-report and put the chart there (using similar formulas as above). I would think you would use the first line for your start date and use the current date for your end date.

kevlray :us: (BOB member since 2010-06-23)

10

I am looking to have the chart display totals for the last 12 months from today.

So would that formula give me those results without having to input a date?

td138 (BOB member since 2015-12-15)

11

In the selection formula, something like this

{Your date field}>= DateAdd (“m”,-12,CurrentDate)-Day (DateAdd(“m”,-12,CurrentDate)) +1
and {Your date field} <= CurrentDate

kevlray :us: (BOB member since 2010-06-23)