Creating a Pivot

Pivots are based on reports. Creating a pivot begins with a report containing all the fields and retrieval criteria. To learn more about reports, refer to the "Reporting" section in this user guide.

<Report>
  <Name>MyFirstReport</Name>
  <AllowPreferences>true</AllowPreferences>
    <SqlDef Name="GetVersion" GroupName="SampleSqls">
        select sys_book_id SYS_BOOK_ID,
        Title,
        ISBN,
        genre GENRE,
        available_online AVAILABLE_ONLINE,
        online_version_link ONLINE_VERSION_LINK,
        return_policy RETURN_POLICY,
        rating RATING,
        PUBLISHER_ADDRESS_COUNTRY,
        PUBLISHER_ADDRESS_COMP1,
        PUBLISHER_ADDRESS_COMP2,
        PUBLISHER_ADDRESS_COMPS,
        published_date PUBLISHED_DATE,
        publishing_period_start_date PUBLISHING_PERIOD_START_DATE,
        publishing_period_end_date PUBLISHING_PERIOD_END_DATE,
        promotional_discount PROMOTIONAL_DISCOUNT,
        price_amount PRICE_AMOUNT,
        price_uom PRICE_UOM,
        sys_sales_rep_id SYS_SALES_REP_ID,
        users.user_name SALES_REP from ZBKS_Book outer join users on
        users.sys_user_id = sys_sales_rep_id
    </SqlDef>
    <Retrieval>
<CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>SysBookId</FieldName>
    </FieldRef>
    <Hidden>true</Hidden>
    <Type>LONG</Type>
    <SimpleMapping sqlName="SYS_BOOK_ID"/>
</CustomRetrievalField>
<RetrievalField levelType="ZBKS.Book" category="PDF">
    <FieldName>Title</FieldName>
</RetrievalField>
<RetrievalField levelType="ZBKS.Book" category="PDF">
    <FieldName>ISBN</FieldName>
</RetrievalField>
<CustomRetrievalField>
   <FieldRef levelType="ZBKS.Book" category="MODEL_LINK">
    <ModelLinkTargetLevelType>Users</ModelLinkTargetLevelType>
         <FieldName>Sales Representative</FieldName>
       </FieldRef>
       <Type>MODEL_LINK</Type>
        <ModelLinkMapping surrogateIdSqlName="SYS_SALES_REP_ID" displayFieldSqlName="SALES_REP">
       </ModelLinkMapping>
     </CustomRetrievalField>
    <CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Genre</FieldName>
    </FieldRef>
    <Type>STRING_ENUMERATION</Type>
                <EnumerationType>ZBKS.BookType</EnumerationType>
       <SimpleMapping sqlName="GENRE"/>
</CustomRetrievalField>
            <CustomRetrievalField>
   <FieldRef levelType="ZBKS.Book" category="PDF">
      <FieldName>AvailableOnline</FieldName>
    </FieldRef>
    <Type>BOOLEAN</Type>
    <SimpleMapping sqlName="AVAILABLE_ONLINE"/>
</CustomRetrievalField>
    <CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Return Policy</FieldName>
    </FieldRef>
    <Type>DURATION</Type>
    <SimpleMapping sqlName="RETURN_POLICY"/>
</CustomRetrievalField>
        <CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Rating</FieldName>
    </FieldRef>
    <Type>FLOAT</Type>
    <SimpleMapping sqlName="RATING"/>
</CustomRetrievalField>
<CustomRetrievalField>
   <FieldRef levelType="ZBKS.Author" category="PDF">
      <FieldName>Publisher's Address</FieldName>
    </FieldRef>
    <Type>ADDRESS</Type>
    <AddressMapping addressCountry="PUBLISHER_ADDRESS_COUNTRY" addressSearchComp1="PUBLISHER_ADDRESS_COMP1" addressSearchComp2="PUBLISHER_ADDRESS_COMP2" addressComps="PUBLISHER_ADDRESS_COMPS"/>
</CustomRetrievalField>
<CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>PublishedDate</FieldName>
    </FieldRef>
    <Type>DATE</Type>
    <SimpleMapping sqlName="PUBLISHED_DATE"/>
</CustomRetrievalField>
<CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Publishing Period</FieldName>
    </FieldRef>
    <Type>DATE_RANGE</Type>
    <DateRangeMapping fromSqlName="PUBLISHING_PERIOD_START_DATE" toSqlName="PUBLISHING_PERIOD_END_DATE"/>
</CustomRetrievalField>
    <CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Promotional Discount</FieldName>
    </FieldRef>
    <Type>DOUBLE</Type>
    <SimpleMapping sqlName="PROMOTIONAL_DISCOUNT"/>
</CustomRetrievalField>
    <CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>price_amount</FieldName>
    </FieldRef>
    <Hidden>true</Hidden>
    <Type>DOUBLE</Type>
    <SimpleMapping sqlName="PRICE_AMOUNT"/>
</CustomRetrievalField>
<CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>price_uom</FieldName>
    </FieldRef>
    <Hidden>true</Hidden>
    <Type>STRING</Type>
    <SimpleMapping sqlName="PRICE_UOM"/>
</CustomRetrievalField>
<CustomRetrievalField>
    <FieldRef levelType="ZBKS.Book" category="PDF">
        <FieldName>Price</FieldName>
    </FieldRef>
    <Type>COMPOSITE</Type>
    <CompositeFieldType name="CurrencyCode">
        <Component name="price_amount" type="DOUBLE"/>
        <Component name="price_uom" type="STRING"/>
    </CompositeFieldType>
    <SimpleMapping sqlName="PRICE_AMOUNT"/>
</CustomRetrievalField>
</Retrieval>
</Report>

To display the Pivot button on the report, define a pivot element after the retrieval criteria, as shown below, with the pivot name. This will display as the name of the tab the pivot will be opened with in the UI. A pivot table can be accessed from a report or can have a direct reference to it from UI.
    ...
    </Retrieval>

    <Pivot>
        <PivotName>BooksStoreCharts</PivotName>
    </Pivot>
</Report
To reference a pivot from a report, refer to the section titled "Report from UI." The following image shows the View Pivot button on a report.
Click the View Pivot button.
The pivot screen for that report displays. Users can make adjustments to the data and chart type.
Click the developer's code popup button as highlighted in the screenshot above.
A popup with the code for the pivot widget displays.
Click the Copy to Clipboard button to copy the code.
Click the Download button to download the code to your computer.

To display a web action reference to a pivot, configure the UI metamodel as follows:

<?xml version="1.0" encoding="UTF-8"?>
<UiMetaModel xmlns="http://www.onenetwork.com/Platform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ValueChainId>9123</ValueChainId>
    <Name>UI Meta Model</Name>
    <Version>1.0</Version>

    <WebInfo>
        <WebAction iconCls="sho-icon-report" name="ReportFrameworkDemonstration">
            <PanelOptions>
                <PanelClass>One.Report</PanelClass>
                <PanelConfig>{reportName: "ZBKS.MyFirstReport", autoExecute: true}</PanelConfig>
            </PanelOptions>
        </WebAction>
        <WebAction iconCls="sho-icon-report" name="PivotDemonstration">
            <PanelOptions>
                <PanelClass>One.Pivot</PanelClass>
                <PanelConfig>{reportName: "ZBKS.MyFirstReport", autoExecute: true}</PanelConfig>
            </PanelOptions>
        </WebAction>

        <RoleTypeUiConfig menuType="menu" titleKey="bookstore.title">
            <RoleTypeRef>
                <IntrinsicRoleType>ZBKS.SampleRoleType</IntrinsicRoleType>
            </RoleTypeRef>
        <Home name="ReportFrameworkDemonstration"/>
        <QuickLaunch name="ReportFrameworkDemonstration" iconCls="temp-icon-1"/>
        <QuickLaunch name="PivotDemonstration" iconCls="temp-icon-3"/>

        </RoleTypeUiConfig>
    </WebInfo>
</UiMetaModel>

Click the X to close the popup.
Click the Favorites icon to save the pivot screen to the list of favorites saved in the Menu/Favs navigation.