The SalesLogix Sales Dashboard – XML Details

 


The screens that comprise the SalesLogix Sales Dashboard all utilize separate XML data definitions to determine what data should be used rendering the GUI of the Dashboard.  If you look at any of the Dashboard forms you will see a line of code in the AXFormOpen sub-routine that defines what XML file is used by the screen.  For the default screens in the Dashboard, the drill down analysis screens also utilize the same XML files.



Sub AXFormOpen(Sender)


   frmCS.HelpFile = cHELP_FILENAME
   frmCS.HelpContext = 70064
   gstrContentSetXML = “Win Rate XML” ‘DNL
   gstrFamily = “Sales Dashboard” ‘DNL
   gblnIsInit = false ‘ For Period Parameters
   SetForm(frmCS)


You will notice that both the XML name and Family are specified above.  The XML definitions are actually stored in the PLUGIN table in the database as “Text” plugins (PLUGIN.TYPE = 34).  The Sales Dashboard utilizes the GetPluginText function of the Application.BasicFunctions object in SalesLogix to return the XML stored in the plugin table in plain text.  This function requires passing in the name of the plugin and the family of the plugin, separated by a colon Application.BasicFunctions.GetPluginText(FAMILY:NAME).


Lets take a look at a one of the existing XML files, the Win Rate XML.



<?xml version=”1.0″ ?>
<!–
    Configuration file for Content Sets.
    Copyright 2005, Sage Software


–>
<Dashboard version=”1″>
        <ContentSets>
                <ContentSet Type=”SUMMARY”>
                        <Name>Win Rate</Name>
                        <BaseTable>Opportunity</BaseTable>
                        <SMaxRows>5</SMaxRows>
                        <LMaxRows>5</LMaxRows>
                        <CalculateOtherRow>T</CalculateOtherRow>
                        <Drill>
                                <Detail IsActive=”T”>
                                        <Family>Sales Dashboard</Family>
                                        <PluginName>Opportunity Detail Analysis View</PluginName>
                                </Detail>
                                <Summary IsActive=”T”>
                                        <Family>Sales Dashboard</Family>
                                        <PluginName>Win Rate Analysis View</PluginName>
                                </Summary>
                        </Drill>
                        <ColorTheme ShowColorColumn=”F”>
                                <Color Red=”55″ Green=”108″ Blue=”58″/>
                                <Color Red=”61″ Green=”86″ Blue=”124″/>
                                <Color Red=”124″ Green=”58″ Blue=”143″/>
                                <Color Red=”172″ Green=”88″ Blue=”60″/>
                                <Color Red=”209″ Green=”214″ Blue=”56″/>
                                <Color Red=”55″ Green=”108″ Blue=”58″/>
                                <Color Red=”61″ Green=”86″ Blue=”124″/>
                                <Color Red=”124″ Green=”58″ Blue=”143″/>
                                <Color Red=”172″ Green=”88″ Blue=”60″/>
                                <Color Red=”209″ Green=”214″ Blue=”56″/>
                        </ColorTheme>
                        <PeriodField>ACTUALCLOSE</PeriodField>
                        <PeriodFieldDisplayName>Close Date</PeriodFieldDisplayName>
                        <Periods>
                                 <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>FYTD</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>FQTD</PeriodName>
                                </Period>
                                <Period IsDefault=”T” IsActive=”T”>
                                        <PeriodName>FMTD</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>YTD</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>QTD</PeriodName>
                                </Period>
                                <Period IsDefault=”T” IsActive=”T”>
                                        <PeriodName>MTD</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>L90D</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>L60D</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>L30D</PeriodName>
                                </Period>
                                <Period IsDefault=”F” IsActive=”T”>
                                        <PeriodName>RANGE</PeriodName>
                                </Period>
                        </Periods>
                        <Selects>
                                <Select Type=”LISTBY” Sort=”F” OrderBy=”NONE” ShowFooter=”F” FooterType=”NONE”>
                                        <FieldName></FieldName>
                                        <AliasName>NAME</AliasName>
                                        <Table></Table>
                                        <DisplayName>Name</DisplayName>
                                        <ColWidth>85</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Text</FormatType>
                                        <HyperLink>T</HyperLink>
                                        <Custom></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”T” OrderBy=”DESC” ShowFooter=”T” FooterType=”AVG”>
                                        <FieldName>WINRATE</FieldName>
                                        <AliasName>WINRATE</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Win Rate</DisplayName>
                                        <ColWidth>85</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Percent</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”AVG”><![CDATA[CAST((SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN 1.0 ELSE 0.0 END)/COUNT(*))AS FLOAT)* 100.00]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”F” OrderBy=”NONE” ShowFooter=”T” FooterType=”SUM”>
                                        <FieldName>WINS</FieldName>
                                        <AliasName>WINS</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Wins</DisplayName>
                                        <ColWidth>35</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Integer</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN 1 ELSE 0 END))]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”F” OrderBy=”NONE” ShowFooter=”T” FooterType=”SUM”>
                                        <FieldName>LOSSES</FieldName>
                                        <AliasName>LOSSES</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Losses</DisplayName>
                                        <ColWidth>35</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Integer</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN 1 ELSE 0 END))]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”AVG”>
                                        <FieldName>AMTWINRATE</FieldName>
                                        <AliasName>AMTWINRATE</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Amt. Win Rate</DisplayName>
                                        <ColWidth>85</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Percent</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”AVG”><![CDATA[CAST(1.0/(1.0 + (SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN BT.SALESPOTENTIAL ELSE 0.0 END)/(CASE WHEN SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END) = 0.0 THEN 1.0 ELSE SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END) END)))AS FLOAT)*100.00]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”SUM”>
                                        <FieldName>ACTUALAMOUNT</FieldName>
                                        <AliasName>WONAMOUNT</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Won Amount</DisplayName>
                                        <ColWidth>85</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Currency</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END))]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                                <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”SUM”>
                                        <FieldName>SALESPOTENTAIL</FieldName>
                                        <AliasName>LOSTMOUNT</AliasName>
                                        <Table>Opportunity</Table>
                                        <DisplayName>Lost Amount</DisplayName>
                                        <ColWidth>85</ColWidth>
                                        <Align>Left</Align>
                                        <FormatType>Currency</FormatType>
                                        <HyperLink>F</HyperLink>
                                        <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN BT.SALESPOTENTIAL ELSE 0.0 END))]]></Custom>
                                        <Joins JID=””></Joins>
                                </Select>
                        </Selects>
                        <BaseFilters>
                                <BaseFilter BeginBracket=”F” Operator=”3″ EndBracket=”F” AndOr=”0″>
                                        <FieldName>CLOSED</FieldName>
                                        <Value>T</Value>
                                </BaseFilter>
                        </BaseFilters>
                        <Parameters>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”T” IsActive=”T”>
                                        <ParamName>Opportunity Status</ParamName>
                                        <AliasName>STATUS</AliasName>
                                        <Table>Opportunity</Table>
                                        <FieldName>STATUS</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo></DrillTo>
                                        <Joins JID=””></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”T” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Account</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Account</Table>
                                        <FieldName>Account</FieldName>
                                        <DefaultValue  Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo></DrillTo>
                                        <Joins JID=”J1″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”T” Type=”USER” EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Account Manager</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>UserInfo</Table>
                                        <FieldName>UserName</FieldName>
                                        <DefaultValue  Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>Opportunity Type</DrillTo>
                                        <Joins JID=”J2″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Account Owner</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>SECCODE</Table>
                                        <FieldName>SECCODEDESC</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>Opportunity Type</DrillTo>
                                        <Joins JID=”J3″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”T” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Opportunity</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Opportunity</Table>
                                        <FieldName>Description</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>Account</DrillTo>
                                        <Joins JID=””></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Opportunity Type</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Opportunity</Table>
                                        <FieldName>Type</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>Country</DrillTo>
                                        <Joins JID=””></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>State/Prov</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Address</Table>
                                        <FieldName>State</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>Account Manager</DrillTo>
                                        <Joins JID=”J4″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
                                        <ParamName>Country</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Address</Table>
                                        <FieldName>Country</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>State/Prov</DrillTo>
                                        <Joins JID=”J4″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”F” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”F”>
                                        <ParamName>Product</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Product</Table>
                                        <FieldName>Name</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>State/Prov</DrillTo>
                                        <Joins JID=”J5″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                                <Parameter IsFilter=”T” IsListBy=”F” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”F”>
                                        <ParamName>Product Family</ParamName>
                                        <AliasName>Name</AliasName>
                                        <Table>Product</Table>
                                        <FieldName>Family</FieldName>
                                        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
                                        <DrillTo>State/Prov</DrillTo>
                                        <Joins JID=”J5″></Joins>
                                        <Selects></Selects>
                                </Parameter>
                        </Parameters>
                        <JoinGroups>
                                <Joins JID=”J1″>
                                        <Join>
                                                <ParentTable>Opportunity</ParentTable>
                                                <ParentField>AccountID</ParentField>
                                                <ChildTable>Account</ChildTable>
                                                <ChildField>AccountID</ChildField>
                                        </Join>
                                </Joins>
                                <Joins JID=”J2″>
                                        <Join>
                                                <ParentTable>Opportunity</ParentTable>
                                                <ParentField>AccountManagerID</ParentField>
                                                <ChildTable>UserInfo</ChildTable>
                                                <ChildField>UserID</ChildField>
                                        </Join>
                                </Joins>
                                <Joins JID=”J3″>
                                        <Join>
                                                <ParentTable>Opportunity</ParentTable>
                                                <ParentField>AccountID</ParentField>
                                                <ChildTable>Account</ChildTable>
                                                <ChildField>AccountID</ChildField>
                                        </Join>
                                        <Join>
                                                <ParentTable>Account</ParentTable>
                                                <ParentField>SECCODEID</ParentField>
                                                <ChildTable>SECCODE</ChildTable>
                                                <ChildField>SECCODEID</ChildField>
                                        </Join>
                                </Joins>
                                <Joins JID=”J4″>
                                        <Join>
                                                <ParentTable>Opportunity</ParentTable>
                                                <ParentField>AccountID</ParentField>
                                                <ChildTable>Account</ChildTable>
                                                <ChildField>AccountID</ChildField>
                                        </Join>
                                        <Join>
                                                <ParentTable>Account</ParentTable>
                                                <ParentField>AddressID</ParentField>
                                                <ChildTable>Address</ChildTable>
                                                <ChildField>AddressID</ChildField>
                                        </Join>
                                </Joins>
                                <Joins JID=”J5″>
                                        <Join>
                                                <ParentTable>Opportunity</ParentTable>
                                                <ParentField>OpportunityID</ParentField>
                                                <ChildTable>Opportunity_Product</ChildTable>
                                                <ChildField>OpportunityID</ChildField>
                                        </Join>
                                        <Join>
                                                <ParentTable>Opportunity_Product</ParentTable>
                                                <ParentField>ProductID</ParentField>
                                                <ChildTable>Product</ChildTable>
                                                <ChildField>ProductID</ChildField>
                                        </Join>
                                </Joins>
                        </JoinGroups>
                </ContentSet>
        </ContentSets>
</Dashboard>


While the XML is quite long, it is logically broken into various parts.  Lets examine them starting from the top.  First off, we have a node section called <ContentSet>  this is the primary node that contains all of the details about the XML. 



Main Attributes


At the start we have some specific about how the XML is used on the screen:



<ContentSet Type=”SUMMARY”>
                        <Name>Win Rate</Name>
                        <BaseTable>Opportunity</BaseTable>
                        <SMaxRows>5</SMaxRows>
                        <LMaxRows>5</LMaxRows>
                        <CalculateOtherRow>T</CalculateOtherRow>


The Name attribute is used on things like the Excel export title.


The BaseTable is used to form the SQL statements that will be used as the basis of the recordsets that display.  It also is used to determine the primary key which is used to act as a basis for hyperlinking to the entity from the Dashboard detail drill-down view.


The SMaxRows attribute is used for the primary dashboard screen contents.  It determines how many rows are displayed be defualt.


The LMaxRows is the attribute used in the drill-down screen off the main screen.  It determines how many rows of data show by default on the analysis screens  You could for instance set the first screen to default to 7 and the drill down to default to return 10 rows.


The CalculateOtherRow attribute determines wether or not the “Other” row shows on the Dashboard screen.  This row is an aggregate summary of all of the data that is not showing on the standard data grid rows.


Next up we have the <Drill> Node.  This node defines what screens open when the user drills into the top level Dashboard screen.



<Drill>
     <Detail IsActive=”T”>
           <Family>Sales Dashboard</Family>
           <PluginName>Opportunity Detail Analysis View</PluginName>
     </Detail>
     <Summary IsActive=”T”>
            <Family>Sales Dashboard</Family>
            <PluginName>Win Rate Analysis View</PluginName>
     </Summary>
</Drill>


The Detail sub-node defines the lowest level screen that can be drilled into.


The Summary sub-node defines the first level of drill-down off the main Dashboard view.



Themes


Next up is the <ColorTheme> node which defines the various colors to be used on this dashboard screen, based on entries using an RGB color scheme.



<ColorTheme ShowColorColumn=”F”>
        <Color Red=”55″ Green=”108″ Blue=”58″/>
        <Color Red=”61″ Green=”86″ Blue=”124″/>
        <Color Red=”124″ Green=”58″ Blue=”143″/>
        <Color Red=”172″ Green=”88″ Blue=”60″/>
        <Color Red=”209″ Green=”214″ Blue=”56″/>
        <Color Red=”55″ Green=”108″ Blue=”58″/>
        <Color Red=”61″ Green=”86″ Blue=”124″/>
        <Color Red=”124″ Green=”58″ Blue=”143″/>
        <Color Red=”172″ Green=”88″ Blue=”60″/>
        <Color Red=”209″ Green=”214″ Blue=”56″/>
</ColorTheme>


The Color attributes defined here are placed into an array and used in the charts on the GUI.  The ShowColorColumn attribute, if set to “T”, will display a color column in the data grid to show the corresponding color code for each line item.



Period Details


Next is the PeriodField attribute.  This defines what field from the base table is used for the date range restriction on the screens.



<PeriodField>ACTUALCLOSE</PeriodField>
<PeriodFieldDisplayName>Close Date</PeriodFieldDisplayName>


The PeriodField is the date field from the primary table to use as the filter.


The PeriodFieldDisplayName is the alias that will display when exporting the Dashboard data to Excel.


Next we have the Periods node with various Period sub-nodes.  these define the choices in the Date Range filter on teh GUI.



<Periods>
         <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>FYTD</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>FQTD</PeriodName>
        </Period>
        <Period IsDefault=”T” IsActive=”T”>
                <PeriodName>FMTD</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>YTD</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>QTD</PeriodName>
        </Period>
        <Period IsDefault=”T” IsActive=”T”>
                <PeriodName>MTD</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>L90D</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>L60D</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>L30D</PeriodName>
        </Period>
        <Period IsDefault=”F” IsActive=”T”>
                <PeriodName>RANGE</PeriodName>
        </Period>
</Periods>


The <Period> node has three attributes IsDefault which defines which value is used as the default date range, IsActive which, if set to “F”, will not display the choice in the date range list, and PeriodName which has a special meaning to the Content Set Support script to denote the various date ranges available to filter by.



Selects


The Selects node is next. The Select sub-nodes define the data points that are available to be used in the GUI.  The Select nodes are used to create the SQL Select statement that is then used to build the recordsets used by the charts and graphs of the Dashboard.



<Selects>
        <Select Type=”LISTBY” Sort=”F” OrderBy=”NONE” ShowFooter=”F” FooterType=”NONE”>
                <FieldName></FieldName>
                <AliasName>NAME</AliasName>
                <Table></Table>
                <DisplayName>Name</DisplayName>
                <ColWidth>85</ColWidth>
                <Align>Left</Align>
                <FormatType>Text</FormatType>
                <HyperLink>T</HyperLink>
                <Custom></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”T” OrderBy=”DESC” ShowFooter=”T” FooterType=”AVG”>
                <FieldName>WINRATE</FieldName>
                <AliasName>WINRATE</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Win Rate</DisplayName>
                <ColWidth>85</ColWidth>
                <Align>Left</Align>
                <FormatType>Percent</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”AVG”><![CDATA[CAST((SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN 1.0 ELSE 0.0 END)/COUNT(*))AS FLOAT)* 100.00]]></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”F” OrderBy=”NONE” ShowFooter=”T” FooterType=”SUM”>
                <FieldName>WINS</FieldName>
                <AliasName>WINS</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Wins</DisplayName>
                <ColWidth>35</ColWidth>
                <Align>Left</Align>
                <FormatType>Integer</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN 1 ELSE 0 END))]]></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”F” OrderBy=”NONE” ShowFooter=”T” FooterType=”SUM”>
                <FieldName>LOSSES</FieldName>
                <AliasName>LOSSES</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Losses</DisplayName>
                <ColWidth>35</ColWidth>
                <Align>Left</Align>
                <FormatType>Integer</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN 1 ELSE 0 END))]]></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”AVG”>
                <FieldName>AMTWINRATE</FieldName>
                <AliasName>AMTWINRATE</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Amt. Win Rate</DisplayName>
                <ColWidth>85</ColWidth>
                <Align>Left</Align>
                <FormatType>Percent</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”AVG”><![CDATA[CAST(1.0/(1.0 + (SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN BT.SALESPOTENTIAL ELSE 0.0 END)/(CASE WHEN SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END) = 0.0 THEN 1.0 ELSE SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END) END)))AS FLOAT)*100.00]]></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”SUM”>
                <FieldName>ACTUALAMOUNT</FieldName>
                <AliasName>WONAMOUNT</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Won Amount</DisplayName>
                <ColWidth>85</ColWidth>
                <Align>Left</Align>
                <FormatType>Currency</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END))]]></Custom>
                <Joins JID=””></Joins>
        </Select>
        <Select Type=”CUSTOM” Sort=”F” OrderBy=”DESC” ShowFooter=”T” FooterType=”SUM”>
                <FieldName>SALESPOTENTAIL</FieldName>
                <AliasName>LOSTMOUNT</AliasName>
                <Table>Opportunity</Table>
                <DisplayName>Lost Amount</DisplayName>
                <ColWidth>85</ColWidth>
                <Align>Left</Align>
                <FormatType>Currency</FormatType>
                <HyperLink>F</HyperLink>
                <Custom Type=”SUM”><![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Lost}’ THEN BT.SALESPOTENTIAL ELSE 0.0 END))]]></Custom>
                <Joins JID=””></Joins>
        </Select>
</Selects>


The Select nodes can be either Custom or a straight display of a field.  The Type attribute defines wether or not the field is displayed as is, or wether a custom statement of math function is to be applied. The types of custom fields can be “Normal”,”Sum”,”Count”,”Avg”,”Custom”,”ListBy”, “NCount”.  The Sort attribute determines if the data is to be sorted when returned.  If Sort=”T” then the OrderBy attribute determines the sort order, either “DESC” or “ASC”.  If Sort=”F” OrderBy should be “NONE”.  The other primary attributes are ShowFooter, which determines if the column has a footer displayed below the grid.  The FooterType determines how the data is summarized on the footer bar.  The choices for this are “NONE”, “SUM”, “COUNT”, “AVG”, “MIN”, “MAX”.


Now lets take a look at the remaining attributes of a Select node.


Fieldname defines the database field this data point refers to.  If you are using a field from the base table than you can simply express the field name.  If it is coming from another table you may need to preface the field with the table alias created in the from statement.  I will talk more about this in a later blog post.


AliasName allows you to set an alias for the FieldName used, in the case you use the same FieldName more than once..  The AliasName must be unique among all of the Select nodes.


Table identifies what table the FieldName is coming from.  This could either be the main table or an ancillary table.  If it is not the main table you will also need to specify the Join attribute, discussed in a moment.


DisplayName defines how the field will display as column headers or when exporting to Excel.


ColWidth defines the width of the data column in a grid.


Align should define how the data should align in a column.  This attribute is not currently implemented and the value set here will not determine alignment.


FormatType defines how the data should be formatted.  Available choices include “Text”, “Currency”, “Numeric”, “Integer”, “Percent”, “Date”, “DateTime”, “Time”.


Hyperlink defines if the column should be a hyperlink in the datagrid, allowing you to drill into the analysis view based on the data element displayed. Set to either “T” or “F”.


Custom defines, if the <Select Type = “CUSTOM”, what kind of custom field it is.  Again the choices for this include “Normal”,”Sum”,”Count”,”Avg”,”Custom”,”ListBy”, “NCount”  The custom field also takes a custom SQL statement that is used to build the custom SQL select column in the SQL statement.  This attribute would look something like:



<![CDATA[(SUM(CASE BT.STATUS WHEN ‘{Closed – Won}’ THEN BT.ACTUALAMOUNT ELSE 0.0 END))]]>


The final attribute is the Join.  This defines which Join should be used to link the Table attribute specified to the main base table of the XML file.  If the field used in the select statement is in the base table then this attribute can be left empty:



<Joins JID=””></Joins>


Otherwise you can specify a JoinID from the Join Node discussed later:



<Joins JID=”J6″></Joins>



Base Filters


The next node is the BaseFilters:



<BaseFilters>
        <BaseFilter BeginBracket=”F” Operator=”3″ EndBracket=”F” AndOr=”0″>
                <FieldName>CLOSED</FieldName>
                <Value>T</Value>
        </BaseFilter>
</BaseFilters>


The base filters <BaseFilters> node is not currently implemented in the standard screen logic.  This area would appear to allow you to set an overall filter for the screen, however while this data is accessed by the Content Set  Support script, in the GetBaseFilter public function, this function is not actually ever used.  Also problems within this function prevent a properly formed Where clause from being constructed.  Do not use this node, instead use the <Parameters> node discussed next.


Parameters



The Parameters node determines what filters are applied by default and also what choices the user has to filter by or display data by.  The Parameter node has several attributes:



<Parameter IsFilter=”T” IsListBy=”T” IsDefault=”F” Type=”1″ EnableGroup=”F” ReadOnly=”F” IsActive=”T”>
        <ParamName>Account Owner</ParamName>
        <AliasName>Name</AliasName>
        <Table>SECCODE</Table>
        <FieldName>SECCODEDESC</FieldName>
        <DefaultValue Localize=”F”>(ALL)</DefaultValue>
        <DrillTo>Opportunity Type</DrillTo>
        <Joins JID=”J3″></Joins>
        <Selects></Selects>
</Parameter>


IsFilter – Determines if the field named is used to filter data.  If so this is set into the where clause used to build the datasets.


IsListBy – Determines if this parameter is available in the list of filters on the analysis screenand if it is a selectable filter on screen.


IsDefault – Determines what the default parameter is for showing what data in the List By drop down.


Type– can be 0-4.  Denotes the type of filter allowed.  0 = All, 1 = Single value, 2 = Multiple Values, 3 = Unknown, 4 = Group.


EnableGroup – Determines if the user is able to select from a group of records rather than a single entity.  This gives access in the filter area to allow you to select a group like “Latest Accounts”.


ReadOnly– Determines if the filter is editable or not.  Set to “T” if you wish to have a filter that can not be disabled.


IsActive– If set to “F” this filter is not available for us in the GUI.


The following are the sub-nodes under the Parameter node.


ParamName – this is what the user sees on the GUI as far as what the filter is called.


AliasName – U


Table – The table that the FieldName used in the Parameter is from.


FieldName – the name of the field that will be used as the displayed data element or filter.


DefaultValue – What the default filter or selection value is.  There is also an attribute for wether or not to allow SalesLogix localization on the default value.


DrillTo – Tells the system what to drill into on the analysis view when the specified Parameter is the currently displayed data set.


Joins identifies what Join from the Join area is used to link the Table specified to the main Table of the screen.


Selects – I am not sure about this functionality. It appears to allow you to add additional select statements when this particular data element is chosen.  The functionality of attribute this is not used in the standard screens and I was unable to use it successfully.


Joins


The final section of the XML file is the Joins Node within the JoinGroups node.  In this node you can define how the various tables used in the Selects and Parameters nodes are tied together.  This section essentially builds the From clause used with the GUI’s datasets.



<Joins JID=”J5″>
        <Join>
                <ParentTable>Opportunity</ParentTable>
                <ParentField>OpportunityID</ParentField>
                <ChildTable>Opportunity_Product</ChildTable>
                <ChildField>OpportunityID</ChildField>
        </Join>
        <Join>
                <ParentTable>Opportunity_Product</ParentTable>
                <ParentField>ProductID</ParentField>
                <ChildTable>Product</ChildTable>
                <ChildField>ProductID</ChildField>
        </Join>
</Joins>


Joins are identified by an ID attribute.  Within the join you can then define how the tables relate to the main table of the XML file.  In the case where you need to drill down a couple of tables you can use the format as shown above.


Note that you can have a max of 9 joins in the Joins node.  The Content Set Support script will not handle Join IDs higher than J9.

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Hi Kris

    How would you change the Sales Dashboard (Pipeline Status) to only show opportunities that have a tick next to “add to forecast”.

    By default the system shows all opportunities if it is on the forecast or not.

    Thank you in advance

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!