| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 03 Mar 2011 at 9:52am |
create a formula and use it to group on
totext(
(datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) - ((datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) MOD 5)
,0,'') + ' service years'
|
 IP Logged |
|
bayhr
Newbie
Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
|
Posted: 03 Mar 2011 at 10:04am |
Thnx but is there a way that the grouping will show say 5, 10, 15 years etc...I tried your formula to group it but it only shows 5 yrs. It doesn't give me the option to select say 5, 10, 15 etc...in the groupings. Pls help?
The grouping worked but I want to find out if it's possible to have a select criteria for the years of service to have the option of 5, 10, 15, 20 etc...so that the user can select those from the select criteria which will in turn populate them in the report as groupings. Your help will be great again. Thnx
Edited by bayhr - 03 Mar 2011 at 10:18am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2011 at 10:20am |
are you limiting the data in the select expert?
that formula will convert all the rows to there respective grouping of 5-10-15-20-etc.
|
|
IP Logged |
|
bayhr
Newbie
Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
|
Posted: 03 Mar 2011 at 10:24am |
Yes I'm limited them in the select criteria. However, if I go back to change the criteria to either exclude or include other years, I do not see all the years in the dropdown list in the select criteria
example: I selected 5, 10, 15 years but later wanted to exclude 5,10years and to include 15,20years...I don't see them in the select criteria. I only see the ones that I previously selected (i.e. 5,10,15years) Could you please help
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2011 at 10:29am |
|
how did you create your drop down list?
|
|
IP Logged |
|
bayhr
Newbie
Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
|
Posted: 03 Mar 2011 at 10:35am |
I meant when restricting the criteria like say service years: is one of, equal to etc...it's supposed to give an option to see all the years that I can either include/exclude at anytime I want, right? but like I said earlier as an example if I include 5,10,15 years etc...and later want to exclude say 10, 15 and rather include 20,25 yrs it doesn't show all the years as an option to include/exclude
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 03 Mar 2011 at 10:58am |
ahhh, you are using the select expert menu.
you need to use the paramters on a report refresh in order for end users to select the data.
Did you create a paramater to use?
|
|
IP Logged |
|
bayhr
Newbie
Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
|
Posted: 04 Mar 2011 at 3:00am |
I used the parameters on a report refresh but the end user can only select one option (i.e. 5 years of service) at a time, which is ok but they can't select all options (i.e. to include all years of service) at the same time if they want to include all years of service, right? so I tried using the select expert menu to restrict the criteria like say service years: is one of, equal to etc, which I think it's supposed to give an option to see all the years that I can either include/exclude at anytime I want, right? but like I said earlier as an example if I include 5,10,15 years etc...and later want to exclude say 10, 15 and rather include 20,25 yrs it doesn't show all the years as an option to include/exclude.
1) Could you please assist if it's even possible to use the parameters on a report refresh to select all years of service at the same time
or
2) Is it possible to see all the years of service in the select expert menu so end users can either include/exclude the years of service they want to see on the report?
Please help and again thanks for all your valuable help. It is greatly appreciated!
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Mar 2011 at 3:37am |
create a numeric static parameter with the options you want in the value rows (5/10/15/20/etc.)
In the parameter options set it to allow for multiple values
in your select expert make your select statement use that param with the other formula that gets that 5 year values
{?Param} = (datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) - ((datediff('yyyy',{table.hiredate},currentdate) - if datepart('y',currentdate)<datepart('y',{table.hiredate}) then 1) MOD 5)
Edited by DBlank - 07 Mar 2011 at 4:46am
|
|
IP Logged |
|
bayhr
Newbie
Joined: 02 Mar 2011
Online Status: Offline
Posts: 20
|
Posted: 07 Mar 2011 at 4:44am |
|
You're really the best of the best :):) Thanks a zillion times for all your valuable help
|
|
IP Logged |
|
|
|
