Archive for the ‘SQL’ Category

Lot of times i have seen users asking when should i be creating a report in Excel Services or should i be using SSRS, in my future post i will be covering around performance point reports as well but for now i would be just giving you high level objective guidance on when to use Excel Services vs when to use SSRS

Note: this is my personal experience and would be applicable generally but exceptions are everywhere

Excel Services When to consider

  • Interactive Filtering : As we all know with excel services you can still filter data even when the data has been rendered within the sheet, but for SSRS report you need to predefined filters in form of parameters, once the data is rendered you need to reapply the filter and refresh in order to render the report, hence consider excel services wherein interactive data filtering would be required  
  • Conditional / Interactive Formatting : Conditional / Interactive  formatting is something which is easily available and can be done within excel but with SSRS though conditional formatting might take some effort to develop but interactive formatting might not be available at all
  • Pivot table Layout : So one of the things excel does the best is Pivoting of data, it’s not that SSRS can’t do that, with matrix & query combination it can be achieved to a degree but considering the effort i would suggest use Excel services than to be using SSRS
  • Quick Report : Another aspect to decide how quickly you need  the report, with excel services you might be up & running within few minutes than SSRS taking more time to design & develop the report, hence  for rapid report development, often i use term RRD to as an acronym, use Excel services
  • Usability : From usability standpoint since still more than 90% of the people use Excel as their reporting tool, hence people love to see their report in Excel, so it also depends upon audience what sort of audience you have

SSRS Reports when to consider

  • Tabular layout : If you are developing reports with tabular layout, personal recommendation would be to use SSRS than Excel services specifically if there are external data ranges, though even pivot table data can be shown as tabular format in excel but in my personal experience i have had problems with tabular reports, hence i recommend SSRS for the same
  • Dynamic parametrized query : Though some of them can be achieved by using query filter webpart and by passing parameter or Using UDF in combination but needs additional effort, also excel loads all the data at once and can only be filtered wherein all data might not be required upfront, also increased load time of report, whereas SSRS renders limited set of data as per parametrize filter 
  • External Images / 3D Charts : If you are using external linked images SSRS would be the way to go, also you would notice that charts within excel services renders flat rather than three-dimensional, also have experienced issues with colors being plotted on excel services as when hosted have experienced color disorientation
  • User Specific Permissions :  This can still be done in Excel services but at times is a security overhead which needs to be managed but can easily be done with SSRS report 
  • Multiple Export Options : There are multiple report export options available within SSRS whereas with Excel services you can only export as excel
  • Subscriptions : One of the best advantages is see is automated subscriptions from SSRS which is not available out of box with excel services though the same can be achieved using customizations but as always an additional overhead

Hope this helps clarifying and narrowing down options for reports


Read Full Post »

Thought of sharing for people who are looking for reporting data on Portfolio strategy, as you know Portfolio Strategy data is not available out of box with Project Server  2010 installation, hence before proceeding for reporting you need to get the “Portfolio Strategy Object Publish to RDB” solution starter installed in your environment, get it from here :: http://archive.msdn.microsoft.com/P2010SolutionStarter , post installation make sure you publish your scenarios, analysis solution drivers , priorities etc… before you would find actual data in DB

Since this being a solution starter the schema is not documented and at times its hard to look where to find which data, other way would be to look for sample reports & data connection queries, that gets installed upon deploying the solution starter, or if you want to understand the complete schema look at the attached diagram, download the image below & enlarge to see

Portfolio Strategy RDB DataSchema









Let us know if this helps

Read Full Post »

To help you determine when you have to scale up or scale out your Microsoft Project Server farm, use performance counters to monitor the health of the system. Use the information in the following tables to determine which performance counters to monitor, and to which process the performance counters should be applied, For more Information Refer to :


Read Full Post »

received a question recently thought would share with all :), Especially for those who haven’t planned upgradation to Project Server 2010, be aware that MS Project Server & MS Portfolio Server main stream support end date is 4 /10/2010, though extended support end date is far enough, hence Plan your upgrade accordingly 🙂

For other MS product life cycle refer to : http://support.microsoft.com/lifecycle/search/default.aspx?sort=PN&alpha=project&Filter=FilterNO

Read Full Post »

Often we need to know the projects which has been saved by PM’s but hasn’t been published, in order to check with PM’s if they forgot   to publish or intentionally they have saved the changes, all this is to make sure the correct data is reflected  🙂 , Make sure to run this against draft DB

Note: querying draft DB directly is not supported by MS, hence perform this at your own risk

So here is the query by which you can find out


Read Full Post »

Older Posts »