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.
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.
|
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.
|
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.
|
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.