Want to know more about what's in the reporting Database

Hello all

Long time no see!

Really short blog today, I wanted to report on specific information from the Business drivers and Portfolio priorization.
I used the Database Reference Schema to help me find where to find the data in the Reporting Database.
So, I would like to remind all my colleagues who want to access to Reporting Database that you can use the schema reference that can be found here:
http://blogs.msdn.com/b/project_programmability/archive/2013/01/28/project-server-2013-reporting-database-rdb-schema-reference.aspx 

Hope this will help your report programmings

See you!

New filtering feature in Excel 2013 : the timeline!

For once, I won't write about Project Server but about a related topic: Excel! Actually both applications are closely interrelated since Excel through Excel Services allows creating tremendous reports using Office Data Connection (ODC) files on OLAP database or reporting table (it took me a while to get used saying "table" instead of "database" for PS2013!).

Excel 2010 brought a nice feature as the slicer for filtering data in pivot tables. This gave a nice graphical and mobile-adapted (smart phones, tablets) way to filter information. You could deploy thanks to this feature more user-friendly dashboards.

Excel 2013 goes on furhter this way introducing the timeline filtering feature that works similarly to the slicers. You can insert a timeline based on the time dimension (year, month,...) and link it to one or more pivot tables. Here is an example on the Excel Services sample dashboard that comes out-of-the-box in Project Server 2013 BI Center.

Figure 1: timeline filtering feature in a Excel Services dashboard

Then after filtering with the timeline:

Figure 2: dashboard filtered with the timeline

Now how to do it? You first need to start with an Excel file embedding data. It could be with an ODC (reporting table or OLAP data) as per my example below. Note that it won't work for MS Project visual reports, since timeline cannot use offline OLAP connections.

From the "insert" tab in the ribbon, "filter" section, select "timeline" option:

Figure 3: insert a timeline on a pivote table

From the options tab once the timeline is selected, you can connected it to any pivot table and change the formatting options.

Figure 4: timeline connection and formatting options

Et voilà!! If you had fun using the slicers, you'll love creating and using dashboards with the timeline!

Master the Multiple Choice Custom Fields in your Excel Reports!

It happens sometime that there is a need to report information stored in a Multiple Choice Custom Field:

If this happened to you, you must have seen that the custom field is not part of the standard views such as MSP_Project_UserView.
You will have instead a specific view for each Multiple Choice Custom Field named MSPCFPRJ_<CustomFieldName>_AssociationView.

Then, you will have to write down a clever request giving you the list of choices made by projects.
Don't worry, you can find this clever information right below!

SELECT Proj.ProjectName,
       lt.MemberFullValue AS 'VLookupField'
FROM dbo.MSP_EpmProject_UserView AS Proj 
LEFT OUTER JOIN [dbo].[MSPCFPRJ_<YOUR_CUSTOM FIELD NAME>_AssociationView] AS MVassoc -- view for multi value field 
ON proj.ProjectUID = MVassoc.EntityUID 
LEFT OUTER JOIN dbo.MSP_EpmLookupTable AS lt 
ON MVassoc.LookupMemberUID = lt.MemberUID 
order by    ProjectName asc

I would recommend to do the post treatment in the excel itself. it would be possible to link the below request with the one that will gather all your project information but, be careful, it will multiply the number of lines per the number of choices made on projects.

Then, I recommend to create a specific tab in your excel report to show the information
This will return you something like this while rendered in an Excel pivot table with data connection:

Be sure to have checked the "Repeat All Items Label" check box

Now, you may want to return these information on another sheet gathering other project information.
To do so, I will create a one cell concatenation of the selected project.
First step is to gather the information based on the project on several cells

The formula to gather several lines of a specific project is quite ugly:
{=IF(ISERROR(INDEX($A$1:$B$5000,SMALL(IF($A$1:$A$5000=$G$4,ROW($A$1:$A$5000)),ROW(1:1)),2)),"",INDEX($A$1:$B$5000,SMALL(IF($A$1:$A$5000=$G$4,ROW($A$1:$A$5000)),ROW(1:1)),2))}

$A$1:$B$5000 = Where to search
$A$1:$A$5000 = Where are the project names
$G$4 = The project name to search
1:1 = The line number you want to fetch
Be careful, this is array formulas, that means that you will have to click on CTRL+ENTER to validate your formula!

Here is the overall list of formulas, as you can see, only the parameter number changed (I return here a maximum of 12 entries)

To finish, the easiest part is to put all that in a single cell with the following formula.
To render it nice, I created a second column containing the separator so that a separator is only created while a value is returned. I then concatenated the overall in a cell

Separator cells are looking if there is a value after the current one and if yes displays the separator:
=IF(G6<>"",$H$19,"")

Result cell is a concatenation of the entire table:
=CONCATENATE(G5,H5,G6,H6,G7,H7,G8,H8,G9,H9,G10,H10,G11,H11,G12,H12,G13,H13,G14,H14,G15,H15,G16)

Tips: you can use =CHAR(10) as separator to render in a cell on several lines

You are now able to render multi-choice custom field in your excel services report!

Find here an example of all I just said in an excel file!

Help from Microsoft : http://msdn.microsoft.com/en-us/library/office/ee767688.aspx

Create dynamic multi-lingual Excel BI reports

The challenge

In a recent development of BI report for project status, I've been challenged to create an easy bilingual functionality in excel services.
As you may know, Excel services is giving a challenge to not use macros.

The preparation

First, you will have to define the translation terms and identify them with an ID:

Then, you will have to prepare a easy way to select your language.
To do so, you will have to create a "dummy" pivot table to be able to create a slicer on it.
the source data is really simple :

 Selecting this data source, you will have to create a pivot table (Insert/Pivot table) and add the data source as a filter

The Magic

You're now all set to create multi-lingual fields to be included wherever in your Excel Services reports.
The magic is coming from a compilation of pivot tables filters linked to slicer and index/match functions

So, you can now in your report sheet create a slicer pointing to your early created pivot table:

The effect of this is that your filter field seen below will change each time you will select a new language in your slicer.

You can now create a "not so complex" Excl formula pointing to the required data, so, if you want to gather the "Project Status" in an other language, you will use the following formula:

=INDEX($A$1:$D$6,MATCH(1,$A:$A,0),MATCH($J$2,$1:$1,0))

$A$1:$D$6 = the source table
1                 = the index of the term you want to get (1 for the project status translation) = The only variable of the formula
$A:$A         = where the IDs are
$J$2            = the current selected language = the pivot table filter
$1:$1          = where the titles (languages) are

Then, while selecting a language from the slicer, the text will automatically change based on the ID you selected:

 

Hope you like this trick