Reports are used to extract important information about existing data of the Software and are generated using two files: an XML file used to define the report criteria and information displayed, and a JRXML file used to define the report layout. Reports can be generated in any of the following forms: PDF, EXCEL,CSVand HTML.

What does this section cover?

Create Report Pages

To create a new report page you need to:

Create a layout file (a) or use one of the existing CRM.COM report layout templates (b) (.jrxml)
Create a report criteria and content file (.xml)
Define the report in modules metadata file

For an introduction to reports, go to Report Pages.

1. Report Layouts

a. Use Report Layout Templates

CRM.COM Software includes the following report layout templates for landscape and portrait page orientation and for reports containing subreports. All report layout templates can be found under <custom_project>/web/WebContent/reports directory

1. A00_A0_Landscape_Data.jrxml

2. A00_A3_Landscape_Data.jrxml

3. A00_A4_Landscape_Data.jrxml

4. A00_A4_Landscape.jrxml

Landscape Report Page Example

5. Portrait: B00_A4_Portrait.jrxml

6. Report (A00_A4_Landscape_With_SubReport.jrxml) with sub-report (A00_A4_Landscape_SubReport.jrxml)

In the following example, A00_A4_Landscape_With_SubReport.jrxml is used for the main report which is Rewards Participants Balance, and A00_A4_Landscape_SubReport.jrxml is used for the sub-report which includes each participant's participating schemes.

Report with sub-report Page Example

b. Design Report Layout Pages

If none of the above templates meet your needs, you can create your own JRXML file using iReport Designer.

2. Create Report Criteria and Content Pages

To create a new report criteria and content page, you need to create an XML file under <custom_project>/web/WebContent/reports/<module_name> directory.

In the following example Accounts_Receivable_Balance.xml is created under ../reports/AccountsReceivable

Accounts_Receivable_Balance.xml

<?xml version="1.0" encoding="UTF-8"?><definition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../xsd/reports.xsd">
	<name>key_accounts_receivable_balance</name>
	<description>key_rewards_participants_balance</description>
	<entityTable>ACCOUNTSRECEIVABLE</entityTable>
	<entityField>ACCRECID</entityField>
	<dataset/>
	<orderBy>ACCRECNUM ASC</orderBy>
	<joins>
		INNER JOIN ACCCLASSIFICATIONS ON ACCCLASSIFICATIONS.ACCCLASSID = ACCOUNTSRECEIVABLE.ACCCLASSID
		INNER JOIN CONTACTINFORMATION ON CONTACTINFORMATION.CIID = ACCOUNTSRECEIVABLE.CIID
		INNER JOIN CONTACTINFODEFINITIONS ON CONTACTINFODEFINITIONS.CIDEFACTIVE = 1 AND CONTACTINFODEFINITIONS.CIDEFDELETED = 0
		INNER JOIN OUGROUPS ON OUGROUPS.OUGID = ACCOUNTSRECEIVABLE.OWNEROUGID
 		INNER JOIN CREDITLIMITSTATUS ON CREDITLIMITSTATUS.CREDITLIMITSTATUS_ID=ACCOUNTSRECEIVABLE.ACCRECID
	 	LEFT JOIN OUTSTANDINGAMOUNT ON OUTSTANDINGAMOUNT.OUTSTANDINGAMOUNT_ID=ACCOUNTSRECEIVABLE.ACCRECID
 		INNER JOIN BALANCE ON BALANCE.BALANCE_ID=ACCOUNTSRECEIVABLE.ACCRECID
 		INNER JOIN CURRENCIES ON CURRENCIES.CURRENCYID = ACCOUNTSRECEIVABLE.CURRENCYID
		LEFT JOIN CURRENCYRATEPERIODS ON CURRENCYRATEPERIODS.CURRENCYID =  CURRENCIES.CURRENCYID AND CURRENCYRATEPERIODS.CURRATEPERIODTODATE IS NULL
		LEFT JOIN SYSTEMSETTINGS ON SYSTEMSETTINGS.SYSTEMSETTINGNAME= 'DEFAULT_CURRENCY' AND SYSTEMSETTINGS.SYSTEMSETTINGDELETED=0
		LEFT JOIN CURRENCIES DEFAULTCURRENCIES ON DEFAULTCURRENCIES.CURRENCYID = SYSTEMSETTINGS.SYSTEMSETTINGSTRINGVALUE 
	</joins>
	<conditions>
		ACCOUNTSRECEIVABLE.ACCRECDELETED = 0 
	</conditions>
	<with>
	 
 		OUTSTANDINGAMOUNT (OUTSTANDINGAMOUNT_ID, OUTSTANDINGAMOUNT_VALUE) AS 
		(
		SELECT  ACCOUNTSRECEIVABLE.ACCRECID, SUM(INVOICES.INVOICETOTALAMT - COALESCE((SELECT SUM(ALLOCATEDAMT) 
		FROM INVOICEALLOCATIONS 
		WHERE 
		...
	</with>
	<template>A00_A4_Landscape</template>
	<criteria>
		<criterion>
			<criteriatype>0</criteriatype>
			<label>key_owned_by_group</label>
			<extralabel>key_owned_by_group</extralabel>
			<tooltip/>
			<hibernatefield/>
			<entitytablename>OUGROUPS</entitytablename>
			<entityfieldname>OUGID</entityfieldname>
			<displaytable>OUGROUPS</displaytable>
			<displaylabel>key_name</displaylabel>
			<displayfield>OUGNAME</displayfield>
			<fieldtype>ftXString</fieldtype>
			<fieldsize>32</fieldsize>
			<sourcealias>OUGID</sourcealias>
			<displayalias>OUGNAME</displayalias>
			<datasetname>networkmanagement.loadgroups</datasetname>
			<lookupname/>
			<treename/>
			<multiselect>1</multiselect>
			<isMandatory>1</isMandatory>
		</criterion>
		<criterion>
			<criteriatype>0</criteriatype>
			<label>key_life_cycle_state</label>
			<extralabel>key_life_cycle_state</extralabel>
			<tooltip/>
			<hibernatefield/>
			<entitytablename>ACCOUNTSRECEIVABLE</entitytablename>
			<entityfieldname>LIFECYCLESTATE</entityfieldname>
			<displaytable/>
			<displaylabel>key_life_cycle_state</displaylabel>
			<displayfield>LIFECYCLESTATE</displayfield>
			<fieldtype>ftXString</fieldtype>
			<fieldsize>32</fieldsize>
			<sourcealias>LIFECYCLESTATE</sourcealias>
			<displayalias/>
			<datasetname/>
			<lookupname>ejb/CRMUIAccountReceivable.getLifeCycleStateOptions:</lookupname>
			<isMandatory>1</isMandatory>
		</criterion>
		<criterion>
			<criteriatype>0</criteriatype>
			<label>key_date_created</label>
			<extralabel>key_date_created</extralabel>
			<tooltip/>
			<hibernatefield/>
			<entitytablename>ACCOUNTSRECEIVABLE</entitytablename>
			<entityfieldname>ACCRECCREATEDDATE</entityfieldname>
			<displaytable/>
			<displaylabel/>
			<displayfield/>
			<fieldtype>ftXDate</fieldtype>
			<fieldsize>-1</fieldsize>
			<sourcealias/>
			<displayalias/>
			<datasetname/>
			<lookupname/>
			<treename/>
			<multiselect>0</multiselect>
			<isMandatory>0</isMandatory>
		</criterion>
		...
	</criteria>
  	<columns>
			<column>
        		<fieldname>ACCRECNUM</fieldname>
        		<width>10</width>
        		<widthType>PERCENTAGE</widthType>
        		<header>key_number</header>
     		</column>
     		<column>
				<fieldname>ACCOUNTSRECEIVABLE.ACCRECNAME</fieldname>
        		<alias>ACCOUNTSOWNER</alias>
        		<width>10</width>
        		<widthType>PERCENTAGE</widthType>
        		<header>key_account_information</header>
     		</column>
     		<column>
				<fieldname>
					CASE WHEN ACCOUNTSRECEIVABLE.LIFECYCLESTATE='ACTIVE' then 'Active'
			     	WHEN ACCOUNTSRECEIVABLE.LIFECYCLESTATE='SUSPENDED' then 'Suspended'
			     	WHEN ACCOUNTSRECEIVABLE.LIFECYCLESTATE='TERMINATED' then 'Terminated'
					END
				</fieldname>
        		<alias>ACCOUNT_LIFECYCLESTATE</alias>
        		<width>10</width>
        		<widthType>PERCENTAGE</widthType>
        		<header>key_life_cycle_state</header>
     		</column>
     		<column>
				<fieldname>ACCRECCREATEDDATE</fieldname>
				<width>10</width>
				<header>key_date_created</header>
        		<widthType>PERCENTAGE</widthType>
        		<fieldtype>FTXDATE</fieldtype>
		</column>
		<column>
			<fieldname>COALESCE(COALESCE(CURRENCYRATEPERIODS.CURRENCYPERIODINVERSERATE,1) * OUTSTANDINGAMOUNT_VALUE,0)</fieldname>
    		<alias>OUTSTANDINGAMOUNT_VALUE</alias>
    		<width>10</width>
    		<widthType>PERCENTAGE</widthType>
    		<header>key_outstanding_amount</header>
    		<fieldtype>FTXFLOAT</fieldtype>
			<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
	    </column>
		...		
  	</columns>
	<groups>
  		<group>
     		<groupid>OUGNAME</groupid>
     		<sortorder>OUGNAME</sortorder>
        	<groupbyheader>key_owned_by_group</groupbyheader>
     		<header>
        		<column>
           			<fieldname>OUGNAME</fieldname>
        			<width>25</width>
        			<widthType>PERCENTAGE</widthType>
           			<header>key_owned_by_group:</header>
        		</column>
     		</header>
     		<footer>
				<column>
		    		<fieldname>=COUNT({ACCRECNUM})</fieldname>
		    		<header>key_number_of_accounts_receivable:</header>
		    		<width>33</width>
		    		<widthType>PERCENTAGE</widthType>
				</column>
				<column>
			   		<fieldname>=SUM({OUTSTANDINGAMOUNT_VALUE})</fieldname>
			   		<header>key_outstanding_amount:</header>
			   		<width>33</width>
			 		<widthType>PERCENTAGE</widthType>
			 		<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
				</column>
				<column>
		    		<fieldname>=SUM({BALANCE_VALUE})</fieldname>
		    		<header>key_account_balance:</header>
		    		<width>33</width>
		    		<widthType>PERCENTAGE</widthType>
		    		<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
				</column>
     		</footer>
  		</group>
  		...
	</groups>
	<footers>
     		<column>
	    		<fieldname>=COUNT({ACCRECNUM})</fieldname>
	    		<header>key_number_of_accounts_receivable:</header>
	    		<width>33</width>
	    		<widthType>PERCENTAGE</widthType>
	 	</column>
	 	<column>
	    		<fieldname>=SUM({OUTSTANDINGAMOUNT_VALUE})</fieldname>
	    		<header>key_outstanding_amount:</header>
	    		<width>33</width>
	    		<widthType>PERCENTAGE</widthType>
	    		<alias>OUTSTANDINGAMOUNT_VALUE</alias>
				<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
	 	</column>
	 	<column>
	    		<fieldname>=SUM({BALANCE_VALUE})</fieldname>
	    		<header>key_account_balance:</header>
	    		<width>33</width>
	    		<widthType>PERCENTAGE</widthType>
				<displaydefaultcurrencysymbol>true</displaydefaultcurrencysymbol>
	 	</column>
	</footers>
</definition>

Accounts Receivable Balance Report Criteria

Accounts Receivable Balance Report Results

For a full list of report page attributes, go to Report Pages Documentation

3. Define Report in Metadata

In order for the report to be available to the user, it must be defined in metadata file modules.xml, which is located under<custom_project>/web/WebContent/metadata directory.

modules.xml

<modules>
	<module>
		<moduleid>ACCOUNTS_RECEIVABLE</moduleid>
		<applicationid>FINANCE</applicationid>
		...
		<features>
			<feature>
				...
				<reports>
			    	<report>
			      		<id>ACCOUNTS_RECEIVABLE_BALANCE_REPORT</id>
			      		<name>key_accounts_receivable_balance</name>
			      		<description>key_view_the_accounts_receivable_balance</description>
			      		<reportfilename>AccountsReceivable/Accounts_Receivable_Balance.xml</reportfilename>
			    	</report>
					...
			    </reports>
			</feature>
			...
		</features>
	</module>
</modules>

Reports Menu

For a full list of metadata report attributes, go to Reports Metadata.

Create Reports