Forum post
I have attached a screen capture of a report. I have to report on our membership statistics every month.
A member can either put in his 1. Put in his petition 2. Have his EA (1st) degree 3. FC (2nd) degree 4. MM (3rd) Degree 5 Change his membership to another Lodge (Affiliate) or 6. be removed (Death, Withdrawl etc)
I have a field called Status where 1 of those steps is entered in the Database, then I run a report at the end of the month sorted by Status.
What I would love to do is just have the report show only the date that is associated with the relevant Staus. Right now I have to have a column for each Status category.
To clarify, on the attached photo I have circled the Category and category column that relates. Ideally that would be all I would see.
Any help is appreciated
Answer
You could use a calculation field with Case to return the data appropriate to the specified Status:
Case ( Status = "Waiting" ; Petition ;
Status = "EA" ; EA ;
Status = "MM" ; MM ;
Status = "Removed" ; Removed )
Select date as your Return type and you can reduce this report down to a status field and this calculation field.
Be the first to like this
|
Sign in to "Like"
Is there also a way to set a label to either print or not print based upon a calculation
Be the first to like this
|
Sign in to "Like"
Is this a new question or a continuation of the first? (I don't see the connection here.)
You can use either a calculation field that is empty when it shouldn't print as your "label" or you can give it a conditional format with the calculation as its expression.
Be the first to like this
|
Sign in to "Like"
Yes continuatuation. Man I forgot about that conditional formatting. and I have used that often. Darn.
Be the first to like this
|
Sign in to "Like"
The best way to make layout text invisible via a conditional format is to set the font size to 500.
Be the first to like this
|
Sign in to "Like"
OK last "Related" question. I hope. on my report, Waiting, EA and MM are all on the plus side, but Removed is a subtraction, can I calculate that difference.
Be the first to like this
|
Sign in to "Like"
Text to 500. NIFTY!
I work in wide format printing I set type in 500 pts all the time. That's chump change.
Be the first to like this
|
Sign in to "Like"
OK last "Related" question. I hope. on my report, Waiting, EA and MM are all on the plus side, but Removed is a subtraction, can I calculate that difference.
Subtracted from what?
If you are using a summary field to count up all records minus those with status: Removed, you can set up a similar field to the first calculation:
Case ( Status = "Waiting" ; 1 ;
Status = "EA" ; 1 ;
Status = "MM" ; 1 ;
Status = "Removed" ; 0 )
A summary field computing the total fo this calculation field would then give you that total. (And for those following along at home, there are several variations of this combination of calc field and summary field that will produce the same result.)
Be the first to like this
|
Sign in to "Like"
The total of the plus side minus the total of the subtractions. So at the end of the month we could say we had a net gain or net loss in membership
Be the first to like this
|
Sign in to "Like"
I suppose
Status = "Removed"; -1 could it be that simple. Have to try that.
Be the first to like this
|
Sign in to "Like"