| Panel | ||
|---|---|---|
| ||
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?
|
...
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. Landscape: A00 A00_A4_Landscape_Data.jrxml
4. A00_A4_Landscape.jrxml
| Expand | ||
|---|---|---|
| ||
25. Portrait: B00_A4_Portrait.jrxml
36. Report (A00_A4_Landscape_With_SubReport.jrxml) with sub-report (A00_A4_Landscape_SubReport.jrxml)
...
In the following example Accounts_Receivable_Balance.xml is created under ../reports/AccountsReceivable
| Code Block | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||
<?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> |
...