Tracking Goals 1

[jwmott]

Using Crystal 10

I need to read a years worth of records in. These records are a list of goals set with some goals met. I need to report on a fiscal quarter basis how many people met at least one goal each quarter. Once a person has met a goal, they cannot be counted in any subsequent quarter, even if they have com,pleted a goal in a subsequent quarter.

Got any suggestions? The first part, who met a goal on a quarterly basis is easy, but I am having problems flagging a record so that it doesn't get counted in subsequent guarters.

 

[lbass]

Try adding a command object that is set up something like this:

SELECT min(Table.`Date`) as FirstMetGoal, `Table`.`Client ID`
FROM `Table` Table
WHERE Table.`MetGoal` = 'Y'
GROUP BY Table.`client ID`

Link this command to your other table by FirstMetGoal to {Table2.Date} and by client ID to clientID. Right click on the join->link options and choose "enforce both joins".

This will return only the first record per person that met the goal. If you are defining a time period, that would need to be built into the command in the where clause.

You can then insert a group on the date by quarter and insert counts, ,since only one record will be returned per person.

If you need more help, you should provide the fields you are working with and some more detail about your report structure (groups, etc.).

-LB

 

[jwmott]

Thanks, your info got me started. I am having some additional problems creating user defined quarters however. I could simply prompt the user for the beginning and endind dates for each quarter, but there has to be a way to calculate quarters.

Using MS-SQL 200 server, ADO connection, Prior to your response I used the following command

select distinct
he.clientname,
he.clientid,
he.entrydate,
sl.grantdesc,
goals.evalcode,
goals.goaldesc,
goals.goaldt as setdate,
goals.dateestcomplete as estimatedcomplete,
goals.dateattained,
goals.status,
goals.activestatus,
goals.orgid,
goals.restrictorg,
cbbi.itemdesc as GoalStatus

from

HomelessEnrollment he

right outer join

hmStewartlog sl

on sl.grantid = he.grantid

right outer join

cmgoals goals

on

goals.clientid = he.clientid

left outer join

cmComboBoxitem cbbi

on

cbbi.item = goals.status

where

cbbi.combobox = 'Status' and
cbbi.comboboxgrp = 'CMGOL' and
cbbi.activestatus <>'d'

I group the report on Command.grantdesc, then on @Quarters, then on Command.ClientName

@Quarters is as follows

if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 3 then
formula = "1st"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 6 then
Formula = "2nd"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 9 then
Formula = "3rd"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 12 then
Formula = "4th"
else
Formula = "Error"
end if
end if
end if
end if

I don't think it gives me the correct results (doen't handle the change in year properly) I searched through the posts and didn't find a solution to user defined quarters.

 

[lbass]

Why wouldn't you just insert a group on {Command_1.FirstMetGoal} and choose "print on change of quarter"? Am I missing something?

-LB

 

[jwmott]

It is more likely that I am missing something. How would I get teh gropup header to display the right Quarter information since the quarter is based on a user defined start date?

 

[lbass]

Change your formula to:

if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 2 then
formula = "1st"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 5 then
Formula = "2nd"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 8 then
Formula = "3rd"
else
if datediff("m",{?BegDate},{Command_1.FirstMetGoal}) <= 11 then
Formula = "4th"
else
Formula = "Error"
end if
end if
end if
end if

This assumes the user has a list of quarter start dates to choose from so that odd quarters don't result (like Feb - Apr). This also means that what constitutes Quarter 1 is based on the user's selection.

-LB

 

[jwmott]

Thanks LB, I thought of that too. I thinki I am getting closer to the right solution.