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

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

• 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)

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">