locked
Adding a WEEK parameter to SSRS Report RRS feed

  • Question

  • I have an SSRS report which reports on Timesheet data from EPM. The dataset returns rows of data, each representing a week. Columns returned: Project, Submitted Date, Year, Month, Hours Submitted.

    Example Dataset:

    Project z, 2014-01-01, 2013, 12, 8
    Project x, 2014-01-01, 2013, 12, 8
    Project x, 2014-01-07, 2013, 12, 5
    Project x, 2014-01-14, 2013, 12, 5
    Project x, 2014-01-21, 2013, 12, 6
    Project x, 2014-01-28, 2013, 12, 6

    If I add a multi select Parameter called WEEK how do I set up the SQL or Report so users can choose the weeks to display?

    Tuesday, January 28, 2014 1:46 PM

Answers

  • Create a dataset say named week, use the timesheet dataset to get all the dates and extract weeks using the Datepart function, once the dataset has been created use this dataset as parameter, use this parameter to filter the main dataset

    There one good examples provided for timesheet in project server report pack available with project server 2007 report pack, take a look at it and i believe it has all you need

    Let us know, if this helps   


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    • Marked as answer by _c Wednesday, January 29, 2014 9:03 AM
    Tuesday, January 28, 2014 2:25 PM
  • Just to add on Sunil's advice, you can also use default database view in reporting database, I think MSP_TimeByDay_OlapView, thats the name. It has both Calendar And Fiscal Year, Qtr, Months Weeks and Days.

    You can populate the your parameter directly fetching CalendarMemberNameWeek or CalendarMemberNameWeek colum or as Sunil said you can use Datepart function to populate your report parameter.

    Also checked my SQL query I posted on your another post, that query fetched Assignemnt Work and Capacity on Weekly Basis to calculate resource utilization.

    Thanks !!!


    Sachin Vashishth MCTS


    • Edited by Sachin Vashishth Tuesday, January 28, 2014 7:06 PM
    • Marked as answer by _c Wednesday, January 29, 2014 9:03 AM
    Tuesday, January 28, 2014 6:49 PM

All replies

  • Create a dataset say named week, use the timesheet dataset to get all the dates and extract weeks using the Datepart function, once the dataset has been created use this dataset as parameter, use this parameter to filter the main dataset

    There one good examples provided for timesheet in project server report pack available with project server 2007 report pack, take a look at it and i believe it has all you need

    Let us know, if this helps   


    Thanks | Sunil Kr Singh | http://epmxperts.wordpress.com

    • Marked as answer by _c Wednesday, January 29, 2014 9:03 AM
    Tuesday, January 28, 2014 2:25 PM
  • Thanks. I kind of follow.. How does this work with setting Week 1, Week 2, Week 3 etc. labels?
    Tuesday, January 28, 2014 2:51 PM
  • Just to add on Sunil's advice, you can also use default database view in reporting database, I think MSP_TimeByDay_OlapView, thats the name. It has both Calendar And Fiscal Year, Qtr, Months Weeks and Days.

    You can populate the your parameter directly fetching CalendarMemberNameWeek or CalendarMemberNameWeek colum or as Sunil said you can use Datepart function to populate your report parameter.

    Also checked my SQL query I posted on your another post, that query fetched Assignemnt Work and Capacity on Weekly Basis to calculate resource utilization.

    Thanks !!!


    Sachin Vashishth MCTS


    • Edited by Sachin Vashishth Tuesday, January 28, 2014 7:06 PM
    • Marked as answer by _c Wednesday, January 29, 2014 9:03 AM
    Tuesday, January 28, 2014 6:49 PM
  • Thanks Sunil and Sachin!
    Wednesday, January 29, 2014 9:03 AM