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

Dynamic field positioning.

Report design issues and solutions.
Post Reply
ian332isport
User
User
Posts: 12
Joined: 12 Jul 2016, 15:59
Sage Version: v2016 22 UK/Europe/Africa

Dynamic field positioning.

Post by ian332isport » 19 Aug 2020, 13:36

Hi,

Is there a way to have the position of a data field move dynamically based on the size of the adjacent field?

I've got a currency symbol next to an invoice value. This looks fine when the invoice value is left justified, but not if right justified. I'd like the position of the currency symbol to move depending on the length of the invoice value.

Failing that, is it possible to get the report to automatically add the correct currency symbol to values? I've tried selecting 'Show as currency' in the numeric formatting section, but it always seems to show a UK pound sign (£). I can obviously force this to a specific currency symbol, but it then won't change with the currency.

Thanks,
Ian.

ian332isport
User
User
Posts: 12
Joined: 12 Jul 2016, 15:59
Sage Version: v2016 22 UK/Europe/Africa

Re: Dynamic field positioning.

Post by ian332isport » 19 Aug 2020, 15:44

Hi Bruce,

That's fine with fixed currency, but I'm trying to have a single report that works with multi currency.

If I use custom currency formatting, it fixes the report to a single currency symbol. If I just select 'Show as currency', with no custom formatting, it shows a currency symbol exactly where I want it, but it's always a UK pound sign (£). It doesn't show the symbol of the report currency. I assume this is a bug in the report designer, or the 'Show as currency' option is of little use.

Thanks,
Ian.

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

Re: Dynamic field positioning.

Post by brucedenney » 19 Aug 2020, 15:52

You can do this with the FormatString function, e.g.

FormatString("{0} {1:F}",CURRENCY.SYMBOL,SALES_LEDGER.BALANCE )

I have left a space between the Symbol and the first digit of the number.

FormatString is not properly documented.

It appears to follow the Microsoft String.Format method

Here is the detailed blurb from Microsoft.

https://docs.microsoft.com/en-us/dotnet ... 1#Starting
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.

ian332isport
User
User
Posts: 12
Joined: 12 Jul 2016, 15:59
Sage Version: v2016 22 UK/Europe/Africa

Re: Dynamic field positioning.

Post by ian332isport » 21 Aug 2020, 08:10

Hi Bruce,

Thanks very much. That did the job nicely.

Just to expand on this slightly. I did have an issue where I used this FormatString on a statement, and got the following error.

The report could not be generated.
No matching overload for function
'Sage.Expressions.DefaultLibraries.Aggregates.Sum' exists.'


Googling this led me here:

https://my.sage.co.uk/public/help/askar ... leid=19077

Which states:

To resolve this you should edit the report or layout and ensure that any variables or expressions that show text are set to Signed and have a function of None.

On the statement, my fields had both a function of 'Sum' and a sign of 'Debit' or 'Credit', so this was my problem. I had to modify the FormatString to include the function and sign. On the report properties I set the function to 'None' and the sign to 'Signed'. This is what my final FormatString looks like.

FormatString("{0} {1:F}",CURRENCY.SYMBOL,Debit(sum(AUDIT_SPLIT.FOREIGN_AGED_BALANCE)))

All working now though.

Thanks again,
Ian.

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

Re: Dynamic field positioning.

Post by brucedenney » 21 Aug 2020, 09:38

The other way you could have resolved that would be to leave the original expression intact with the sum, but suppress it from printing, then in your new expression function reference the original expression.
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.

Post Reply

Who is online

Users browsing this forum: Ahrefs [Bot] and 13 guests