Creating a Period to Date Choice Prompt in Bullhorn Reporting (Previously Canvas)

So you’ve discovered how to filter a report for Year to Date (YTD), Quarter to Date (QTD), Month to Date (MTD), and Week to Date (WTD). Now you want to create a prompt that allows you to choose which one to apply when running your report to increase its efficiency and minimize duplicate reports.

The following instructions assume you have a report built for the purpose of counting and summarizing your data. In our example, we are counting the number of contacts added in the date range and by whom. Our report is built with the User Activity Analysis package.

Modifying the Report

From your list view, select More next to your desired report and choose Modify Report.

Select Report, then click Query1.

Ensure your query includes the Date Added field that coincides with what you’re counting.

Adding the Prompt

  1. Select Report, then click Prompt Pages.
  2. Click within your report to make the filter icon appear in the Toolbar.
  3. Select the Filter icon and choose Edit Filters.
  4. Select the Add button. This will open the Create Filter window.
  5. Under Insertable objects > Toolbox > double-click Page.
  6. Double click Prompt page1.
  7. From the Toolbox, select Prompting, and then click and drag Value Prompt to the prompt page.
  8. In the Prompt Wizard - Value prompt pop up, within the Create a new parameter field, type “Date Range”, and select Finish.
  9. Select the new prompt so that it highlights.
  10. In the Show properties pane, under Data, select Static Choices, then click More.
  11. In the Static Choices pop up, click Add.
  12. In the Edit pop up, under Use, type “YTD”.
  13. Under Display, type “Year to Date”, and select OK.
  14. Repeat the last three steps for Quarter to Date (QTD), Month to Date (MTD), and / or Week to Date (WTD), as desired, and select OK.
    • You must create at least 2 values before proceeding.
  15. When finished adding values, under Static choices, click OK.

Applying the Filter Based on the Prompt Choice

  1. Select Report, then click Page1.

  2. Click the three dots on the chart, select Filter and choose Edit Filters…
  3. Under the Filters - Query1 pop-up, click on Add .
  4. Under the Create filter pop-up, select Advanced, and then click OK.
  5. Under the Detail filter expression - Query1, in the Expression Definition box, paste in the below expression:
    Copy
    (?Date Range? = 'YTD' AND ([Date Added] between _make_timestamp(_year(current_date),1,1) AND current_date)) OR
    (?Date Range? = 'QTD' AND (_year([Date Added]) = _year(current_date) AND (( _month (current_date) in (1,2,3) AND _month([Date Added])  in (1,2,3)) OR (_month (current_date) in (4,5,6) AND _month([Date Added]) in (4,5,6)) OR (_month (current_date) in (7,8,9) AND _month([Date Added]) in (7,8,9)) OR (_month (current_date) in (10,11,12) AND _month([Date Added]) in (10,11,12))))) OR
    (?Date Range? = 'MTD' AND ([Date Added] between _first_of_month(current_date) AND current_date)) OR
    (?Date Range? = 'WTD' AND (_year([Date Added]) = _year(current_date) AND _week_of_year([Date Added]) = _week_of_year(current_date)))

    • Notice that the above expression is looking for the choice made in the ?Date Range? parameter / prompt and also looking at the [Date Added] field. If you’re using this concept for your own report, ensure you edit these items as necessary to match your report’s field and parameter.

    • This expression assumes you're using all 4 options from the above steps. If you opted to use only a few, you'll want to remove the associated filter(s). You can identify which ones to remove by looking at the ?Date Range? = 'XTD' sections of the expression.

  6. Click Validate, to validate the expression.
  7. A prompt pop up will appear. Choose any date range.
  8. Click OK.
  9. If present, correct any errors.
  10. Click OK.
  11. On the Detail filter expression- Query1 screen, click OK.
  12. Save and Run your report.

You now have a report that you can filter by YTD, QTD, MTD, and / or WTD depending upon your choice in the initial prompt. You can use this concept on any other reports you need.

Related