SQL Server Performance

Calculation On Matrix Columns

Discussion in 'SQL Server 2005 Reporting Services' started by shivi, Nov 16, 2007.

  1. shivi New Member

    Hi,
    I am creating a Matrix report and I need to perform few calculations on the columns that are dynamically created.
    customer/yearJan-07Feb-07Mar-07Apr-07
    a123223245
    b24312423 53
    c1355 I need to create an additional column where I need to perform few calculations (subtraction and percentage) on the values last 2 columns alone.. But in a matrix I cant refer to last 2 columns directly.
    Please help.
    Shivi
  2. jagblue New Member

    Hi Shivi,
    In seect statment you can add one more column
    SELECT [customer/year],[Jan-07],[Feb-07],[Mar-07],[Apr-07],[Mar-07])/100 AS Percentage
    Or if you give us brif Idea how you create matrix then it will be more easy to anser your question
    Thnaks
    Jagblue
  3. shivi New Member

    Thanks for your quick reponse.. Actually my table is
    CustomerNameProcessingMonthYearcnt
    A Jan-20071
    A Feb-200723
    A Mar-2007223
    A Apr-2007425
    B Jan-20072
    B Feb-20074212
    B Mar-2007423
    B Apr-200753
    C Jan-20071
    C Feb-20073
    C Mar-20075
    C Apr-20075after forming the matrix I get the grid shown in my first posting.. when the matrix is created in reporting services in the layout , you cannot specify last 2 columns as they are created dynamically while the report is created..
    Here I need to perform the calculations in the cnt column for the latest 2 months..
    Thanks,
  4. jagblue New Member

    Hi
    As from the heading of the Post I assume your database is SQL 2005
    If that's true then try this QuerySELECT
    * INTO #Test FROM(SELECT CustomerName, ProcessingMonthyear
    ,cntFROM
    dbo.TestTable) pPIVOT(SUM (cnt) FOR ProcessingMonthyear IN ([Jan-07],[Feb-07],[Mar-07],[Apr-07])) AS pvt
    SELECT *,([Apr-07] - [Mar-07]) As Subtraction ,([Apr-07] - [Mar-07])/100 as Percentage FROM #Test
    DROP TABLE #Test
    Thanks
  5. shivi New Member

    We are connecting to Oracle.. which doesn't have any easy command like Pivot and Unpivot and there where the problem started,

Share This Page