XML Report Definition files

XML in general

You can find a basic introduction into XML and DTDs here:

• http://www.xmlfiles.com/xml/xml_intro.asp
• http://www.xmlfiles.com/dtd/dtd_intro.asp

See also the short description about how to write special characters in XML (e.g. <, >, ...)

General hints about SQL

See the SQL compatibility rules for writing code that will run in several database systems, eg. Progress, MySQL and PostgreSQL.

General layout of a report

This layout drawings use the example of six columns, though any number of columns is possible.

In the XML file, the first column has the number 0, the same applies to the fields "left", and "header" in the level section.

In the page header section, the names are constants, so they (still) start with 1.

The descriptions formatted in italic are dealt with by the report generator, and cannot be controlled via the XML file.

report header, page header

reportHeader	(not implemented)
pageHeader	Page Number title1 Date title2 and Time descr1 period1 descr2 type period2 descr3 period3 left1 left2 Caption Col 0 Caption Col 1 Caption Col 2 Caption Col 3 Caption Col 4 Caption Col 6 second and third line of caption separator line
detail	Normal Level
pageFooter	(not implemented)

normal level

groupHeader	header 1 header 0
detail	either recursively Normal Level or lowest level
groupFooter	SpaceAbove LineAbove lineAbove lineAbove lineAbove lineAbove lineAbove lineAbove left 0 column 0 column 1 column 2 column 3 column 4 column 5 left 1 lineBelow lineBelow lineBelow lineBelow lineBelow lineBelow LineBelow SpaceBelow

detailed level, lowest level

detail	left 0 left 1 column 0 column 1 column 2 column 3 column 4 column 5

Use for each level an identification. This is usually a partner key. In the detailed tab page of the report this identification is used to open the correct partner in the partner edit form when you double click on the partner key. For example (EmergencyContactKey is a partner key):

<level name="Emergency Detail Level" identification="EmergencyContactKey">

Keywords used in Report Definition files

• condition for a level: this determines if the line is printed, invisible or even completely dropped
  • if the condition contains a variable ({} curly brackets), it is only evaluated once. Otherwise the condition is evaluated again after all rows have been calculated.
  • if the condition contains the word invisible, then the row is not displayed. But it is used for calculating totals.

Formatting of values

• currency (takes note of thousandsonly, etc.)
• dayofyear
• formatteddate (23-MAR-2007)
• partnerkey
• percentage2decimals
• percentage
• "#,##0.00 CR;#,##0.00 DR;0.00; ": positive numbers;negative numbers; 0; NULL; see also http://msdn2.microsoft.com/en-us/library/0c899ak8.aspx

Functions Available

Standard Functions

• see detailed description of all functions at Special Functions For Reports

• maths
  • sub, add, div, mod, ...
• parameter
  • assign, exists
• string
  • containscsv
• getting already calculated values
  • FirstLevelColumn
• new function conditionRow sets parameter DONTDISPLAYROW, which will remove the row from the result (example: TotalGiftsPerDonor, remove all donors that are not from a specified country)

Finance Module Functions

See Special Functions For Reports - Finance

FinDev Module Functions

See Special Functions For Reports - Financial Development

Partner Module Functions

See Special Functions For Reports - Partner

Personnel Module Functions

See Special Functions For Reports - Personnel

Conference Module Functions

See Special Functions For Reports - Conference

Special scenarios and solutions

Detail Reports

• all existing parameters are sent to the detail report
• of the selected row, through param_calculation the name of each column calculation is found, and this is used for a parameter, whose value is the value of the column
• on the grid, there is a popup menu, with all the detailreport ids (which are returned when the first report is calculated); associated to it is the action, which can either be
  • an xml file or an id for a Petra screen (e.g. Partner Edit)
• Should columns with a partnerkey in it be recognised automatically? Open "column caption" in Partner Edit Screen
• the parameters of the detail report are added to the returned parameters after the first report has been calculated, same as the query
• "action" is a link to a settings file; "id" is used as the caption for the context menu

example:

• • Find all Methods of Giving
  • Click on one, that shows the GiftTransactions
  • Click on one, select either "Open Donor in Partner Edit Screen" or "Open Gift Transaction Edit Screen"

optional lower level line

This can be seen in the accountdetail.xml: If param_with_analysis_attributes is set, then there should be a group of Analysis Attributes below the normal detail line, which is the transaction detail.

The solution is to have a switch in the detail of the level, and either display 2 lowerLevelReports beneath each other, or just the detail fields, if the report is without analysis attributes.

long column caption lines

see in common.xml, variation: one short and one long version of the caption

Several calculations in one call

• in one calculation element
  • see APPaymentExport, calculation "Select Payments by Batch Number"
  • first query detail contains value text="NO-SQL"; the other values can call functions or other calculations
  • In this example, there is a SELECT sql statement at the end of the query, and that is executed

• in the calculation attribute of the field
  • see GiftBatchExport
  • field whichfield="columns" calculation="Select RecipientDetails for Gift Transaction, Select Banking Details of partner"

Return results in a csvList

• see APPaymentExport, calcution "Select TransactionNarrative by GL Batch Number"
  • returns="TransactionNarrative" returnsFormat="list"
  • all the variables in returns are added up in a comma separated list
  • this helps when you need a total sum of the details amount, but also a sum of the references
  • returnsFormat="list" will return the values in a list (for internal calculations)
  • returnsFormat="csvlistslash:partnerkey" will print the values (each formatted as partnerkey in this example) and use the slash as a separator (for column calculations) (this is implemented in Ict.Common.Variant.pas)

build flexible query based on comma separated value list in a parameter

value function = "csv" text="..." variable="..."

• This will split the values stored in the variable, and compare each of them with the text, and all are related with an OR.
• If the value of the variable is empty, a false condition is inserted.
• If the text is a comma separated list, then every pair of variable is combined with an AND (see example below)

Examples (ap_payment_export):

 value function = "csv" text="PUB_a_ap_payment.a_payment_number_i" variable="paymentNumbers"
 value of paymentNumbers: 34,76,98
 results in sql snippet:
         (PUB_a_ap_payment.a_payment_number_i=34 OR PUB_a_ap_payment.a_payment_number_i=76 OR PUB_a_ap_payment.a_payment_number_i=98)

 value of paymentNumbers is empty:
 results in sql snippet:
         (true = false)

Example (FDIncomeByFund):

 value function = "csv" text="detail.a_motivation_group_code_c,detail.a_motivation_detail_code_c" variable="param_exclude_motivation"
 value of param_exclude_motivation: GIFT,PERSONAL,GIFT,BIRTHDAY
 results in sql snippet:
         ((detail.a_motivation_group_code_c='GIFT' AND detail.a_motivation_detail_code_c='PERSONAL') OR (detail.a_motivation_group_code_c='GIFT' AND detail.a_motivation_detail_code_c='BIRTHDAY'))

Add a quoted function result in an sql query

• The problem would be that a space is added between the value tags;
• The solution is to use format="quotes"
• see FDIncomeByFund.xml,

AND detail.a_cost_centre_code_c = </value>
<value function="div(Template:UnitPartnerKey,10000)" format="quotes"/>
<value>

prepared default setting: local costcentre

• in TransactionBase, if the parameter param_cost_centre_codes has the value *LOCAL*, it is replaced with <ledgernr>00

returnsformat row, returns Automatic

To save the effort of listing all the columns from the query, you can just write it like this (e.g. GiftDataExport.xml):

<calculation id="Select Gifts"
                   returns="Automatic"
                   returnsFormat="row">

report calculated in code instead of sql query in the xml file

• see https://github.com/openpetra/openpetra/blob/master/csharp/ICT/Petra/Server/lib/MPersonnel/queries/ReportBirthday.cs
• see https://forum.openpetra.org/t/new-option-of-writing-reports/253

I have now rewritten the birthday report, in a way that the main calculation is not parsed SQL from the report xml file anymore, but the xml report file refers to a C# method for the calculation:

<calculation id="Select Partners"
returns="automatic"
returnsFormat="row">
<query>
<queryDetail><value>Ict.Petra.Server.MPersonnel.queries.QueryBirthdayReport.CalculateBirthdays</value></queryDetail>
</query>
</calculation> <!-- Select Partners -->

The advantage is, that you can run a quite complex query, and then do the filtering, or fetching more data, inside that method. The only requirement is that you return a DataTable with the rows and columns that you want to process further in the report.

This method might be an interesting option for reports, that are too complicated or too slow to be written in several sql queries in the xml report files.

Time issues

Sometimes calculating reports can take a lot of time. There are three main areas of concern:

SQL Query

In Petra2.3 with the Progress Database Sql querries need to be carefully designed, otherwise they might take up a lot of time to execute. Since we use PostgreSql, SQLite or MySql in OpenPetra this is no longer such a big issue any more. These databases tend to execute the sql querries very quickly. But never the less care should be taken as well when you define the querries.

Generating the report and working through the levels in the xml file

If the report has to go through many levels and has to execute in each level some special functions and / or sql querries then this might use a lot of time. The main source of time consumption is usually when the report calls these special functions because the parameters need to be parsed and converted. There might be room to improve the process but it needs further investigation and analyzing.

• One example to improve this issue of timing is the "Top Donor Report" in the Financial Development Module. The main calculation of the report happens in the file "csharp\ICT\Petra\Server\lib\MReporting\MFinDev\Function.cs" (MakeTopDonor).
  • Generating the report without using the "MakeTopDonor" function took 5 minutes.
  • Generating the report with the "MakeTopDonor" function takes 10 seconds with the same data.
  • In "MakeTopDonor" (in Function.cs) we loop through the sql rows and copy the relevant data to the reporting results. When we did this just in the report (without using the "MakeTopDonor" function), we had to call several other functions for each row("eq", "add", "conditionrow" ...) to work through the loop and this made the whole report much slower.
• The disadvantage with using "MakeTopDonor" is that the columns for the reports are predefined and the user can't define them.

Printing the report

The time used for printing the reports depends on the amount of data that needs to be printed. If the report contains several pages, the printing can take up a few seconds (or even minutes). At the moment the "Print Preview" and the "Text Preview" are always generated. It might be worth to consider to generate the "Print Preview" always and the "Text Preview" only when the user wants to see it.