-
AuthorPosts
-
February 18, 2010 at 11:58 am #31067
Paul Frishert
ParticipantWhen 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 #32388Hi
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 #32389Paul Frishert
ParticipantThis 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 theafter ‘insertion’ should not be printed. I hope this clarifies. 😕
February 23, 2010 at 9:33 am #32390This 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 perPlease 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 #32391Paul Frishert
ParticipantThanks for the respons.
Luckily, I am able to read and understand the code. Onlywhere 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.
February 25, 2010 at 8:34 am #32392Support
KeymasterYou forgot to change ‘per.middlename‘ to ‘patientper.middlename‘ in the CASE statement.
March 1, 2010 at 10:06 am #32393Paul Frishert
ParticipantOops 😳
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 #32394Support
KeymasterPlease 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 #32395Paul Frishert
ParticipantResult 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 #32396Support
KeymasterPlease post the full SQL statement that you’re using.
March 2, 2010 at 8:10 am #32397Paul Frishert
ParticipantHere 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.linenumIf 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
March 2, 2010 at 8:32 am #32398Paul Frishert
ParticipantAgain 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 perand 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 perSo 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 #32399Support
Keymaster>> So the number of characters after the ELSE-statement,
>> determine the number of characters that are printed for the
>> THEN-statementNo, 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 #32400Paul Frishert
ParticipantGives 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?March 2, 2010 at 5:32 pm #32401Support
KeymasterIn 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, -
AuthorPosts
- You must be logged in to reply to this topic.