Viewing 15 posts - 1 through 15 (of 16 total)
  • Author
    Posts
  • #31067
    Paul Frishert
    Participant

    When creating a report, there are fields that have content or that are empty. When you embody such a field in a report and it’s empty, a space is ‘printed’. How can this be avoided? Or, is there a parameter or someting like that, for regulating this?

    #32388

    Hi

    Thanks for your post. Could you please be more specific in what you are trying to do? Is this in the ClinicOffice Word Processor or are you using the Report Designer? Please let us know what exactly you are trying to achieve.

    Many thanks.

    #32389
    Paul Frishert
    Participant

    This question is related to the Report desginer.
    What I am trying to achieve, is to have three name related fields used on a report, where the second one can be empty.
    For example: <space><space>
    In this case the field ‘insertion’ can be empty and in that case the field itself and the after ‘insertion’ should not be printed.

    I hope this clarifies. 😕

    #32390

    This can be achieved is by editing the SQL SOURCE of the report. For example, to concatenate ‘firstname’, ‘middlename’, ‘lastname’ with the correct spacing, the code would be as follows :-

    select id,
    per.firstname ||
    (case when per.middlename is not null and per.middlename <> '' then ' ' || per.middlename || ' ' else ' ' end) ||
    per.lastname
    from per

    Please bear in mind that editing the source code for reports is quite technical and we strongly recommend joining our Support Plan so that we can assist you with this kind of thing.

    You can find more information about the Support Plan from here :-
    http://pioneersoftware.co.uk/cosp

    #32391
    Paul Frishert
    Participant

    Thanks for the respons.
    Luckily, I am able to read and understand the code. Only

    where per.middlename <> ''

    does give a result.
    In the report Invoice I use


    Select
    patientper.firstname, patientper.middlename,

    (case when patientper.middlename <> '' then ' ' || per.middlename || ' ' else ' ' end) as tussenvoegsel,

    patientper.lastname, patientper.address1, etc...

    In a test only ‘middlename’ shows a result and ‘tussenvoegsel’ doesn’t. What could be the reason??

    BTW: when I do not add ‘as tussenvoegsel’ to the code, I cannot select the field in the report.

    #32392
    Support
    Keymaster

    You forgot to change ‘per.middlename‘ to ‘patientper.middlename‘ in the CASE statement.

    #32393
    Paul Frishert
    Participant

    Oops 😳

    Still one question: the line

    (case when patientper.middlename <> '' then '' || patientper.middlename || '' else '' end) as tussenvoegsel

    is OK now.

    When I drag the field to the report, only one character is printed. I have done some debugging, like this

    (case when patientper.middlename <> '' then '12' || patientper.middlename || '2' else '3' end) as tussenvoegsel

    When the field middlename has no value, the number ‘3’ is printed, when it has a value, only ‘1’ is printed, while it should print ’122′. I have checked the parameters in the report and checked ‘Auto width’ and ‘can grow’, but these don’t do the job.
    Is there another setting or parameter?

    #32394
    Support
    Keymaster

    Please try this instead :-

    (case when (patientper.middlename is not null and patientper.middlename <> '') then '12' || patientper.middlename || '2' else '3' end) as tussenvoegsel

    Please let us know how you get on.

    #32395
    Paul Frishert
    Participant

    Result is the same, only ‘1’ is printed, when the field middlename has a value. A ‘3’ is printed, when there is no value.

    #32396
    Support
    Keymaster

    Please post the full SQL statement that you’re using.

    #32397
    Paul Frishert
    Participant

    Here you are:

    select

    inv.id, inv.invoicenum, inv.datetime, cast(inv.datetime as date) as _invdate,
    inv.discpercent, inv.discamount, inv.nettotal, inv.taxtotal, inv.total,
    inv.customerref, inv.address, inv.notes,

    patientper.firstname, patientper.middlename,

    (case when (patientper.middlename is not null and patientper.middlename <> '') then '12' || patientper.middlename || '2' else '3' end) as tussenvoegsel,

    patientper.lastname, patientper.address1,
    patientper.postcode, patientper.towncity, patientper.knownas,
    patientper.dob, cast(patientper.dob as DATE) as geboorte,

    invline.linenum as _linenum, item.itemcode as _lineitemcode,
    invline.description as _linedescription, invline.quantity as _linequantity,
    invline.unitcost as _lineunitcost, invline.taxpercent as _linetaxpercent,
    invline.net as _linenet, invline.tax as _linetax, invline.total as _linetotal,

    inv.clinic_id, clinic.name as _clinicname, clinic.address as _clinicaddress,

    inv.per_id as recipient_per_id, @NAME:recipientper as _recipientname,

    inv.patient_per_id as patient_per_id, @NAME:patientper as _patientname,
    patientper.code as patientcode,

    inv.staff_id, staff.knownas as _staffname,
    inv.apptype_id, apptype.name as _appointmenttype,
    inv.room_id, room.name as _roomname

    from inv
    left join invline on (inv.id=invline.inv_id)
    left join item on (invline.item_id=item.id)
    left join per as recipientper on (inv.per_id=recipientper.id)
    left join per as patientper on (inv.patient_per_id=patientper.id)
    left join staff on (inv.staff_id=staff.id)
    left join apptype on (inv.apptype_id=apptype.id)
    left join clinic on (inv.clinic_id=clinic.id)
    left join room on (inv.room_id=room.id)

    order by inv.datetime, invline.linenum

    If you enter this complete statement in the SQL (advanced database operation) editor, the the result is the same. So something is not OK in the statement

    (case when (patientper.middlename is not null and patientper.middlename <> '') then '12' || patientper.middlename || '2' else '3' end) as tussenvoegsel
    #32398
    Paul Frishert
    Participant

    Again I have done some testing. Please try this

    select

    per.firstname, per.middlename,

    (case when (per.middlename is not null and per.middlename <> '') then '1'||per.middlename||'2' else '33'||'' end) as tussenvoegsel,

    per.lastname, per.address1

    from per

    and this

    select

    per.firstname, per.middlename,

    (case when (per.middlename is not null and per.middlename <> '') then '1'||per.middlename||'2' else '33333'||'' end) as tussenvoegsel,

    per.lastname, per.address1

    from per

    So the number of characters after the ELSE-statement, determine the number of characters that are printed for the THEN-statement. Do you understand?

    #32399
    Support
    Keymaster

    >> So the number of characters after the ELSE-statement,
    >> determine the number of characters that are printed for the
    >> THEN-statement

    No, it’s not the ELSE statement which is determining the width. Rather, PGSQL is fixing the length of the calculated column based on the result retrieved for the first record in the dataset. Unfortunately we can’t reproduce this here as we’re running a newer version of PGSQL which doesn’t seem to have the same issue.

    Please try casting the expression to a TEXT field as follows :-

    (cast(case when (per.middlename is not null and per.middlename <>  '') then '1'||per.middlename||'2' else '33333'||'' end) as text) as tussenvoegsel,
    #32400
    Paul Frishert
    Participant

    Gives this error:
    [attachment=0:1zjywui3]SQLerror.PNG[/attachment:1zjywui3]
    But this works:

    (cast((case when (per.middlename is not null and per.middlename <>  '') then '1'||per.middlename||'2' else '33333' end) as char(10))) as tussenvoegsel

    I tried with

    char(count(per.middlename))

    but this does not work.
    Maybe another statement that can count the number of characters of the field?

    #32401
    Support
    Keymaster

    In that case, perhaps try CASTing the individual parts of the case statement i.e. :-

    (case when (per.middlename is not null and per.middlename <>  '')
    then cast('1' || per.middlename || '2' as varchar(100))
    else cast('33333' as varchar(100)) end)
    as tussenvoegsel,
Viewing 15 posts - 1 through 15 (of 16 total)
  • You must be logged in to reply to this topic.