Hey guys, you have been very helpful since I joined the other week and I hope you can help me with a small problem I am having. I have created a report with a Chart that displays the Average Time to Complete Work Orders by Month. In the Select Expert I have plugged in the following formula:

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
IsNull({TASKS.TYPE}) or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI Request" or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI Production Requests" or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI QA Team"


I've done this to exclude the BI work order types and to make sure nulls are included. Also, in the Chart Expert > Data I have the Chart to display On change of @dateCompleted (parameter) and by TASKS.TYPE in specified order (where TASKS.TYPE <> BI Pro Request OR BI Request OR BI QA Team).

I have set the report up to allow drill down for each month, which displays each work order individually, the time it was opened, time it was completed, and the total hours it took. In the Group Footer it shows COUNT of Work Orders, SUM of ElapsedHours (formula defined as: CF_DURATION ({@dtOpenDate},{@dtCompleted}, "h")), and also in the Group Footer is AverageHours (formula defined as: Round(Average ({@ElapsedHours}, {@dtCompleted}, "monthly"),2)

So the problem arises when drilling down to validate the data. For example, in May there is only one work order by B.I. which took 19.73 hours. When drilling down, this particular record will not display because of the Select Expert code above. But the totals in the group footer are unchanged either way! Any help or advice would be very much appreciated.


Edited by little wing - 23 Dec 2011 at 2:50am

 try this
{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{@dtCompleted} = {?Completed_Date_ Range} and
{TASKS.TYPE} <> ["BI QA Team", "BI Request",  "BI Production Requests"]

You guys are great. I actually got it to work with the following:

{@dtCompleted} = {?Completed_Date_ Range} and
IsNull({TASKS.PRIORITY})and
IsNull({TASKS.TYPE}) or

{@dtCompleted} = {?Completed_Date_ Range} and
IsNull({TASKS.PRIORITY}) and
not ({TASKS.TYPE} in ["BI Request", "BI Production Requests", "BI QA Team"]) or


{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
IsNull({TASKS.TYPE}) or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI Request" or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI Production Requests" or

{@dtCompleted} = {?Completed_Date_ Range} and
not ({TASKS.PRIORITY} in ["Documentation only", "Project"]) and
{TASKS.TYPE} <> "BI QA Team"

Thanks guys, have a happy holiday, if you're into that type of thing