locked
SSRS 2005 - Dynamic Columns or Matrix Without Aggregates? RRS feed

  • Question

  • I need to produce a Training Matrix Report with columns showing the Course Name and rows showing the names of the Personnel who will complete the courses.

    The intersection of the two will show coloured cells depending on whether the course has been completed or is overdue.

    I've tried creating a report using a matrix in SSRS 2005, but I can't get it to work because SSRS says: "The BackgroundColor expression for the textbox ‘dateValue’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling." Even if I try to print the date inside the cell, it doesn't display.

    I can't think of a way of using an aggregate value for the date.

    Is there another way of doing this in SSRS? The possible number of courses is unlimited - is it possible to create a report containing a table and programmatically expand the number of columns in the table?

    Regards.

    Matt.
    Saturday, September 12, 2009 9:44 AM

All replies

  • Can you post your code?


    AE, PMP, MCTS
    Saturday, September 12, 2009 7:20 PM
  • I'm using the following query to get the data:

    SELECT RTRIM(tna.forenames) + ' ' + RTRIM(tna.surname) as fullname, ctna.modname, MAX(ctna.req_by) as due_date
    FROM tna, ctna
    WHERE tna.idtna = ctna.idtna
    GROUP BY RTRIM(tna.forenames) + ' ' + RTRIM(tna.surname), modname

    I then add a matrix to the report, and add the two grouped fields to the top and side of the matrix.

    However, adding the date to the data field in the matrix gives me the error quoted above.

    Saturday, September 12, 2009 8:23 PM
  • I changed your code to use Joins(since I'm used to 2005). I tested the code and I got an out of memory error because when you put the due date in the datafield, the fullname is spanning a lot of columns. Here is the code that I used(similar to yours but I was lazy to create new data structure, I just used tables that I already have)

    select fullname,eventid,max(due_date) as dueDate
    from 
    (
    SELECT RTRIM(ph.legalname) + ' ' + RTRIM(ph.lastname) as fullname, pex.eventid, pex.lstdtechg as due_date 
    FROM participanth ph join participanteventx pex on ph.id = pex.participantid 
    where ph.id between 80000 and 90000) as T 
    
    GROUP BY fullname, eventid
    I changed the between 80000 and 90000 to 80000 and 80005, and the report worked fine. I put the fullname in columns, and EventID(which is your modname) in the rows, and duedate in the data field.

    Hope this helps
    AE, PMP, MCTS
    Saturday, September 12, 2009 9:03 PM
  • I don't think you've understood my problem - the query worked fine.  The problem is displaying the data in a report, as described in my first message.
    Saturday, September 12, 2009 9:38 PM
  • I didn't say your code is wrong, I said I changed it just because I'm used to use joins since my experience started with SQL Server 2005 and not 2000.
    Feel free to keep your code the way it is, just read the rest of my post and tell me if I'm missing something with your issue.

    Thanks


    AE, PMP, MCTS
    Saturday, September 12, 2009 10:48 PM
  • The point you're missing is that the problem doesn't have anything to do with whether I use joins or not - your code has no effect on the problem.

    I've quoted the part you are missing below - it concerns displaying a date in a matrix in a SSRS report:

    I've tried creating a report using a matrix in SSRS 2005, but I can't get it to work because SSRS says: "The BackgroundColor expression for the textbox ‘dateValue’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling." Even if I try to print the date inside the cell, it doesn't display.

    I can't think of a way of using an aggregate value for the date.

    Saturday, September 12, 2009 11:56 PM
  • Hello

    Again, I'm not referring to your query in any way.
    I guess you are using an Expression in the Background property to seperate the colors of the due_date field, right?
    Where is the dateValue coming from if what you are putting in there is due_date?
    I usually get an error about the scope of the field being used, but I'm not very familiar with the error you are getting.

    Click on the due_date field, and use something like the following in the Backgroundcolor property and see if you still get the error.
    =iif(Fields!due_Date.Value < Now,"Blue", "Red")

    Hope this helps


    AE, PMP, MCTS
    Sunday, September 13, 2009 3:22 AM
  • Hi,


    The Background property of the cell in the matrix has an iif statement similar to what you have posted - that is what gives the error.

    The errors I'm getting are in the Matrix object in the report:

    [rsNonAggregateInMatrixCell] The BackgroundColor expression for the textbox ‘dateValue’ references a field outside an aggregate function.  Value expressions in matrix cells should be aggregates, to allow for subtotaling.
    [rsNonAggregateInMatrixCell] The Value expression for the textbox ‘dateValue’ references a field outside an aggregate function.  Value expressions in matrix cells should be aggregates, to allow for subtotaling.

    The following screenshots show the problem:

    http://i664.photobucket.com/albums/vv1/foxprorawks/report1.jpg

    http://i664.photobucket.com/albums/vv1/foxprorawks/report2.jpg
    Sunday, September 13, 2009 9:05 AM
  • Are you using anything else in the due_date cells? Like navigation or links? If yes, remove it and see what happens.
    Also, these are warnings and not errors, so your report should be running fine.
    AE, PMP, MCTS
    Sunday, September 13, 2009 12:16 PM
  • There is nothing else in the due_date cells.

    Also, although these come up as warnings, the cells don't changes colour as they should (they are all yellow, regardless of the contents of the database) and the date isn't displayed in the matrix when you run the report.
    Sunday, September 13, 2009 12:41 PM