pulling data based on "months" report

[Pluto87]

Hi, I am having a trouble with a report I am trying generate. I have some experiences with crystal reports, but this report I am trying to pull has gotten me stump. Any help/advice is very much appreciated.

We have a database that data gets enter on a daily basis. I connect to crystal reports through OLE DB (ADO). The following fields are fields that I need to show in the report.

Table name: FEES
LastName_
FirstName_
State_ (ex: GA)
Month_ (number 1-12)
Date_Created (date and time)

A total of 2140 people are in the database.

The field name "Date_created" (located in the same FEES table) which records the date and time that the record was recorded.

The report that I need to generate needs to pull data (lastname, firstname, state, and month, Date_Created) that did not report at the end of the month.

So, if John Smith did not reported his data for the month of October, I need his information (lastname, firstname, state, month, Date_Created) to pull from the data.

I tried using the Select Expert with this formula:

{FEES_Month} = 9.00 and
not ({FEES.Date_Created} in DateTime (2011, 05, 03, 08, 26, 48) to DateTime (2011, 10, 07, 13, 27, 02))

But there are data (35 out of 2140) that have not been enter in the month 9 and it is not showing up.


Thanks!

 

[MCuthill]

Pluto,

Is there a separate table containing the customer information or is it the same list as the prior month?

** The reason I ask, is it is hard to know if someone is missing for a month, if there is not a "complete customer list" to compare it to. **

1) If you have a separate listing for people, you would have a Left-Outer join, and look for a "null" value in the FEES table. (the rest of the criteria limiting as in your post).

2) If same as prior, you could run the report for the month prior, and the reporting month, groupig on person. Created a formula look for the month of prior, and a second for the reporting month. (if month then 1 else 0) sum both formulas and look for the ones with >0 for prior and 0 for reporting month.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Pluto87]

Hi MCuthill, thanks for the reply.

The FEES is the same list as the prior month. I had a thought that this report might not work since the data is being enter as the fees are paid and a "customer" list is not available.

I will try the two options, thanks!

 

[lbass]

In what way is the Date Created field relevant? Don't you just want to identify rows where there is no entry for a specific fees.month/year? Not sure whether there are multiple rows per name, but let's say there are. Then remove your selection formula, insert a group on {@name} (last name + first), and then create a formula in the field explorer:

//{@Sept}:
if {fees.month} = 9 and
{fees.year} = year(currentdate) then 1

Then go to report->selection formula->GROUP and enter:

Sum({@Sept},{@name}) = 0

This would show those customers with no Sept entries.

-LB

 

[MCuthill]

Thanks LBass!

Hadn't thought of that second idea being more simple than a two month comparison.

Brilliant, as always.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[Pluto87]

Hi, the Date_Created field shows the date/time users enter the information. This field helps to show when the user enters the data because we need to know who had not paid fees at the end of the month. (not sure if that makes sense or I'm just repeating myself)

LB, the database does not have fees.year, can replace that with Date_Created?

 

[lbass]

If you don't have a year field and you know the data is always for the year of interest, just leave that out of the formula. So are you trying to determine not just if there were fees paid for a particular month but whether they were paid on time? What if you saw the following--which people would you want appearing on the report?
Month Date Created
Jones 8 8/15/2011
Jones 9 9/15/2011
Smith 9 10/7/2011
Doe 8 8/31/2011
Brown 10 10/7/2011
Green 9 8/31/2011

I realize now that if month is only populated when a fee is paid, I'm not sure how you would know whether a fee is due. For example, could Doe have terminated the service and not owe for September? Could Brown be new in October? What about early payments, as for Green?

-LB

 

[Pluto87]

The count list people is 2140, there won't be any new person unless the dba manually adds it in the database, and all other fields are left blank (month, fees_paid) only names, and contact information is entered.)

The only permission users have is to enter how much of the fees clients pay each month. Date_Created is automatically entered when users enter the fees information.

Fees need to be paid at the end of each month, but some will stagger in at the beginning of the next month or early before a determined month is due, but that doesnt matter. If it is the end of the month of September, I need people who still have not paid their monthly fees.

If this is shown:
Month Date_Created
Jones 8 8/15/2011
Jones 9 9/15/2011
Smith 9 10/7/2011 -- he is late, but it doesn't matter
Doe 8 8/31/2011
Brown 10 10/7/2011
Green 9 8/31/2011 -- he is early, doesn't matter either.



For example, my data shows something like this:

Month Date_Created
Jones 7 6/12/2011 -- he paid 7th month's fees - ok
Smith 7 7/12/2011 -- paid on time -- ok
Mark 7 7/25/2011
Mark 8 --------- Mark paid for the 7th month, but has not paid the 8th month
Ken 8 --------- Ken is missing his payment for the 8th month as well.

I need to show on the report that Mark and Ken has not paid the 8th month.

 

[lbass]

But I thought you said the Month field is empty if there are no fees? If that is the case, then my original solution should work correctly. Did you try it? What happened?

-LB

 

[Pluto87]

Just enter this? if {fees.month} = 9 then 1

Opps -- had to check with the dba, the month field is already enter in database (1-12)

 

[Pluto87]

I'm sorry, correction again,

Date_Created is updated by system automatically upon record creation and month data (1-12) is already recorded, this means, the Date_created needs to show date and time if fees were paid within a month's period.

 

[lbass]

//{@Sept}:
if
(
not isnull({fees.create_date}) or
date({fees.create_date}) <> date(0,0,0)
) and
{fees.month} = 9 then 1

Then follow the group selection steps outlined earlier.

-LB

 

[Pluto87]

It works! thanks so much lb!