Sunday, March 24, 2019

Transaction Saved Search to Show Invoice Amount per Year in Columns

1. Navigate to Reports> New Search> click Transaction> click Create Saved Search button.

2. Rename Search Title

3. On the Criteria tab> Standard sub tab > it must contain the following Filter and Description:

a. Type is any of Invoice

b.  Main Line is set to Yes

4. On the Results tab>click Remove All button then mark Show Totals check box

after which add the following Fields :

a. Date

b. Number

c. Name then set Summary Type as Group  ** this is the Customer Name

d. Amount

e. Formula (Currency) then set Summary Type as Sum > on the Formula Column copy and paste below:

case when TO_CHAR({trandate},'YYYY') = '2010' then {amount} else 0 end 

** This is to show 2010 Invoices in one column. It would be best to add Custom Label and Summary Label as "2010"

f. Formula (Currency) then set Summary Type as Sum > on the Formula Column copy and paste below:

case when TO_CHAR({trandate},'YYYY') = '2011' then {amount} else 0 end 

** This is to show 2011 Invoices in one column. It would be best to add Custom Label and Summary Label as "2011"

You can copy the above Formula then just change the Year for succeeding years. ('YYYY')

5. Click Save and Run

 

No comments:

Post a Comment