>

Category Archives: SSRS

Walkthrough: Creating a Report Bound to a Report Data Provider Class (X++ Business Logic) [AX 2012]

In this walkthrough, you use a report data provider (RDP) class with business logic to process data and then display the outcome of the business logic on a report.

An RDP class is an X++ class that is used to access and process data for a Reporting Services report. The options for a data source type for a Microsoft Dynamics AX report are query, business logic, and RDP. An RDP class is an appropriate data source type when the following conditions are met.

  1. You cannot query directly for the data you want to render on a report.
  2. The data to be processed and displayed is from Microsoft Dynamics AX.

The following illustration is a preview of the report that you create in this walkthrough.

clip_image001Note

The data that displays in your report may vary depending upon the sample data that is available to you.

The following elements are required to set RDP as your data source type.

  • Temporary table – RDP class fills a temporary table with data that will be used by Reporting Services to display the report.
  • Data contract class – defines the parameters in the report.
  • Report data provider class – processes business logic based on parameters and a query, and then returns the tables as a dataset for the report.

This walkthrough illustrates the following tasks:

  • Creating a temporary table
  • Defining a report data provider class
  • Defining the report parameters
  • Defining a method to return data to Reporting Services
  • Adding business logic for the report
  • Creating a reporting project
  • Binding a report to a report data provider class

Creating a Temporary Table

Data for an RDP report is preprocessed and then stored in a temporary table. The temporary table is used by Reporting Services when the report is displayed. A table returned by a method can be a temporary table (InMemory or TempDB) or a regular table. When the data returned is used for reporting only, it is a best practice to use a temporary table.

  • Use an InMemory temporary table if the dataset is small, for reports that will display fewer than 1000 records.
  • Use a TempDB temporary table for large datasets to improve performance.

In this section you will create a temporary table to store the data for the report.

To create a temporary table

  1. In the AOT, expand the Data Dictionary node, right-click the Tables node, and then click New Table.
  2. Right-click the table, and click Properties.
  3. In the Properties window, set the Name property to TmpCustTableSample and set the Table Type property to TempDB. This will define the table as a SQL Server temporary table.
  4. Expand the node next to the TmpCustTableSample table so that you can see the Fields node.
  5. Press Ctrl+D to open another AOT window and move the window so you can see both AOT windows.
  6. In the second AOT, expand the Data Dictionary node, expand the Extended Data Types node, and drag the following types to the Field node in the first AOT window:
    • AccountNum
    • CustName
    • LogisticsAddressing
    • CustGroupId
    • Phone
    • CustInvoiceAccount
    • ActionDays
    • InclTax
  7. In the second AOT window, expand the Base Enums node and drag the CustAccountStatement enumeration to the Fields node of the first AOT window.

Defining a Report Data Provider Class

The RDP class is a data provider that allows you to add business logic for the data that is displayed on a report. The business logic for this example prompts the end user for parameter values, processes business logic to generate data in a table, and then returns the table to render in the report. In this section, you define the RDP class by extending the SRSReportDataProviderBase class. Then add the TmpCustTableSample table as a global variable. The following list describes the attributes that are attached to the RDP class for this example:

  • Attach the SRSReportQueryAttribute attribute to specify the query to use to get the data for the report.

For this example, set the attribute to the Cust query.

  • Attach the SRSReportParameterAttribute attribute to specify the data contract class that defines the report parameters for the report.

For this example, set the attribute to the SrsRDPContractSampledata contract.

To define a report data provider class

  1. In the AOT, right-click the Classes node, and then click New Class.
  2. Right-click the new class, click Rename, and then enter SrsRDPSampleClass.
  3. Expand SrsRDPSampleClass, right-click classDeclaration, and then click View Code.
  4. In code editor, enter the following code in the class declaration to define the class.

X++

[

SRSReportQueryAttribute (querystr(Cust)),

SRSReportParameterAttribute(classstr(SrsRDPContractSample))

]

public class SrsRdpSampleClass extends SRSReportDataProviderBase

{

TmpCustTableSample tmpCust;

}

Defining a Data Contract Class

An RDP class must have a data contract if the report has one or more report parameters. This example defines three report parameters for account number, account statement, and whether to include tax. When you print the report, you can specify which data to print based on the report parameters. For example, you can specify to only print transactions for account number 4000.

A data contract is an X++ class with getters, setters, and the DataMemberAttribute attribute. The data contract defines the parameters that the report uses.

To define a data contract class

  1. In the AOT, right-click the Classes node, and then select New Class.
  2. Right-click the new class, click Rename, and then enter SrsRDPContractSample.
  3. Expand SrsRDPContractSample, right-click classDeclaration, and then click View Code.
  4. In code editor, enter the following code in the class declaration to define the class.

X++

[DataContractAttribute]

public class SrsRDPContractSample

{

AccountNum accountNum;

CustAccountStatement accountStmt;

boolean inclTax;

}

A data contract class has methods with the DataMemberAttribute attribute. The name that follows this attribute is the parameter name that displays in Visual Studio when you bind a report data set to the RDP class. In this section, add a method for each of the report parameters and name them parmAccountNum, parmAccountStmt, and parmInclTax.

To define data contract methods

  1. Right-click SrsRDPContractSample, point to New, and then click Method.
  2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("AccountNum")]

public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)

{

accountNum = _accountNum;

return accountNum;

}

  1. Right-click SrsRDPContractSample, point to New, and then click Method.
  2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("CustAccountStatement")]

public CustAccountStatement parmAccountStmt(CustAccountStatement _accountStmt = accountStmt)

{

accountStmt = _accountStmt;

return accountStmt;

}

  1. Right-click SrsRDPContractSample, point to New, and then click Method.
  2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("InclTax")]

public boolean parmInclTax(boolean _inclTax = inclTax)

{

inclTax = _inclTax;

return inclTax;

}

Defining a Method to Return Data to Reporting Services

A method to return the processed data in the temporary table to Reporting Services is needed. In this section, add a method named getTmpCustTable and attach the SRSReportDataSetAttribute attribute to indicate the dataset for the report.

To define a method to return the data to Reporting Services

  1. Right-click SrsRdpSampleClass, point to New, and then click Method.
  2. Edit the method so that it contains the following code.

X++

[SRSReportDataSetAttribute("TmpCust")]

public TmpCustTableSample getTmpCustTable()

{

select * from tmpCust;

return tmpCust;

}

Adding Business Logic for the Report

The business logic for this example prompts the end user for parameter values, processes business logic to generate data in a table, and then returns the table to render in the report.

The report business logic is provided in the processReport method. This method is called by Reporting Services at runtime. The following example illustrates how the processReport method computes data and populates the data table that is returned to Reporting Services. In this section, override the processReport method to provide business logic for your report.

To add business logic for the report

  1. Right-click SrsRdpSampleClass, point to Override method, and then click processReport.
  2. Edit the method so that it contains the following code.

X++

public void processReport()

{

AccountNum accountNumber;

CustAccountStatement custAcctStmt;

boolean boolInclTax;

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

CustTable queryCustTable;

SrsRdpContractSample dataContract;

// Get the query from the runtime using a dynamic query.

// This base class method reads the query specified in the SRSReportQueryAttribute attribute.

query = this.parmQuery();

// Get the parameters passed from runtime.

// The base class methods read the SRSReportParameterAttribute attribute.

dataContract = this.parmDataContract();

accountNumber = dataContract.parmAccountNum();

custAcctStmt = dataContract.parmAccountStmt();

boolInclTax = dataContract.parmInclTax();

// Add parameters to the query.

queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));

if(accountNumber)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountNum));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum));

}

// If an account number has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(accountNumber);

}

if(custAcctStmt)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountStatement));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountStatement));

}

// If an account statement has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(int2str(custAcctStmt));

}

if(boolInclTax)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, InclTax));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, InclTax));

}

// If flag to include tax has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(int2str(boolInclTax));

}

// Run the query with modified ranges.

queryRun = new QueryRun(query);

ttsbegin;

while(queryRun.next())

{

tmpCust.clear();

queryCustTable = queryRun.get(tablenum(CustTable));

        tmpCust.AccountNum = queryCustTable.AccountNum;

        tmpCust.CustName = queryCustTable.name();

        tmpCust.LogisticsAddressing = queryCustTable.address();

        tmpCust.CustGroupId = queryCustTable.CustGroup;

        tmpCust.Phone = queryCustTable.phone();

        tmpCust.CustInvoiceAccount = queryCustTable.InvoiceAccount;

        tmpCust.CustAccountStatement = queryCustTable.AccountStatement;

        tmpCust.InclTax = queryCustTable.InclTax;

        tmpCust.insert();

}

ttscommit;

}

Creating a Reporting Project

Next, create a reporting project in Microsoft Visual Studio. When you create a reporting project, you use the Report Model to create a Reporting Services report.

To create a reporting project

  1. Open Microsoft Visual Studio.
  2. On the File menu, point to New, and then click Project. The New Project dialog box displays.
  3. In the Installed Templates pane, click Microsoft Dynamics AX. In the Templates pane, click Report Model.
  4. In the Name box, type SampleRDPReport, and in the Location box, type a location.
  5. Click OK.

A reporting project contains a report model where you can add a report to the model.

Binding a Report to a Report Data Provider Class

Now that you have created a reporting project, you are ready to define an auto design report that displays data from the Cust query. The following procedure explains how to create an auto design report that uses the RDP class as the data source.

To create an auto design report

  1. In Solution Explorer, right-click the ReportModel node, point to Add and then click Report.
  2. In Model Editor, right-click the Report1 node, and then click Rename.
  3. Type CustomerReport as the name.
  4. Right-click the Datasets node, and then click Add Dataset.
  5. In the Properties window, specify the following values.

Property

Value

Data Source

Dynamics AX

Data Source Type

Report Data Provider

Default Layout

Table

Dynamic Filters

True

clip_image001[1]Note

This setting is for dynamic parameters on the report. Setting the property to True allows you to filter the report by setting a range on any fields from the data source table.

Name

Customer

Query

Click the ellipsis button (…). A dialog box displays where you can select an RDP class that is defined in the AOT and identify the fields that you want to use. Select the SrsRDPSampleClass class and click Next. In the Select Fields dialog box, keep all the checkboxes selected, and click OK.

clip_image001[2]Note

Based on what you select, the Query property value is updated. In this case, the query is SELECT * FROM SrsRDPSampleClass.TmpCust.

  1. In Model Editor, select the Customer node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.

clip_image001[3]Note

If you expand the Parameters node you see the parameters that you defined in the data contract class. When you define nested data contract parameters, they are listed under the Parameters node also.

Select the SrsRDPSampleClass_DynamicParameter parameter. In the Properties window, the AOT Query property is set to the query specified in the parmQuery method in the RDP class.

  1. Select AutoDesign1 and then click the Preview button.
  2. Enter a value for Account number and Account statement that returns data and then click the Report tab to preview the report.

clip_image001[4]Note

You can use * to display all of the account numbers on the report. Account statement is an enumeration and there is no wild card value for enumerations. Check your sample data to determine a valid enumeration value for Account statement.

The Select button displays because you set the Dynamic Filters property to True on the dataset. You have the option to click the Select button to specify a range to filter the report and limit the data that displays on the report.

The next steps are to add layout and style templates, deploy, and add the report to a menu item so you can see it in Microsoft Dynamics AX.

How to: Grant User Access to a Report Server (Report Manager)

Reporting Services uses role-based security to grant user access to a report server. On a new report server installation, only users who are members of the local Administrators group have permissions to report server content and operations. To make the report server available to other users, you must create role assignments that map  user or group accounts to a predefined role that specifies a collection of tasks.

For a report server that is configured for native mode, use Report Manager to assign users to a role. There are two types of roles:

  • Item-level roles are used to view, add, and manage report server content, subscriptions, report processing, and report history. Item-level role assignments are defined on the root node (the Home folder) or on specific folders or items farther down the hierarchy.
  • System-level roles grant access to site-wide operations that are not bound to any specific item. Examples include using Report Builder and using shared schedules.

    The two types of roles complement each other and should be used together. For this reason, adding a user to a report server is a two-part operation. If you assign a user to an item-level role, you should also assign them to a system-level role. When assigning a user to a role, you must select a role that is already defined. To create, modify, or delete roles, use SQL Server Management Studio.

For a report server that is configured for SharePoint integrated mode, you configure access from a SharePoint site using SharePoint permissions. Permission levels on the SharePoint site determine access to report server content and operations. You must be a site administrator to grant permissions on a SharePoint site.

Before you start

 

Review the following list before adding users to a native mode report server.

  • You must be a member of the local Administrators group on the report server computer. If you are deploying Reporting Services on Windows Vista or Windows Server 2008, additional configuration is required before you can administer a report server locally.
  • To delegate this task to other users, create role assignments that map user accounts to Content Manager and System Administrator roles. Users who have Content Manager and System Administrator permissions can add users to a report server.
  • In SQL Server Management Studio, view the predefined roles for System Roles and User Roles so that you are familiar with the kinds of tasks in each role. Task descriptions are not visible in Report Manager, so you will want to be familiar with the roles before you begin adding users.
  • Optionally, customize the roles or define additional roles to include the collection of tasks that you require. For example, if you plan to use custom security settings for individual items, you might want to create a new role definition that grants view-access to folders.
To add a user or group to a system role
  1. Start Report Manager.
  2. Click Site Settings.
  3. Click Security.
  4. Click New Role Assignment.
  5. In Group or user name, enter a Windows domain user or group account in this format: <domain>\<account>. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
  6. Select a system role, and then click OK.

    Roles are cumulative, so if you select both System Administrator and System User, a user or group will be able to perform the tasks in both roles.

  7. Repeat to create assignments for additional users or groups.
To add a user or group to an item role
  1. Start Report Manager and locate the report item for which you want to add a user or group.
  2. Hover over the item, and click the drop-down arrow.
  3. In the drop-down menu, click Security.
  4. Click New Role Assignment.

    Note: If an item currently inherits security from a parent item, click Edit Item Security in the toolbar to change the security settings. Then click New Role Assignment.

  5. In Group or user name, enter a Windows domain user or group account in this format: <domain>\<account>. If you are using forms authentication or custom security, specify the user or group account in the format that is correct for your deployment.
  6. Select one or more role definitions that describe how the user or group should access the item, and then click OK.
  7. Repeat to create assignments for additional users or groups.

Procedure: Grant Users Access to Reports in AX 2012

Configure security settings in Microsoft Dynamics AX
Complete the following tasks in Microsoft Dynamics AX:
• Determine which reports each Microsoft Dynamics AX role should have access to.
• Verify that each Microsoft Dynamics AX role has the correct duties and privileges assigned to it in order to access the reports.
• Assign users to Microsoft Dynamics AX roles.
• Secure the data shown in reports.

Configure security settings in Reporting Services Complete the following tasks in Reporting Services:
• Assign users to the DynamicsAXBrowser role in Reporting Services.
For detailed instructions about how to assign users to Reporting Services roles, refer to my post (How to: Grant User Access to a Report Server).
• Identify the account that is used to run the Application Object Server (AOS) service and the account that is used as the Business Connector proxy. Assign those accounts to the DynamicsAXBrowser role in Reporting Services.
• Restrict access to report folders and reports. Reporting Services includes security features and tools that you should use to help control access to report folders and published reports. Refer to the SQL Server documentation on MSDN for detailed conceptual information and step-by-step tutorials that will help you administer
security in Reporting Services.

Grant users access to reports [AX 2012]

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This topic explains how to give users access to reports. Two procedures are described in this topic. The procedure that you should use depends on whether you are running Microsoft SQL Server Reporting Services in native mode or SharePoint integrated mode.

NoteNote

SharePoint integrated mode is supported if you are using Microsoft Dynamics AX 2012 R2.

Assign users to the DynamicsAXBrowser role on the Report Manager site

If you are running Reporting Services in native mode, you must assign users or groups to the DynamicsAXBrowser role on the Report Manager site. The following procedure explains how to complete this task.

  1. Open the Report Manager website for the Reporting Services instance. By default, the URL is http://[SSRSServerName]:80/Reports.

  2. Click the DynamicsAX folder.

  3. Click Folder Settings.

  4. Click Security.

  5. Click New Role Assignment.

  6. Enter the Active Directory user name or group to assign to the DynamicsAXBrowser role.

  7. Select the DynamicsAXBrowser role.

  8. Click OK.

Grant users permission to view reports in SharePoint

If you are running Reporting Services in SharePoint integrated mode, you must grant users permission to view reports in SharePoint. To grant this permission, grant users Read permission to the document library that stores the reports. Alternatively, if the document library inherits permissions from the site, you can grant users Read permission to the site. The following procedure describes how to grant users Readpermission to the site.

ImportantImportant

If the SharePoint site is configured for claims-based authentication, you must also grant the following accounts Read permission to the document library or site:

  • The account that is used as the Business Connector proxy

  • The account that is used to run the Microsoft Dynamics AX Application Object Server (AOS) service.

  1. Open your browser and navigate to the SharePoint site that contains the document library that stores the reports.

  2. Click Site Actions > Site Permissions.

  3. Click Grant Permissions. The Grant Permissions window is displayed.

  4. In the Users/Groups field, enter the Active Directory names of the users or groups that you want to view reports.

  5. In the Grant Permissions area, select the Grant users permission directly option.

  6. Select the Read check box.

    NoteNote

    If you want users of Enterprise Portal for Microsoft Dynamics AX to be able to filter reports by using a custom parameter value, select the Design check box. For more information about the permissions that are required to use Enterprise Portal, see Enable users to access Enterprise Portal.

  7. Click OK.

Create a document library to store reports [AX 2012]

Applies To: Microsoft Dynamics AX 2012 R2

If you are using Microsoft Dynamics AX 2012 R2, and if Microsoft SQL Server Reporting Services is running in SharePoint integrated mode, create a document library in SharePoint to store your reports. Complete this procedure before you deploy the default reports that are included with Microsoft Dynamics AX.

NoteNote

This procedure does not apply to you if you are running Reporting Services in native mode.

Create a document library

Create a document library on your SharePoint site to store reports. For information about how to create a document library, see the SharePoint documentation.

After you create the document library, add Reporting Services content types to the library. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) in the SQL Server documentation.

Specify the URL of the document library

After you have created the document library, complete the following procedure to specify the URL of the document library in the Report servers form in Microsoft Dynamics AX.

  1. Open Microsoft Dynamics AX.

  2. Click System administration > Setup > Business intelligence > Reporting Services > Report servers.

  3. In the Configuration ID field, enter a name that identifies the Reporting Services instance and the Application Object Server (AOS) instance that you are connecting.

  4. In the Description field, enter a brief description to help you identify the Reporting Services instance and the AOS instance that you are connecting.

  5. Select the Default configuration check box to make the Reporting Services and AOS instances that are specified in this record the active connection.

  6. On the Reporting Server information tab, enter the following information:

    1. In the Server name field, enter the name of the server that is running Reporting Services.

    2. In the Server instance name field, enter the name of the Reporting Services instance.

      NoteNote

      If you are using Reporting Services 2012, enter @Sharepoint.

    3. Leave the Report Manager URL field blank. This field becomes unavailable when you select the SharePoint integrated mode check box in a later step.

    4. In the Web service URL field, enter the URL of the Reporting Services web service.

      • If you are using Reporting Services 2008, the URL is typically http://[SSRSServerName]/ReportServer.

      • If you are using Reporting Services 2012, the URL is typically http://[SharePointServerName]/_vti_bin/ReportServer or http:[SharePointServerName]/sites/[SiteName]/_vti_bin/ReportServer.

    5. Select the SharePoint integrated mode check box.

    6. In the Microsoft Dynamics AX report folder field, enter the URL of the document library that you created to store reports.

      For example, suppose that you have created a document library that is named Reports on a SharePoint site that is named Contoso. In this example, the URL is as follows:

      http://[SharePointServerName]/sites/Contoso/Reports

  7. On the Application Object Server information tab, select the name of the AOS instance.

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!