locked
dynamic columns RRS feed

  • Question

  • I am facing a problem with the SQL reporting Service and also am pretty much new to the reporting service…

    I need to create a yearly report from a table called say tblData. It has rows like 
    [items] [Amount] [Date].
    Item1 100 05/2005
    Item1 110 06/2005
    Item2 200 05/2005
    Item2 230 06/2005

    Now I want the report like 
    [Items] [First month] [Second month] etc...

    The data for the first month, second month etc will be the [amount] for that month.
    ie the report rows should be like

    [item] [05/2005] [06/2005]

    item 1 100 110 
    item 2 200 230 

    In the report the month columns will be dynamic according to the data in the tblData table...

    Now how should I implement this?

    First I though of writing up a Stored Procedure so that the sp returns the formatted data (i.e. a returning a table having the having the dynamic columns). But how can I create the report using these dynamic columns in the reporting service?
    Friday, October 26, 2012 3:52 PM

Answers

  • For that you use a matrix, not a table.

    Check this: Adding a Matrix (Reporting Services)

    1. Add the Item column on the Rows cell.
    2. Add the Month column on the Column cell.
    3. Add the Value column on the Data cell.

    • Edited by irusul Friday, October 26, 2012 4:05 PM
    • Proposed as answer by Charlie Liao Monday, October 29, 2012 3:28 AM
    • Marked as answer by Furamikea Thursday, November 1, 2012 1:19 PM
    Friday, October 26, 2012 4:03 PM