none
Report Templates Connecting to Reporting DB vs Sample Reports using OLAP RRS feed

  • Question

  • What I'm trying to do might be impossible - I haven't found anything like it around the web or on forums, but that might be because I'm using the wrong terminology.

    I have basically learned to hack the data connection "command text" by looking at the examples and figuring it out on my own, and I admit I don't fully understand the commands at the bottom - INNER JOIN, etc.  So far, I've been able to pull the fields that I want (and only the fields I want) into a pivot table taken directly from the reporting database, but what I can't do is add a way to view the data by TIME.  I know that this can be accomplished in the OLAP cube - the olap report OlapAssignmentTimephased does exactly what I need - except that my company is very driven by work hours and actuals, and I have to run reports in the middle of the day, after PMs have just updated their schedules - and, of course, they don't want me building those reports from yesterday's data.

    So to sum up - my question is if there is anyway to get timephased data from the reporting database into a pivot table.

    I know you can pull the resource assignments out of the resource assignments view, but you can only select 100 resources at a time, and the formatting when it's exported to Excel leaves a lot to be desired. (unlike in 2007, when it was automatically grouped and could actually be sorted in a useful way)

    Additionally, we do not use Timesheets.

    If you're still reading this, thanks! Hopefully there is a solution, but I'm not too hopeful...


    • Edited by ElliJ Friday, September 2, 2011 7:28 PM
    Friday, September 2, 2011 4:18 PM

Answers

All replies

  • Download the schema for the reporting db. Its in the Project SDK, a free download from MSDN. In it you will find timephased data in the reporting db so you can report by day or roll up to weeks, months etc.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Saturday, September 3, 2011 3:33 AM
    Moderator
  • I have the SDK, and I can see that there is timephased data in the Reporting Database, but I don't know how to make that data show up in the pivot table in a useful way.  I'm pretty sure it's just a matter of what code you put into the command text in the data connection...but I'm not a programmer, and don't have the resources to teach myself how to put together that code.
    Tuesday, September 6, 2011 4:03 PM
  • Just open Excel...select the Data option > From New Sources > Analysis Services,
    enter the name of the OLAP cube server, find the right cube....and then it's
    all in Excel.
     
    Of course, all of that is done for you if you just click on one of the OLAP
    ODC files in the BI Center.  That will launch Excel with the right connection.
     From there, it's just a matter of building a PivotTable.
     
     

    Andrew Lavinsky [MVP] Blog: http://azlav.umtblog.com Twitter: @alavinsky
    Wednesday, September 7, 2011 12:52 AM
    Moderator
  • I don't want to use the OLAP cube because we can't build in the middle of the day.

     

    I've got this working with day by day or monthly data, all I have to do now is figure out how to break it into weeks.

    I think it was as I thought, a matter of terminology.  The "command text" is just an SQL query, which is something I didn't realize.  Anyway, thanks for your replies.  I'm fairly determined so I'll get this working just the way I want...then have to change it when we upgrade...

    Wednesday, September 7, 2011 4:00 PM