none
Report Builder 3.0 - Create a matrix with Day of Week and Day number as the column heading

    Question

  • Hi,

    I have to create a report that has column headings that represent each day (M,T,W,T,F,S,S) and the date (1,2,3,4,5, etc) based on a Month slected in the report parameter.

    So, the Column heading would look similar to:

                      MARCH 2012

    T  F  S  S  M  T  etc....

    1  2  3  4  5   6  etc....

    Then i can put my data under those columns. Data is a colour indicating whether a person is available or not (similar to free/busy I suppose under Exchange). i get this date data from a database Calendar datasource.

    is there an easy way to accomplish those column headings?

    What would be the best way to create this (if there is a way)?

    Thanks,

    Rob


    • Edited by RB0135 Wednesday, February 29, 2012 2:15 AM
    Wednesday, February 29, 2012 2:14 AM

All replies

  • It's easy.

    =left(Fields!YourDateString.Value,1)


    Regards, Nighting Liu

    Wednesday, February 29, 2012 2:19 AM
  • Thanks Nighting Liu

    I can see how that will be useful, but I might have left some important information out.

    The column headings have to be shown for the whole month, whether I have data or not. The database only stores some dates for the person, not a whole month.

    Regards,

    Rob

    Wednesday, February 29, 2012 2:26 AM
  • Got it, my suggestion is:

    Try to handle this in you query, you know in visual studio, Analysis Services Project can help you generate a Time dimension table automatically.

    You can right outer join a month to your data by this table.

    Hope it helps


    Regards, Nighting Liu

    Wednesday, February 29, 2012 2:35 AM
  • Thanks again. Not a bad suggestion, but I am not using Visual Studio. I am using Report Builder that is launched through SharePoint
    Wednesday, February 29, 2012 3:16 AM
  • My point is: Report Builder is driven by DataSet, so base on your requirement, you need to put your focus on data query

    Regards, Nighting Liu

    Wednesday, February 29, 2012 3:22 AM
  • Ok, I am pretty lost with this at the moment but giving it a go.

    I have managed to create a Time Dimension table, but, this is only accessible internally to the company and thus the report has to be programmed on the server within the company.

    So, On the report, i have managed to create the columns, by asking for a Date parameter then returning the range from a dataset linked to the Dimensions Table.

    So far so good.

    However, I am having issues now trying to connect that to the date values based on per person.

    I have the three datasets (accommodation, PersonCalendar and DimDate). The structure is that the Accommodation has to be linked to the personcalendar and then if they are in the accommodation based on the calendar information, then I need to highlight that particular day with a colour)

    Whats the best way now to get the data out and displayed? I have tried a few things but i cant seem to link it correctly.

    Thanks,

    Rob

    Wednesday, February 29, 2012 4:34 AM