SQL Server Performance

Matrix report issuw

Discussion in 'SQL Server 2005 Reporting Services' started by abhi81, Feb 3, 2007.

  1. abhi81 New Member

    Hello,
    I developed a report using the new matrix control. In the report I have 2
    rowsgroups(monthname of the orderdate as parent,the orderdate as the 2nd one)
    and 1 columngroup(year of the orderdate). In the details field i have 4
    column fields Ordercount,Order valuee in $'s, Shipped value in $'s,Total
    Shipped value in $'s.

    In my dataset i have restricted it for 2 years 2005 and 2006. the report
    runs as desired breaking the month datewise and showing the values of each
    date for the 2 years side by side.

    The problem however is that i need to add a new column at the end,i.e.,
    after the value for 2007 has been rendered.

    In this column i need to find out the difference between the ordercount in
    2007 as compared to 2006 for each date monthwise.

    Can any one please tell me how can i access the ordercount values for a
    particular date for the 2 year values and put the difference in the new
    column at the end.

    The report shud look something like this

    2006 2007 (ordcnt)
    January ordercnt order($) ship($) tot($) ordercnt order($) ship($) tot($)| Change
    1/1/2007 2 25 50 70 15 56 400 500 13
    1/2/2007 3 100 150 200 45 566 456 456 42
    .
    .
    .
    1/31/2007 6 300 430 600 56 356 567 563 50


    I just need to plaace the last column in the matrix report. Any help will be greatly appreciate.



    thanks
  2. satya Moderator

    Using SSIS in BIDS you can set a pre-compiled value by taking the required columns, if you are using Analysis Services then there are Measures that will take care such pre-defined values.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page