Share via

Create a report by using Power View to connect to a cube

  • Article

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

Power View is a tool that you can use to create highly-interactive, ad-hoc reports. This article helps you create a Power View report that displays data from a Microsoft SQL Server Analysis Services cube. To create the report, you must first select the cube that you want to use, and create a data source to connect to that cube. Then, you can create the Power View report by selecting the fields that you want to display on the report. Finally, if you are a system administrator, you can display the report in a web part on a Role Center page in Microsoft Dynamics AX.

Tasks

1. Verify prerequisites

2. Create a data source to connect to a cube

3. Create the report

4. Display the report on a Role Center page

 Resources

Walkthrough: Creating an Analyze Data Button on a List Page (article)

Power View documentation (articles)

Better together: Microsoft Dynamics AX 2012 R2 and SQL Server Power View (blog post)

1. Verify prerequisites

To complete the procedures in this topic, verify that the following requirements are in place.

Required software

The following software must be installed in the Microsoft Dynamics AX environment:

Requirement

Notes

Microsoft Excel

Excel 2013 Professional Plus Edition

Microsoft SharePoint Server 2010 or 2013 Enterprise Edition must be installed.

For information about how to install SharePoint Server 2010, see Deployment for SharePoint Server 2010.

For information about how to install SharePoint Server 2013, see Install SharePoint 2013.

Microsoft SQL Server Reporting Services 2012 Service Pack 1 must be installed in SharePoint integrated mode with cumulative update 4 applied.

-OR-

Microsoft SQL Server Reporting Services 2014 must be installed in SharePoint integrated mode.

For information about how to install Reporting Services in SharePoint integrated mode, see the Configure Reporting Services 2012 or 2014 in SharePoint integrated mode section of Before you install the Reporting Services extensions.

If you install Reporting Services 2014, you must modify the SharePoint web.config file. This file is typically located at C:\Inetpub\wwwroot\wss\VirtualDirectories\80. Add the following line of code to the appSettings section:

<add key="DynamicsAxPowerViewSQLVersion" value="12"/>

Microsoft SQL Server Analysis Services 2012 Service Pack 1 must be installed in multidimensional mode with cumulative update 4 applied.

-OR-

Microsoft SQL Server Analysis Services 2014 must be installed in multidimensional mode.

For information about how to install Analysis Services in multidimensional mode, see Install Analysis Services in multidimensional and data mining mode.

If you install Analysis Services 2014, you must modify the SharePoint web.config file. This file is typically located at C:\Inetpub\wwwroot\wss\VirtualDirectories\80. Add the following line of code to the appSettings section:

<add key="DynamicsAxPowerViewSQLVersion" value="12"/>

The Power View site collection feature in SharePoint must be activated.

For information about how to activate this feature, see the Active the Power View Site Collection Feature section of Install Reporting Services SharePoint Mode as a Single Server Farm.

Microsoft Dynamics AX 2012 R2 with cumulative update 6 or higher, or Microsoft Dynamics AX 2012 R3 must be installed. The following components are required:

  • Enterprise Portal and Role Centers must be deployed to SharePoint.

  • The cubes that are included with Microsoft Dynamics AX must be deployed to Analysis Services.

For information about how to install Microsoft Dynamics AX, see Install Microsoft Dynamics AX 2012.

For information about how to deploy Enterprise Portal and Role Centers, see Checklist: Deploy an internal Enterprise Portal site that has Role Centers.

For information about how to deploy the cubes, see Checklist: Configure Analysis Services and deploy cubes.

If Power View is not enabled in the environment before Role Centers are deployed, you may need to complete the following steps to redeploy some specific Role Center pages. Keep in mind, when you redeploy the Role Center pages, you will lose customizations made to the pages.

  1. Open the Microsoft Dynamics AX client with administrator privileges.

  2. Open the AOT.

  3. Expand the Web > Web Files > Page Definitions node.

  4. Right-click RoleCenterCFOPV and click Deploy Element.

  5. Right-click RoleCenterTreasurerPV and click Deploy Element.

  6. Right-click PowerViewDataSourceGeneratorPV and click Deploy Elements.

If you receive errors when trying to redeploy the files listed above, you may need to complete the following steps:

  1. In the AOT, expand the Classes node.

  2. Verify that the SrsReportHelper class includes the removeFromCache method.

  3. Right-click the removeFromCache method and click Compile.

    The method should compile without errors or warnings.

  4. Click Build > Generate Incremental CIL.

    When the process is complete, the Infolog form will appear and display a message stating that the incremental CIL generation is done.

  5. Redeploy the page definitions mentioned in the previous procedure.

Required permissions

To create a Power View report, you must have the permissions listed in the following table.

To create a Power View report, you must:

Articles that explain how system administrators can give you permission:

Be assigned to a Microsoft Dynamics AX role.

Assign users to security roles

Have permission to view reports in SharePoint.

Security settings for reports (See the Configure security settings in SharePoint section.)

Be assigned to a role in Analysis Services that has access to the cube that you want to use.

Grant users access to cubes

Default Analysis Services roles

2. Create a data source to connect to a cube

The first step is to determine which cube contains the data that you want to display on the report. To view the data that is contained in each cube, see Cube and KPI reference for Microsoft Dynamics AX 2012 R2.

After you determine which cube you want to use, you may need to create a data source to connect to that cube. By default, data sources that connect to the Accounts payable, Accounts receivable, General ledger, and Sales cubes have been created for you. If you want to display data from any other cube, you must use the following procedure to create the data source.

  1. Open your browser and go to the Enterprise Portal site. The URL for the Enterprise Portal site is typically http://[SharePointServerName]/sites/DynamicsAX.

    Note

    For security reasons, integrating Power View with Microsoft Dynamics AX is not supported in environments where the Enterprise Portal site is configured for multiple data partitions.

  2. Go to the Power View Reports folder. The URL for this folder is typically http://[SharePointServerName]/sites/DynamicsAX/Power%20View%20Reports.

  3. Click Documents > New Document > Report Data Source. The Data Source Properties page is displayed.

  4. In the Name field, enter a name for the data source. For example, if the data source will connect to the Retail cube, you may want to name the data source Retail cube.

  5. In the Data Source Type area, select Microsoft BI Semantic Model for Power View.

  6. In the Connection string area, enter the following connection string:

    Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=[ServerName];Initial Catalog=[DatabaseName];Locale identifier=[LocaleID];Cube=[CubeName]

    The following table lists the documentation conventions that are used in the connection string.

    Value

    Description

    [ServerName]

    The name of the server on which Analysis Services is installed.

    [DatabaseName]

    The name of the Analysis Services database that contains the cube that you want to connect to.

    [LocaleID]

    The identifier that designates the language of the labels to display on the report. For example, if you want measures and dimensions to be shown in German, you must add German translations to the cube and specify the locale identifier for German.

    For more information about how to add translations to a cube, see Update an Existing SQL Server Analysis Services Project. For a list of the locales that are supported by both Enterprise Portal and SharePoint, see Install Enterprise Portal on a single server.

    [CubeName]

    The name of the cube that contains the data that you want to display on the report.

    For example, if you want to connect to the Retail cube that is provided with Microsoft Dynamics AX, the default connection string is:

    Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=[ServerName];Initial Catalog=Dynamics AX initial;Locale identifier=1033;Cube=Retail cube

  7. In the Credentials area, select Windows authentication (integrated) or SharePoint user.

    Note

    If Analysis Services and Enterprise Portal are installed on different computers, you must use Kerberos security. To configure Kerberos security, see this white paper. After you have configured Kerberos security to work with the servers, complete the following steps:

    1. In the Credentials area, select Stored credentials.

    2. Enter the user name and the password for the account that is assigned to the Analysis Services server administrator role. In most cases, this is the Business Connector proxy account. For more information, see the Assign the Business Connector proxy account to the Analysis Services server administer role section in Before you configure Analysis Services.

    3. Select the Use as Windows credentials check box.

    4. Select the Set execution context to this account check box.

  8. Click Test Connection to verify the connection to the cube.

  9. In the Availability area, select the Enable this data source check box.

  10. Click OK. The data source is created.

3. Create the report

To create and design the Power View report, complete the following procedure.

  1. Go to the Power View Reports folder. The URL for this folder is typically http://[SharePointServerName]/sites/DynamicsAX/Power%20View%20Reports.

  2. Click the data source that you created in the previous procedure. A blank, untitled Power View report is displayed in your browser.

  3. To add fields to the report, select the fields in the Field List pane, or drag the fields onto the report.

    For example, suppose that you want to create a report that shows sales amounts for your retail stores. In this scenario, you would:

    1. Expand the Retail channel node and select Retail channel – Name.

    2. Expand the Customer invoice lines node, and select Customer invoice line amount – accounting currency.

    3. Expand the Date node and drag Month to the Filters area of the report.

  4. Enter a name for the report in the title area.

  5. Use the options on the Styles, Design, and Layout tabs to format the report as necessary. For more information about how to use the Power View options to format the report, see Power View Design Experience in the SQL Server documentation.

  6. Save the report.

4. Display the report on a Role Center page

To display the report in a web part on a Role Center page, complete the following procedure. You must be a Microsoft Dynamics AX system administrator to complete this procedure.

If you’d rather display a link to the report in the Quick Links web part on a Role Center page, follow the instructions in Manage quick links (Enterprise Portal).

  1. Open the Microsoft Dynamics AX client.

  2. Click System administration > Common > Users > User profiles. The User profiles form is displayed.

  3. Select the Role Center that will display the report.

  4. Click View role center. Your browser opens and displays that Role Center page.

  5. Click Site Actions > Edit Page. The page is displayed in design mode.

  6. Find the location where you want to display the report, and click Add a Web Part in that area. The top of the page now displays an area where you can select the web part that you want to add.

  7. To select the web part that you want to add, do the following:

    1. In the Categories area, select Microsoft Dynamics AX.

    2. In the Web Parts area, select SQL Server Power View.

    3. In the About the Web Part area, click Add.

    The SQL Server Power View web part is displayed on the page.

  8. In the web part’s menu, click Edit Web Part. The web part’s properties pane, where you can configure the web part, is displayed on the right side of the page.

  9. To configure the web part, do the following:

    1. In the Select a report area, click the Browse icon to select the Power View report that you created.

    2. In the Title field, enter an appropriate name for the web part.

    3. Set the Height and Width properties so that the web part is displayed optimally on the page.

    4. Click OK to save your changes.