How to Dynamically Select the Data File for a Report at Print Time

[Former Member]

How do you configure a Crystal report to ask for the file to be reported on as the report is being printed, and allow the user to browse to the file?

The environment is Crystal Reports XI, SP3, with ODBC connection to Sage Timberline Office data version 9.7. The client names their Payroll unposted time file each pay period, and also needs to report on their posted data file, depending on the time period for the report. The client will need to select both the date range and the file name.

I have created a SQL statement in Add Command in Database Expert, which prompts for a file name, but it does not let you browse to select a file on the computer.

Therefore, in the prompts when they print the report, the parameter offers the user a default file name similar to the name they currently use, so they only have to change the payroll period end date in the supplied file name to run the report successfully.

The client is concerned that sometimes a user will name their data file differently, and not know how to input the file name into the Crystal report prompt at print time.

My research on dynamic prompts showed you can link to fields inside the data record, but I did not see a way to dynamically link to select the actual files used in the report.

Another question is that the naming convention used by the SQL query is different than the basic Windows file name, but I think I can handle that issue.

The actual file name is typically similar to:

04-10-11 BP NEW.PRT

However, in the SQL query, the record ID looks like:

PRT_00-00-00 BP NEW__TIME

The SQL Statement using a parameter is:

(

SELECT

"PRT_CURRENT__TIME"."Employee",

"EMPLOYEE1"."Employee_Name",

"PRT_CURRENT__TIME"."Date",

"PRT_CURRENT__TIME"."Units",

"PRT_CURRENT__TIME"."Job",

"JOB1"."BP_Emps_Used"

FROM

(

"PRT_CURRENT__TIME" AS "PRT_CURRENT__TIME"

INNER JOIN "JCM_MASTER__JOB" AS "JOB1"

ON "PRT_CURRENT__TIME"."Job"="JOB1"."Job"

)

INNER JOIN "PRM_MASTER__EMPLOYEE" AS "EMPLOYEE1"

ON "PRT_CURRENT__TIME"."Employee"="EMPLOYEE1"."Employee"

WHERE "JOB1"."BP_Emps_Used" = 1

AND

("PRT_CURRENT__TIME"."Date" BETWEEN

{?As of Date} - 41 AND {?As of Date})

)

UNION ALL

( SELECT

"PRT_NEW__TIME"."Employee",

"EMPLOYEE2"."Employee_Name",

"PRT_NEW__TIME"."Date",

"PRT_NEW__TIME"."Units",

"PRT_NEW__TIME"."Job",

"JOB2"."BP_Emps_Used"

FROM

(

"{?NEWPRT}" AS "PRT_NEW__TIME"

INNER JOIN "JCM_MASTER__JOB" AS "JOB2"

ON "PRT_NEW__TIME"."Job"="JOB2"."Job"

)

INNER JOIN "PRM_MASTER__EMPLOYEE" AS "EMPLOYEE2"

ON "PRT_NEW__TIME"."Employee"="EMPLOYEE2"."Employee"

WHERE "JOB2"."BP_Emps_Used" = 1

AND

("PRT_NEW__TIME"."Date" BETWEEN

{?As of Date} - 41 AND {?As of Date})

)

[former_member11696]

Hello,

Sorry you'll have to contact Sage on how to do this. We can help you once you get connected but we can't help you get around their connection methods.

There is no Preview Set Database Connection method you can use in CR Designer. The Designer assumes you select it first or use the Set Location option before previewing or refreshing the data.

If you are doing this in the Sage program itself we can't help you, you'll have to contact Sage for assistance.

Sage is an OEM Partner they are responsible for supporting their product and CR. If they have issues help you then they will contact us directly for assistance.

Thank you

Don