|
I have a report that has the following selection formula to only pull records that have work orders submitted between 5:00 and 5:30.
Selection Formula:
{@Open Date} = {?Request Date Range}
and
datepart('w',{@Open Date}) in 2 to 6
and
time({@Open Date}) in time(17,0,0) to time(17,30,0)
However, in order to calculate an accurate average per day, I need to include the days that had 0 work orders between 5:00 and 5:30. I calculate the number of days as follows to be used in the average formula.
date({TASKS.OPENDATE})
Unfortunately this only counts the number of days that had work orders between 5:00 and 5:30 because of the selection formula. I need to know the number of weekdays that there were work orders submitted in general. Is there something I can type in the Number of Days formula to ignore the selection formula when calculating that number and add a separate selection formula within the Number of Days formula? I would want to pull the number of days from the requested date parameter and M-F only and NOT the 5:00-5:30 selection.
|
|
{@Open Date} = {?Request Date Range}
and
datepart('w',{@Open Date}) in 2 to 6
//and
//time({@Open Date}) in time(17,0,0) to time(17,30,0)
this should get you all the weekdays.
you can then put logic to suppress and calculate what you want. It will probably be tricky to calculate what to display...and remember that all the aggregate function work on all the data, so avg() will give the average of all the data whether or not you displayed it.
shared variables in the formulas would probably be your best bet for calculations as you can control what values you choose to include or exclude from the calculations.
HTH
|
IP Logged
