Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

TOTAL VALUES BASED ON DISPUTED

Report design issues and solutions.
Post Reply
AJFIELD
User
User
Posts: 4
Joined: 09 Feb 2018, 13:22
Sage Version: v2018 24 UK/Europe/Africa

TOTAL VALUES BASED ON DISPUTED

Post by AJFIELD » 09 Feb 2018, 13:36

Hi
I have created a report that we send out to our suppliers showing what invoices are on our system and what is approved for payment and what is in dispute and the dispute code/reason. I am unable to add total boxes to identify the approved value for the month and the disputed value for the month.
Please see example attached. Can anybody advise please how to insert the total values of the invoices for approved and disputed?
Eg. on the report a box that shows approved value = £5,714.97 / disputed value = £11.64

Thanks in advance
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4618
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by brucedenney » 10 Feb 2018, 14:54

You need to add a field for the disputed value, set it to 0 for non disputed transactions and sum it in the footer.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AJFIELD
User
User
Posts: 4
Joined: 09 Feb 2018, 13:22
Sage Version: v2018 24 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by AJFIELD » 12 Feb 2018, 09:59

Hi Bruce
Thanks for taking the time to reply.

I don't understand what you mean by set it to 0 for non disputed transactions?

I have added a variable in the account ref footer (audit.split.disputed) and set the function to sum?

Thanks

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4618
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by brucedenney » 12 Feb 2018, 14:55

Words can be difficult.

You need a value in the details section that you add up in the footer.

The value of this wants to be set to 0 with an "if" construction where the item is not disputed. When you total this you will have the value of disputed items.

If you are stuck post the sage .report file I can use it to show you how you do it.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AJFIELD
User
User
Posts: 4
Joined: 09 Feb 2018, 13:22
Sage Version: v2018 24 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by AJFIELD » 12 Feb 2018, 15:53

Thanks Bruce
I have attached the file as I am not familiar with the if functions or expressions.
Login to access the files attached to this post.

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4618
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by brucedenney » 14 Feb 2018, 15:20

I had a go with your report, but I only have demo data and it doesn't have items of the right age to work with it.

But I can see from it what I think you are trying to do.

I have made an attempt to illustrate what you need to do with a different report a supplier statement just because it is easier for me to work with.

So what I did was to add an expression item to the details section I called it DisputedCount.
DisputedCount.png
The expression it contains is

Code: Select all

AUDIT_SPLIT.DISPUTED=0?0:1
This is an "if" construct, so If the disputed flag is 0 it has the value 0 else it has the value 1

It does not print because the Suppress Print property is set to True

At the bottom of the report in the Account Ref footer I have added another expression, it has the value of DisputedCount and it has the Sum function (so it adds up all the DisputedItem values on all the items) It has the reset Section set as the AccountRef Footer so it starts a new count for each invoice.

-------------------------------------------

You could now go further and add an extra detail section which uses a filter so it only prints for disputed items, that contains the dispute text.

-----------------------------------------

Attached is my final version and a pdf of its output, which has text lines for each disputed item and a total number at the bottom.

SAMPLE PL_STAT4.pdf
SAMPLE PL_STAT4.REPORT
Login to access the files attached to this post.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

AJFIELD
User
User
Posts: 4
Joined: 09 Feb 2018, 13:22
Sage Version: v2018 24 UK/Europe/Africa

Re: TOTAL VALUES BASED ON DISPUTED

Post by AJFIELD » 15 Feb 2018, 13:57

Hi Bruce
Thanks for the time taken.

Much appreciated.

Post Reply

Who is online

Users browsing this forum: No registered users and 9 guests