Build Report using Selected Date period

2 replies [Last post]
AussieWombat
Offline
Last seen: 1 year 2 weeks ago
Title: ★★
Joined: 9 Dec 2014
Posts: 16
Hi,

I have a report, that is built from a sql query, that selects date from multiple tables with common JobID. The query use’s a ‘BETWEEN’ command word.

Is it possible to, when requesting the report, the user can select the two dates (period) to build the report from, rather than manually editing the date in the query?

SQL Query:


SELECT "tbl_JobData"."Date" AS "Date", "tbl_JobData"."JobNumber" AS "JobNumber", "tbl_JobData"."Customer" AS "Customer", "tbl_JobData"."Division" AS "Division", "tbl_JobData"."Total Qty", "Q_Sum_SubJobdata"."SUM_Quantity", "tbl_JobData"."Expected Time", "Q_Sum_SubJobdata"."SUM_Time", "tbl_JobData"."Overall Efficiency" AS "Overall Efficiency" FROM "Q_Sum_SubJobdata", "tbl_JobData" WHERE "Q_Sum_SubJobdata"."FK_JobID" = "tbl_JobData"."JobID" AND "tbl_JobData"."Date" BETWEEN {D '2015-04-01' } AND {D '2015-05-01' } AND "tbl_JobData"."Division" LIKE 'Digital'

Regards AussieWombat

frofa
frofa's picture
Offline
Last seen: 13 hours 24 min ago
Title: ★★★★★
Joined: 24 Feb 2012
Posts: 804
Use a parameter-based query
Hello AussieWombat:

Just base your report on a QUERY using PARAMETER (user) input. For example, your WHERE clause could be re-written…


WHERE "Q_Sum_SubJobdata"."FK_JobID" = "tbl_JobData"."JobID" AND "tbl_JobData"."Date" BETWEEN :start_date AND :end_date AND "tbl_JobData"."Division" LIKE 'Digital'

When you run the report the user-entry window should appear asking for the two date-range values.

AussieWombat
Offline
Last seen: 1 year 2 weeks ago
Title: ★★
Joined: 9 Dec 2014
Posts: 16
[SOLVED] Build Report using Selected Date period
Thanks Frofa, worked a treat.

AussieWombat

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.