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

Filter help: date expression

Report design issues and solutions.
Post Reply
gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Filter help: date expression

Post by gazmoz17 » 28 Oct 2019, 15:51

Hi,

I have this filter: SALES_ORDER.DESPATCH_DATE = REPORT.DATE

This report shows me sales orders that need to be despatched today...today being current date e.g. report date.

How do I write this:
SALES_ORDER.DESPATCH_DATE = REPORT.DATE + 1 day.

Idea is run this report at end of current day and it shows me sales orders that need to be despatched tomorrow?

Many Thanks
Gareth

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

Re: Filter help: date expression

Post by brucedenney » 28 Oct 2019, 19:04

You can't do a filter with an expression, you have to use a field on the report.

So add a field with the expression REPORT.DATE + 1 give is a name TARGET_DATE
You may not want it to print so turn on the suppress print attribute.

Then the filter SALES_ORDER.DESPATCH_DATE = TARGET_DATE should work.

If you fancy being extra clever...

How about adding a criteria to the report and adding the value given at run time, so you can run for any number of days ahead, eg on Friday you could run the report for Monday. Or just add a criteria for the DESPATCH_DATE and run it for any date you like.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter help: date expression

Post by gazmoz17 » 11 Nov 2019, 15:57

Thats great thanks Bruce huge help. I did what you said with the target date. Yes I added a seperate report where the despatch date is a criteria to select any outbound jobs for any date specified.

But I dont understand the bit below sorry?:

How about adding a criteria to the report and adding the value given at run time, so you can run for any number of days ahead, eg on Friday you could run the report for Monday.

Many Thanks
Gareth

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

Re: Filter help: date expression

Post by brucedenney » 11 Nov 2019, 16:19

What I am suggesting is that rather than make it a fixed number of days relative to today, you make it a user definable number of day by passing the number of days to the report in a variable.
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter help: date expression

Post by gazmoz17 » 11 Nov 2019, 16:29

Hi, thanks for quick response. So you would specify the number of days, like show all orders (despatch date) in next 6 days and it would pull them through. Would you enter the number of days in a criteria box?

Sorry if this isnt what you mean.

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

Re: Filter help: date expression

Post by brucedenney » 12 Nov 2019, 10:42

You could do it either way, you could specify a date or a number of days.

Which would you prefer to give a date or to set a number of days?
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter help: date expression

Post by gazmoz17 » 12 Nov 2019, 11:44

Hi Bruce, thanks for reply. Both are useful, probably number of days would be better. Is there a way I can edit the tomorrow one so that if I run it on a friday it shows outbound jobs for Monday. Can I change my TARGET_DATE to REPORT DATE + 1 working day?

Many Thanks

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

Re: Filter help: date expression

Post by brucedenney » 12 Nov 2019, 15:06

Sort of... What you can do is use a criteria and a prompt when you run the report

Add a criteria such as the SALES_ORDER_CONTACT_NAME and change the prompt to "How many days in advance?"

Choose the preset option and set it to "Is Not Equal" to 1

Then edit it again and set it to Enabled. This sounds a bit strange, but by setting it as a preset first it will now prompt you with the "Is not equal to" and the Number "1" when you run it, which will be the default position. Obviously if you had a contact name of "1" this could cause problems, but you wouldn't would you so it is not an issue, all orders will pass the criteria and we can use that value to do whatever we want.

Now when you run the report you have a criteria field you can access CRITERIA.SALES_ORDER_CONTACT_NAME_FROM and it will contain the number you type in.

The only thing you need to deal with is the conversion of the string to a number using a function when you do the maths. StringtoInteger or Stringtofloat would work I think.

e.g.

SALES_ORDER.DESPATCH_DATE = REPORT.DATE + StringToInteger(CRITERIA.SALES_ORDER_CONTACT_NAME_FROM)
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.

gazmoz17
User
User
Posts: 108
Joined: 19 Sep 2018, 13:58
Sage Version: v2018 24 UK/Europe/Africa

Re: Filter help: date expression

Post by gazmoz17 » 12 Nov 2019, 19:37

Cool I’ll have a go tomorrow when I’m back at work and have sage access 👍.

Post Reply

Who is online

Users browsing this forum: No registered users and 14 guests