Category: Project management

How to create timeline chart in SSRS report

Timeline chart can be very helpful to present what is happening during a specific time frame, enabling a clear visualization of all running background jobs. In SQL Server Reporting Services (SSRS), there is no direct control available for creating a time line chart. For example, if we have a SharePoint list that stores seminars going to happen this year, we can display this information using a timeline chart in SSRS report. 

There can be multiple approaches to create a timeline chart. Here I am sharing the approach that is very easy to use. 
We will be creating an SSRS report for Project Server data. As the project will have multiple milestones, so we can create a timeline chart that will display all milestones between project start and finish date. 

Below is a step by step procedure to create the chart:

1. Create a report server project and a new Report “Timeline.rdl” as shown below.

2. Create connection to an instance of SQL Server within the report, and create a new Data Source.

3. Create a store procedure that will retrieve milestones from Project Server for a selected project. 

• Have Project Name as filter 
• To show milestone in different levels in timeline chart, apply logic for even/odd row based on row number. All the records in an even row will be displayed at one level and odd rows will be displayed in the other level in timeline chart through below query:

Case MilestoneName when ‘Start’  then ‘0’ when ‘End’ then ‘0’   else   ROW_NUMBER() over(order by MilestoneDate)%2 + 2  end as evenodd

In the above query, MilestoneDate represents TaskStartDate of milestone. 

Output of this store procedure will be as follows: 

MilestoneDate for Start and End MilestoneName represents “ProjectStartDate and ProjectFinishDate”

Now we are ready to create the chart.

1. Go to report design and right click on report area. Select Insert -> Chart




2. Select Category Groups as MilestoneDate and Values as Sum(evenOdd)




    Modify the Category group Label to show the date in “dd/mm/yyyy” using expression

   =Microsoft.VisualBasic.Strings.format(Fields!MilestoneDate.Value, “dd/MM/yyyy”)

3. Remove Chart Title, Axis Title, Legend
4. Enable Show Data Label in chart and modify the series label property to show Milestone Name
5. Modify the Horizontal Axis property Major Tick Marks






6. Modify horizontal axis property Axis Type, and disable side margins.

.

7. Modify horizontal axis property Line and make the Label Font bold. 



8. Now select the Bar and go to the property window by pressing F4 and set properties as shown below:



9. Hide vertical axis and run the chart report, timeline chart will be displayed as shown below: 

Conclusion:

This timeline chart can be exported in PDF and shared across the team.  Similar approach can be used to represent seminars within the years or any scenario. 

Adding Custom Validations on JS Grid of MY Task Web Part in Project Server

In Project Server and SharePoint, we commonly use JS Grid control to display the tabular data in grid view. If you are using Project Server, then you must have seen that JS Grid is used on most of the out of the box available web parts. JS Grid also provides flexibility to carry out customization using its available APIs.

In one of the projects, there was a requirement to validate actual start date of the assignments of logged in user for My Task Web part. This implementation called for a custom solution.

Here’s a solution in which we have to validate if task Actual start date is null i.e. task not yet started, then Task Start Date must be later than or equal to current date.  If the validation rule fails, then we need to highlight those task rows.

The implementation steps for the solution are as follows:

1. Create a SharePoint page and add My Task Web Part on it.



2. To carry out custom validation on the JS Grid, we have to create a JavaScript file to add custom code, upload it on a SharePoint document library and add it on the SharePoint page created above using a content editor web part.
3. Now, let’s take a look at various JS grid methods used.

a. Get the instance of MY Task Grid

Each of the JS Grid Instance available out of the box has its predefined name.  When My Task Web part is added on a page, we get the component name as “MyTasksComponent” and we can use below code to get its instance on the page:

_grid = MyTasksComponent.get_GridSatellite().GetJsGridControlInstance();

b. Get the list of all Records of My Task Grid

To apply validation rule when page loads, we have to retrieve all JS Grid records using below code:

 allRecords = MyTasksComponent.get_GridSatellite().get_tableCache().GetRecords(ranges).records[0]

The rangesvariable contain the start and end value to fetch the records.

 ranges = [{pos: 0,
count:             MyTasksComponent.get_GridSatellite().get_tableCache().GetRecordCount()}];

MyTasksComponent.get_GridSatellite().get_tableCache().GetRecordCount() gives the count of total records in the grid.

c. Set the indicator for error on JS Grid row

_grid.SetRowError(allRecords[index].recordKey, “Custom Validation Message”);

After loading sp.js file on page, the custom JavaScript code runs which parse all MY Task JS grid and check Task Start date and Actual Start date value and highlight all the rows which fails the validation rule.

Let’s say the current date is 31 Jan 2016, then you can see that there are two tasks which are not yet started i.e. Actual start date is null and Task Start date has passed out as highlighted.

When you click on the indicator then the custom messages will be visible.

      The complete JavaScript code is here.

    /// <reference name=”MicrosoftAjax.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/Utility.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/WebMethodManager.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/shell.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/TimesheetSatellite.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/RemoteTextConv.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/ProjectFramework.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/GridSatellite.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/projectserverscripts.debug.js”/>

    /// <reference path=”~/_layouts/inc/pwa/library/pagepropertymgr.debug.js”/>

    /// <reference path=”~/_layouts/SP.core.debug.js”/>

    /// <reference path=”~/_layouts/JsGrid.debug.js”/>

    /// <reference path=”~/_layouts/JsGrid.Gantt.debug.js”/>

ExecuteOrDelayUntilScriptLoaded(TaskGridLoad, “sp.js”);

function TaskGridLoad() {

    try { 

        if (typeof(MyTasksComponent) === “undefined”) {

            return;

        }

        if (window.PJ == null || MyTasksComponent == null) {

            return;

        } else { 

            _grid = MyTasksComponent.get_GridSatellite().GetJsGridControlInstance();

            var ranges = [{

                pos: 0,

                count: MyTasksComponent.get_GridSatellite().get_tableCache().GetRecordCount()

            }];

            var allRecords = MyTasksComponent.get_GridSatellite().get_tableCache().GetRecords(ranges).records[0];

            var today = new Date();

            var currentDate = new Date(today.getMonth() + 1 + “/” + today.getDate() + “/” + today.getFullYear());

            //Iterate through all records to check for the validation rule

            for (var index = 0; index < allRecords.length; index++) {

                var AssgnStartDate = allRecords[index].properties[“ASSN_START_DATE”].localizedValue

                var AssgnActualStartDate = allRecords[index].properties[“ASSN_ACT_START”].localizedValue

                 var assnStartDate = null;

                if (AssgnActualStartDate == null || AssgnActualStartDate == undefined) {

                    if (AssgnStartDate != null || AssgnStartDate != undefined) {

                        assnStartDate = new Date(AssgnStartDate);

                    }

                     if (assnStartDate < currentDate) {

                      _grid.SetRowError(allRecords[index].recordKey, “Start Date should be later or equal to current date”);

                    } 

                }

            }

         }

    } catch (err) {

        alert (err.Message);

    }

}

 Conclusion:

As we have seen how to validate task rows with custom validation rules, similarly we can carry out any of our custom validations like validating actual and planned hours, or any enterprise task level custom fields value, on various events of JS grid.

Live Webinar: How to harness the power of agile and the structure of MS Project together to increase productivity

More and more companies are reaping the benefits of the agile methodology. Customers and users are more involved, teams are more engaged and solutions garner higher satisfaction from their customers. In many cases, this also means the adoption of a new tool for the project teams. Those adopting scrum are using PostIt Notes on their [Agile] Wall, Excel, Jira, Visual Studio Online or Rally to track the current and future sprint’s user stories or tasks.

The go-to project management tool has been MS Project/Project Online and is being used far and wide. Project managers are now preferring to use tools like Jira and TFS to track agile team’s activities. Visual Studio Online and Jira’s simple UI coupled with their activity planning and activity tracking features make it a favorite among team members. Project Online, Visual Studio Online and Jira each have their strengths and weaknesses.

Those organizations using Project Online for strong portfolio analysis and resource management are not able to find a competitive feature in Visual Studio Online or Jira. Conversely, Visual Studio Online (VSO) and Jira’s sprint planning and tracking is difficult to replicate in Project Online. The optimal solution would be to use Project Online during the project planning, and use VSO/Jira during work monitoring and controlling, and then again use Project Online to control schedule and cost. 

Integration between Project Online and VSO or Jira allows team members to align their tool to their function and maximize efficiency. Project managers can create and manage project plans for prioritization by portfolio managers and utilization planning by resource managers. Selected project tasks in the form of a user story or activity, can be pushed to Jira or VSO project for agile poker/planning and execution. During the sprint, team members can enter new tasks or update task information, which can be pushed back to Project Online, allowing the Project Manager to control the schedule and cost.

Maximize your investments on project management tools with a simple integration and make the joy of eliminating double entry an increase in visibility and digital productivity – attend this webinar to learn how to harness the power of Agile methodology and tools with the structure of Microsoft Enterprise Project Management (EPM) on March 15, 2016  at 1400 hrs PST / 2200 hrs GMT

Create a custom task outcome in Project Server 2013

Workflows in SharePoint provide excellent ways to save effort, time, and cost by automating existing business functions. Here I am sharing the process of creating custom task outcomes in Project Server workflow.

SharePoint 2013 out of the box has two task outcomes – Approved and Rejected as shown below: 

Let’s consider a use case – While creating a workflow in Project Server 2013, there is a requirement that if a project manager creates a project and submits it, it should go to the PMO for approval, and the PMO should get three options for approval- Forward to Manager, Approved, and Rejected as shown below: 

Below are the steps to address this requirement through the custom task outcome functionality.

a)    Create a new task outcome column-

1. Go to PWA Site Settings – > Site columns









2. Click Create






3. Enter column name as Project Action Outcome and in the additional column settings add all the desired task outcomes. Click on OK.

b)    Create a new content type

1. Go to PWA Site Settings -> Site Content Type



2. Click Create



3. Create a custom content type as Project Action Content Type with below selections:



4. Click OK.
5. On the content types settings page, click Add from existing site column.
6. Add Project Action Outcome column to it.

c) Add a newly created content type to the workflow task list.

1. Open Project Server Workflow Task List
2. Go to List Settings -> Add Existing Content Type



3. Add Project Action Content Type

d) Update the wokflow-

1. Open/create Project Server workflow in SharePoint Designer 2013
2. Open the assign task property
3. Expand the Outcome options
4. Select the task content type as Project Action Content Type
5. The output will look like below:




6. Click Save and publish the workflow.
7. Now when you run the workflow and edit the task, you will see a newly created task outcome. 



I have implemented this approach for Project Server workflow; the same approach can be used for any SharePoint workflow.

How to attach an event on JS Grid of Project Server OOB Details web part

I have been working on project server customization and development since more than four years, and have helped clients with specific custom solutions to suit their business requirement. These customizations include site customization, custom web part creation and workflows. Recently I came across a requirement to attach an event on JS Grid of Project Server out of box Details Web Part on Project Server 2013. Here I share the solution we implemented for this requirement. The JS Grid control helps display and manipulate tabular data in Web Parts for Microsoft Project Server 2010/2013. Project Server Out of box web parts like My Task, Project Center and Details use JSGrid. 1.       Get the JS Grid control instance Component object of ‘Details’ web part of Project Server is ‘projectDrilldownComponent’. If we add ‘Details’ web part on Project Detail Page (PDP), then projectDrilldownComponent object will be available. Using ‘projectDrilldownCompsonent’, get the JS Grid Control Instance Code:  _grid = projectDrilldownComponent.get_GridSatellite().GetJsGridControlInstance(); 2.       Attach event on JS Grid The JS Grid control offers the following client-side events. Link has details for the list of event on JSGrid. AttachEvent () function of JSGrid can be used to attach event on JS Grid. Argument for this function is the type of event to be attached and the function to be called on this event. In below sample code, we are attaching the OnRowFocusChanged event on JS Grid and getting the selected record. _grid.AttachEvent(SP.JsGrid.EventType.OnRowFocusChanged, function (eventArgs) { TaskChangeEvent(eventArgs) Below is the complete sample code. Create a text file with the below code and upload the file on any document library, and create a PDP page. Add the Details out of box web part from Project Web App category on the page. Also, add the content editor web part and give the reference of text file that we have uploaded in the document library.

How to filter specific project report on project site

In an organization, there are multiple projects and for each project there are multiple reports generated. For effective tracking and monitoring, various out of the box reports are available in the Project Server. Quite often it happens that project managers and team members demand for report view that corresponds only to their projects and not the entire organization. To meet this requirement, here is a solution that can filter out specific project data from the excel report, so that the user does not have to go through the complete list of projects to find data related to his project on the Project Site. Below is the excel report showing the count of completed tasks for all projects. Now to filter out the specific list, below are the steps to be followed: Add Slicer to the excel report. Add a web part to add the excel report. Add Content Editor web part to refer to the JavaScript file that will filter out specific project in the excel reports. Note: Pre-Requisite: SQL Server analysis services in Power Pivot mode must be installed.  Detailed steps: Add Slicer to the Excel Report : Open the excel report in edit mode in excel from the SharePoint document libraryBusiness Intelligence. Select the Pivot Table. Under the ANALYSE tab, select Insert Slicer. On the Slicer window, select the project name (which will be used for filtering). Add Slicer as a parameter: Select Info of the excel -> select Browser View Options->a pop up window will appear -> Select Parameter tab -> Add the desired slicer so that it can be used as filter parameter in the web browser. Save and upload the excel report. Add a web part to add the excel report.   Navigate to the project site page where we need to add the excel reports (For example – Project Q 003). Open the page in edit mode . Click on Add a Web Part A selection window will open. From the Business Data category select Excel Web Access web part and click Add. The web part will be added. Click on link Click here to open the tool pane to edit the web part and set properties We need to fill out the workbook field with the URL of the workbook. Browse to the location of the excel report and add it. Add Content Editor web part to refer to the JavaScript file that will filter out specific project in the excel reports. Upload the JQuery file named “jquery-1.11.2.min.js” into a SharePoint shared document library present on the root site. Upload the Java script file named “QueryFilterData.txt” into a SharePoint shared document library present on the root site. Before uploading the custom  QueryFilterData.txt file, we need to update the Project Server URL. In this case – “http://tfspsdemo2013/PWA” is our development machine URL. Now add the content editor web part to refer to the JavaScript file. Click on Add a Web Part. A selection window will open. From Media and Content category, select Content Editor web part and add it. Edit the web part and enter the path of the JavaScript file (Name: QueryFilterData) added in Step 2 and click on Test Link. If the file opens in another tab then the link is correct. Click Apply and then click OK. Once all the steps are done, click Stop Editing and there you go! You will see the report data showing only filtered specific project data. You may also want to refer to the below Java script: Java Script Code:   To find the project name: function getProjectNameCallback() { projectUID = spWebProps.get_fieldValues()[“MSPWAPROJUID”]; projContext = PS.ProjectContext.get_current(); projects = projContext.get_projects(); projContext.load(projects, ‘Include(Name, Id)’); projContext.executeQueryAsync( iterateThroughProjects, errorCallbac); } function iterateThroughProjects(response) { var enumerator = projects.getEnumerator(); while (enumerator.moveNext()) { var project = enumerator.get_current(); var id = project.get_id(); if (id == projectUID) { projectdetail = project.get_name(); if (typeof(projectdetail) != undefined) { // alert(projectdetail); PageLoad();   } break; }   }  }; Set Project Name to the Slicer: Each EwaControl is associated with only one workbook. The EwaControl.getActiveWorkbook method returns the workbook associated with the specified EwaControl object. ewaInstance =Ewa.EwaControl.getInstances().getItem(index); (In Case there is single excel report then use Index as 0, in case of multi excel report we need to iterate through it). var objCollection = {}; (Array) objCollection[‘Slicer_ProjectName’] = [projectdetail];  (Slicer_ProjectName is the Excel Slicer Name) ewaInstance.getActiveWorkbook().setParametersAsync(objCollection, SetParamsAsyncCallback, null); (This will set the Slicer parameter on the report).  

How to authenticate Android, iOS apps to access Project 2013

Accessing OData endpoint of the Project Online 2013/Project Server 2013 in any custom mobile app requires some authentication mechanism. For accessing Project Online/Project Server 2013, you need to implement one of the below described authentication modes/method and also enable special permissions for the user who accesses OData endpoints via apps. Authentication Providers There are a variety of Security Support Provider Interfaces (SSPI) by Microsoft that are available for the authentication purpose. SharePoint users are most familiar with – Microsoft NTLM and Microsoft Kerberos. NTLM and Kerberos are the types of Windows Claims-based Authentication using Active Directory Services (AD DS) as the authentication store and user credentials validation. Kerberos as a security support provider has been a part of Windows since Windows Server 2000 and was proposed as a replacement for NTLM. The two often work together or in a mixture across Windows environments. Authentication methods for Android and iOS Project Online 2013 and Project Server 2013 support multiple authentication methods and authentication modes. When you plan for mobile app access, you must consider the available authentication methods for the mobile OS. 1.Claims-based authentication with mobile apps 2.NTLM authentication with mobile apps 1. Claim based authentication with mobile apps What is Claim? A claim is a simple piece of information that describes a given identity on some aspect. A claim can be held in the authentication token like an envelope that may contain other identifying information and a signature that can be used to verify that the token is not tampered on its way from remote machine to your system. Claim based authentication Claims-based authentication is now a more general way of authentication, which allows users to authenticate on an external system. It is the process of establishing users’ identity and ensuring that the information is not tampered. Courtesy TechNet library Flow for Claim based authentication with app 1.Request to Project Online 2013 via web view 2.System redirects user to the authentication page of Office 365 3.After successful authentication to Office 365, it will return cookie and tokens 4.App validates the cookie and tokens 5.Once the token and the cookie is received, the app is able to access ODATA 2. NTLM authentication with mobile apps What is NTLM authentication It is the windows challenge/response (NT-LAN manager) authentication protocol used on networks that include systems running the Windows operating system and on stand-alone systems. In terms of the mobile app, an Interactive NTLM authentication over a network typically involves two systems: a mobile app, where the user is requesting authentication, and a domain controller, where information related to the user’s password is kept. Un-interactive authentication, which may be required to permit an already logged-on user to access a resource such as a server application, typically involves three systems: a mobile app, a server, and a domain controller that does the authentication calculations on behalf of the server. Flow for NTLM Authentication with app 1.A user accesses a mobile app and provides a domain name, user name, and password. The mobile app computes a cryptographic hash of the password and discards the actual password. 2.The app sends the user name to the server (in plaintext). 3.The server generates a 16-byte random number, called a challenge and sends it to the mobile app. 4.The app encrypts this challenge with the hash of the user’s password and returns the result to the server. This is called the response. 5.The server sends the following three items to the domain controller:            1.User name            2.Challenge sent to the app            3.Response received from app 6.The domain controller uses the user name to get the hash of the password from the Security Account Manager database. This password hash is used to encrypt the challenge. 7.It then compares the encrypted challenge computed in step 6 to the response computed in step 4. If they are identical, authentication is successful. Working with Project Online 2013 Accessing Project Online 2013 via mobile apps require some more efforts. You need to develop the header to access project server online otherwise it will return an unauthorized access error. To create a valid header, first you need to verify two cookies FedAuth and rtFa. If they exist, then create a new header named Cookie and add to your request, then after the GET request can be made to the server. 1.Write a code to read data from server 2.Set HTTP method as ‘GET’ 3.Add another header Cookie and add both cookies ‘FedAuth’ and ‘rtFa’, which are required to access Project online Working with Project Server 2013 To access Project Server 2013, app required to implement NTLM authentication. For iOS, there is already a library which provides NTLM authentication, for android either you can write a code manually or can use third party libraries like https://github.com/masconsult/android-ntlm Accessing Project server OData endpoints with mobile apps Now that you have finished with the complicated part, all you need to do is, write a code to access web service end points (OData endpoints). OData API URL To access OData, you also need the URL of Project Online 2013 or Project Server 2013 OData API. In most of the cases it will be: / _api/projectdata/ Try this URL with your browser to list the end points. Then use endpoints according your need. Permissions required To access OData services, you need to enable reporting permissions which can be found in global permissions. Using $filter, $select and other oData query oData queries can be used with a mobile app by ensuring that the URL you develop for query contains valid characters. Conclusion Using the above described method, a user can authenticate mobile apps (Android or iOS) to access Project Online 2013 or Project Server 2013. Users can also call GET web requests in the app to access Project Online 2013 or Project Server 2013 data like current project, resources, timesheet, tasks and assignment information. For additional HTTP verbs like PUT and POST, one needs to add some

How to implement QuickProject in your organization

Microsoft Project Server 2013 is a flexible on premise solution for Project Managers, PMOs & team members in project planning, project tracking and managing portfolio; but it is only accessible via a PC or a laptop. Staying connected with the project is an all-time need for project team. Considering this challenge, Advaiya has developed QuickProject project management app that provides a mobile interface to help project managers and team stay connected with Microsoft Project Server 2013. It has an easy and user-friendly interface which helps to track and update assignments anywhere and anytime. We have talked a lot about the key functionalities of the project management app in these blogs: How is project management on the go with QuickProject QuickProject app for Project Server 2013 Update timesheets on the go with QuickProject       Let’s discuss on how you can implement this app.   To get started with this app for your organization, as administrator, you need to ensure: – You provide acces rights to Project Server 2013 onpremise or online (whichever you have) to the users for whom you want to configure the app – You also need to ensure that you enable the  & Status Broker Permission for these users. To enable ‘Access Project Server Reporting Service’ permission, follow these steps: – 1. Click on PWA/server settings 2. Select ‘Manage Groups’ under security section 3. Select group name 4. In Global Permissions, Under General section, check the check box for Access Project Server Reporting service. To enable ‘Status Broker Permission’, follow these steps – 1. Click on PWA/server settings 2. Select ‘Manage Groups’ under security section 3. Select group name 4. In Global Permissions, Under Time & Task Management section, check the check box for Status Broker Permission. The app works for both Project server 2013 (on-premise) as well as online accounts. If the app is configured with ‘Project server online’ PWA URL, the user will not be able to update assignment(s) and access timesheet to save and submit. If the app is configured with Project Server 2013 (on-premise), user can update assignments and also save and submit the timesheet.   The quick link of QuickProject app on iTunes is available here. Some minutes spent on this app, will definitely let the user understand its ease of use and benefits. So, what are you waiting for, enable the quick configuration for your organization and get the Project access on your mobile  

Advaiya announces the launch of QuickProject to manage project activities on the go

Advaiya introduces a MS Project timesheet app QuickProject for Project 2013 users, which allows project managers and team members to manage their project activities with an easy interface available on their mobile devices. QuickProject, available online on App Store, features easier project monitoring, faster project execution and increased user adoption and better team interaction, from anywhere, anytime. It is first of its kind Enterprise Project Management iOS app developed to work with Project 2013 on premise as well as online accounts and extends the capabilities of Project Server to mobile devices. “Company-wide adoption of Microsoft Project Server 2013 is a challenge for many organizations today. Without a desktop/laptop, employees are unable to view/update their task status, submit timesheets and view project synopsis”, said Swati Shrimali, EPM COE Lead at Advaiya. “This app helps project managers and team members in their day to day project management on the go or even while working remotely, helping organizations in timesheet compliance process.” QuickProject is an ideal solution to help project managers, resource managers and team members to view and update their projects details, assignments, and timesheet status and to track the project details from anywhere, anytime. This app will be available for Android users very soon. For more information on the QuickProject application, visit – QuickProject MS Project Timesheet App. You can download the app here – QuickProject iOS app