Project Online Calendar Exception in Power BI

Recently i was working with Time Sheet Reports and one of the request was to incorporate calendar exceptions in order to avoid sending emails for holidays and hence started working on getting calendar exceptions in Power BI report, once i got the calendar exceptions it was easy to incorporate it as filter in all the reports

The intent of this post is to show how to bring in calendar exceptions in Power BI, once in the usages are numerous and any Power BI report developer can easily incorporate it in various ways

Step 1.  Launch your Power BI .pbix file

Step 2.  Navigate to Data in Left bar >> Edit Queries

Step 3. New Source  >> OData

Step 4. Add New Odata URL as https://Your.servername.com/sites/pwa/_api/ProjectServer/Calendars

Notice i am not going to oData instead using REST API

Once added >> you should see the Calendars as above

Step 5. Load the Query in connection Model

Step 6. Once Created i renamed the Query1 dataset to “AllCalendarException”

Step 7. Expand the “BaseCalendarException” Tables

Step 8. Once Expanded you would notice all the exceptions available in your dataset for your use

 

Disable Project Professional Save As

As we know, there are issues with having downloading project plans(local copy) from project pro and then re-synching them with server version, I won’t get into specific issues

In order to avoid this issue we had asked our users not use the the Save As command, but as you know guidelines almost never works 100% 😉

Hence we decided to block/Disable the Save As  functionality from Project Pro, what better way would it be other than a simple macro to prevent users to do a Save As

And so we wrote an event module, which overrides the Save As event and displays a message to user of this being disabled

Just for sake of completeness i have embedded my whole macro which ahs another function running for validating other ECF’s but not needed only to disable Save As

Step 1.  Check out Enterprise Global

Step 2.  Event and Class Module

Step 3. Save and checkin Ent Global

Note: Reopen project pro, connect to project server, open any project and then try to do Save As, see snapshots below

 

<Code>

Option Explicit
Option Base 1

Public WithEvents App As Application
Public WithEvents Proj As Project

Private Sub App_ProjectBeforeSave2(ByVal pj As Project, ByVal SaveAsUi As Boolean, ByVal Info As EventInfo)
Dim change As Integer
Dim msgRes As VbMsgBoxResult

If (App.Projects.Count > 0) Then
change = DetectInvalidEntries() ‘ this is a separate function to detect a specific Custom field value and allow user to save the project
If (change > 0) Then
msgRes = MsgBox(“For my Owner Org, Department should alwayss be India IT”, vbCritical, “Owner Org Change”)
‘Cancel the save
Info.Cancel = True
End If
End If

If (SaveAsUi) Then ‘Traps the SaveAsUi event and cancels the event
If ((InStr(UCase(App.UserName), “ADMIN”)) < 1) Then ‘ Check to Only allow Admin User to still use the functionality
MsgBox (“‘Save As’ function has been disabled, please contact Administrator”)
Info.Cancel = True
End If
End If
End Sub
Public Function DetectInvalidEntries() As Integer

Dim projectFieldDept, projectFieldPrjOwner As Long
Dim departmentsValue As String
Dim ProjOwnerOrg, scheduleDrivenCheck, multiDepartmentsCheck As Integer

‘Get the field values
projectFieldDept = FieldNameToFieldConstant(“Project Departments”, pjProject)
projectFieldPrjOwner = FieldNameToFieldConstant(“Owner Org”, pjProject)

‘See if the value changed
ProjOwnerOrg = InStr(ActiveProject.ProjectSummaryTask.GetField(projectFieldPrjOwner), “My Owner Org”)

‘load the Project Departments value
departmentsValue = ActiveProject.ProjectSummaryTask.GetField(projectFieldDept)
scheduleDrivenCheck = InStr(departmentsValue, “India IT”)
multiDepartmentsCheck = InStr(departmentsValue, “India IT,Manilla IT”)

‘Detect the change
If (ProjOwnerOrg > 0) Then
If (scheduleDrivenCheck > 0 Or multiDepartmentsCheck > 0) Then
DetectInvalidEntries = 1
End If
End If
End Function
‘—————Initiate the Event Class module for auto open——————–

Option Explicit

Dim x As New EventClassModule

Sub Auto_Open()
Set x.App = MSProject.Application
Set x.Proj = Application.ActiveProject
End Sub

</Code>

 

Macro to extract project specific resources

While working and extending a few macros recently, noticed i didn’t had access to database (same as in Project Online, well i could use Odata but again it cumbersome, so i went with my comfort zone VBA), and i needed to extract 15 project specific resources and thats where i started thinking should i do it manually, well easy enough if i only had 15 projects what if more than 100 and hence started writing macro, didn’t took longer may be 20 min and here is is

<code>

Sub GetAllRes()

‘Declare Object Variables for use

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

‘Set Excel Application, Workbook, Worksheet reference to be used
Set xlApp = New Excel.Application
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = “AllRes”
Set xlRow = xlApp.ActiveCell

Dim allProj()

‘ Well i could have read this from a file as well, just got lazy as there were only handful projects 😉
allProj = Array(“Prj1”, “Prj2”, “Prj3”, “Prj4”, “Prj5”, “Prj6”, “Prj7”, “Prj8”, “Prj9”)
Dim CurrArr
Dim Res As Resource
Dim Row As Integer
Row = 1

xlSheet.Rows.Cells(0, 0) = “ProjectName”
xlSheet.Rows.Cells(0, 1) = “ResourceName”
xlSheet.Rows.Cells(0, 2) = “Resource Type”

For Each CurrArr In allProj
PrjName = “<>\” + CurrArr
FileOpenEx Name:=PrjName, ReadOnly:=True
For Each Res In ActiveProject.Resources
‘MsgBox (Res.Name + “–” + CStr(Res.EnterpriseUniqueID))
xlSheet.Rows.Cells(Row, 1) = CurrArr
xlSheet.Rows.Cells(Row, 2) = Res.Name
If Res.EnterpriseUniqueID = -1 Then
xlSheet.Rows.Cells(Row, 2) = “Local”
Else
xlSheet.Rows.Cells(Row, 2) = “Enterprise”
End If
Next

Next

End Sub

</code>

Let me know what you think

Reading Enterprise Custom Field Using Macro

Several Times I have seen this question coming up on how to read values of Enterprise Custom Fields, thought just inserting few lines here to aid out people looking for these, the below mentioned method will not only get you access to the Enterprise but out of box fields as well as local custom fields if any

<code>

Sub GetEntECFVal()

Dim tsks As Tasks
Dim tsk As Task

For Each tsk In ActiveProject.Tasks
MsgBox (tsk.Name + “—” + tsk.GetField(FieldNameToFieldConstant(“Field Name”)))
Next

End Sub

</code>

Mobile Device Compatible OData Project Status Report Part I

Once again back with a new post, recently got a requirement to develop a Project Status Report(PSR), as we all know, for on Premise environment this should be very easy, just write a bunch of SQL queries and joins and you should be good to go, however in this case the requirement was to get the report out of Project Online with bunch of custom fields and additional information from Project Site (Custom Lists)

Still not a big deal, you can easily get the information using OData except making joins is not as easy as it seems, so in this example you will notice quite a few things I have done as workaround and tried to manage and map within JQuery & JavaScript rather than just manipulating the OData

well I started working on it and then came the additional requirement can we make it mobile device compatible and the answer was yes why not, just that it takes time & effort J

Anyways started exploring options, and as we all know, JQuery & JavaScript is what you have to use
however which framework should we use for making the report mobile device compatible, one of my colleague recommended to use Bootstrap J started working on it and below are the results

 

Desktop version

Looks Pretty and they are real time too 🙂

Now let’s take a look at Ipad Version on how it looks on Ipad, though i could simulate it in browser but wanted to show real

IPad Version

Notice i have Content focused mode enabled 🙂 to maximize the report on PDP

Okay so that was IPad version, let’s see how it looks on iPhone

IPhone Version

          

Hopefully this should give you a good preview of mobile device compatible report, in my next post i will post the code and explain on how was this developed