Pass Parameter to SQL Server 2005 Table and Excel (or Access)

[DarkAngel]

Hello!

We have a report grouped by Teams. The teams are currently maintained in an Excel file which allows the user to maintain the Teams. I know having Excel as a data source is not ideal, but the data is not available in the SQL Server tables.

The report is based on an SQL command with a couple of parameters in the where clause. The SQL command is joined to the Excel file in Crystal.

A new need has come up for the team members to be start and end dated to track team history. For example the Excel (or Access) data would include:


LoginID StaffName Team TeamStartDate TeamEndDate
A123 Smith, Becky Broward 1/1/2010 3/12/2011
A123 Smith, Becky Broward/ROVING 3/13/2011  


If Becky had:

6 appointments week of 03/06/11 - 03/12/11
2 appointments week of 03/13/11 - 03/19/11

Run report for date range: 02/27/11 - 03/26/11

Results should be:
6 encounters for Becky with Broward Team
2 encounters for Becky with Broward/ROVING Team

Is it possible to combine data sources in an SQL command and pass the same parameter to both? Where "Date" is from SQL Server table and "TeamStartDate" and "TeamEndDate" are from Excel/Access and the WHERE statement is something like:

WHERE (("Date" BETWEEN ({?RptStartDate}-6) AND {?RptStartDate})
AND ("TeamStartDate" >= {?RptStartDate} AND ("TeamEndDate" is null or <= {?RptStartDate})))

Thanks,
DA

Crystal Reports 2008
BusinessObjects Enterprise XI 3.1
MS SQL Server 2005 (SQL Server 9.0.4035)

 

[lbass]

I don't think you can use a command alone because you would have two different drivers. In addition to your current link, you could, however, link {command.date} to both date fields in Excel, using an equal join, and then change the join type to {command.date} >= {xl.teamstartdate} and {command.date} <= {xl.teamenddate}. This would require you to add a default value in Excel for the teamenddate that is always greater than any possible value, e.g., 9/9/9999.

It is not generally a good idea to link commands to tables, because the links occur locally, but in this case, I think you need to.

-LB

 

[DarkAngel]

Thank you Linda! I'll give this a shot.