February 18, 2010 at 11:58 am #31067
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?February 19, 2010 at 10:46 am #32388
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.February 21, 2010 at 8:50 pm #32389
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.
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. 😕February 23, 2010 at 9:33 am #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 :-
(case when per.middlename is not null and per.middlename <> '' then ' ' || per.middlename || ' ' else ' ' end) ||
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/cospFebruary 24, 2010 at 3:47 pm #32391
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
(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.February 25, 2010 at 8:34 am #32392
You forgot to change ‘per.middlename‘ to ‘patientper.middlename‘ in the CASE statement.March 1, 2010 at 10:06 am #32393
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 ’12
2′. 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?March 1, 2010 at 10:21 am #32394
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.March 1, 2010 at 4:05 pm #32395
Result is the same, only ‘1’ is printed, when the field middlename has a value. A ‘3’ is printed, when there is no value.March 1, 2010 at 9:08 pm #32396
Please post the full SQL statement that you’re using.March 2, 2010 at 8:10 am #32397
Here you are:
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,
(case when (patientper.middlename is not null and patientper.middlename <> '') then '12' || patientper.middlename || '2' else '3' end) as tussenvoegsel,
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
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 tussenvoegselMarch 2, 2010 at 8:32 am #32398
Again I have done some testing. Please try this
(case when (per.middlename is not null and per.middlename <> '') then '1'||per.middlename||'2' else '33'||'' end) as tussenvoegsel,
(case when (per.middlename is not null and per.middlename <> '') then '1'||per.middlename||'2' else '33333'||'' end) as tussenvoegsel,
So the number of characters after the ELSE-statement, determine the number of characters that are printed for the THEN-statement. Do you understand?March 2, 2010 at 10:48 am #32399
>> So the number of characters after the ELSE-statement,
>> determine the number of characters that are printed for the
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,March 2, 2010 at 3:56 pm #32400
Gives this error:
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
but this does not work.
Maybe another statement that can count the number of characters of the field?March 2, 2010 at 5:32 pm #32401
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)
- You must be logged in to reply to this topic.