TOPICA Built_in Report Format

[This is preliminary documentation and is subject to change.]

TOPICA's built-in report format is the easiest way to build simple report. This format can be uaed to:

Display simple tables with no grouping, totals etc.
Exporting delimited text files (e.g. comma-separated files) for use in other applications (e.g. Excel).
Show simple graphs.

This report format has the following limitations:

No groupings or totals.
No "nesting" of data sets ("master/detail" relationships).
No support for export to PDF.
Very limited formatting possibilities.

If you need some of the above mentioned unsupported features in your report, consider using Reporting Services Reports instead.

Report definition example

Below example may be used as a template for reports, that may be run with or without an organizational unit in context.

Copy

<?xml version="1.0" ?>
<ReportDefinition
    HeaderText="Report header text"
    HeaderTag="h3"
    IntervalText="Interval:"
    IntervalDefault="Day"
    IntervalNone="true"
    IntervalDay="true"
    IntervalMonth="true"
    IntervalYear="true"
    DefaultDays="30"
    DefaultMonths="12"
    DefaultYears="3"
>

    <ReportHeader
        HeaderText="User: {user.name} - date/time: {now} "
        HeaderTag="p"
    />

    <!-- Display organizational context -->

    <ReportHeader
        Condition="isnotnull(orgunit)"
        HeaderTag="h5"
        HeaderText="Organizational unit: {orgunit.type} {orgunit.code} : {orgunit.name}"
    />

    <ReportHeader
        Condition="isnull(orgunit)"
        HeaderTag="h5"
        HeaderText="No organizational unit in context"
    />

    <!-- Display date interval entered by user at start up -->

    <ReportHeader Condition="IsNotNull(datefrom) OR IsNotNull(dateto)" HeaderTag="h5" HeaderText="Date interval: {CStr(DateValue(datefrom))} - {CStr(DateValue(dateto))}" />

    <ReportHeader Condition="IsNull(datefrom) And IsNull(dateto)" HeaderTag="h5" HeaderText="No date interval" />

    <!-- Compute SQL query -->

    <ReportVariable VariableName="wcOrg" ExpressionString="
        If
        (
            IsNull(orgunit)
            ,
            Blank
            ,
            SQL_And
            (
                SQL_StringEqual(idstring(ORGUNIT_TYPE),orgunit.type)
                ,
                SQL_StringEqual(idstring(ORGUNIT_CODE),orgunit.code)
                )
            )
        "
    />

    <ReportVariable
        VariableName="wcInterval"
        ExpressionString="SQL_DateInterval(&quot;DATE_START&quot;, datefrom, dateto)"
    />

    <ReportVariable
        VariableName="WhereClause"
        ExpressionString="SQL_Where(SQL_And(wcOrg, wcInterval))"
    />

    <ReportVariable VariableName="Query" ExpressionString="
        Replace
        (
            "
                SELECT
                    *
                FROM
                    MyTableName
                {WhereClause}
            "
        )
        "
    />

    <!-- Generate output -->

    <ReportTable
        HeaderText="MyTableName"
        HeaderTag="h5"
        SqlQuery="{Query}"
    />

    <ReportExport
        Condition=""
        HeaderTag="h5"
        SqlQuery="{Query}"
        ExportName="MyTableNameExport"
        ExportComma="Export (comma separated)"
    />

</ReportDefinition>

Example walk-through

Copy

<ReportDefinition
    HeaderText="Report header text"
    HeaderTag="h3"
    IntervalText="Interval:"
    IntervalDefault="Day"
    IntervalNone="true"
    IntervalDay="true"
    IntervalMonth="true"
    IntervalYear="true"
    DefaultDays="30"
    DefaultMonths="12"
    DefaultYears="3"
>

See TOPICA Report ReportDefinition.

The report is named "Report header text", and this header is displayed in <h3>-tags.

When the report is selected, the date interval start-up form is displayed. Per default interval type "Day" is selected, and the date interval is set to 30 days, ending today.

The user may select the other interval types: "None" (no selection on dates), "Month" (default interval of 12 months, ending the current month), "Year" (default interval of 3 years, ending current year).

Copy

<ReportHeader
    HeaderText="User: {user.name} - date/time: {now} "
    HeaderTag="p"
/>

See TOPICA Report ReportHeader.

The report displays name of user that started the report, and the date and time.

Copy

<ReportHeader
    Condition="isnotnull(orgunit)"
    HeaderTag="h5"
    HeaderText="Organizational unit: {orgunit.type} {orgunit.code} : {orgunit.name}"
/>

Note the condition attribute: this header is only displayed, when the report is run with an organizational unit in context.

Copy

<ReportHeader
    Condition="isnull(orgunit)"
    HeaderTag="h5"
    HeaderText="No organizational unit in context"
/>

Only when no organizational unit in context (i.e. when report is started from the "System Reports" menu item), above header is displayed.

Copy

<ReportVariable VariableName="wcOrg" ExpressionString="
    If
    (
        IsNull(orgunit)
        ,
        Blank
        ,
        SQL_And
        (
            SQL_StringEqual(idstring(ORGUNIT_TYPE),orgunit.type)
            ,
            SQL_StringEqual(idstring(ORGUNIT_CODE),orgunit.code)
        )
    )
"/>

This computes the logical SQL-expression to be used in a WHERE-clause to select for the organizational unit in context. If no organizational unit in context, the result is Blank, i.e. "". If there is an organizational unit in context, the result will be somthing like "(ORGUNIT_TYPE='sgh') AND (ORGUNIT_CODE='7026')" if the org.unit in context has type="sgh" and code="7026".

See also TOPICA Basic SQL Functions

Copy

<ReportVariable
    VariableName="wcInterval"
    ExpressionString="SQL_DateInterval(&quot;DATE_START&quot;, datefrom, dateto)"
/>

See TOPICA Basic Function SQL_DateInterval.

This computes the logical SQL-expression to be used in a WHERE-clause to select records according to the date interval entered by the user when starting the report. datefrom and dateto are variables, that are automatically computed by the framwork accroding to the user's input. Note that thes values will have the proper values, regardless of which interval type the user entered. E.g. if the user selected intraveltype "Year", and entered "from year" = 2008 and "to year" = 2009, datefrom will be January 1st, 2008, and dateto will be December 31st, 2009.

Note the construction "...". TOPICA Basic requires text constants to be written in doblue quotes "...", but in this case the TOPICA Basic is written in an XML-file, where quotes are used to delimit attribute values. Therefor it is necessary to use the special XML construction "..." to specify "..."

Copy

<ReportVariable
    VariableName="WhereClause"
    ExpressionString="SQL_Where(SQL_And(wcOrg, wcInterval))"
/>

See TOPICA Basic Function SQL_And and TOPICA Basic Function SQL_Where.

This computes the full WHERE-clause, which consists of an SQL-expression selecting by org.unit (variable wcOrg) and an SQL-expression selecting by date interaval (variable wcInterval).

Note that if there is no org.unit in context, variable wcOrg will be blank, and if user did not select a date inteval, variable wcInterval will be blank. The use of TOPICA Basic function SQL_Where assures, that the resulting WHERE-clause will be blank is this case.

Copy

<ReportVariable VariableName="Query" ExpressionString="
    Replace
    (
        "
            SELECT
                *
            FROM
                MyTableName
            {WhereClause}
        "
    )
"/>

This constructs the final SQL query to be used to get values for our report. We define this in a variable, because we want to use the same SQL fro both the table output and the export (see below).

Copy

<ReportTable
    HeaderText="MyTableName"
    HeaderTag="h5"
    SqlQuery="{Query}"
/>

<ReportExport
    Condition=""
    HeaderTag="h5"
    SqlQuery="{Query}"
    ExportName="MyTableNameExport"
    ExportComma="Export (comma separated)"
/>

See TOPICA Report ReportTable and TOPICA Report ReportExport.

This is where the report output is generated. Note that you may place any number or ReportTable and ReportExport elements in your report.

Adapting the example to you report

Obviously you would have to change something in the above template for your "real" reports:

The SQL query (the definition of variable Query) should be changed to read the relevant fields from your database table. You may read from joins, views or table valued functions defined in the database, too (anything that returns a "record set").
In the date interval selection (the definition of variable wcOrg) you should check on a date/time field in your query.
You may add something to the WHERE-clause (in the definition of variable WhereClause). This may include values of parameters entered by the user in the start-up form (not shown in this example).

Other Resources

TOPICA Report Format

TOPICA Basic