Parameter for empty fields

[Paul Frishert]

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?

[Mat Larkin]

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.

[Paul Frishert]

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. 😕

[Mat Larkin]

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

[Paul Frishert]

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.

[Support]

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

[Paul Frishert]

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?

[Support]

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.

[Paul Frishert]

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

[Support]

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

[Paul Frishert]

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

[Paul Frishert]

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?

[Support]

>> 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,

[Paul Frishert]

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?

[Support]

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,

[Author]

Posts