Multiple tables, one report

[dbodenheimer]

Hi, I have a report that uses 2 tables for the results. Sounds simple right? well one of the tables stays the same, while the other changes. I would like to be able to pass the table name to the report and have the SQL statement change. Is there any way to do this?

Here is the SQL

SELECT
    PEPHR0303."LastName", PEPHR0303."FirstName", PEPHR0303."MI", PEPHR0303."SSN", PEPHR0303."DOB", PEPHR0303."HireCode", PEPHR0303."Region", PEPHR0303."PositionCode", PEPHR0303."HireType", PEPHR0303."HireDate",
    Wip."InquiryType", Wip."DateCompleted", Wip."hit", Wip."Info1", Wip."Info2", Wip."DontBill", Wip."CollectionDate"
FROM
    { oj "ops"."dbo"."?tbl" ?tbl INNER JOIN "ops"."dbo"."Wip" Wip ON          ?tbl."SSN" = Wip."SSN"}  
WHERE
    Wip."InquiryType" = 37 AND
    Wip."DontBill" = 0 AND
    Wip.&quot;Info1&quot; <> ''  
ORDER BY
    ?tbl.&quot;HireCode&quot; ASC,      ?tbl.&quot;Region&quot; ASC

 

[shokti]

are you using odbc connection? if you are, use CrystalReport1.SQLQuery = "write your sql string here with the table name you like to pass"

like this:
CrystalReport1.SQLQuery = "SELECT
PEPHR0303."LastName", PEPHR0303."FirstName", PEPHR0303."MI", PEPHR0303."SSN", PEPHR0303."DOB", PEPHR0303."HireCode", PEPHR0303."Region", PEPHR0303."PositionCode", PEPHR0303."HireType", PEPHR0303."HireDate",
Wip."InquiryType", Wip."DateCompleted", Wip."hit", Wip."Info1", Wip."Info2", Wip."DontBill", Wip."CollectionDate"
FROM
{ oj "ops"."dbo"."mytable" mytable INNER JOIN "ops"."dbo"."Wip" Wip ON mytable."SSN" = Wip."SSN"}
WHERE
Wip."InquiryType" = 37 AND
Wip."DontBill" = 0 AND
Wip.&quot;Info1&quot; <> ''
ORDER BY
mytable.&quot;HireCode&quot; ASC, mytable.&quot;Region&quot; ASC&quot;


i hope this helps.

 

[synapsevampire]

You might use a stored procedure to do this.

The SP could then be passed a parameter which is used to construct dynamic SQL.

-k