Create interactive Power BI reports for Project Server
Pictures are worth a 1000 words. This hold true whether you are giving directions to your favorite pub or presenting the status of your projects to your key stakeholders and sponsors. Every time you are in front of your executives you want to impress. So it goes without saying that you want to impress your executives when presenting your project details. The ideal report and dashboard need to accurately convey your project metrics while being interactive, which allows management to explore the data to derive deeper insights. With today’s technological advancements and always connected devices, organizational leaders expect to access and share these reports and dashboards within a few clicks. Of course, you don’t want to spend all day or randomize your key resources writing code to create the reports. Power BI allows you to quickly visualize project details, review project health, resources utilization, and more through the use of a simple OData Service. And all this, without the need for technical coding skills. To make Project Online (or Project Server) reports and dashboards in Power BI we simply establish an OData connection. Then we select the tables and fields we want to be part of our report. Finally, we choose the visualization that brings our reports to life. With Power BI we also have more advanced capabilities such as creating custom calculated fields and key performance indicators. Looking to gain more information about Power BI reports and dashboards, its features, and how to use visualization for creating interactive dashboards? Watch the recording of the webinar on Advanced Power BI reports on Project Server Data, and create stunning reports and dashboards using Microsoft Project Online data. Power BI helps in creating intuitive reports and dashboards that you can easily create and share. However, it only adds value if the features are used effectively. Some important features of Power BI are: 1. NQL: NQL termed as Natural Query Language is a very powerful feature of Power BI, where we explore our data using intuitive, natural language capabilities, and receive answers in the form of charts and graphs. 2. Sharing Dashboards: It allows sharing dashboards with colleagues, whether inside or outside the organization. It also allows for colleagues to re-share the dashboards. 3. Importing visuals in Power BI: It facilitates easy import of various visualizations available in Power BI Visual gallery. 4. Creating group workspaces: Creating a group in Power BI helps you collaborate on your shared reports, dashboards, and datasets. Know more on how you can leverage Power BI solutions to create impressive project graphs and visuals for executives or important client meetings.
Create custom list template in SharePoint 2007
A SharePoint 2007 list template can be easily developed to reuse it across an organization. In lieu of recreating the list every time you want to use it, a SharePoint list template can be created once, which can then be used whenever creating a new list. In this blog, we will learn to create a custom solution for SharePoint 2007 that will have custom content types, using which we can create a custom list template. To start with, we need to install WSPBuilder on our development server that will help us to create custom solution in Visual studio 2005. Once we are done with configuring the WSPBuilder tool on our server, we can start with a custom solution creation. Open Visual studio and go to File > New > Project that will open a window, where we can see WSPBuilder in Project types. Select WSPBuilder project template from the left window, and name the project as SP2007Demo and click OK. As we are using the WSPBuilder project template for our solution, it by default provides a solution structure that can be used for SharePoint site. Here, we do not need to create the whole structure on our own. Below is the solution structure we get: Now to create a custom list template for SharePoint, we need to follow the steps below: Create custom site columns Create custom site content types Create Custom list template Create custom site columns In this section, we are going to create a few custom fields/columns which will be a part of the list template we are planning to create. To add custom fields in SharePoint site, we need to use the SharePoint feature through which we can deploy our custom fields on SharePoint site. So let’s create our first SP2007 feature. Right click on Project and select Add > New Item. A dialog box will open as below: Select Blank Feature and name it ConfigurationFeature, and click on Add button. It will open another dialog to setup feature settings. Set Scope value as Site. This will add our first feature in SharePoint solution: Edit the element.xml file with following values: We have created an XML structure for our custom fields, which are going to be deployed on SharePoint 2007 site through ConfigurationFeature feature we added. Note: To generate ID you can use the Create GUID feature available under Tools in Visual Studio. Now let us deploy this feature on our site and see do we get these columns in our site or note. To deploy the solution on SharePoint, right click on Project and under WSPBuilder, click on Deploy. Once the deployment gets done, go to the site and check site collection feature where you will be able to see a custom feature in list. Click on Activate button and go to Site Settings > Site Columnspage, which will show all the site columns available in your site. Select Custom Columnsgroup from dropdown available on right side on page to filter the list. There we can see the fields we have added through our custom feature. Create custom site content types Once we are done with creating our own custom fields for SharePoint site, the next step is to create a site content type. So what exactly a site content type is and what’s the use of it? The answer is, Content Type gets used for virtual grouping of SharePoint columns or fields and use it as a reusable component. Content type contents reference of all the fields which you want it to be part of that Content Type. Now let’s create a custom content type with reference of the above fields we have created. Add one more feature like we did in Create custom site columns section as name EmpContentTypeFeature and the scope of that feature will be Site level. Once the feature gets added update the elements.xml file as below screenshot. In the above screenshot, as we see, we have referenced the custom fields we have created by using their ID and Name properties. Once you deploy the solution on your site and activate EmpContentTypeFeature feature, you will be able to see the EmpContentType content type by going at Site Settings > Site Content Type page. This content type can be used for any list, library or pages. Create custom list template After creating custom content type which is referencing our custom fields only, we will leverage that content type and create a list template out of it. To create custom list template first we need to do is add a new feature in our solution with name EmpListTemplate and update the element.xml file of that feature as below: Here we have used this XML file to create a custom list template with name as EmpListTemplate with some predefined properties like i.e. Name, Type, BaseType, etc. Now, for creating this custom template we provide schema, to do so we need to add a folder in this feature with name EmpListTemplate that is same as the Name property in element.xml file. In that folder, add an XML file with the name schema.xml. Now once you add that, your solution will look like this: To update the schema.xml file, we have to use a predefined format that will contain the definition for list templates i.e. View, Content Type, fields, etc. We can get the format from the following location: C:Program FilesCommon FilesMicrosoft Sharedweb server extensions12TEMPLATEFEATURESCustomListCustListschema.xml. Copy the file content in our solution schema.xml file and update the file as below: Once we deploy the solution on our site, we can see a custom template once we go to View All Site Content > Create.
SharePoint 2016 – new, improved and deprecated features
Unlike the early days of the Web, today, content management system (CMS) is a combination of multiple tightly-integrated systems. CMSs are evolving everyday with new features. The SharePoint 2016 is the best example of it, where the technology is bridging the gap between the data residing inside organizational premises and that outside it along with the implementation of viewership and rights to it. SharePoint 2016 has evolved in capturing, storing and maintaining large volumes of data. The changes made and the new features list is quite vast; however, here I explain some of them which may come handy.
Migration from Project Server 2007 to Project Server 2013
Recently, for one of our clients, we migrated Project Server 2007 to Project Server 2013. The client had been using Project Server 2007 since past four years, but was looking for an upgrade to experience extensive capabilities and new features of Project Server 2013. There is no direct method to migrate from Project Server 2007 to Project Server 2013. You have to first migrate to Project Server 2010 and via that you can upgrade to Project Server 2013 through database migration. Migration prerequisites Project Sever 2007, SharePoint 2007 and SQL Server 2005 should be updated to the latest available service packs. If they are not already installed, first install the service packs. Install new fresh hardware for Project Server 2010 and Project Server 2013 with latest service packs. Steps to be performed on Project Server 2007 Open SharePoint Central Administration, navigate to Project Server Service application page and find the archive, draft, published, reporting and SharePoint content database for the PWA site to be migrated, and make a note of it. Open SQL Server and take a backup of these five databases – archive, draft, published, reporting and SharePoint content database, and save it in a folder in a hard drive. Copy the saved backup files and save it in 2010 database server. Steps to be performed on Project Server 2010 Restore the five databases in SQL 2008 server. Mount the SharePoint content database using SharePoint Management Shell script. Example : Mount-SPContentDatabase -Name -WebApplication Create the PWA site using SharePoint Management Shell script. Example : New-SPProjectWebInstance -Url http://<2007ApplicationServerName>/pwa -AdminAccount “” -PrimaryDbserver “<2007DatabaseServerName>” -PublishedDbname “” -ArchiveDbname “” -DraftDbname “” -ReportingDbserver “<2007DatabaseServerName>” -ReportingDbname “” PWA Site is now created. You can see the home page. Turn off backward compatibility mode in Project Server 2010 from Project Server 2010 home page. Click Server Settings on the Server Settings page, in the Operational Policies section, and click Additional Settings. On the Additional Settings page, in the Project 2007 Compatibility Mode section, clear the Enable Project 2007 Compatibility Mode check box and click OK. Bulk update the project sites so that all issues and risks are created in 2010. Test and verify if all data is correctly migrated. Also, create new projects to check if they are correctly created. Take a backup of the five databases – archive, draft, published, reporting and SharePoint content database for migration to 2013 and save it in a hard disk. Steps to be performed on Project Server 2013 Restore five databases in SQL 2012 server. Test the content database using SharePoint Management Shell script. Example: Test-SPContentDatabase -Name –WebApplication http://<2013ApplicationServerName>/ Once successfully run, mount the database using SharePoint Management Shell script. Example: Mount-SPContentDatabase –WebApplication http://<2013ApplicationServerName> –NoB2BSiteUpgrade Make your account the secondary owner of the PWA Site using SharePoint Management Shell script. Example: Set-SPSite -Identity http://<2013ApplicationServerName>/pwa -SecondaryOwnerAlias “” If you are migrating Project Server 2010 users who were using Windows Classic authentication over to claims-based authentication, you will need to run the following SharePoint Management Shell script. Without running this script, users will not be able to log on to Project Web App after upgrade. Example: (Get-SPWebApplication http://<2013ApplicationServerName>).migrateUsers($true) Check your PWA Site collection using SharePoint Management Shell script for problems that can cause upgrade failure. Example: Test-SPSite –Identity http://<2013ApplicationServerName>/pwa Upgrade the Project Web App site using below SharePoint Management Shell script. Example: Upgrade-SPSite -Identity http://<2013ApplicationServerName>/pwa –VersionUpgrade Consolidate your Project Server 2010 databases to a Project Web App database. Example: ConvertTo-SPProjectDatabase -WebApplication http://<2013ApplicationServerName> -dbserver <2013DATABASESERVERNAME> -ArchiveDBName -DraftDBName -PublishedDBName -ReportingDBName -ProjectServiceDBName <2013DatabaseName> Attach the Project Services database to the web application using SharePoint Management Shell script. Example: Mount-SPProjectDatabase –Name <2013DatabaseName> –WebApplication http://<2013ApplicationServerName> Check your Project Web App database for problems occurring to cause upgrade to fail using SharePoint Management Shell script. Example: Test-SPProjectDatabase –Name <2013DatabaseName> Upgrade the Project Web App database using SharePoint Management Shell script. Example: Upgrade-SPProjectDatabase -Name <2013DatabaseName> -WebApplication Mount the Project Web App instance using SharePoint Management Shell script. Example: Mount-SPProjectWebInstance –DatabaseName <2013DatabaseName> -SiteCollection Check the Project Web App instance for any problem occurring to cause upgrade failure using SharePoint Management Shell script. Example: Test-SPProjectWebInstance –Identity Use the below SharePoint Management Shell script to get results of the health check in notepad to get more detailed and helpful information. Test-SPProjectWebInstance –Identity/pwa | Format-Table -Wrap -AutoSize | Out-File -FilePath c:output.txt Upgrade the Project Web App instance using SharePoint Management Shell script. Example: Upgrade-SPProjectWebInstance -Identity Enable Project Web App features using SharePoint Management Shell script. Example: Enable-SPFeature -Identity pwasite –URL http://<2013ApplicationServerName>/pwa Bulk update all sites to get issues, risks and documents restored. Check with the previous environment and verify if there is any inconsistency in data.
Critical Path Method: Some rudimentary knowledge
What is the roadmap to follow to ensure a project schedule will be successful or not? How would we be able to deliver it on time? These are some questions that project managers usually ask themselves. Well, the answer is ‘Critical Path Method’ — any organization which can successfully manage all the activities on the critical path can deliver the project on time. If we go by the Project Management Book of Knowledge (PMBOK) 5.0, critical path can be defined as “a sequence of activities that represents the longest path through a project, and determines the shortest possible duration.” Activities or tasks on this path have to be dealt with the utmost urgency to smoothly run your project. If the schedule goes wrong for the activities on this chain of activities, your project is doomed to be delayed and potential risks will become issues, and change requests will be needed. Want to know more about critical path and how you can see it using MS Project? Learn more about the critical path and how Microsoft Project helps, in this webinar on Using Microsoft Project to determine where projects lie in relation to the critical path” on April 20, 2016 by Darrin Lange, director of operations and project management at Advaiya. Determining critical path tasks is a one step process in Microsoft Project, where you enter your tasks and establish predecessors and duration. The following processes and tasks establish the critical path, which play an important role in determining and monitoring the project. Forward Pass: A process of using a fixed project start date and activity duration to calculating the early start and early finish dates for all of the activities along a chain or path. This includes both the critical path and non-critical paths. Backward Pass: A process of using either a fixed end date or the end date determined by the forward pass and activities during to calculating the late start and late finish for all activities along a chain or path. Total Float: It is the amount of time by which an activity may be delayed from its early start without delaying the project finish date. Float can change as the project progresses and changes are made to the project plan. Also known as total slack or path float. Free Float: It is the amount of time by which an activity can be delayed without delaying the early start of any immediately following activities. For a rudimentary use of critical path method, the following are some essential requirements to be fulfilled: List of activities (also known as your WBS) Duration required for task completion The dependency between predecessors and successor activities Rational endpoints like milestones, deliverables or the project Knowing your critical path activities well can ensure, not only project completion on-time, but the most effective type of risk mitigation, successful escalation negotiation with resource managers and more effective status reports to your sponsor.
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. Go to report design and right click on report area. Select Insert -> Chart 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”) Remove Chart Title, Axis Title, Legend Enable Show Data Label in chart and modify the series label property to show Milestone Name Modify the Horizontal Axis property Major Tick Marks Modify horizontal axis property Axis Type, and disable side margins. . Modify horizontal axis property Line and make the Label Font bold. Now select the Bar and go to the property window by pressing F4 and set properties as shown below: 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.
Add custom validations to JS Grid 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: Create a SharePoint page and add My Task Web Part on it. 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. 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. 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.
Webinar: Combine Agile and MS Project for better 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- Go to PWA Site Settings – > Site columns Click Create 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 Go to PWA Site Settings -> Site Content Type Click Create Create a custom content type as Project Action Content Type with below selections: Click OK. On the content types settings page, click Add from existing site column. Add Project Action Outcome column to it. c) Add a newly created content type to the workflow task list. Open Project Server Workflow Task List Go to List Settings -> Add Existing Content Type Add Project Action Content Type d) Update the wokflow- Open/create Project Server workflow in SharePoint Designer 2013 Open the assign task property Expand the Outcome options Select the task content type as Project Action Content Type The output will look like below: Click Save and publish the workflow. 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. Shruti Vyas Shruti is a technical consultant at Advaiya. With over nine years of experience in the IT industry, Shruti has a passion for data and an ability to understand and analyze it effectively. She shares her insight on various topics such as custom application development, project server customization, client-side scripting etc.