How to add a date prompt to a report

Document ID : KB000055126
Last Modified Date : 14/02/2018
Show Technical Document Details

Description

This document will outline a simple process for creating a date range selection prompt for reports.

This document assumes that there are date/time fields in your report.

Examples will be using the sample data that comes with CleverPath Reporter.

Solution

If you already have a report that has date fields in it, then you can skip to section x below; otherwise you can follow the steps here to create a simple report to work with.

Creating your initial report:

  1. Open CleverPath Reporter Builder and select New Report

  2. Select the type Quick

  3. For Database Login select the Source Type ODBC and the Data Source CleverPath Reporter Demo

  4. On the Quick Report - Report Summary screen just select Next to go to the Field Selection screen

  5. From Available Tables select Inventory, then from Available Columns select the 'isbn', 'cost', and 'inv_date" fields and use the right arrow to move these columns into the Report Fields section and click Finish

  6. On the Create Report Confirmation dialog click the Yes, I am done button

Creating the date prompt:

  1. Using the report created above, under the 'inv_date" field use your mouse to click on one of the dates. This will place an outline box around the date

  2. With the date selected go to the Query menu at the top of the builder screen. Under the Query menu select Filter Rows (SQL)...

  3. In the Filter Rows Sql dialog, select 'inv_date' from the columns box and click the Add button.

  4. You will now have an Operators selection box. From here highlight between and click the Add button to the right

  5. To the right in this dialog select the button "Variable". For Choices make sure this is set to Date Variable and leave the Variable Name as 'inventory_inv_date".

  6. For the Default Date and Default Time fields do not worry about setting default values now, just leave these as they appear. You can set your Date & Time Formats using the drop down menu.

  7. For User Prompt type in something like Select a beginning date and click the Add button. This will add the first prompt.

  8. Follow the directions for steps 5, 6, and 7 to create the second prompt default value. Notice that the Variable Name changes slightly from "inventory_inv_date" to "inventory_inv_date2". Leave this. Change the User Prompt to Select ending date, and the Default Date and Default Time can be left as they appear.

  9. Click Add and then click OK.

  10. You will now be prompted to select a default date and time. You can use the browse button or click on the Values button to the right. If you use the Values button, click on your selection then click on the Select button, then click on the Close button. Click continue to enter the ending date following the same steps then click continue again.

  11. Now select File -> Print Preview. You will see the Run-Time Variables dialog where you can select your dates. Again; use either the browse button and arrows to change the date and time, or use the Values button to the right. Select your first date and time, click continue, select your second date and time and click continue again to run your report with the selected values

You can now save your report (close the print preview screen) and distribute to your users.