Report Designer Page Break
Report Designer Page Break
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.
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.
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Report Designer Page Break
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 :- 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.
Re: Report Designer Page Break
How would I do this then?
Re: Report Designer Page Break
1. Select the Item Number header on your invoice
2. Go at the top and create a new Group header/footer:
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
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).
5. Now you will see a new Footer, click that.
6. On the properties window on the right, set the following to true:
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
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
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
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?
My guess would be that your header and footer are not in the right place.
Does the report run at least?
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.Do you think this is possible?
- brucedenney
- Site Admin
- Posts: 4631
- Joined: 28 Feb 2006, 09:56
- Sage Version: v28 UK/Europe/Africa
Re: Report Designer Page Break
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.
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 :- 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.
Re: Report Designer Page Break
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
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
Login to access the files attached to this post.
Re: Report Designer Page Break
Try putting the LOCATION HEADER below the Substring() Header.
Similarly put the LOCATION FOOTER above the Substring() Footer.
Does that work?
Similarly put the LOCATION FOOTER above the Substring() Footer.
Does that work?
Re: Report Designer Page Break
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
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.
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
Please scratch what I said earlier. The Substring() should be under LOCATION HEADER.
Like so:
Then it should be sorted A-Z on it's own.
Like so:
Then it should be sorted A-Z on it's own.
Re: Report Designer Page Break
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.
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
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!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.
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?
Who is online
Users browsing this forum: No registered users and 3 guests