Suppres Group Footer Based on Maximum Formula Field Results

[lesleycampbell]

I am using CR11 and have a report where I am trying to only look at employees whose last check date was greater than or equal to 01/01/2008. I have the report grouped by employee id and check and a maximum check date formula field in the group footer. I'm trying to suppress the group footer by entering

{@maxcheck} < date(01/01/2008)

into the x-2 formula field to suppress section. It is still pulling the same records and not suppressing any information.

Help?

 

[briangriffin]

It would be better to not bring in all the data you don't want to see, rather than discarding or suppressing it.

If you can write a command object it would be something like this:

select employeenumber, max(paycheckdate) from paychecktable group by employeenumber having max(paycheckdate) >= '1/8/2008'

The command appears in the database expert the same as a table - join from your existing table to the command on employeenumber, and the data returned to the report is already filtered.

Let me know if you need more help on using commands, or if this isn't a viable option in your environment.

 

[lesleycampbell]

I actually got it to work. I had to use a datetime format versus a date format. Sometimes it's so simple, it makes you feel so silly! Thank you!

 

[briangriffin]

I would still caution you about group suppression - it can be responsible for slow performance, and totals are often wrong depending on what you're trying to do. All those extra records are still in there, just hidden.

But I'm glad you got it working!

 

[lesleycampbell]

I'm not familiar with using command objects. How would I know if I can add one to my report or not?

 

[briangriffin]

In the Database Expert, you'll see the first item under the connection name is 'Add Command'. Double click on that, and you can paste in any valid SQL statement that returns a dataset.

So the first hurdle is to write the SQL statement for your platform that returns the correct employee numbers (see my example). If you're using SQL Server then you should only have to change the field and table names.

Once that is done the command will appear as a second table and you can join to your existing table normally. Post back if you get stuck.