If statement in accounting report

Postby beckyc » Thu Jul 20, 2017 11:19 am

I'm trying to create a report that lists amounts grouped by category.

Is there some way to write in an if statement that would say If category=I then write INCOME? An then the text box would say Income and not just show the I.

Thanks,
Becky Coleman
beckyc
 
Posts: 18
Joined: Mon Jul 29, 2013 5:09 pm

Re: If statement in accounting report

Postby NeilZ » Thu Jul 20, 2017 11:56 am

beckyc wrote:I'm trying to create a report that lists amounts grouped by category.

Is there some way to write in an if statement that would say If category=I then write INCOME? An then the text box would say Income and not just show the I.

Thanks,
Becky Coleman


I believe the IIF logical operator would work in this case, you would need to edit the report and double click on the label to use it. This knowledgebase article has a report that uses the IIF: https://www.powerchurch.com/support/224 ... unt-report

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 8905
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: If statement in accounting report

Postby beckyc » Fri Jul 21, 2017 9:00 am

Thanks - that worked.

To document - here is what I did.

1. Go to REPORT/VARIABLES
2. Add a new variable - m.categorytitle
3. in the Value to Store: IIF(Qmf.temp.category="E","EXPENSES",m.categorytitle)
note: using the expression builder to pick out the logical expression and the fields will help to keep your wording accurate
4. in the Initial value: "INCOME"
note: I have it sorted to that income will come first. I might have been able to write an expression with an OR but this worked for me
5. choose ok to save your new variable
6. back to the report form - in the group footer band, I added a field containing: m.categorytitle
so at the bottom of the grouping I have a text field saying "TOTAL FOR:" and then a data box
which will fill in with Income or Expenses - whichever is appropriate for that grouping


I also added a running sum using the variables m.Isum and m.Esum
1. Go to REPORT/VARIABLES
2. Add a new variable - m.Isum (I did the same with m.Esum)
3. in the Value to Store: IIF(Qmf.temp.category="I",m.Isum+Qmf.temp.annualbud,m.Isum)
4. Initial value = 0
5. then back to the report form - I added a field in the detail band containing m.Isum
6. I previewed the report to make sure it was summing correctly and then made the text white so
that it was not visible on the printed report
7. Then in the page footer, I added a field box with m.Isum-m.Esum to give me my final total.
(I needed to do this since both numbers are positive and when I used the regular list of reports then I would
get a huge number when in reality I wanted to show the difference of these numbers.)
beckyc
 
Posts: 18
Joined: Mon Jul 29, 2013 5:09 pm