August 22, 2015 at 8:10 pm
serviceaellis (8/22/2015)
Thank you very much!Data seems to be resulting the way it's expected on the reporting side with an edit in the Report | Select Expert on Record.
{v060ClubOfficersPresidentMbrCounts.YearEnd} in {?Enter Season Year} to {?Enter Season Year}+4
The end-user needs to see for the year they are wanting to get the total memberships for.
Formula on YearEnd to show between entered Year and Year+4. So that the totals will show for those membershiptypeid = 3. Since they are 4 year memberships. If entered 2016, the total from 2016-2019 is required to report to show "current" memberships. Example clubno 22.
Total from 2015-2019 is 82. Total from 2016-2019 is 42. and son.
Without the YearEnd prompt they'd just see 82 until Jan 2016. Which isn't really correct for what they are looking for.
Oh and I had to add back this to the Join to the updated View. Somewhere it got left out. There are several membership types and need to filter on these only. In the sample data set, these were the only ones in it 🙂
AND P.MembershipTypeId IN (1,2,3,4)
Thanks again for your patience and working with me to the very end. Hopefully nothing comes up. Whew!
Glad you have what you are looking for. One question remains. How are you deciding which years appear in the parameter list? I ask because if the user selects 2016, that's not going to suddenly display any memberships that are for 2020. The view ONLY looks at current year's 6/30 expiration and the next four 6/30 expirations, so 2020 data won't be able to come from this view until January 1st, 2016. If a selection of 2016 requires 2020 data, then the view would be better as a stored procedure that uses the parameter from Crystal, and then you won't need a Record Selection Formula, as the stored procedure will do all the work. The changes needed are trivial. Let me know.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 22, 2015 at 9:09 pm
Glad you have what you are looking for. One question remains. How are you deciding which years appear in the parameter list? I ask because if the user selects 2016, that's not going to suddenly display any memberships that are for 2020. The view ONLY looks at current year's 6/30 expiration and the next four 6/30 expirations, so 2020 data won't be able to come from this view until January 1st, 2016. If a selection of 2016 requires 2020 data, then the view would be better as a stored procedure that uses the parameter from Crystal, and then you won't need a Record Selection Formula, as the stored procedure will do all the work. The changes needed are trivial. Let me know.
It needs to be limited to 4 years out from I understand.
Can there be membership further out, possibly.
There is one thing that that arose with some more verifying data.
Since all memberships type IN 1,2,4 are or should be only 1 year and membership type 3 is always 4 years, they originally had this in the Report Selection formula:
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} in [1, 2, 4] AND
{v060ClubOfficersPresidentMbrCounts.YearEnd} = {?Enter Season Year} OR
{v060ClubOfficersPresidentMbrCounts.YearEnd} in {?Enter Season Year} to {?Enter Season Year}+3 AND
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} = 3
Because of the membership types are in the record selection formula none of the NULL Clubs show.
Why I removed it and only left the in {?Enter Season Year} to {?Enter Season Year}+3
the grand total changed, though by just 1 count. Found a record with type 1 and a 2017 ending date. Being off by 1 is insignificant however I'm concerned if that increases at some point that criteria might need to be able to handle that like in the original selection formula shown.
August 23, 2015 at 2:23 pm
serviceaellis (8/22/2015)
Glad you have what you are looking for. One question remains. How are you deciding which years appear in the parameter list? I ask because if the user selects 2016, that's not going to suddenly display any memberships that are for 2020. The view ONLY looks at current year's 6/30 expiration and the next four 6/30 expirations, so 2020 data won't be able to come from this view until January 1st, 2016. If a selection of 2016 requires 2020 data, then the view would be better as a stored procedure that uses the parameter from Crystal, and then you won't need a Record Selection Formula, as the stored procedure will do all the work. The changes needed are trivial. Let me know.
It needs to be limited to 4 years out from I understand.
Can there be membership further out, possibly.
There is one thing that that arose with some more verifying data.
Since all memberships type IN 1,2,4 are or should be only 1 year and membership type 3 is always 4 years, they originally had this in the Report Selection formula:
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} in [1, 2, 4] AND
{v060ClubOfficersPresidentMbrCounts.YearEnd} = {?Enter Season Year} OR
{v060ClubOfficersPresidentMbrCounts.YearEnd} in {?Enter Season Year} to {?Enter Season Year}+3 AND
{v060ClubOfficersPresidentMbrCounts.MembershipTypeId} = 3
Because of the membership types are in the record selection formula none of the NULL Clubs show.
Why I removed it and only left the
in {?Enter Season Year} to {?Enter Season Year}+3the grand total changed, though by just 1 count. Found a record with type 1 and a 2017 ending date. Being off by 1 is insignificant however I'm concerned if that increases at some point that criteria might need to be able to handle that like in the original selection formula shown.
That's why it's best to use SQL to solve these problems and try to avoid using Crystal to do so. If you have to restrict the membership types, then just change the CTE that is titled Memberships to include the following at the end of the INNER JOIN's ON clause:
AND P.MembershipTypeId IN (1,2,3,4)
As to your date troubles, if you are going to use Crystal to do your record selection from the view, just be aware that the records returned from the view will be memberships expiring on June 30th of the current year, or June 30th of each of the following 4 years, for a total window of 5 distinct years. Thus in 2015, you will be able to see data from 2015 through 2019. Come January 1st of 2016, you can no longer see 2015 memberships, and will then see data from 2016 through 2020. The ONLY way to get more flexibility is to let the user specified parameter control the first year of the window, in which case you will have no choice but to eliminate the year restrictions in the view completely, or, to go to a stored procedure.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 23, 2015 at 3:18 pm
Yes, I added the MembershipTypeID IN (1,2,3,4) in the last updated View you provided.
I think, aside from that one lone record ending in 2017 being a member type 1, I think it's ok.
Humh, I guess that is why J Livingston SQL was asking what is expected come Jan 2016.
I'm thinking that's fine that 2015 data will no longer be available since half into the new season (in this case 2016) is already over.
Come June, that is when they'll want to see 2017-2020 with the option to check on 2016 numbers still.
So I think it's ok for now.
Thanks again for all your help! Your patience with me is greatly appreciated, having stuck through until resolved!
Viewing 4 posts - 91 through 93 (of 93 total)
You must be logged in to reply to this topic. Login to reply