Newbie Help with Selection

[schief13]

This is probably pretty easy but I need help with a selection formula. I want to first select customers by their contract date from one table and then include transactions from another table, based on a date range, if any. If there are no transactions just display customer info. The formula below will return the result I am looking for only if it finds data in the (trxhistoryhdr) table. Thanks


{Site.EndDate} in DateTime (2010, 01, 01, 00, 00, 00) to DateTime (2010, 01, 31, 00, 00, 00) and
{Site.ContractType} in ["KNBT", "LA1", "LA12", "LA2", "LA3", "LA4", "PM1", "PM12", "PM2", "PM3", "PM4"] and
{TrxHistoryHdr.TrxDate} in DateTime (2009, 01, 01, 00, 00, 00) to DateTime (2009, 12, 31, 00, 00, 00) and
{TrxHistoryHdr.PostedFromAppl} = "SB" and
{TrxHistoryHdr.CallType} > "0" and
{TrxHistoryHdr.TrxType} = 0

 

[Turkbear]

Hi,
Since you need to apply selection criteria for each table, the usual method of using an OUTER join will not work, so you will probably need to use a subreport to get the TrxHistoryHdr info .
Try this:
In the Main report,create a group for the site.ContractType and use it and the date range as your selection criteria.
Place the sub report in the details of that group to get all associated TrxHistory data for that Contract type.

This assumes a link can be made between data in the Site table and data in the TrxHistoryHdr one.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

An aside first--your datetimes are not picking up an entire month. Your ending datetimes should read,e.g., datetime(2009,12,31,23,59,59) to pick up the entire month.

As an alternative to a subreport, it is possible to also maintain a left join by incorporating the table criteria in the "from" clause (instead of using a where clause) in a command that is used as the sole datasource for your report.

-LB

 

[schief13]

I had not utilized a subreport before but I spent the afternoon working through it and succeded. Thanks to everyone for your help.