Page 1 of 2

Report Designer Page Break

Posted: 25 Nov 2020, 15:50
by LisaPD
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.

Re: Report Designer Page Break

Posted: 25 Nov 2020, 15:57
by brucedenney
Yes, you should be able to add a group of the first letter of the location and have a page break on the footer

Re: Report Designer Page Break

Posted: 25 Nov 2020, 15:59
by LisaPD
How would I do this then?

Re: Report Designer Page Break

Posted: 26 Nov 2020, 10:09
by sageNoob
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!

Re: Report Designer Page Break

Posted: 27 Nov 2020, 10:43
by LisaPD
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

Re: Report Designer Page Break

Posted: 27 Nov 2020, 17:31
by sageNoob
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.

Re: Report Designer Page Break

Posted: 30 Nov 2020, 15:47
by brucedenney
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.

Re: Report Designer Page Break

Posted: 03 Dec 2020, 10:39
by LisaPD
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

Re: Report Designer Page Break

Posted: 03 Dec 2020, 10:45
by sageNoob
Try putting the LOCATION HEADER below the Substring() Header.

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

Does that work?

Re: Report Designer Page Break

Posted: 03 Dec 2020, 10:54
by LisaPD
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.

Re: Report Designer Page Break

Posted: 03 Dec 2020, 11:02
by sageNoob
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?

Re: Report Designer Page Break

Posted: 03 Dec 2020, 11:09
by LisaPD
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.

Re: Report Designer Page Break

Posted: 03 Dec 2020, 11:14
by sageNoob
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.

Re: Report Designer Page Break

Posted: 03 Dec 2020, 11:22
by LisaPD
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.

Re: Report Designer Page Break

Posted: 03 Dec 2020, 11:25
by sageNoob
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?