Logo
CAR REVIEW
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
PREGNANCY
 
 
Windows Server

Reporting in Dynamics AX 2009 : Ad Hoc Reporting

9/23/2013 2:36:08 AM

Reporting Services ad hoc reporting, the second major category of Dynamics AX reports, enables users to create and view Dynamics AX reports without needing assistance from developers or IT staff. Users can simply use SQL Server Report Builder to create their own ad hoc reports.

Deployment: Perspectives and SMDL Models

Before users can create and view ad hoc reports, developers and administrators must configure the ad hoc reporting functionality in Dynamics AX by deploying ad hoc reporting models to the Reporting Services server. Once these models are deployed and secured, users can start creating ad hoc reports without further assistance from developers or IT staff.

Developers build the ad hoc models from Dynamics AX perspectives, which is nothing but a collection of references to tables in Dynamics AX. Developers create the Dynamics AX perspectives, and the IT staff deploys the ad hoc models in the Reporting Services server. These models are stored as SMDL files, which Reporting Services understands.

Performance and Platform Considerations

The performance of ad hoc reports doesn’t match the performance of production reports. Ad hoc reports work best for simple, small reports that don’t involve large data sets. If the performance of an ad hoc report is insufficient, you should consider whether creating a production report instead is worth the time and effort.

Ad hoc reports are implemented in SMDL. Because SMDL is a concept specific to SQL Server, ad hoc reports work only with SQL Server. Dynamics AX 2009 doesn’t support ad hoc reporting with Oracle databases.

Security

Unlike production reporting, which depends on the AOS to implement security, the ad hoc reporting framework is based on secure views. A secure view is a normal SQL database view that enforces security on tables when they are accessed from outside Dynamics AX. When the IT staff generates or updates a report model, the Dynamics AX application creates secure views in the OLTP database for every table referenced in a report model. When a user accesses report data using Report Builder, security is automatically enforced via these secure views, so users see only the data they are supposed to see (according to their user group membership).

You can also generate secure views using the Create All Secure Views form in Dynamics AX, which is at Administration\Periodic\Business Analysis.

Creating Ad Hoc Reports

To create an ad hoc report, go through the following steps in order. The steps shown here are meant to give you an overview of the end-to-end scenario involved in deploying SMDL models and creating ad hoc reports. Dynamics AX has published detailed procedures for each of these steps in the Microsoft Dynamics AX 2009 SDK.

1.
Configure the Reporting Server in Dynamics AX for report models. (Find the Reporting Servers form, shown in Figure 1, at Tools\Business Intelligence Tools\Reporting Servers.) Enter the Server Name, Description, Report Manager, and Web Service URLs.

Figure 1. Reporting Servers form, in which you configure the report servers in Dynamics AX

Note

This form is automatically populated when you install the Dynamics AX reporting extensions component on your report server. Make sure you enter the correct port number for the Report Manager and Web Service URLs. If you are using SSL, make sure the URLs are preceded by https.

2.
Define perspectives in the AOT.

3.
Configure BI properties on the tables and fields that are included in the perspectives. Make sure to set the Usage property to ad hoc reporting for the perspective in question. Figure 2 shows the AOT Perspectives form.

Figure 2. AOT perspectives


4.
Configure the model generation options in the Model Generation Options form (shown in Figure 3), found at Tools\Business Intelligence Tools\Report Model Generation Options. Choose the languages that are needed, and set the corresponding report servers.



Figure 3. Model Generation Options form

5.
Set the default report server to be used for report models in the Default Report Servers form, found at Tools\Business Intelligence Tools\Default Report Servers.

Note

At any given time, you can use only a single report server instance for report models.

6.
Publish the SMDL models on the report server by clicking the Update A Report Model button in the Model Generation Options form.

7.
Launch the Report Builder Options form (shown in Figure 4) from Tools\Business Intelligence Tools\Report Builder Options. In this form, you can specify the report model you want to use and the required language. Choose the report data (Perspective label) and the language, and click OK.

Figure 4. Report Builder Options form


8.
Launch the Report Builder from Tools\Business Intelligence Tools\Report Builder, and design the report by dragging the required fields from the left side onto the report design.

9.
Generate the report.

Troubleshooting

This section contains some of the most common ad hoc reporting framework issues and possible solutions that testers identified during the Dynamics AX 2009 release cycle.

Unable to Validate the Report Server
  • Click the Create button in the Reporting Servers form, found at Tools\Business Intelligence Tools\Reporting Servers, and make sure that a report folder and data source have been created on the report server. Click the Validate button.

  • Configure the firewall settings appropriately on the report server box.

  • Make sure the report manager and report server URLs are correct.

  • Make sure the Dynamics AX user has permissions on the report server box.

Unable to Generate a Report
  • Make sure the report server account configured in the report data source on the report server has read permissions on the Dynamics AX SQL database.

  • Configure the firewall settings on the database box appropriately.

  • Check that the user name and password provided for the report server account in the data source on the report server are correct.

SMDL Model Is Not Seen on the Report Server After Successful Model Generation
  • Check that the correct report server is configured in the Default Report Servers form for report models.

  • Make sure the Publish To Report Server option is selected in the Report Generation Options form.

Unable to Generate an SMDL Model Because One or More Languages Are Missing Some Information
  • Make sure the Default Currency for the Default Exchange Rate Company has been specified correctly in the Company Information form, found at Basic\Setup\Company Information.

  • Make sure the Destination Server is specified for the selected language.
Other -----------------
- Reporting in Dynamics AX 2009 : Building Dynamics AX Reporting Services Reports Using Visual Studio
- Reporting in Dynamics AX 2009 : Inside the Dynamics AX 2009 Reporting Framework
- Reporting in Dynamics AX 2009 : Reporting Overview
- Windows Server 2012 : Installing roles and features (part 2) - Installing roles and features using Windows PowerShell
- Windows Server 2012 : Installing roles and features (part 1) - Installing roles and features using Server Manager
- System Center Configuration Manager 2007 : Desired Configuration Management - Troubleshooting
- System Center Configuration Manager 2007 : Desired Configuration Management - DCM Strategies
- Microsoft Exchange Server 2007 : Leveraging the Capabilities of the Outlook Web Access Client - Understanding OWA Security Features, Tips for OWA Users with Slow Access
- Microsoft Exchange Server 2007 : Leveraging the Capabilities of the Outlook Web Access Client - Using Contacts in OWA
- What's new and improved in SharePoint 2013 : Creating an eDiscovery Center
- What's new and improved in SharePoint 2013 : Creating badges, Using Visual Designer for workflows within SharePoint Designer
- What's new and improved in SharePoint 2013 : Previewing search documents, Using the Community Site template
- Windows Small Business Server 2011 : Adding a Terminal Server - Adding a RemoteApp to Remote Web Workplace
- Windows Small Business Server 2011 : Adding a Terminal Server - Configuring RemoteApps (part 2) - Deploying with .rdp and .msi files
- Windows Small Business Server 2011 : Adding a Terminal Server - Configuring RemoteApps (part 1) - RemoteApp Manager
- SQL Server 2012 : XML and the Relational Database - Querying XML Data Using XQuery (part 2) - SQL Server XQuery in Action, XML DML
- SQL Server 2012 : XML and the Relational Database - Querying XML Data Using XQuery (part 1) - Understanding XQuery Expressions and XPath
- SQL Server 2012 : XML and the Relational Database - Shredding XML Using OPENXML
- Maintaining Windows Home Server 2011 : Defragmenting the System Drive, Reviewing Event Viewer Logs, Setting Up a Maintenance Schedule
- Maintaining Windows Home Server 2011 : Deleting Unnecessary Files from the System Drive
 
 
Most view of day
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 1) - Failover Clustering prerequisites
- System Center Configuration Manager 2007 : Distributing Packages - Creating Collections (part 3) - Creating a Dynamic Collection Limited to a Collection
- Windows Phone 8 : Orientation and the PhoneApplicationPage Class (part 5) - Animating the Entire Page When Orientation Changes
- Managing Windows 7 : Adding or Deleting User Accounts
- System Center Configuration Manager 2007 : ConfigMgr Classic Reports Versus SQL Reporting Services
- Microsoft Dynamics AX 2009 : Form Customization (part 3) - Displaying an Image on a Form
- Windows Server 2012 : Administering Active Directory using Windows PowerShell (part 2) - Finding Active Directory administration cmdlets
- Integrating Systems Management Server 2003 into Patch Management Processes (part 2) - Authorizing and Distributing Software Updates
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 2) - Adding Failover Clustering feature
- Windows Server 2003 on HP ProLiant Servers : File and Print Services, Selection of ProLiant Servers for the Enterprise
Top 10
- Windows Phone 8 : Scheduled Tasks - Scheduled Task API Limitations
- Windows Phone 8 : Scheduled Tasks - Updating Tiles Using a Scheduled Task Agent
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 5) - Editing an Existing To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 4) - Creating the To-Do Item Shell Tile, Saving a To-Do Item
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 3) - Debugging Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 2) - TodoService, TodoItemViewModel
- Windows Phone 8 : Scheduled Tasks - To-Do List Scheduled Task Sample (part 1) - TodoItem,TodoDataContext
- Windows Phone 8 : Scheduled Tasks - Using Scheduled Tasks
- Windows Phone 8 : Scheduled Tasks - Background Agent Types
- Windows Phone 8 : Windows Phone Toolkit Animated Page Transitions - Reusing the Transition Attached Properties
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro