Composite Mappings
Sometimes we want to create a single column in the report which combines multiple columns from the SQL. These are generally referred to as "Composite" mappings.
For example, the Publisher Address field on the book is composed of multiple database columns. To fetch a single Address column, we can use the AddressMapping instead of a SimpleMapping: (Add Address field data into Book.xml for some books record so you can able to see Address data on UI)
select TITLE, ISBN, PUBLISHED_DATE, QUANTITY_SOLD, PROMOTIONAL_DISCOUNT, STATE,PUBLISHER_ADDRESS_COUNTRY,PUBLISHER_ADDRESS_COMP1, PUBLISHER_ADDRESS_COMP2, PUBLISHER_ADDRESS_COMPS from ZBKS_BOOK<CustomRetrievalField> <FieldRef levelType="Undefined" category="PDF"> <FieldName>Publisher Address</FieldName> </FieldRef> <Type>ADDRESS</Type> <AddressMapping addressCountry="PUBLISHER_ADDRESS_COUNTRY" addressSearchComp1="PUBLISHER_ADDRESS_COMP1" addressSearchComp2="PUBLISHER_ADDRESS_COMP2" addressComps="PUBLISHER_ADDRESS_COMPS"/></CustomRetrievalField>
Similar to the Address, a DateRangeMapping is available for Date Ranges. Try it with the Promotional Period field of Book:
select TITLE, ISBN, PUBLISHED_DATE, QUANTITY_SOLD, PROMOTIONAL_DISCOUNT, STATE,PUBLISHER_ADDRESS_COUNTRY,PUBLISHER_ADDRESS_COMP1, PUBLISHER_ADDRESS_COMP2, PUBLISHER_ADDRESS_COMPS,PROMOTIONAL_PERIOD_START_DATE, PROMOTIONAL_PERIOD_END_DATEfrom ZBKS_BOOK<CustomRetrievalField> <FieldRef levelType="Undefined" category="PDF"> <FieldName>Promotional Period</FieldName> </FieldRef> <Type>DATE_RANGE</Type> <DateRangeMapping fromSqlName="PROMOTIONAL_PERIOD_START_DATE" toSqlName="PROMOTIONAL_PERIOD_END_DATE"/></CustomRetrievalField>
When we start using composite columns or other columns with lengthy output, we frequently see a need for customizing the default table column width. You can modify this by adding a ColumnWidth tag after Type:
<CustomRetrievalField> <FieldRef levelType="Undefined" category="PDF"> <FieldName>Promotional Period</FieldName> </FieldRef> <Type>DATE_RANGE</Type> <ColumnWidth>300</ColumnWidth> <!-- this tag is new --> <DateRangeMapping fromSqlName="PROMOTIONAL_PERIOD_START_DATE" toSqlName="PROMOTIONAL_PERIOD_END_DATE"/></CustomRetrievalField>Apply this change, relaunch the report, and you should see a better outcome:
