Alpha Video Training
Results 1 to 12 of 12

Thread: Report Help

  1. #1
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Report Help

    Hi Everyone

    I'm lost (again...)

    I have a calculated field on a report based on a MySql table.

    I'm using sql_lookup in the field. In the expression builder the result works but in the report it only shows 8 characters.

    Ie: The field in the table has 12345678910 (which is displayed in the expression builder) but on the report it displays as: 12345678

    For the life of me I can't find where to change the display length properties of the field on the report as with normal fields.

    Can someone point me in the right direction.

    Regards,

  2. #2
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Update:

    Fixed but I'm not sure if this is the right way

    Copied the display properties from another field and applied it to the calculated field

    Can someone tell me if there is another way to change the display properties of a calculated field.


    Regards,

  3. #3
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,267

    Default Re: Report Help

    Field
    ..properties
    ....format
    .....field length - about the middle of the format tab
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  4. #4
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Hi Ted

    Thanks for your reply

    Yeah I know that's where it's supposed to be be but this is what it looks like

    FormatTab.png

  5. #5
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,267

    Default Re: Report Help

    And it's not in one of the other tabs?
    If I recall correctly, a Calculated field has mixed up settings.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  6. #6
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Hi Ted

    No it's not. I've looked everywhere and can't find where to change the display width.
    I have other cal fields on the report which show the display width in the properties but not this one.
    Does it have to do with the sql_lookup used in the cal expression? I've never used it before so just asking.

    Strange how it shows correctly in the expression builder but not on the report.

  7. #7
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,267

    Default Re: Report Help

    Apologies Keith, I missed your last post.
    I will try and recreate the situation on my box.

    Edit.
    In your MySql table, set the value of the numeric field to BIGINT.
    This will give you 20 places to play with.
    You cannot change the table but you can look at the new field length in the Rules screen.
    Refresh the table definition in Alpha, then look at the field options again.
    When you test it, the field characteristics are not always re-set.
    Try entering a new long number - up to N 20.
    The Report should now take the long number.

    It is all to do with INT and BIGINT

    Hth.
    T
    Last edited by Ted Giles; 04-25-2019 at 12:40 PM.
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  8. #8
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Hi Ted

    Sorry for the delayed reply

    Thx for the input but I'm dealing with character data in the MYSQL field, varchar(80)

    Seems strange that when I use the sql_lookup function in a calculated field the display length isn't available in the format tab.

    FormatTab.png

    I solved the issue by coping the display width from another field and applying it to this field but I'd like to know if I did something wrong

    Regards,

  9. #9
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,267

    Default Re: Report Help

    Ok, so what does the code look like which populates the lookup character calculated field?
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  10. #10
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Here's the calc expression:

    CalInvprime = SQLDATASOURCE->invprime
    CalDesc = sql_lookup("::Name::llh","masterinv","invprime="+calc->CalInvprime,"[desc]")

    invprime is INT(11) in the MYSQL table

    In the expression builder the CalDesc result shows correctly but when on the report is only shows 8 characters.

  11. #11
    "Certified" Alphaholic Ted Giles's Avatar
    Real Name
    Ted Giles
    Join Date
    Aug 2000
    Location
    In the Wolds, Louth, Lincolnshire, UK
    Posts
    4,267

    Default Re: Report Help

    Nope, cannot get it to fail using BIGINT as the invprime placeholder.

    This is the usual construction of the lookup;

    sql_lookup(connection,table,filter,result_expression,args)

    From the Help Section
    'In this example we pass in an open connection.
    'Since the function will not have to open and then close the connection, it is significantly faster
    dim cn as sql::connection
    cn.open("::Name::Northwind")
    table = "customers"
    result_expression = "concatenate(city,' - ',contactname)"
    filter = "customerid = 'bolid'"
    ?sql_lookup(cn,table,filter,result_expression)
    = "Madrid - Mart
    Ted Giles
    Example Consulting - UK
    .

    http://ec12.example-software.com//
    See our site for Alpha Support, Conversion and Upgrade.

  12. #12
    Member
    Real Name
    Keith Hart
    Join Date
    Aug 2010
    Location
    Calgary, AB
    Posts
    116

    Default Re: Report Help

    Hi Ted,

    Thx for the reply.

    I'll try changing the MYSQL field to BIGINT.

    Regards,

Similar Threads

  1. Replies: 0
    Last Post: 06-21-2017, 10:43 AM
  2. report.print() report.saveas() syntax for Project Report
    By rnoguera in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 05-04-2015, 10:51 AM
  3. Convert Free-Form report to Layout Report missing
    By TheSmitchell in forum Application Server Version 11 - Web/Browser Applications
    Replies: 3
    Last Post: 08-23-2013, 06:09 PM
  4. Report Help! Print, email, delete Specific report based on selection
    By markbwillard in forum Alpha Five Version 11 - Desktop Applications
    Replies: 12
    Last Post: 02-04-2013, 11:32 AM
  5. How to convert Free-Form report to Layout Report automatically?
    By Andiyanto Bun in forum Application Server Version 11 - Web/Browser Applications
    Replies: 7
    Last Post: 02-18-2012, 11:47 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •