No replies
nitzang
Offline
newbie
Last seen: 2 years 8 weeks ago
Timezone: GMT+2
Joined: 2014-11-25
Posts: 1
Points: 2

Hi,

In my XML code there is : <style name="NotSame" font-family="helvetica" size="9" weight="plain" text-color="red"></style>

My issue is i want to painting just the negative value in the table and not all the column like now,
If you can help my and looks the XML code to see if there is optins to paint just the negative value in table

This is the XML:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE reportdef SYSTEM "ReportDef19.dtd">
<reportdef>
    <properties>
        <id>CompareLYFCBDByLoc</id>
        <print-setup>
            <orientation>portrait</orientation>
            <margin-left>36</margin-left>
            <margin-right>36</margin-right>
            <margin-top>36</margin-top>
            <margin-bottom>36</margin-bottom>
        </print-setup>
        <document-summary>
            <name>Comparison of current, last year, budget and forecast sales</name>
            <description>First level of drill down for sales comparison</description>
            <brief-description>CompareLYFCBDByLoc</brief-description>
            <author>MICROS-CompareLYFCBDByLoc.xml</author>
            <generator>Manual</generator>
            <revision>4.1.0.1</revision>
        </document-summary>
        <!--<translation-prefix>stdRpt</translation-prefix>-->
    </properties>
    <queries>
        <query name="MainQuery" type="sql" result-type="multiple-rows" datasource="">
            <raw-sql vendor="ms-sql-2000">
                SELECT
                    a.organizationID,
                    a.locationID AS locID,
                    f.name AS locationName,
					a.businessDate,
                    SUM(a.netSalesTotal) AS netSalesTotal,
					SUM(a.lastYearChecks) AS lastYearChecks,
                    SUM(ISNULL(b.total, a.lastYearSales)) AS lastYearsTotal,
					SUM(a.checksTotal) AS checksTotal,
                    SUM(ISNULL(c.total, <img src="https://csscreator.com/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content" />) AS budgetTotal,
                    <!--SUM(ISNULL(d.total, <img src="https://csscreator.com/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content" />) AS forecastTotal,-->
					SUM(ISNULL(g.total, <img src="https://csscreator.com/sites/all/modules/smileys/packs/Roving/innocent.png" title="Innocent" alt="Innocent" class="smiley-content" />) AS budgetChecks
                FROM
                    (SELECT organizationID, businessDate, locationID, SUM(netSalesTotal) AS netSalesTotal,  SUM(lastYearSales) AS lastYearSales , SUM(numChecks) AS checksTotal,  SUM(lastYearChecks) AS lastYearChecks
                     FROM    operations_daily_total
                     WHERE organizationID @organizationid@ AND             
                                   businessDate @businessdate@ AND
                                   locationID @locationid@
                     GROUP BY organizationID, businessDate, locationID) a
 
                     LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     history_interface
                       WHERE  organizationID @organizationid@ AND             
                                     DATEADD(dd, 364, businessDate) @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 1
                       GROUP BY organizationID, businessDate, locationID) b
                     ON b.organizationID = a.organizationID AND
                           b.locationID = a.locationID AND
                           DATEADD(dd, 364, b.businessDate) = a.businessDate
 
                      LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     budget_interface
                       WHERE  organizationID @organizationid@ AND             
                                     businessDate @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 1
                       GROUP BY organizationID, businessDate, locationID) c
                     ON c.organizationID = a.organizationID AND
                           c.locationID = a.locationID AND
                           c.businessDate = a.businessDate
 
                      LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     forecast_interface
                       WHERE  organizationID @organizationid@ AND             
                                     businessDate @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 1
                       GROUP BY organizationID, businessDate, locationID) d
                     ON d.organizationID = a.organizationID AND
                           d.locationID = a.locationID AND
                           d.businessDate = a.businessDate
<!--  test read checks -->
 
                     LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     history_interface
                       WHERE  organizationID @organizationid@ AND             
                                     DATEADD(dd, 364, businessDate) @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 3
                       GROUP BY organizationID, businessDate, locationID) j
                     ON j.organizationID = a.organizationID AND
                           j.locationID = a.locationID AND
                           DATEADD(dd, 364, j.businessDate) = a.businessDate
 
                      LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     budget_interface
                       WHERE  organizationID @organizationid@ AND             
                                     businessDate @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 3
                       GROUP BY organizationID, businessDate, locationID) g
                     ON g.organizationID = a.organizationID AND
                           g.locationID = a.locationID AND
                           g.businessDate = a.businessDate
 
                      LEFT OUTER JOIN 
                       (SELECT organizationID, businessDate, locationID, SUM(total) AS total
                       FROM     forecast_interface
                       WHERE  organizationID @organizationid@ AND             
                                     businessDate @businessdate@ AND
                                     locationID @locationid@ AND
                                     type = 3
                       GROUP BY organizationID, businessDate, locationID) h
                     ON h.organizationID = a.organizationID AND
                           h.locationID = a.locationID AND
                           h.businessDate = a.businessDate
 
<!--    End test read checks-->
                      LEFT OUTER JOIN location_hierarchy_item f
                        ON f.locationID = a.locationID
 
                GROUP BY
                    a.organizationID,
                    a.locationID,
					a.businessDate,
                    f.name
            </raw-sql>
 
        </query>
    </queries>
    <parameters>
        <parameter name="organizationid" type="value" data-type="int64"></parameter>
        <parameter name="businessdate" type="list" data-type="date">
            <parameter-default default-type="system" default-value="CurrentFinancialWeek"></parameter-default>
        </parameter>
        <parameter name="locationid" type="list" data-type="integer"></parameter>
    </parameters>
	<styles>
        <style name="NotSame" font-family="helvetica" size="9" weight="plain" text-color="red"></style>
    </styles>
    <drilldowns>
    </drilldowns>
    <report>
        <frame type="flow" behavior="report-header">
            <bands>
                <band type="stripe">
                    <cell type="text" data-format="text" alignment="center" style="header_0" print-width="500">Budget report</cell>
                </band>
            </bands>
        </frame>
        <frame type="table" query-name="MainQuery">
            <groups>
                <group name="GrpOrg">
                    <group-item name="organizationID" data-type="integer"></group-item>
        <drilldown name="DrillCompareLocDate" report="CompareLYFCBDByLocDate">
            <drilldownparm type="parm" value="organizationid"></drilldownparm>
            <drilldownparm type="parm" value="locationid"></drilldownparm>
            <drilldownparm type="parm" value="businessdate"></drilldownparm>
        </drilldown>
                    <group-expr name="OrgSalesTotal" type="sum" column="netSalesTotal"></group-expr>
                    <group-expr name="OrgLastYearsTotal" type="sum" column="lastYearsTotal"></group-expr>
					<group-expr name="OrgLastYearChecks" type="sum" column="lastYearChecks"></group-expr>
                    <group-expr name="OrgBudgetTotal" type="sum" column="budgetTotal"></group-expr>
					<group-expr name="OrgBudgetChecks" type="sum" column="budgetChecks"></group-expr>
                    <group-expr name="OrgChecksTotal" type="sum" column="checksTotal"></group-expr>
					<group-calc name="OrgVarianceSales" data-type="currency">OrgSalesTotal - OrgBudgetTotal</group-calc>
					<group-calc name="OrgVariancePct" data-type="currency">(OrgSalesTotal - OrgBudgetTotal) / OrgBudgetTotal</group-calc>
					<group-calc name="OrgVarianceChecks" data-type="integer">OrgChecksTotal - OrgBudgetChecks</group-calc>
					<group-calc name="OrgVarPercentChecks" data-type="percent">(OrgChecksTotal - OrgBudgetChecks) / OrgBudgetChecks</group-calc>
					<group-calc name="OrgAverageCheck" data-type="currency">OrgSalesTotal / OrgChecksTotal</group-calc>
					<group-calc name="OrgAvgBudCheck" data-type="currency">OrgBudgetTotal / OrgBudgetChecks</group-calc>
					<group-calc name="OrgAvgVarCheck" data-type="currency">OrgAverageCheck - OrgAvgBudCheck</group-calc>
					<group-calc name="OrgAvgVarPctCheck" data-type="percent">(OrgAverageCheck - OrgAvgBudCheck)/ OrgAvgBudCheck </group-calc>
                </group>
                <detail>
                    <detail-item name="locationName" data-type="text"></detail-item>
					<detail-item name="businessDate" data-type="text"></detail-item>
                    <detail-item name="netSalesTotal" data-type="number"></detail-item>
                    <detail-item name="lastYearsTotal" data-type="number"></detail-item>
                    <detail-item name="budgetTotal" data-type="number"></detail-item>
                    <detail-item name="checksTotal" data-type="number"></detail-item>
                    <detail-item name="SalesPercent" data-type="number"></detail-item>
                    <detail-item name="BudgetPercent" data-type="number"></detail-item>
                    <detail-item name="ChecksPercent" data-type="text"></detail-item>
					<detail-item name="lastYearChecks" data-type="number"></detail-item>
					<detail-item name="BudgetChecks" data-type="number"></detail-item>
					<detail-item name="AvgChecks" data-type="number"></detail-item>
					<detail-item name="AvgVarChecks" data-type="number"></detail-item>
					<detail-item name="AvgVarPctChecks" data-type="text"></detail-item>
                    <sort-rules>
                        <sort-rule name="s1">
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s2">
                            <sort-field name="businessDate" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s3">
                            <sort-field name="netSalesTotal" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s4">
                            <sort-field name="lastYearsTotal" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s5">
                            <sort-field name="SalesPercent" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s6">
                            <sort-field name="budgetTotal" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s7">
                            <sort-field name="BudgetPercent" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s8">
                            <sort-field name="checksTotal" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                        <sort-rule name="s9">
                            <sort-field name="ChecksPercent" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s10">
                            <sort-field name="lastYearChecks" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s11">
                            <sort-field name="budgetChecks" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s12">
                            <sort-field name="AvgChecks" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s13">
                            <sort-field name="AvgVarChecks" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
						<sort-rule name="s14">
                            <sort-field name="AvgVarPctChecks" sort-type="descending"></sort-field>
                            <sort-field name="locationName" sort-type="ascending"></sort-field>
                        </sort-rule>
                    </sort-rules>
                </detail>
            </groups>
            <sort-actions default="a1">
                <sort-action name="a1">
                    <sort-action-rule group-name="detail" sort-rule="s1"></sort-action-rule>
                </sort-action>
                <sort-action name="a2">
                    <sort-action-rule group-name="detail" sort-rule="s2"></sort-action-rule>
                </sort-action>
                <sort-action name="a3">
                    <sort-action-rule group-name="detail" sort-rule="s3"></sort-action-rule>
                </sort-action>
                <sort-action name="a4">
                    <sort-action-rule group-name="detail" sort-rule="s4"></sort-action-rule>
                </sort-action>
                <sort-action name="a5">
                    <sort-action-rule group-name="detail" sort-rule="s5"></sort-action-rule>
                </sort-action>
                <sort-action name="a6">
                    <sort-action-rule group-name="detail" sort-rule="s6"></sort-action-rule>
                </sort-action>
                <sort-action name="a7">
                    <sort-action-rule group-name="detail" sort-rule="s7"></sort-action-rule>
                </sort-action>
                <sort-action name="a8">
                    <sort-action-rule group-name="detail" sort-rule="s8"></sort-action-rule>
                </sort-action>
				<sort-action name="a9">
                    <sort-action-rule group-name="detail" sort-rule="s9"></sort-action-rule>
                </sort-action>
				<sort-action name="a10">
                    <sort-action-rule group-name="detail" sort-rule="s10"></sort-action-rule>
                </sort-action>
				<sort-action name="a11">
                    <sort-action-rule group-name="detail" sort-rule="s11"></sort-action-rule>
                </sort-action>
				<sort-action name="a12">
                    <sort-action-rule group-name="detail" sort-rule="s12"></sort-action-rule>
                </sort-action>
				<sort-action name="a13">
                    <sort-action-rule group-name="detail" sort-rule="s13"></sort-action-rule>
                </sort-action>
				<sort-action name="a14">
                    <sort-action-rule group-name="detail" sort-rule="s14"></sort-action-rule>
                </sort-action>
            </sort-actions>
            <bands>
                <band type="table-column-header">
                    <cell type="text" data-format="text" alignment="left" style="header_1" sort-action="a1">Location</cell>
					<cell type="text" data-format="text" alignment="left" style="header_1" sort-action="a2">Business Date</cell>
                    <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a3">Current Sales</cell>
                 <!--   <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a3">Last Years Sales</cell>-->                   
                    <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a5">Budget Sales</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a4">Variance NIS</cell>					
                    <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a6">Variance %</cell>
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
                    <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a7">Checks</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a10">Budget Checks</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a9">Variance Qty</cell>
                    <cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a8">Variance</cell>					
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a11">Avg Check</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a12">Avg Budget Check</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a13">NIS Variance</cell>					
					<cell type="text" data-format="text" alignment="right" style="header_1" sort-action="a14">% Variance</cell>					
                </band>
               <band type="table-group-header" group-name="GrpOrg">
                    <cell type="text" data-format="text" alignment="left" style="header_1" drilldown="DrillCompareLocDate">Total</cell>
					<cell type="text" data-format="text" alignment="right"></cell>
                    <cell type="column" data-format="currency" alignment="right" decimal-places="0" style="header_1">OrgSalesTotal</cell>
                    <!--<cell type="column" data-format="currency" alignment="right" decimal-places="0" style="header_1">OrgLastYearsTotal</cell>-->					
                    <cell type="column" data-format="currency" alignment="right" decimal-places="0" style="header_1">OrgBudgetTotal</cell>					
                    <cell type="column" data-format="currency" alignment="right" decimal-places="0" >OrgVarianceSales</cell>
					<cell type="column" data-format="percent" alignment="right" decimal-places="2" >OrgVariancePct</cell>
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
                    <cell type="column" data-format="integer" alignment="right" style="header_1">OrgChecksTotal</cell>                    
					<cell type="column" data-format="integer" alignment="right" style="header_1">OrgBudgetChecks</cell>                    
					<cell type="column" data-format="integer" alignment="right" >OrgVarianceChecks</cell>
                    <cell type="column" data-format="percent" alignment="right" decimal-places="2" >OrgVarPercentChecks</cell>
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
					<cell type="column" data-format="currency" alignment="right" decimal-places="1" style="header_1">OrgAverageCheck</cell>
					<cell type="column" data-format="currency" alignment="right" decimal-places="1" style="header_1">OrgAvgBudCheck</cell>					
					<cell type="column" data-format="currency" alignment="right" decimal-places="2" >OrgAvgVarCheck</cell>
					<cell type="column" data-format="percent" alignment="right" decimal-places="2" >OrgAvgVarPctCheck</cell>
                </band>  
                <band type="table-body">
                    <cell type="column" data-format="text" alignment="left" print-width="100" indent-level="3">locationName</cell>
					<cell type="column" data-format="date" alignment="left" print-width="100" indent-level="3">businessDate</cell>
                    <cell type="column" data-format="currency" alignment="right" decimal-places="0" print-width="70">netSalesTotal</cell>
                   <!-- <cell type="column" data-format="currency" alignment="right" decimal-places="0" print-width="80">lastYearsTotal</cell>                    -->
                    <cell type="column" data-format="currency" alignment="right" decimal-places="0" print-width="70">budgetTotal</cell>					
<!--					<cell type="expr" data-format="currency" alignment="right" decimal-places="0" suppress-zeroes="true" print-width="60" name="SalesPercent" style="notSame">(netSalesTotal - budgetTotal)</cell>-->
					<cell type="expr" data-format="currency" alignment="right" decimal-places="0" suppress-zeroes="true" print-width="60" name="SalesPercent" >(netSalesTotal - budgetTotal)</cell>
                    <cell type="expr" data-format="percent" alignment="right" decimal-places="2" suppress-zeroes="true" print-width="60" name="AverageSales" >(netSalesTotal - budgetTotal) / budgetTotal</cell>				
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
                    <cell type="column" data-format="integer" alignment="right" print-width="70">checksTotal</cell>
					<!--<cell type="column" data-format="integer" alignment="right" print-width="70">LastYearChecks</cell>-->
					<cell type="column" data-format="integer" alignment="right" print-width="70">BudgetChecks</cell>
					<cell type="expr" data-format="integer" alignment="right" decimal-places="0" suppress-zeroes="true" print-width="60" name="ForecastPercent" >(checksTotal - BudgetChecks)</cell>
                    <cell type="expr" data-format="percent" alignment="right" decimal-places="2" suppress-zeroes="true" print-width="60" name="AveragePercent" >(checksTotal - BudgetChecks) / BudgetChecks</cell>				
					<cell type="text" data-format="text" alignment="right" print-width="2" >|</cell>
					<cell type="expr" data-format="currency" alignment="right" decimal-places="1" suppress-zeroes="true" print-width="60" name="BudgetPercent" >netSalesTotal / checksTotal</cell>				
					<cell type="expr" data-format="currency" alignment="right" decimal-places="1" suppress-zeroes="true" print-width="60" name="AvgCheck" >budgetTotal / BudgetChecks</cell>				
					<cell type="expr" data-format="currency" alignment="right" decimal-places="2" suppress-zeroes="true" print-width="60" name="AvgVarCheck" >(netSalesTotal / checksTotal)-(budgetTotal / BudgetChecks)</cell>				
					<cell type="expr" data-format="percent" alignment="right" decimal-places="2" suppress-zeroes="true" print-width="60" name="AvgVarPercent" >((netSalesTotal / checksTotal)-(budgetTotal / BudgetChecks)) / (budgetTotal / BudgetChecks)</cell>				
                </band>
            </bands>
        </frame>
        <frame type="grid">
            <bands>
                <band type="stripe">
                    <cell type="text" data-format="text" alignment="left" print-width="540">CompareLYFCBDNote</cell>
                </band>
            </bands>
        </frame>
    </report>
</reportdef>