SQL XML - Font colour in a case statement

  • Hi, 
    I've written a few SQL XML statements before where I can change the font colour and size using case, when, then colour = xxx
    But how do I code it to change colour based on the output of a case statement? What I want to add to the below statement is to say if the store like 'ZZ%' then use substring and for those rows change the colour to xxx else just return the rows in the standard colour...
    SELECT
        td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end, 
        '',

    Thank-you
    Simon

  • You've been around long enough to know to provide sample data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Tuesday, December 19, 2017 10:17 AM

    You've been around long enough to know to provide sample data and expected results.

    Drew

    He he, if you cannot spell it, don't ask it!
    😎

    ..and of course we all adjust the colour of our G-string using xml these days 😛

  • Sorry was just trying to get rid of clutter.  It's reading from a coulpe of table variables built from a basic select statement.

    DECLARE @tableHTML NVARCHAR(MAX) ;
    SET @tableHTML =
    N'<H2>Daily Sales by Store</H2>' +
    N'<table border="1">' +
      N'<tr>' +
        N'<th>Store</th>' +
        N'<th>Sales Value This Year</th>' +
        N'<th>Sales Value Last Year</th>' +
        N'<th>Percentage Difference</th>' +
      N'</tr>' +
     cast ( (
     
        SELECT
        td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end, 
        '',
        td = TY.Quantity,
        '',
        td = LY.Quantity,
        '',
        td = CASE
                WHEN TY.Quantity = 0 THEN -100
                WHEN LY.Quantity = 0 THEN 100
            ELSE convert(decimal(8,2),(((TY.Quantity-LY.Quantity)/ABS(LY.Quantity))*100)) end,
        ''
    from @DailySales TY inner join @DailySalesLY LY
    on TY.store = LY.store
    where TY.Store != 'GRAND TOTAL'

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
      N' ' ;

    The result will be a list of stores (rolled up by market) followed by a total value for that market.  There are about 10 markets and I want to highlight the rolled up total for each market in a colour or just as bold font please...Thank-you 

    Västra Frölunda 44270.50 60752.28 -27.13
    Växjö 23687.52 26935.25 -12.06
    Visby 20388.33 21608.50 -5.65
    Sweden TOTAL 2774644.46 3525880.91 -21.31

    ?CS?\m

  • Can you post the DDL for the table variables and some sample data as insert statements please?
    😎

  • Hi
    Yes ok, here's the whole statement...

    DECLARE @Result numeric(20, 4), @lastyear datetime, @StartDateLastYear DateTime, @EndDateLastYear DateTime, @datetime DATETIME, @Hour INT, @StartDateToday DateTime, @EndDateToday DateTime
        
        SET @datetime = GETDATE()
        SET @Hour = DATEPART(hh, @datetime)
        SET @lastyear = DATEADD(d,-364,@datetime)
        SET @StartDateLastYear = CONVERT(DATETIME,(CONVERT(VARCHAR(12),@lastyear,13)))
        SET @EndDateLastYear = DATEADD(hh,@Hour,@StartDateLastYear)
        SET @StartDateToday = CONVERT(DATETIME,(CONVERT(VARCHAR(12),@datetime,13)))
        SET @EndDateToday = DATEADD(hh,@Hour,@StartDateToday)

    -- Start at midnight on the given day and end at the run time hour, THIS YEAR
        
        DECLARE @DailySales TABLE (
          Store varchar (64),
            Quantity decimal (16,2))

    insert @dailysales
        select
        CASE
       WHEN (src.region) IS NOT NULL and src.store IS NULL THEN 'ZZ ' + (src.region) + ' TOTAL'
            WHEN (src.region) IS NULL and src.store IS NULL THEN 'GRAND TOTAL'
            ELSE (src.store)
      END as store,    
        cast (src.qty as numeric(16,2)) as qty
        from
        (
        select
        distinct (sg.description) as Region,
        st.description as Store,
        SUM(effective_price_per_unit*units_sold_qty) as qty
        from
            storegroup sg(readuncommitted),
            storegroupmem sgm (readuncommitted),
            store st (readuncommitted),
            sale s (INDEX = isalebytime readuncommitted),
            saleline sl (INDEX = bysaleline readuncommitted)    
        where sg.storegroupclass_code = 'COUNTRY'
         and sgm.storegroup_code = sg.storegroup_code
         and st.store_code = sgm.store_code
         and s.store_code = st.store_code
         and s.sale_status_ind = 'A'
         and s.transaction_date_time between @StartDateToday and @EndDateToday
         and st.[description] NOT LIKE '%CLOSED%'
         and sl.sale_code = s.sale_code
         and sl.saleline_type_ind = 'N'
         and sl.saleline_status_ind <> 'V'
        group by sg.description, st.description with rollup
            ) as src
    ORDER BY
      CASE
                WHEN region IS NULL
                THEN 'TOTAL' ELSE region
        END,
    store

    -- Start at midnight on the given day and end at the run time hour, LAST YEAR

        DECLARE @DailySalesLY TABLE (
        Store varchar (64),
        Quantity decimal (16,2))

    insert @dailysalesLY
        select
        CASE
       WHEN (src.region) IS NOT NULL and src.store IS NULL THEN 'ZZ ' + (src.region) + ' TOTAL'
            WHEN (src.region) IS NULL and src.store IS NULL THEN 'GRAND TOTAL'
            ELSE (src.store)
      END as store,    
        cast (src.qty as numeric(16,2)) as qty
        from
        (
        select
        distinct (sg.description) as Region,
        st.description as Store,
        SUM(effective_price_per_unit*units_sold_qty) as qty
        from
            storegroup sg(readuncommitted),
            storegroupmem sgm (readuncommitted),
            store st (readuncommitted),
            sale s (INDEX = isalebytime readuncommitted),
            saleline sl (INDEX = bysaleline readuncommitted)    
        where sg.storegroupclass_code = 'COUNTRY'
         and sgm.storegroup_code = sg.storegroup_code
         and st.store_code = sgm.store_code
         and s.store_code = st.store_code
         and s.sale_status_ind = 'A'
         and s.transaction_date_time BETWEEN @StartDateLastYear AND @EndDateLastYear
         and sl.sale_code = s.sale_code
         and sl.saleline_type_ind = 'N'
         and sl.saleline_status_ind <> 'V'
        group by sg.description, st.description with rollup
            ) as src
    ORDER BY
       CASE
                WHEN region IS NULL
                THEN 'TOTAL' ELSE region
            END,
    store

    DECLARE @tableHTML NVARCHAR(MAX) ;
    SET @tableHTML =
    N'<H2>Daily Sales by Store</H2>' +
    N'<table border="1">' +
      N'<tr>' +
        N'<th>Store</th>' +
        N'<th>Sales Value This Year</th>' +
        N'<th>Sales Value Last Year</th>' +
        N'<th>Percentage Difference</th>' +
      N'</tr>' +
     cast ( (
     
        SELECT
        td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end, 
        '',
        td = TY.Quantity,
        '',
        td = LY.Quantity,
        '',
        td = CASE
                WHEN TY.Quantity = 0 THEN -100
                WHEN LY.Quantity = 0 THEN 100
            ELSE convert(decimal(8,2),(((TY.Quantity-LY.Quantity)/ABS(LY.Quantity))*100)) end,
        ''
    from @DailySales TY inner join @DailySalesLY LY
    on TY.store = LY.store
    where TY.Store != 'GRAND TOTAL'

    FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
      N' ' ;

  • INSERT/SELECT statements do us no good, because we don't have access to the tables in the SELECT part of the statement.  You need to provide sample data as INSERT/VALUES statements.  Again, you've been around long enough to know this.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • I have your answer and will provide it once you provide consumable data.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hi Drew, sorry I'm not quite sure what you mean.  This is an insert into a table variable using a select statement.  I've provided the whole code and the result set from it. Does the output from the query I sent above not help?

  • simon.letts - Wednesday, December 20, 2017 8:22 AM

    Hi Drew, sorry I'm not quite sure what you mean.  This is an insert into a table variable using a select statement.  I've provided the whole code and the result set from it. Does the output from the query I sent above not help?

    Without the underlying tables and data, this query will not run, most of us are too busy to guess the data and construct the schema and data.
    😎

  • The problem is that we don't have access to your tables, so we cannot select from them, so we cannot insert into the table variable.  If I try to run your code, I get

    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'storegroup'.

    Many of us have links in our signatures about how to provide sample data.  I suggest you actually follow one and read it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply