| :: Filter and Group By issue :: (1) | Post a New Message | Post a Reply |
 Filter and Group By issue | Post a Reply |
since: Sep 11, 2007
from: Roosendaal, The Netherlands
select group_id, course_id, count(*) as completers from view_ltCompletion
where dept='a value'
group by group_id, course_id
order by group_id, course_id
The where clause should be a user filter. The user may select other fields as part of the filter. The fields in the filter may or may not be in the original field list.
When I set up the report using the Wizard, I am able to select the two display fields. I then select Summary report and use Record Count. All this seems fine. When I click Finish I see all records. When I then want to use a filter, I am unable to select "dept" or any other field not in the field list as the field to filter on. When I make that field part of the field list, it will be used in the group by and may not give me the result I want. Even when I hide the field, it seems that it is used in the list like this:
select group_id, course_id, dept, count(*) as completers from view_ltCompletion
where dept='a value'
group by group_id, course_id, dept
order by group_id, course_id, dept
I hope this makes sense. Any ideas?
Thanks - Peter