Forum Discussion

BrandiSullivan's avatar
BrandiSullivan
Qrew Member
3 years ago

Getting Report Formulas to follow Date Range filter rules

I have entered a report formula into my report to show all call types related to a user's ID # in the same column.
For example, if user ID 123456 had one call on 5/1/22 for Call Type A and one call on 5/18/22 for Call Type B in the data table, the report would show only one line for user ID 123456 and in the Call Types column it would list both A and B showing that in the data user ID 123546 has had calls for both Call Types A and B.
In my report I then added a filter to filter the report ​by a date range which would be selected by the user (Date of call is on or after <ask user> AND Date of call is on or before <ask user>). The report formula is not following the report filter I have added. 
For example:
Expected Result - If the date range entered is 5/1/22 to 5/10/22, the report will show one line of date for user ID 123456 and only show the call type A that is in the data table for the call that came in on 5/1/22.
Actual Result - If the date range entered is for 5/1/22 to 5/10/22, the report shows one line of data for user ID 123456 but in the call type column it still shows Call Types A and B even though Call Type B came in on 5/18/22 which was outside of the date range I selected for my filter. 

How do I get my report formula to only show results for the date range I have selected?

------------------------------
Brandi Sullivan
------------------------------
Formula and functions
reports and charts
  • JamesGrove's avatar
    JamesGrove
    Qrew Member
    3 years ago

    You can accomplish what you want with the following setup:

    a) Create a new Report Dates table where you would have just one record.  The Record would have 2 fields, Begin Date and End Date.  This record and the dates that you enter there would effectively replace your Ask The User filter on your Summary Report. 

    b)  Create 2 new formula date fields (Report Begin Date and Report End Date) in your main table .  You would use a Formula Query to pull the Begin Date from the Report Dates table into the Report Begin Date field and the End Date into the Report End Date field.

    c)  Create a 3rd formula checkbox field on the main table (Include Record in Summary Report) that checks to see if the Call Date is in the Date Range defined by the dates in the Report Date table.  If the date is within the date range then set the CheckBox  value to True.  If it is not in range then it remains as false.

    d) Update your Summary Report on the main table so that it only include records where that CheckBox formula field (Include Records on Summary Report) is set to True.

    e)   Update your Report Formula Query to also only pull in records where that CheckBox formula field is true.  This would limit the Call Type output so that only Call Types on records within the specified Date Range would be included in the final Summary.

    The Ask The User report filter questions basically act like variable values when running the report.  As there is no way to incorporate those values into your Report Formula Query we must think outside the box to come up with a way to be able to use those values.  By defining these date values in a field in a new table, you can now create a Formula field that uses a formula query to grab that value which then effectively defines that variable for you but also allows you to use that field value in your Report Filter and Report Formula Query.

    Not an ideal solution for running your report but it does solve your issue and allow you to get back the report results that you want when there was really no way to do it before within the confines of the report.



    ------------------------------
    James Grove
    ------------------------------