Report Builder Filter Problem: Can I filter a report after group by?

  • I need to build a report to display staff weekly working hours which larger than 50. For each total working hours larger than 50 I need to display the related staff information.

    So In SQL-Statement I need to select all working hours > 50's staff by using group by. In addition I will select employee information by using all selected staff.

    I have tried to add a Filter condition in the Report Builder and it will only filter out a staff's daily working hours > 50. The Hours field's isAggreate property already set to True. I am not sure whether RB provides that kind of functionality, please advise. Thanks very much.

  • When you build your Report Model, build this field with >50 value, so it'll be a part of it.

    then, when you make your ad-hoc report off this model, it'll be just a drop-down item :-).

    is it what you need?

    thanks.

  • another possible solution:

    Create a secondary report that displays the 'staff details' e.g. name, address, phone, whatever

    Then, on the main report, turn the name of each staff member displayed on the main report into a hyperlink - passing the staff member ID as a parameter

    When one of the staff names is clicked, the secondary report will be called and display the staff details

    I have done this on several different reports at my place of work 🙂

  • Hi maya

    Thanks for the reply.

    Can you provide me more information on how to create a field that will filter out weekly working hours > 50 in the Report Model Designer (The underlying transaction contains daily working hours informaton)? Is is possible to do it in the Report Designer (Filter session)? Thanks.

  • sure, something like this would work:

    case when loan_balance > 50 then '> 50'

    case when loan_balance <=50 then'<=50'

    else 'who cares!'

    end as balance_range

    then, you'll filter by that column, 'balance_range' :-), as you'd filter by any other parameters.

  • maya potiyevskaya (2/11/2008)


    sure, something like this would work:

    case when loan_balance > 50 then '> 50'

    case when loan_balance <=50 then'<=50'

    else 'who cares!'

    end as balance_range

    then, you'll filter by that column, 'balance_range' :-), as you'd filter by any other parameters.

    OK Thanks.

    Let me try to explain your method and see if I am wrong or not.

    So your method seems need to build a group by statement in the Report Model and add a virtual column (e.g. balance_range) that contains a no. of hours flag (lets say > 50, <=50 ) to a table. Later, use the filter option to select the balance_range column accordingly within the Report Designer.

    It seems work. But my point is can i do the group-by solely in the Report Designer by using the Filter option? So I dont need to hard-code the no. of hours in the Report Model and I can let the end users to design the value they want.

  • you can do GROUP BY on filtered records, you just won't have all groups :-), right?

    let's say, you have 3 ranges. try to to group by first. then, try to apply a filter on a top of it and you'll see you'll only have the filtered records.

    hope this helps.

  • RB

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply