Logic issue for data selection

[dbaJSmith]

Hello all:
I'm using CR11 to pull several tables, and I'm having trouble getting the right data onto the list. The table I end up with (after joins) looks something like this, with other fields:

Category | Name | Date | Result
1 | Risk A | 1/1/2007 | True
1 | Risk B | 1/1/2008 | False
1 | Risk C | 1/1/2008 | True
...

I'm trying to show each Category and Name, but filter the data shown by date. On my report, I want to see this:

Category: 1
Risk A - 0 True 0 False
Risk B - 0 True 1 False
Risk C - 1 True 0 False
...

The problem is, when I do the record selection, it removes the Names which do not have a value in the date range, so I end up with Risks B and C. I think my solution may be in a logic structure, but I'm not sure. I was going down the lines of "not isnull({Name}) or {Date} = {?prmDate}", but this does not work obviously. Any ideas would be great, thanks!

 

[IanWaterman]

What is the logic for the True or false for each Risk type.

Not sure why Risk A is zero for both.
Ian

 

[dbaJSmith]

The logic isn't a deciding factor in the data pull; it is summarized in the report. Risk A is zero for both because the date is outside the range for the Risk A response. So, if the parameter for prmDate is 1/1/2008, Risk B and Risk C would show up as part of the data pull, but Risk A wouldn't. What I want to do is show Risk A on the list with B & C, but not include the result as part of its count.

 

[IanWaterman]

Create two formula for true and false

@true
If prmDate = 1/1/2008 and result = true then 1 else 0

@false
If prmDate = 1/1/2008 and result = false then 1 else 0

YOu can replace date with a daterange parameter if that is more useful.

YOu can then sum these formula in either group or report footers.

Ian

 

[dbaJSmith]

Is there another method that would keep me filtering on the record selection, rather than each formula? Basically, the client wants to see each Name field, whether there is a value for it or not.

 

[lbass]

How are your tables linked? Please show the resulting SQL query.

-LB

 

[dbaJSmith]

Four tables. End goal is to show all RiskFactor.Name fields, whether or not the Service.ServiceDate falls within the parameter range. Grouping in report is 2 levels: RiskFactors.Category, then RiskFactors.Name.

SELECT RiskFactors.Category, RiskFactors.Name, Person.ID, Service.ServiceDate, Service.Results, Service.ID
FROM ((Service LEFT OUTER JOIN Person
ON Service.Client=Person.ID)
LEFT OUTER JOIN Service_RiskFactors ON Service.ID=Service_RiskFactors.Service)
LEFT OUTER JOIN RiskFactors ON Service_RiskFactors.RiskFactors=RiskFactors.ID
WHERE RiskFactors.Name IS NOT NULL AND (Service.ServiceDate>={d '2008-04-01'} AND Service.ServiceDate<={d '2008-06-30'})
ORDER BY RiskFactors.Category, RiskFactors.Name

Sorry if I wasn't clearer before, this has been a long week.

Also, I was playing around with the idea of using a logic control in my record selection to fix this; something along the lines of an "xor", but I can't figure out the T/F balance on it. Any ideas would be useful.

 

[lbass]

I think you need to change your joins. Try using a command as your datasource, like this, so that you can do a select on a left outer joined table without limiting the records returned in the first table:

SELECT RiskFactors.Category, RiskFactors.Name, Person.ID, Service.ServiceDate, Service.Results, Service.ID

FROM (((RiskFactors left outer join Service_RiskFactors on
RiskFactors.ID = Service_RiskFactors.RiskFactors and
RiskFactors.Name IS NOT NULL)

left outer join Service on
Service_RiskFactors.Service = Service.ID and
(Service.ServiceDate>={d '2008-04-01'} AND Service.ServiceDate<={d '2008-06-30'}))

left outer join Person on
Service.Client = Person.ID)

ORDER BY RiskFactors.Category, RiskFactors.Name

Not absolutely sure I've got this right, but it's worth a try.

-LB

 

[dbaJSmith]

Not working; comes back as "unsupported use of outer join." Might be the database I'm connecting to.

In any case, the client has changed their request, so I'm good to go. Thanks anyways!