Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

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 Member
Not what you were looking for? View more on this topic or Ask a question