Need more, need help now? we offer pay-as-you-go support, no contract, tenth of an hour billing, expert help, fast service, no call queue
We can offer lower cost Sage50cloud subscriptions for customer using pay-as-you-go support or who are self-supporting.
Need integration, your shipping system, your website, your invoicing system, your crm.
Need a bespoke solution, Mini Manufacturing, Spreadsheet order import, Back to back orders.
Want web hosting, email, your own domain name, a shop, we do it all!

Report Designer Page Break

Report design issues and solutions.
LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Report Designer Page Break

Post by LisaPD » 25 Nov 2020, 15:50

Hi,

I just wondered if it is possible to add a filter into a report, so it page breaks when something starts with a different letter of the alphabet - this may be a bit too complicated for Sage, but I thought it was worth asking.

So, I am trying to run a Stock Take report, which sorts the productions by STOCK.LOCATION. We have different locations, i.e. A1, A2, A3 etc. B1, B2, B3 and so on. I would like the page break to be so all the locations starting with 'A' stay together, and all the locations starting with 'B' stay together etc. rather than a page break for each and every location. I hope this makes sense.

I look forward to hearing from you.

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

Re: Report Designer Page Break

Post by brucedenney » 25 Nov 2020, 15:57

Yes, you should be able to add a group of the first letter of the location and have a page break on the footer
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 25 Nov 2020, 15:59

How would I do this then?

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 26 Nov 2020, 10:09

LisaPD wrote:
25 Nov 2020, 15:59
How would I do this then?
1. Select the Item Number header on your invoice

Image

2. Go at the top and create a new Group header/footer:

Image

3. Enter the following expression (STOCK.LOCATION is where the location is stored, I’m assuming).
(If you are using web_category for example, replace stock location with that).

https://my.sage.co.uk/public/help/askar ... d=25078#43

Image

4. You will see the following group, we need to move it above, just below INVOICE_NUMBER header. So click that button till it reaches there:
(Might take a few clicks).

Image

5. Now you will see a new Footer, click that.

Image

6. On the properties window on the right, set the following to true:

Image

Save the layout. It should now page break on each new location.

Extra:
You can play with the substring function by increasing its value to change the level of grouping. For example, we have a system of Location like [1] 1 A, [1] 2 A, etc. The “[1]” is repeated for all items in that Bay. So we need to group further as grouping by just “[“will have no page breaks. We need to group all the way till “[1] 1” i.e. 5 characters (includes space). All items on row 1 will be grouped, all on row 2 will be grouped and so on. To achieve this, simply increase the final parameter of the substring function: Substring(STOCK.LOCATION, 0, 5). This will change the level of grouping. If I wanted to only group by Bay number “[1]”, I’d choose 3 characters: Substring(STOCK.LOCATION, 0, 3).

Good luck!

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 27 Nov 2020, 10:43

Hi,

Thank you for your response. I tried this, but unfortunately it doesn't seem to work.

Our location names can go up to 16 characters and can look like this (examples):

'A1' or 'A13-A19-A25-A31' or 'A34-D6/D'
or
'B1' or 'B1/B-B1/BB' or 'B5/B-B5/BB'
Right up to:
'Z1' etc.

I am trying to run a report in 'Products>Stock Take' Reports, so all the locations that start with 'A' stay together (regardless of what comes after it), then a page break, then all the locations that start with 'B' stay together, then a page break etc.

Do you think this is possible?

Kind regards

Lisa

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 27 Nov 2020, 17:31

Well, what I've given should work in theory.

My guess would be that your header and footer are not in the right place.

Does the report run at least?
Do you think this is possible?
Yes, 100%. I've tested it on my own invoice layouts. The logic is also correct - you just group by the first few characters of the locations using the Substring() method. If there any problem, it's due to the implementation/specifics of your report.

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

Re: Report Designer Page Break

Post by brucedenney » 30 Nov 2020, 15:47

sageNoob seems to have done most of the work for you (Well done and thanks).

Where are you having an issue with?

Perhaps you could upload where you have got so far and we can look and see what the issue is.
For just about anything Sage :- switch to subscription, pay-as-you-go support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me. Image

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 03 Dec 2020, 10:39

Hi,

That would be great if you could review what I have done so far - see attachment.

I must be doing something wrong. The report does run, but it doesn't seem to be working correctly with the location page breaks.

I look forward to hearing from you.

Many thanks

Lisa
You do not have the required permissions to view the files attached to this post.

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 03 Dec 2020, 10:45

Try putting the LOCATION HEADER below the Substring() Header.

Similarly put the LOCATION FOOTER above the Substring() Footer.

Does that work?

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 03 Dec 2020, 10:54

No, it is still page breaking at every different 'location'. ie. A10 (page break), A11 (page break) etc. So it's not keeping all the 'A's' together.

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 03 Dec 2020, 11:02

LisaPD wrote:
03 Dec 2020, 10:54
No, it is still page breaking at every different 'location'. ie. A10 (page break), A11 (page break) etc. So it's not keeping all the 'A's' together.
Make a copy of the report and delete the Location Header and Footer. What happens?

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 03 Dec 2020, 11:09

O'h, I might have just done it, by just changing the Substring to '0.1', instead of '0.3'!

Is there a way of getting the locations to run in alphabetical order properly. At the moment, it runs say 'A4, A40, A41, A42, A5, A51' etc., rather that 'A1, A2, A3' etc.

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 03 Dec 2020, 11:14

Please scratch what I said earlier. The Substring() should be under LOCATION HEADER.
Like so:

Image

Then it should be sorted A-Z on it's own.

LisaPD
User
User
Posts: 15
Joined: 19 Oct 2020, 09:51
Sage Version: Other

Re: Report Designer Page Break

Post by LisaPD » 03 Dec 2020, 11:22

It definitely needs the 'Substring' line above the 'location' line on mine, otherwise it doesn't page break properly at all.

If I can't get it to run alphabetically properly, I can live with that. At least it seems to be keeping the groups together and page breaking properly now; so thank you for all your help.

sageNoob
User
User
Posts: 34
Joined: 03 Aug 2020, 12:04
Sage Version: v2015 21 UK/Europe/Africa

Re: Report Designer Page Break

Post by sageNoob » 03 Dec 2020, 11:25

LisaPD wrote:
03 Dec 2020, 11:22
It definitely needs the 'Substring' line above the 'location' line on mine, otherwise it doesn't page break properly at all.

If I can't get it to run alphabetically properly, I can live with that. At least it seems to be keeping the groups together and page breaking properly now; so thank you for all your help.
Well the reason it won't sort is because you are grouping by 1 character (A). This means that sorting is useless because you can't sort a list of A's!

I suppose it makes sense to have the Location below Substring... but that would print the "Stock,Description,..." Header over and over.. I think? Can you show how the report looks?

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest