Return Zero Counts for Items

[infotech2]

I have a SQL view that I created to return a list of services performed on contract vessels for a specific time frame. I wanted to return all contract vessels even if no services were performed and have a zero for those results.

In order to do this I had to set the "where" conditions at the Join level.

Example:
LEFT OUTER JOIN dbo.tblServiceRequest Services ON Vessels.VesselID = Services.VesselID AND Services.AttendanceDate BETWEEN CONVERT(DATETIME,
'2009-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-01-31 00:00:00', 102) AND Services.RequestType = '1'

If I create the same data set in Crystal using the same tables and use the select expert to filter the data it doesn't return vessels with zero services.

Is there a way to accomplish this in Crystal?

 

[IanWaterman]

I am guessing in select exprt you have

Services.RequestType = '1'
When you put a condition on the right hand table the Left outer id over ridden.

Try

(isnull(Services.RequestType) or Services.RequestType = '1')

Ian

 

[IanWaterman]

Alternatively why not report off your view or if you prefer convert your view to a command and report off that.

Ian

 

[infotech2]

Ian,

Thank you for the prompt response!

I'll try the isnull filter. Along with the date parameters it might work.

I am reporting off of my view, but eventually I want to hand this off to users and allow them to enter the dates they want.

What is a command? I see it under the connections window but I'm not familiar with it.

 

[IanWaterman]

When selecting data in data expert just select command.

This will open a SQL window to which you can add your bespoke sql and run report off data returned.

YOu can pass parameters to commands too, search this forum as there are postings on how to do this.

Thus users will be able to select dates which will go into your query.

Ian

 

[lbass]

Using isnull() does not necessarily solve the issue, because there could be records that are not null but don't meet the condition.

The best solution really is to use a command, where you can add criteria in the from clause as you have done, instead of the where clause, since this will preserve the left join. As Ian notes, you create parameters within a command.

-LB

 

[infotech2]

I did run into that problem with trying to use NULL as a filter. That's what led me to adding criteria to the FROM clause.

Thanks for the replies. "Command" looks like a great feature I haven't even begun to explore. Much appreciated!