Simple Selection Formula not making sense

[vblack7770]

I am using Crystal 8.5 with a standard ODBC-Oracle connection. I have a simple report to pull in records, group them and then count/sum them. When I use the following selection criteria:

{LT_MASTER.APPLICATION_DT} in MonthToDate or
{LT_MISC_INFO.USER_DTS##1} in MonthToDate or
{LT_MISC_INFO.USER_DTS##3} in MonthToDate or
{LT_MISC_INFO.USER_DTS##4} in MonthToDate or
{LT_MASTER.PRECLOSE_DT} in MonthToDate or
{LT_MASTER.CLOSE_DT} in MonthToDate


I receive very few records for the last two groups.

//{LT_MASTER.APPLICATION_DT} in MonthToDate or
//{LT_MISC_INFO.USER_DTS##1} in MonthToDate or
//{LT_MISC_INFO.USER_DTS##3} in MonthToDate or
//{LT_MISC_INFO.USER_DTS##4} in MonthToDate or
{LT_MASTER.PRECLOSE_DT} in MonthToDate or
{LT_MASTER.CLOSE_DT} in MonthToDate

When I comment every other date out and just pull in the last two groups, the totals come back correctly.

Any idea how else I can write this so it pulls back all records for the last groups even when adding the initial groups? The records don't seem to be falling into the other groups, or showing up at all.

This may be a shot in the dark, but I am stumped.

 

[Turkbear]

Hi,
Try commenting out each one in turn to see what is happening..If any of the fields tested has a NULL value, then the formula parts after that one may not work correctly..so test for NULL first ( for each field or be sure the 'Convert NULL Values to Default' is set)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[vblack7770]

So the test for null would have to be outside of the selection criteria as a seperate formula and then I would base selection criteria off the formual?

 

[Turkbear]

Hi,
Actually it can be part of the formula:

(
If Not IsNull({LT_MASTER.APPLICATION_DT}) and
    {LT_MASTER.APPLICATION_DT}  in MonthToDate
)
 or
(
If Not IsNull({LT_MISC_INFO.USER_DTS##1}) and
{LT_MISC_INFO.USER_DTS##1} in MonthToDate
)
 or
....

Or maybe you can use a formula to eliminate the Nulls:

(           [COLOR=green]// Get rid of Nulls first [/color]
Not IsNull({LT_MASTER.APPLICATION_DT}) and 
Not IsNull({LT_MISC_INFO.USER_DTS##1}) and
Not IsNull({LT_MISC_INFO.USER_DTS##3}) and
Not IsNull({LT_MISC_INFO.USER_DTS##4}) and
Not IsNull({LT_MASTER.PRECLOSE_DT}) and
Not IsNull({LT_MASTER.CLOSE_DT})
)
and    [COLOR=green] // Now process the rest of the rows [/color]
(
{LT_MASTER.APPLICATION_DT} in MonthToDate or
{LT_MISC_INFO.USER_DTS##1} in MonthToDate or
{LT_MISC_INFO.USER_DTS##3} in MonthToDate or
{LT_MISC_INFO.USER_DTS##4} in MonthToDate or
{LT_MASTER.PRECLOSE_DT} in MonthToDate or
{LT_MASTER.CLOSE_DT} in MonthToDate
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

What is your group field? I think the problem is that these fields coexist in the same record, so that if a record meets one of the earlier criteria, it will not be assessed for the later criteria. This will pull in the correct records for the report, but grouping on them and getting the correct counts is another matter--a record can only appear in one group.

Maybe you should explain what you are trying to do. You might need to combine all of the datefields into one field using a union all in order to achieve the results you are looking for. Alternatively, you could use running totals or conditional formulas to get the correct counts per type of date.

-LB

 

[vblack7770]

And that is exactly what I need. These date represent stages of a file. If the first date is populated and none of the others are, then it would go in the first group and so on. However, some date fields will not be populated as a file may skip a stage. Does that make sense?

 

[lbass]

Try writing a formula like this {@date}:

if not isnull({LT_MASTER.CLOSE_DT}) then
{LT_MASTER.CLOSE_DT} else
if not isnull({LT_MASTER.PRECLOSE_DT}) then
{LT_MASTER.PRECLOSE_DT} else
if not isnull({LT_MISC_INFO.USER_DTS##4}) then
{LT_MISC_INFO.USER_DTS##4} else
if not isnull({LT_MISC_INFO.USER_DTS##3}) then
{LT_MISC_INFO.USER_DTS##3} else
if not isnull({LT_MISC_INFO.USER_DTS##1}) then
{LT_MISC_INFO.USER_DTS##1} else
if not isnull(LT_MASTER.APPLICATION_DT}) then
{LT_MASTER.APPLICATION_DT}

This should return the date representing the latest stage of a particular record (file). Then in the record selection formula use:

{@date} in MonthToDate

You could also create a formula that would group by type of date:

if {@date} = {LT_MASTER.CLOSE_DT} then "Closed Date" else
if {@date} = {LT_MASTER.PRECLOSE_DT} then "Pre-close Date" else//etc.

You could then insert a group on this to show the stages within the month, and insert counts by stage. You should create the formula in descending order of stage so that the most recent stage is always picked up in the case of ties.

-LB