Reporting

This section provides additional detail on report functionality within Altus.

• SSRS Reports
• Power BI Reports

SSRS Reports

SSRS reports for Dynamics

Dynamics and Model Driven Power Apps have the facility to render SQL Server Reporting Services (SSRS) reports. To create and customise them requires a very specific tool-chain.

To get started with using SQL Server Reporting Services (SSRS) report with model-driven PowerApps, you need to run some specific software. You will need to use FetchXML to write queries against Dataverse tables. You will need to install the following software in this order.

1. Install Visual Studio Enterprise 2019
   • Include the 'Data storage and processing' workload under 'Other Toolsets'
2. Install Visual Studio Extensions
   • Web Live Preview (Preview)
   • Microsoft Reporting Services Projects
   • Microsoft Analysis Services Projects
3. Install the v9.0 Dynamics 365 Report Authoring Extension

You can then use Visual Studio 2019 to author reports.

To add a datasource, you use an embedded connection and choose “Microsoft Dynamics 365 Fetch” as your type. For the Connection String, type in your Dynamics 365 root URL.

Add a dataset and use FetchXML as your query text. Note, you can use FetchXML Builder from the XRM Toolkit to help you build FetchXML queries.

Save the .rdl file and then to Power Apps and navigate to your solution. You can then upload your report and choose the related table (such as projects for a project status report). To overwrite an existing report, go into Edit mode for the report and then select Choose File and upload your .rdl to overwrite.

Power BI Reports

Steps to Update Power BI Reports to the Latest Versions

Altus is deployed with a set of Power BI reports to provide insights into the data contained within the app. After the initial deployment of the solution and associated report files, these files are no longer updated, as many of our clients opt to customize the reports to deliver insights into the customized version of the solution and model-driven app. Periodically, updates to these Power BI files occur and in order to receive these updates, our clients or consultants can follow the steps below to publish the new files, and opt to either overwrite the existing version or publish as a new file.

Note

If opting to overwrite as the update method, any customizations to the report will be lost. If customizations have been made to the reports our recommendation would be to publish the report using a new file name so that your organization can review and compare the versions and choose which file you would like to embed in Altus.

1. Access the Altus Power BI versions page by clicking on the “secret” button inside of the ‘Info Pane’ on the first page of any of the Reports that are deployed to your Altus app.
   
2. From the Altus Report Template Versions page, select and download the file(s) you wish to install. Version notes are available below the table of report templates. You can use the quick links to jump to a specific version on the right.
   
3. For each downloaded template file (.pbit) complete the following steps:

• Open the file in Power BI Desktop.
• Provide the Altus URL for your Altus Deployment in the parameter for “Altus URL” in the format: https://ALTUSENVIRONMENTNAME.crm#.dynamics.com (for the Altus Portfolio Report, leave the Idea Effort Multiplier parameter set to 100)
• Provide the Altus_TDSServer value, which should be just the server name from the Altus URL above, example: ALTUSENVIRONMENTNAME.crm#.dynamics.com
• Provide the Altus_TSDDatabase value, which should be just the environment name value from the Altus URL above, example: ALTUSENVIRONMENTNAME
• Sign in if prompted using credentials that have access to the data source (Dataverse)
• Specify "Organizational" for privacy settings if prompted.
• Click “OK” and wait for the data to load to the report.
• Once the data has loaded, feel free to browse the report and view the changes specified by the versions page in the new template.
• OPTIONAL: from the File menu > Options and Settings > Options > Current File > Data Load tab, click to “Enable parallel loading of tables”. This will allow refreshes to occur in less time as the data will be loaded to the tables in a parallel manner instead of waiting for each table load to finish before loading the next table’s data.
• Click “Publish” from the Home ribbon > Share section.

• Choose the workspace for your Altus reports.
• If a file with the same name already exists in the workspace, you will be notified that you will be overwriting the existing version. In this case, you will NOT need to update the Altus Configuration Setting for the report with a new embed link.

Note

If you do not want to overwrite the existing version, save the pbix file with a new filename first and republish to the workspace.

• If you choose to save and publish the file with a different name, you’ll need to update the Altus report configuration setting with a new embed link (see below).

Steps to configure a Altus Report Configuration setting with a new embed link

1. Review the table below to understand which setting to Update:

   Power BI Report Configuration Settings

   Report	Setting
   Altus Work	PowerBIReport_MyWork_IQA
   Altus Project	PowerBIReport_Insights_IQA
   Altus Portfolio	PowerBIReport_PortfolioInsights_IQA
   Altus Portfolio – Innovation	PowerBIReport_InnovationInsights_IQA
   Altus Portfolio – Intake	PowerBIReport_IntakeInsights_IQA
   Altus Strategy	PowerBIReport_StrategyInsights_IQA
   Altus Resource	PowerBIReport_ResourceInsights_IQA

2. Open the that requires an update in the Power BI Workspace for Altus.

3. Click the “File menu > Embed Report > Website or portal” option.
   

4. Copy the embed link from the top box in the dialogue that will appear.
   

5. Navigate to Altus > Settings area > Configuration Settings.
   

6. Open the setting specified in the table above for the report that you wish to update. In the example below, we’re updating the Altus Project Report, so we’re using the PowerBIReport_Insights_IQA setting.

7. Paste the copied embed link into the “Value” input box.
   

8. Click “Save and close”.

Reporting on Sensei_Tasks and Sensei_Assignments in Altus Reports

When a user sets the 'Effort' on a Sensei_task (This is a task using the standard Altus scheduling tool inside Altus), the value entered for effort will be split evenly in the reports between all resources assigned to the task.

As an example, a Project Manager sets the effort on a Sensei_task in their task list at 100 hours and the Project Manager then assigns 4 users to the task. Each resource would be “allocated” 25 hours of the effort for this task in the reports. Assignment units have no bearing on this division of effort. As the task is progressed, it is assumed that it is progressed evenly across all assignments. So if the same task mentioned above is progressed to 50%, then each resource "assignment" in the Altus reports will have 12.5 hours of effort completed, and 12.5 hours of effort remaining.

Synthesizing Time-phased assignment data from Altus Schedule Assignments

In providing core scheduling capability, Altus schedule does not store the assignment or task-level values in a time phased way. This means that we do not have access to the per-day values for work that we might find in Project Online or other Project management tools.

In order to synthesize this data from the assignment data that we do have access to in Dataverse, a method was utilized in which a row is created for each day for an assignment using the start date and finish date. These rows are then filtered by the rows in the resource capacity table, ensuring that rows for days on which the resource is not working are not included. Finally, the number of rows (working days) for each assignment is calculated by counting the remaining rows for each assignment, and then the total effort for the assignment is divided by this number giving us the daily effort for each assignment. An example of this process is depicted in the animation below.

Please note, this method assumes that the effort is spread evenly over all working days for the assignment and results in a flat (un-contoured) assignment, as the true daily values are unavailable for reporting.

Also of note is that because generic resources do not have associated capacity, there will not be an opportunity to synthesize the time-phased resource assignments for this resource type.

External Execution Tool Assignment Effort

There is a limitation when using External Execution tool Task Sync where if tasks in an external execution tool that are assigned to multiple resources have varying values for work (effort) assigned to each resource, these specific values are not synced into Altus. For example, if a task from Project Online (external execution tool) has three resources assigned to it, with a total work value of 100 hours, is split unevenly in the following manner: 50 hours for resource 1, 25 hours for resource 2 and 25 hours for resource 3, these assignments are imported into Altus with an EVEN split on the task, and the reporting for the Altus "assignments" will indicate 33.33 hours for each resource. This is due to the nature of the Altus Schedule control, and the fact that the work/effort data is not stored at an assignment level or in a timephased manner.

Because of this limitation, Altus recommends that when choosing to sync tasks from an external system the Managers of those schedules should follow the suggestions below:

1. Only assign one resource to each task on the external schedule.
2. If assigning one resource to each task is not possible, assume/allow for an EVEN split of the assignment work (effort) on the task.

If following the above recommendations is not possible, your users should understand that there will be some degree of data ambiguity at an assignment level in your Altus reports.

If your organization feels that this is unacceptable, you may choose to work with your Engagement Lead to customize your reporting to obtain data directly from the source execution tool system using Professional Services hours.

Creating a pre-filtered Status Report embedded in Teams

Altus Project Insights report has been updated with v2021.04.30 to support use of the “Project Status” hidden page auto filtered for a specific project. This allows a Project Manager or Project Team to view a pre-filtered version of this report page in their Teams Channel Tab without having to click to drill through in the standard embedded report. To add this to a specific Team Channel follow the steps below:

1. You will need to create a URL for embedding using two pieces of information:
   

   1. The URL to the hidden Report tab “Project Status”
      
   2. The name of the Project you would like to filter for.

2. To recover the URL to the hidden Report tab “Project Status”, Navigate to your Power BI workspace that contains the Altus reports. Open the “Altus Project” report, and from the “All Projects” page, right click the Project you’re interested in creating a pre-filtered status report for and click “Drill through > PROJECT STATUS”
   

3. Copy the URL from the address bar to your clipboard. It should appear to be in the following format: https://app.powerbi.com/groups/56fb10f4-70fa-426e-88ad-b88a03bc3d29/reports/510be801-8af0-4548-8c9f-65c69e82951f/ReportSectione1da9ba420a88d51560b

4. To be sure you have the exact name of the project, open the “Filters” pane and look at the “Filters for this page > Project filter” – make note of the name in that filter. In our example it is “AA Tenant Renewal R300”.
   
   

5. Ensure that before you leave the Power BI Workspace, you collapse the filters pane and navigation pane.

6. At this point, you can create your Teams Embed URL by putting this data together in the following fashion.

   https://app.powerbi.com
     /groups/56fb10f4-70fa-426e-88ad-b88a03bc3d29
     /reports/510be801-8af0-4548-8c9f-65c69e82951f
     /ReportSectione1da9ba420a88d51560b
     ?filter=Projects/Project eq 'ProjectName'
   

   So the example for our project above would look like the following:

   https://app.powerbi.com/groups/56fb10f4-70fa-426e-88ad-b88a03bc3d29/reports/510be801-8af0-4548-8c9f-65c69e82951f/ReportSectione1da9ba420a88d51560b?filter=Projects/Project eq 'AA Tenant Renewal R300'
   

7. From here, navigate to Teams, and to the channel that represents your project.

8. Click the ‘+’ symbol on the tabs to create a new tab and select “Website”.
   

9. Name the tab “Status Report” and paste in the URL that you’ve constructed for your Project Status report. (Optionally deselect “Post to the channel about this tab”.)
   

10. Test the new tab by clicking on it to see the pre-filtered report.

We like this method for embedding the Project Status Report, as the controls for Power BI are still visible in the frame, and you can use helpful features like bookmarks, comments, and other visibility controls.

Note

The above process can also be used to create pre-filtered Portfolio and Program status reports by using the Portfolio and Program status reports from the Altus Portfolio Insights report. The filters will be in the following format: ?filter=Portfolios/Name eq 'PortfolioName' and ?filter=Programs/Name eq 'ProgramName'

Note

Be careful with the single quote character in the above URLs - the quote must be a "straight single quote" like this ' and not an "opening single quote" or "closing single quote" which tend to be curly in appearance like this ‘ and ’ .

Adding/Embedding new or custom Power BI reports in Altus

The standard Power BI reports embedded in Altus are configured in a unique way, where the embed link is read from a configuration setting and an iframe is used to display the Power BI Report. Microsoft has now updated Power Apps to allow for embedding reports and dashboards from Power BI workspaces in a simpler way. Please refer to the documentation here for steps on how to do this: https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/create-edit-powerbi-embedded-page

Creating a Custom Project for the web DataReader Security Role

To facilitate the retrieval of data from a Project for the web Dataverse Database, an account must have permissions to read the data from the Project for the web tables. Some organizations choose to simply provide the account that is accessing the data (credentials running the data flow) with the “System Administrator” role in the Power Apps environment (typically the default environment in a tenant). However, following the principle of least privilege, it is our recommendation that a custom security role be created in the P4TW environment that provides read permissions to the Project for the web tables and no others.

Steps to create a custom security role for P4TW DataReader

• Access the Power Apps admin portal at https://admin.powerapps.com
• Once in the portal, select the environment where the changes must be applied
• Click “Settings”
• Expand the “Users + permissions” grouping and click “Security Roles”
  
• Click “New Role”.
  
• Give the role a name, we have used the name “P4TW DataReader”, which we will reference throughout the steps below.
  
• On the following tabs, provide “read” access to the following tables at an organizational level (full green circle).
  • “Service” tab
    • Bookable Resource
  • “Business Management” tab
    • User
  • “Custom Entities”
    • Project
    • Project Bucket
    • Project Task
    • Project Task Dependency
    • Project Team Member
    • Resource Assignment
    • Roadmap
    • Roadmap Item
    • Roadmap Item Link
    • Roadmap Row Link
    • Roadmap User Setting

An example of what this looks like is below for the last 5 tables in the Custom Entities table.

• Click “Save and close” to save the new security role.

Apply the New Custom Role to the Account Running the P4TW to Altus Dataflow(s)

Once the role has been created, it must be applied to the account whose credentials are used for the authentication for the data flow from P4TW to Altus. The data flows for P4TW as an execution tool currently queries for data from the Project, Resource Assignment, and Project Task tables, however, in order to facilitate ease of customization, we’ve defined the role above to access all potential P4TW or Roadmap data.

To apply the role:

• Apply the new role to the user account that is running the data flows to Altus. This can be done a number of ways, navigate to the environment “Advanced Settings” using the “Settings” cog.
• Click the “Settings” menu, then “Security”.
  
• Click “Users”.
  
• Find the user whose credentials will be used for the data flow to Altus. Click the checkbox next to the user name, then click “Manage Roles”.
  
• Find the “P4TW DataReader” role and check the checkbox for it, then click “OK”.
  

Note

If you have created a new user account for NO OTHER purpose in your environment, you should not require any other roles. However, if the user is used for other purposes, please ensure that the appropriate roles required are left on the user.

Run the Data Flow to ensure that data is accessible for retrieval into Altus

Now the data flow can be either refreshed (if it was already configured), or it can be configured using the credentials for the account specified.

㊙