Edit SQL Reports

Use the Edit SQL Reports window to edit SQL reports from within Communicare.

Tip: See SQL Snippets for handy SQL code.

SQL Report options:

Enabled - enables or disables the report.
Viewing Rights - if a viewing right is selected, the report is available only to users who have the selected viewing right.
System Rights - if a System Right is selected, the report is available only to users who have the selected system right.

Preparing an SQL report

SQL Reports can be prepared in the SQL Editor or your favourite text editor (e.g. Notepad).

The first comment included in the sql file should display the purpose of the report. For example /* This report displays the home address of all patients */. The filename will be used by Communicare to display the report within the Report menu. For example, when a new report is imported with a filename of Clinical_Record_Reports Test.sql, Test is added to Report > Clinical Record Reports.

Refer to Report Naming for more details.

Creating Parameters for SQL Reports

To create a parameter in an SQL report simply prefix a colon (:) to a parameter name. Be mindful that it is the parameter name (minus the colon and underscores) which will be displayed in the Report Parameters window as a user prompt.

In the example above the SQL where clause might have looked like this:

where locality_name = :Count_patients_in_locality
   and pat_sex = :Enter_M_or_F_for_gender
   and date_of_birth > :Born_after

You can manually define the order in which the parameters are displayed in the Report Parameters window in the PARAMETERS section. The easiest way to do this is:

Write the report.
Make sure that the report runs, click Preview Query.
Right-click on the SQL Editor form and click Insert parameters.

This will automatically create the PARAMETERS section and insert the parameters with a blank ORDER attribute. If the PARAMETERS section already exists, parameters that aren't already in there will be added to the top. Simply add a number to the ORDER attribute to influence the position of that parameter.

<PARAMETERS UseXMLDisplayCase=off >
  <First_date order=1 />
  <Last_date order=2 />

The Attribute UseXMLDisplayCase above will use the name as it is displayed in the PARAMETERS section instead of how it is displayed in the SQL for the Report Parameters window prompt. This can be turned on and off by changing the value from 'off' to 'on' and back. In the XML, you can change the CASE of the name but you cannot change the actual words without having to update the SQL. This means FIRST_DATE can be updated to First_date without having to update the SQL, but if it is changed to Start_date, any occurance of :FIRST_DATE in the report will need updated to:START_DATE.

Define default values, list or form parameters for SQL Reports

You can provide a default value for a parameter in the PARAMETERS section. This is particularly important if you add a parameter to a report which may be used by a Scheduled Report, otherwise the Scheduled Report may stop working.

/* This report has a default date. */
   /*
   <PARAMETERS>
   <REPORT_DATE
      DEFAULT="01-JAN-1900"
   >
   </REPORT_DATE>
   </PARAMETERS>
   */

   select full_name

   from patient

   where date_of_birth >= :Report_date

If you want a parameter to have a Drop Down List so users can search for the items they want,you can use a special syntax to enable the feature in your report.

Every report has a comment block at the beginning of the report. You can have a second comment block with the Drop Down List Parameters.

Example:

 /* This report will print Medicare Card details about one or all patients. */
   /* This is the second comment block and is an example of how to use the
   special Drop Down List

PARAMETERS is the section that has everything regarding the parameters of the sql report.

 <PARAMETERS>

The way parameter names are displayed on the Parameters form can be changed by using the attribute named DISPLAYCASE or USEXMLDISPLAYCASE (see above for more detail on UseXMLDisplayCase) in the PARAMETERS section or DISPLAYNAME against the specific parameter in the PARAMETERS section. The Parameter USEXMLDISPLAYCASE overrides DISPLAYCASE, and DISPLAYNAME overrides everything.

To use DISPLAYNAME, right-click the report editor and click Insert parameters. This will insert the parameters into a PARAMETERS section automatically. Locate the parameter you are after and add DISPLAYNAME="insert new name here" including the double quotes.

<PARAMETERS UseXMLDisplayCase=off >
  <First_date order=1 DisplayName="First date to report" />
  <Last_date order=2 />

Using DISPLAYCASE, the Parameter Name can be changed to be displayed in Upper Case, Lower Case or Proper Case as long as DISPLAYNAME isn't being used and USEXMLDISPLAYCASE is either off or the paramter doesn't exist in the PARAMETERS SECTION.

Table 1. DISPLAYCASE parameter in SQL reports
DISPLAYCASE Attribute Value	Parameter Before	Displayed Parameter Name
PROPER	REPORT DATE	Report Date
LOWER	REPORT DATE	report date
UPPER	REPORT DATE	REPORT DATE
`NONE`	`REPORT DATE`	`REPORT DATE` (Unchanged)

Example:

<PARAMETERS DISPLAYCASE="PROPER">

Alternatively, PARAMETERS can accept an attribute named Output which can have the values of:

CSV for forcing the report to be output to a Comma Separated Value file and not seen visually
```
<PARAMETERS OUTPUT="CSV">
```
RWS_ for forcing the report to be output to a Comma Separated Value file and uploaded to the web. The report will not be seen visually.
```
<PARAMETERS OUTPUT="RWS_ANFPP">
```

PATIENT_NAME is the name of the parameter you want to define. This has to be a parameter in your sql report query

<PATIENT_NAME

The displayed position in the Report Parameters List can be defined by using ORDER. If you want the parameter to be first then the value is 1, second then the value is 2 and so on.

ORDER="1"

The statement is a normal sql select statement. Anything goes. If a statement is found, a regular drop down parameter is created.

STATEMENT="select FULL_NAME
                          , pat_id from patient
                     union
                     select cast('<All Patients>' as VarChar(40)) FULL_NAME
                          , cast(-1 as integer) pat_id
                     from rdb$database"

A default value can be defined.

 DEFAULT="<All Patients>"

All Properties of a parameter must have an equal sign and the values must be within double quotes.

If a drop down is not wanted but instead one of the built-in functions is required, assign the FUNCTION property.

 FUNCTION="SELECT_PATIENT"

All Properties of a parameter must have an equal sign and the values must be within double quotes.

All section tags are surrounded by the less than and greater than symbols (<>). All Sections must end with a </section>.

>
</PATIENT_NAME>

</PARAMETERS>
*/

This example provides a full description of all properties accepted and recognised by a DROP DOWN LIST PARAMETER in Communicare.

First we have the required header section.

/* This is a sample report */

/*
<PARAMETERS>

PATIENT_NAME is the name of the parameter passed to the following SQL code.

<PATIENT_NAME

STATEMENT is the SQL Query that is used for the Drop Down

STATEMENT="select distinct FULL_NAME
                            , PAT_ID
              from patient"

SEARCH is the field that you want to be ordered on the Drop Down

SEARCH="FULL_NAME"

SEARCHCASE is the case sensitivity for the field. If you want no distinction between upper case and lower case you can say upper or you can say none to make it case sensitive. The default is upper. Possible values are UPPER and NONE.

SEARCHCASE="UPPER"

RETURN_RESULT is the field that you want returned by the Drop Down to the parameter on the report.

RETURN_RESULT="PAT_ID"

ORDER is the desired position that you want this parameter to appear in the SQL Parameters form for entering in data. eg. 1 - First, 2 - Second etc.

 ORDER="1"

Next we finish off the PATIENT_NAME parameter information, the PARAMETERS section and the comment block containing it.

>
</PATIENT_NAME>

</PARAMETERS>
*/

The rest of the file contains the actual query that uses these parameters.

select pat_id
        , FULL_NAME
        , medicare_no
        , medicare_ref_no
        , medicare_expiry

   from patient

   where (pat_id = :Patient_Name
     or Cast(-1 as integer) = :Patient_Name)

The next example is a fully workable sql report. If you want to see how this works you can just copy this report and save it into an sql file then import it into Communicare.

/* This report will print Medicare Card details about one or all patients. */
   /* This is the second comment block and is an example of how to use the
   special Drop Down List
   <PARAMETERS>
   <FIRST_PATIENT
          STATEMENT="select FULL_NAME
                          , pat_id from patient
                       union
                     select cast('<All Patients>' as VarChar(40)) FULL_NAME
                          , cast(-1 as integer) pat_id
                     from rdb$database"
          DEFAULT="<All Patients>"
   >
   </FIRST_PATIENT>

   <SECOND_PATIENT
          FUNCTION="SELECT_PATIENT"
   >
   </SECOND_PATIENT>

   </PARAMETERS>
   */

   select pat_id
        , FULL_NAME
        , medicare_no
        , medicare_ref_no
        , medicare_expiry

   from patient

   where (pat_id = :FIRST_PATIENT
     or Cast(-1 as integer) = :FIRST_PATIENT
     or pat_id = :SECOND_PATIENT)

SMS Report Guidelines

SMS Reports may be created on the SMS Batch Query window, see Sending Batch SMS Messages

These reports must satisfy the following criteria:

They must have an output attribute on the parameters set to 'XML', i.e.:
They must output only the following field names (use field aliases) in exactly the following order:
- PatId (an integer field)
- PatientName (a string field)
- MobileNumber (a string field)
- Text (a string field)
Note that if the Text field is longer than 160 characters this will be truncated down to 160 before the SMS is sent

Reports added in the SMS Batch Query window that satisfy the above criteria will be able to be used to send SMS batches.