I am running CR 9.2 against a SQL Server db using ODBC.
I have 5 tables. I want to report fields from all five on the detail section of the report.
Activity.id field in primary table
links with LOJ to Sale.id field(indexed) in Sale table
& links with LOJ to Oppty.id field(indexed)in Oppty table
& links with LOJ to Tour.id field(indexed) in Tour table
also
Sale.pid links with EJ to Product.pid(indexed) in Product table
Oppty.pid links with EJ to Product.pid in Product table
Tour.pid links with EJ to Product.pid in Product table
I.E. the Sale, Oppty, & Tour pid fields look up the corresponding Product record.
A given product record can be referenced by many records in Sale, Oppty, and Tour tables.
My Select,Record formula is:
(Left({Product.name}, 2) in ["10" to "9Z"])
and
({Activity.date} in [{?start} to {?end}])
If I omit the range restriction on Product.name, the report outputs the records from Activity in date range, but nothing from the Sale, Tour, Oppty, or Product tables. If I apply the restriction I get no records out at all.
The desired output in the detail section for each record in range from Activity, is simply of the form:
RH
PH
Detail
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Tour.id Tour.pid Tour fields Product.pid Product fields
Activity.id Tour.id Tour.pid Tour fields Product.pid Product fields
Activity.id Oppty.id Oppty.pid Oppty fields Product.pid Product fields
RF
PF
So... are multiple links to the same Product table not allowed? Use of LOJ's instead of EJ's does not seem correct because I dont need Sale, Tour, or Oppty records that do not have a corresponding Product.pid values. Does the link processing order matter, especially for SQL? Is there something wrong with my Select formula?
I am pretty new to CR. Can anyone see what I am doing wrong with what I have provided here? Any hints would really be appreciated. Thanks in advance.
I have 5 tables. I want to report fields from all five on the detail section of the report.
Activity.id field in primary table
links with LOJ to Sale.id field(indexed) in Sale table
& links with LOJ to Oppty.id field(indexed)in Oppty table
& links with LOJ to Tour.id field(indexed) in Tour table
also
Sale.pid links with EJ to Product.pid(indexed) in Product table
Oppty.pid links with EJ to Product.pid in Product table
Tour.pid links with EJ to Product.pid in Product table
I.E. the Sale, Oppty, & Tour pid fields look up the corresponding Product record.
A given product record can be referenced by many records in Sale, Oppty, and Tour tables.
My Select,Record formula is:
(Left({Product.name}, 2) in ["10" to "9Z"])
and
({Activity.date} in [{?start} to {?end}])
If I omit the range restriction on Product.name, the report outputs the records from Activity in date range, but nothing from the Sale, Tour, Oppty, or Product tables. If I apply the restriction I get no records out at all.
The desired output in the detail section for each record in range from Activity, is simply of the form:
RH
PH
Detail
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Sale.id Sale.pid Sale fields Product.pid Product fields
Activity.id Tour.id Tour.pid Tour fields Product.pid Product fields
Activity.id Tour.id Tour.pid Tour fields Product.pid Product fields
Activity.id Oppty.id Oppty.pid Oppty fields Product.pid Product fields
RF
PF
So... are multiple links to the same Product table not allowed? Use of LOJ's instead of EJ's does not seem correct because I dont need Sale, Tour, or Oppty records that do not have a corresponding Product.pid values. Does the link processing order matter, especially for SQL? Is there something wrong with my Select formula?
I am pretty new to CR. Can anyone see what I am doing wrong with what I have provided here? Any hints would really be appreciated. Thanks in advance.