February 4, 2008 at 8:34 pm
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.
February 7, 2008 at 1:04 pm
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.
February 10, 2008 at 5:31 pm
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 🙂
February 10, 2008 at 8:40 pm
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.
February 11, 2008 at 8:58 am
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.
February 12, 2008 at 12:06 am
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.
February 12, 2008 at 8:34 am
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.
May 15, 2009 at 10:30 am
RB
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply