Friday, September 29, 2006

Converting DateTime to Date Fields

Many databases do not support a separate Date format from DateTime. SQL Server is one of them - you can't have just a "date" field persay; it ends up being a date/time field.

But when you're putting these fields onto a report, you don't want to show the hour and minutes - you just want to see the date.

With Foxfire!, you can do this by using the TTOD() function in the Layout Expression field. The Report Expression on the main page is passed directly to the SQL Server but the Layout expression field is used on the output.

We've done up a screencast that shows how to do this, using the SQL Server Pubs database.

Check it out here!

Thursday, September 28, 2006

Outer Join Blues (or how to get the results you want)

One of the issues that confuse many Foxfire! report designers is how to handle Outer Joins along with filters.

The classic example (using the Cars database) is I want to see all of my sales people along with any of their Ford sales.

The typical approach would be to build the report showing Salesperson Name, Cars Stock #, Cars Color and Cars Model.
Set the Outer Join to show you All Salespeople including those who don't have any matching cars.

But then if you add a filter to this that says Cars Make Is Like Ford, it immediately negates the Outer Join.

Why? Because you said you only wanted to see Ford Cars.

How do you get around this? By using NULL!

Add an OR to the Filter and add the condition:
Cars Make Is Null

This would make your filter read Cars Make Is Like Ford OR Cars Make Is Null.

When you run this, you will see ALL of your salespeople, not just the ones with Ford - but the only Cars information you'll see will be Fords.

Now this gets a bit trickier when you have multiple filter conditions but it's still doable. Let's say you also want to add an additional condition of sales people who have started since 2000.

So now your condition would be:
Sales Start Date Is After 1/1/2000 (and)
Cars Make Is Like Ford
OR
Cars Make Is Null.

Right?

Not quite. You need to wrap your Cars filters with Parentheses so they read:

Sales Start Date Is After 1/1/2000 (and)
(
Cars Make Is Like Ford
OR
Cars Make Is Null
)

Thankfully, in Foxfire! 8.0, you can highlight all of the conditions and just click one button to wrap them with the parentheses.