Creating a Pivot

  1. Pivots are based on a report. We start by taking a report with all the fields in retrieval criteria. To learn more about reports refer to the chapter titled Reportingin 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>
  2. To display the Pivot button on the report, define a pivot element after the retrieval criteria with pivot name which will be the name of the tab the pivot will be opened with

    	...
    	</Retrieval>
      
    	<Pivot>
    		<PivotName>BooksStoreCharts</PivotName>
    	</Pivot>
    </Report>
  3. A Pivot table can be accessed from a report or can have a direct reference to it from UI.
  4. To reference a pivot from a report, refer to the section titled Report from UI. The following image shows the View Pivot button on report.

  5. To have a direct reference to the Pivot, we need to 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>
  6. In the above example, we have added a web action to directly reference the Pivot. To do this we have set the panel class to be One.Pivot instead of One.Report. The following image show the icon we just created to have a direct reference:

    By clicking on the icon, we can see a blank pivot chart with the available fields in the right side upper corner called Configurator

    All non-numeric fields will be in the Dimension list and the numeric fields will be in the Select Value list.

  7. Select the needed dimensions from the list and drag and drop them into rows and columns.


  8. Select the values from the list to display the aggregated fields. By default the displayed aggregated function will be Sum.


  9. To choose a different aggregate functions, you can click on the icon next to the value.


  10. To display aggregations in the rows, move the Sigma symbol followed by Valued to the rows list in the configurator.

  11. Click on Update. Following shows the resulted pivot table.


  12. The results can be displayed in charts (line/bar etc). To view different charts, select the pop up menu item at the bottom called "Views" and pick the view to display.:
    In this example we have selected the bar chart for display: