Hi everybody, hoping someone can

[Yazster]

Hi everybody,

hoping someone can help me with this one. I have a report to design, I have all the table links and I'm ready to go, but here's the problem:

I have multiple fields, including one DATE field. I want to design a report that includes all the fields, but separated in to 3 categories:

1) All data where DATE is TODAY
2) All data where DATE is EARLIER than today
3) All data where DATE is LATER than today

I don't want the report grouped by date, since this would make one section per date. I want all data dated earlier than today in 1 section, grouped together.

Is there any way to do this?

P.S. I'm using CR8

Thank-you very much!

 

[MalcolmW]

Create a formula, and group by this formula

If {DATE} < PrintDate then
  0
Else If {DATE} = PrintDate then
  1
Else
  2

Note that this formula doesn't deal with null dates specifically, so they will be placed by default in their own group. Depending on how you sort the report, they will be at the end or the beginning of the report. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?

 

[Yazster]

Hi Malcolm,

Thanks for the info. I tried doing what you said. Tried 2 things:

1) Typed the formula into the GROUP SELECTION FORMULA EDITOR, and I get a "THE RESULT OF SELECTION FORMULA MUST BE A BOOLEAN".

2) Typed the formula in the "USE A FORMULA AS GROUP NAME" under the Change Group Options menu. This worked fine for the group name, but the data was still being grouped together. I tried changing the "The section will be printed:" options, and although the group names were changing accordingly, the data was still not being split.

Any ideas on what I'm doing wrong?

 

[MalcolmW]

1) Create a formula
Insert|Formula|New
Give it a name, put the following in, changing {DATE} to the field of your choice.
//Start of Formula
If {DATE} < PrintDate then
0
Else If {DATE} = PrintDate then
1
Else
2
//End of Formula
Save this formula.

2) Group by this formula
Either create a new group, and select the formula as the formula on which to group
OR
Change an existing group to group on the newly created formula.
Note that the order of these two steps is important. Malcolm
wynden@telus.net
November is &quot;be kind to dogs and programmers&quot; month. Or is that &quot;dogs or programmers&quot;?

 

[Yazster]

Thanks Malcolm, worked great!

 

[Yazster]

Malcolm, this formula works great, but as you mentioned, null values are placed in their own group.

In my report, 7 of 12 pages don't have dates associated with them, therefore I don't want them to be reported. Is there any way to remove them from the report?

Thanks for your help!

 

[MalcolmW]

Get rid of the records that have null date values. The way to do that is with the record selection formula, with a filter like
NOT IsNull({DATE}) Malcolm
wynden@telus.net