Help with limiting records to be counted 1

[PerryP38]

I would like to design a monthly statistical report that counts the types of complaint we receive and have a field next to it that shows the number of complaint for that same category for last year, and then show a percentage difference.&nbsp;&nbsp;Some thing like this:<br><br>Type of complaint&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Year to Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Last year to date <br>Rudeness&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;20<br>late for work&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;15&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10<br><br>I can not figure out the syntax to use to limit the count to the type of complaint and the the year.&nbsp;&nbsp;Any help would be greatly appreciated.

 

[MalcolmW]

Do you know how your database is structured?&nbsp;&nbsp;That will affect the calculation. <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[PerryP38]

Yes, I know how the database is configured.&nbsp;&nbsp;I designed the tables.

 

[MalcolmW]

Good. If you could share that information, it would help.&nbsp;&nbsp;Are we looking at one table here with type and date of complaint?&nbsp;&nbsp;Do complaint types change from year to year? <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[PerryP38]

The information I want to count is in one table.&nbsp;&nbsp;The fileds I am looking to use for the report would be a date field indicating the date the complaint was received and a text or string field with the descrption of the complant type.&nbsp;&nbsp;The complaint description or type would not change from year to year.<br><br>If I ran the report today I would like the report to tell me I have had x number of (insert complaint) for this month.&nbsp;&nbsp;I have had y number of (insert complaint) for the yeartodate. And I had z number of (insert complaint) for lastyeartodate.<br><br>It is kind of hard to describe it I hope I have made it a little bit clearer.&nbsp;&nbsp;If not keep asking I and will keep trying.

 

[MalcolmW]

If you are using a local database such as Access, just set up a group by Complaint Type, and pop in the following formulas.&nbsp;&nbsp;If you are using SQL, you could get better performance writing a query to do the summaries on the server, but this method would work as well too.<br>//Reset formula in Complaint Type group header<br>WhilePrintingRecords ;<br>NumberVar MTDComplaints := 0 ;<br>NumberVar YTDComplaints := 0 ;<br>NumberVar LYTDComplaints := 0 ;<br>//end<br>//Summary formula in detail<br>WhilePrintingRecords ;<br>NumberVar MTDComplaints ;<br>NumberVar YTDComplaints ;<br>NumberVar LYTDComplaints ;<br>If {complaints.date} in MonthToDate Then <br>&nbsp;&nbsp;&nbsp;&nbsp;MTDComplaints := MTDComplaints + 1<br>Else <br>&nbsp;&nbsp;&nbsp;&nbsp;0 ;<br>If {complaints.date} in YearToDate Then <br>&nbsp;&nbsp;&nbsp;&nbsp;YTDComplaints := YTDComplaints + 1<br>Else <br>&nbsp;&nbsp;&nbsp;&nbsp;0 ;<br>If {complaints.date} in LastYearYTD Then <br>&nbsp;&nbsp;&nbsp;&nbsp;LYTDComplaints := LYTDComplaints + 1<br>Else <br>&nbsp;&nbsp;&nbsp;&nbsp;0 ;<br>//end<br>//Display results formulas<br>WhilePrintingRecords ;<br>NumberVar MTDComplaints ;<br>//end<br><br>WhilePrintingRecords ;<br>NumberVar YTDComplaints ;<br>//end<br><br>WhilePrintingRecords ;<br>NumberVar LYTDComplaints ;<br>//end<br><br> <p>Malcolm Wynden<br><a href=mailto:wynden@island.dot.net>wynden@island.dot.net</a><br><a href= > </a><br>

 

[Ina]

In case of large numbers, YTD and MTD will slow performace considerably.&nbsp;&nbsp;If you can, offload these calculations on the db and just pull the field in the report.&nbsp;&nbsp;

 

[PerryP38]

Thank You very much for your help.

 

[PerryP38]

Well I thought I had it, and I seem to be getting closer. I created a formula box with this formula in it and placed it in the group header which was based on the complaint number.

WhilePrintingRecords
NumberVAR MTDComplaints:=0;
NumberVAR YTDComplaints:=0;
NumberVAR LYTDComplaints:=0;
//end

I then created a second formula box and placed it in the group detail section
//Summary formula in detail
//WhilePrintingRecords ;
//NumberVar MTDComplaints ;
//NumberVar YTDComplaints ;
//NumberVar LYTDComplaints ;
//If {tblIncident.DateReceived} in MonthToDate Then
// MTDComplaints := MTDComplaints + 1
//Else
// 0 ;
//If {tblIncident.DateReceived} in YearToDate Then
// YTDComplaints := YTDComplaints + 1
//Else
// 0 ;
//If {tblIncident.DateReceived} in LastYearYTD Then
// LYTDComplaints := LYTDComplaints + 1
//Else
// 0 ;
//end
//Display results formulas
//WhilePrintingRecords ;
//NumberVar MTDComplaints ;
//end

//WhilePrintingRecords ;
//NumberVar YTDComplaints ;
//end

//WhilePrintingRecords ;
//NumberVar LYTDComplaints ;
//end

It seems to count the records but it does not break them down to month to date year to date or last year to date.

I must be doing something wrong here any suggestions?

 

[MalcolmW]

Are these formulas in the complaint group footer?
The '//' comments out lines, so any lines that begin with these two characters are ignored. I presume your actual formualas are not commented out.

//Display results formulas
WhilePrintingRecords ;
NumberVar MTDComplaints ;
//end

WhilePrintingRecords ;
NumberVar YTDComplaints ;
//end

WhilePrintingRecords ;
NumberVar LYTDComplaints ;
//end

 

[PerryP38]

I did have them as part of the formula in the detail section. I have removed them and put them in the footer section now.

They are not commented out that was my mistake in the post.

When I run the report I am now getting a group for each complain and then a list of dates with zeros or numbers that incrment by ones. In the footer I get the total fro the group.

There is no count for the month to date year to date or last year to date.

I still must be missing something here

Thanks

 

[MalcolmW]

Yes, it sounds like your missing something, or have something commented out, or have a typo, or something else.