Tue, 2014-11-25 09:56
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://www.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://www.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://www.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 @[email protected] AND businessDate @[email protected] AND locationID @[email protected] GROUP BY organizationID, businessDate, locationID) a LEFT OUTER JOIN (SELECT organizationID, businessDate, locationID, SUM(total) AS total FROM history_interface WHERE organizationID @[email protected] AND DATEADD(dd, 364, businessDate) @[email protected] AND locationID @[email protected] 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 @[email protected] AND businessDate @[email protected] AND locationID @[email protected] 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 @[email protected] AND businessDate @[email protected] AND locationID @[email protected] 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 @[email protected] AND DATEADD(dd, 364, businessDate) @[email protected] AND locationID @[email protected] 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 @[email protected] AND businessDate @[email protected] AND locationID @[email protected] 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 @[email protected] AND businessDate @[email protected] AND locationID @[email protected] 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>
