#ProjectServer 2019 missing data in #OLAP cube? #SSAS #Reporting #Excel

November 29, 2019 at 10:56 pm | Posted in Administration, Configuration, Fixes, Functionality, Information, Installation, Issue | Leave a comment
Tags: , , , ,

Just a quick post and one related to Project Server on-premises which I haven’t done for a long time! A colleague escalated an issue to me where by data was missing in the OLAP cube in a new Project Server 2019 deployment. The symptoms where that data appeared to be missing from the Portfolio Analyzer cube. For example, adding Project List into the pivot table didn’t display any projects but when looking at the values in the Project List dimension from the Pivot Table fields menu you could see the projects:


Adding the Project List to the Pivot table, no Project Names included:


If another cube was used such as the Project Non Timephased, the Projects displayed on the pivot table as expected. If the built in measures where removed from the OLAP cube configuration and the cube rebuilt, the Project List then displayed fine on any OLAP cube. The fix is a simple one, it relates to a the Reporting Timephased Data change that on new installs this is set to Never which is the same behaviour for new PWA site collections in Project Online. Change the Reporting setting to another option such as daily:


Then publish the projects, once the projects are published, ensure the built in measure are enabled as required in the OLAP cube configuration if you have changed these and set the OLAP cube to build. Once processed the OLAP cubes will all work as expected.

#Odata #SQL SSIS component now available for SQL Server 2012 #ProjectOnline #SSRS #SharePointOnline #SharePoint #BI

March 26, 2014 at 9:16 am | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , ,

Quick post to let you know that the SSIS component to export data via ODATA that was mentioned here is now available for download, see the link below:


This will enable you to copy data from Project Online into a custom SQL database, from there you can easily create SSRS reports, custom OLAP cubes, integrate into other LOB systems etc. Look out for more details soon.

#SSRS native report render issue in #SharePoint #SP2013 with #IE 10 or later #PS2013 #BI #SQL

January 15, 2014 at 6:03 pm | Posted in Administration, Configuration, Customisation, Functionality, Information, Issue, Reporting | 1 Comment
Tags: , , , , ,

This post covers an the details around an issue I came across the other day and I wanted to make you aware to help in your deployments / system design. The issue is with displaying SSRS Native mode reports on a SharePoint 2013 page using a page viewer web part when using IE 10 or later. Our preference and recommendation is to usually use SSRS Integrated mode but on occasions some of our clients use SSRS 2008 R2 / 2012 Native mode. This issue doesn’t exist for SSRS Integrated mode.

****** Update ************

This issue is resolved by updating the document mode for the ReportViewer.aspx pages, updating this from <meta http-equiv=”X-UA-Compatible” content=”IE=5″>   to  <meta http-equiv=”X-UA-Compatible” content=”IE=8″> or later did resolve this issue for us. The file can be found on the report server in the following location: C:\Program Files\Microsoft SQL Server\MSRSx.InstanceName\Reporting Services\ReportServer\Pages. Please note, this will impact all the reports on that report server so test on a test server before a production server.


The issue is that the SSRS reports are not displayed correctly on the page, they are truncated:


Other standards-compliant browsers are ok, Chrome is:


IE 8 and 9 also work fine.

The page viewer web part with SSRS Native reports worked fine in SharePoint 2010 in any browser.

I have tested with the SSRS Report viewer web part (the 2008 R2 version as the SQL 2012 version doesn’t deploy to SharePoint 2013) from the RSWebParts.cab file, this has the same issue.

One of our devs had a quick look at this and it stated it was because the web part uses a table that is 3 cells wide. 2 of the cells are related to the document map while the 3rd contains the report itself. The document map cells are hidden by default.

In older versions of IE, a hidden cell in a table counted towards with width of the table, this was against the standard. Now with more standards compliant browsers, hidden cells do not count towards the width of the table.

This means that the report cell is the only cell defined for the row, so the browser forces it into the left most cell space. The end result of this is the SSRS report is truncated to the right as that is the limit of the size of that column.

So the answer going forward if your client wants to embed SSRS reports in SharePoint 2013 pages and they use IE, recommend (and use) SSRS 2012 Integrated mode.

#ProjectServer #SSRS Report with multivalued parameters #SQL #PS2010 #SP2010

March 17, 2013 at 6:03 pm | Posted in Configuration, Customisation, Functionality, Reporting, T-SQL | 1 Comment
Tags: , , , ,

A quick blog post to highlight the use of one of the Project Server Reporting database functions to resolve an issue when using an SSRS multi value parameter.

There are several methods to get multi value parameters working in SQL Server Reporting Services (SSRS) including dataset filters, joining parameters and custom SQL functions – other blogs / forum posts detail these. This post demonstrates using a function that is available in the Project Server Reporting database. The function is called MSP_FN_Utility_ConvertStringListToTable. An example SQL Stored Procedure that will allow multi values can be seen below:

CREATE PROCEDURE [dbo].[SP_ProjectData] (
        @ProjUID NVARCHAR (max)
select            P.ProjectName
           ,    T.TaskName
from            MSP_EPMProject_UserView P
INNER JOIN        MSP_EPMTask_UserView T
ON                P.ProjectUID = T.ProjectUID
INNER JOIN        MSP_FN_Utility_ConvertStringListToTable (@ProjUID) AS PU
On              P.ProjectUID IS NULL or P.ProjectUID like PU.TokenVal

Create the SQL query as normal but instead of using a where clause to filter the Project UIDs join on to the function as shown above.

#ProjectServer optimisations for #SQL Server and #PS2010 databases #SP2010 #MSProject

September 14, 2012 at 9:25 pm | Posted in Administration, Configuration, Installation, T-SQL | 2 Comments
Tags: , , ,

https://technet.microsoft.com/en-us/library/cc298801(v=office.14).aspx#Section6_3A quick blog post to show the recommended SQL Server and database settings for optimal performance of your Project Server environment. See the SQL queries below along with the TechNet documents for reference.


sp_configure ‘clr enabled’, 1
Print ‘CLR Enabled’


SQL Server MAX Degree of Parallelism (Specific for SharePoint 2010):

sp_configure ‘show advanced options’, 1;
sp_configure ‘max degree of parallelism’, 1;
Print ‘Max degree of parallelism set to 1’


Server-wide default fill factor:

sp_configure ‘show advanced options’, 1;
sp_configure ‘fill factor’, 70;
PRINT ‘Default server-wide fill factor set to 70 %, restart the SQL Service’

Auto_Close and Auto_update_statistics_async:

Update the database names for your Project Server PWA databases.

Alter Database VM353_PWA_Archive
Alter Database VM353_PWA_Draft
Alter Database VM353_PWA_Published
Alter Database VM353_PWA_Reporting
PRINT ‘Project Server databases auto close set to off and auto update stats asynchronous set to on ‘


Please note, if copying and pasting the SQL queries from this post you may need to delete and re-enter the apostrophes.

Also remember the database maintenance plans:

Project Server 2010: http://technet.microsoft.com/en-us/library/cc973097.aspx

SharePoint 2010: http://technet.microsoft.com/en-us/library/cc262731.aspx

#SharePoint 2007 / 2010 and #ProjectServer 2007/ 2010 #BI Reporting made easy #SP2010 #PS2010 #businessintelligence #SQL

August 22, 2012 at 12:36 pm | Posted in Add-on, Administration, Configuration, Customisation, Functionality, Information, Reporting, T-SQL | 1 Comment
Tags: , , , , , ,

Following from my previous post where I briefly introduced a new product from CPS called SharePoint ReportLink, as promised, this post demonstrates the ease of use and ease of reporting on SharePoint data when using SharePoint ReportLink.

For the purpose of this blog post, I have already deployed the solution to my test VM. Worth noting at this point, the version I have installed / used for this blog post is not the final release so you will see CPS DataMart referenced as this was the original product name. Functionally the version I have installed on my VM is probably 99% identical. Firstly I need to activate the feature on the site collection, in this case it is one of my PWA site collections. Once the feature is activated a new heading / menu appears on the Site Settings page for that site collection:


You will see CPS DataMart with four links, Application Settings, List Configuration, List Column Mappings and Logging. We will take a look at each page in turn, firstly the Application Settings page:


I didn’t set any database server or database name, as this is has been activated against a PWA site collection the feature automatically populates the database server with the Project Server Reporting SQL Server name and the name of the Project Server reporting database – pretty cool! You can of course update the settings to use a different database server and custom database. If this feature is activated against a normal SharePoint site collection you will see the following on the Application Settings page:


As you can see there is also a logging level, by default this is set to Information but can be updated:


The SharePoint ReportLink will log events to a custom list to ease diagnostics.

Let’s take a look at the next two page pages together, List Configuration and List Column Mappings.

List Configuration:


List Column Mappings:


As you can see these are just standard SharePoint lists, at this point you need to know what lists and what columns on those list you wish to report on. For the purpose of this blog post I will report on the Issues, Risks and Lessons Learned from my projects sites:


See below the populated / configured List Configuration and List Column Mapping lists:



Once the CPS SharePoint ReportLink is activated and configured on the site collection, any items on the lists configured will be synchronised to the SharePoint ReportLink database tables in real time. This includes, new items created, existing items modified and items deleted from those configured lists across all webs within that site collection.

After setting the List Column Mappings, custom tables will be created in the specified SharePoint ReportLink database, in this case the Project Server Reporting database:



Issues list columns:


If those lists already contain items, after setting the column mappings the existing SharePoint items will be created in the SharePoint ReportLink tables.

I then created a few new items on the lists (on PM 1 and PM 2 sites, other items already existed), as you can see from below, the data is available in the custom tables in the reporting database:



Lessons Learned:


As you can see, you can quickly and easily access the SharePoint list data from multiple sites using T-SQL. Also notice the Project_UID, this enables you to easily link the SharePoint ReportLink data to your Project Server data. If SharePoint ReportLink reads data from a non Project Server project site, the Project_UID column will contain an empty GUID.

The final menu we haven’t seen yet is the logging list, this can be seen below:


The warnings are logged above as the new custom list, Lessons Learned, doesn’t exist on the majority of my test project sites.

Now that the SharePoint data is easily accessible using T-SQL, reporting could not be simpler using your preferred reporting tool, SSRS, Excel etc. SharePoint BI made easy!

In case you didn’t see the first post, I have included links to the product below:



I will follow up with a third post and create an SSRS report including the SharePoint ReportLink data in the near future.

#SharePoint 2007 / 2010 Reporting #SP2010 #PS2010 #ProjectServer #BI #businessintelligence #SQL

August 21, 2012 at 11:00 am | Posted in Add-on, Customisation, Functionality, Information, T-SQL | 5 Comments
Tags: , , , , , ,

A quick blog post to highlight a new product that CPS have released. The product is called SharePoint ReportLink. Details for the product can be found below:




This product enables end users to easily create reports on SharePoint data using their favourite reporting technology without having to use the SharePoint web services. The product is an easily installable SharePoint solution (WSP file). Once the solution is installed, configuration is very simple too, users specify what lists and fields in those lists they wish to report on. The data is then available in real time in a specified reporting database. This enables users to create reports on SharePoint data using T-SQL to the custom reporting database rather having to use the SharePoint web services to access the data. This is particularly useful for Project Server programme level reports where you want to read data from multiple project sites in the one report.

Look out for a full blog post very soon where I will demo the configuration and usage to show you how powerful and simple to use this product is.

Blog at WordPress.com.
Entries and comments feeds.