Filtering Time For File Numbers

[yokboylebirsey]

Hello Gurus,

My filter is "([time]<240) AND ([time]>0)" and it works perfectly but when I remove [file number] on the list report, total durations change. However, I need to filter [time] according to [file number] without [file number] on my list.

First report:

Department Staff   File No.   Time

After i remove [file number] it shoud be like this:

Department   Staff   Time

I try "([time]<240) AND ([time]>0) in/for [file number]" but it didn't work.

[jeff_rob]

Hi yokboylebirsey,
Could you give us an example of how [file number] would need to be filtered?

If it's simply an AND clause then file number could be in a separate filter.  For example:

Filter 1 - [time] between 0 and 240
Filter 2 - [file number] between 'xxxx' and 'yyyy'

You said that the total durations change when you remove [file number] from the list.  Are you grouping by [file number]? If so, that will certainly change the totals.  If you need to group by [file number], but don't want it to show, you can hide the column.

JR

[yokboylebirsey]

Hi jeff_rob,

First of all, thanks for your reply. As you said I should give an example;

At the beginning my list report is like this :


Then i give filter on [time] from 0 hours to 240 hours. But i want to show staff's total time without file number item. Thats why i remove [File number] item. After i remove it, the filter get confused so it starts to filter considering staff.

On the example, after the [file number] item deleted, Aaaa Bbbb staff's time should be 101 h and only 1 row. It should ignore the file number is 13-0020.

[jeff_rob]

Hi yokboylebirsey,
You mentioned earlier that you need to filter by file number, I still don't understand what needs to happen with that.

If you just need a total of the hours by staff within department regardless of file number, you would just need to group by department and staff and delete (not cut) file number.  Deleting it will remove it from the query as well as the list.  Cut simply removes it from the list leaving it to still affect the query results.

This should give you something like:

Department     Staff             Time
Automotive      Aaaa Bbbb    101
                      Cccc Dddd     137
Medical           Eeee Ffff           8
                      Rrrr Tttt           -1
Food               Yyyy Uuuu      66
                      Hhhh Oooo     50

Is that what you're after?

JR

[yokboylebirsey]

Hello jeff_rob,
Sorry for bad manner of telling. Yes did understand right. I want to still filter by file number however I don't want to show it on list report. All staff name should be in one row.

If a staff have files -20, 50, 100, 200 and 300 h, the report should be ignored first and last one. After I filter the report by ([time]<240) AND ([time]>0) and remove [file number] data item, the staff won't be shown on the list because his/her total time will be higher than 240 ((-20)+50+100+200+300=630). As I said that I want to filter by file number but don't want to show it on the list and that staff's time should be 50+100+200=350 without shown file number.

[jeff_rob]

Are you using a detail filter for time or a summary filter?  It should be a detail filter, which will only affect the row level data.

If you're using a detail filter and still seeing this behavior, try turning off the Auto Group & Summarize function at the query properties.

JR

[yokboylebirsey]

I used both of them.
However I didn't group the list by staff. After I did, I had the result what I want.
Thanks a lot jeff_rob.

[jeff_rob]

Great!  Thanks for letting me know.