|
|
Developing SSRS Reports for Dynamics
AX
|
|
|
Syed Baber
12/14/2010
|
1. SSRS
Installation and Configuration:
Before
start developing SSRS report for MS Dynamics AX, you need to install BI
components and Visual Studio tools from MS Dynamics AX setup. The setup form is
shown below with BI Components and Visual Studio tools options checked:
After
installing BI Components and Visual Studio tools, you are ready to start
working on SSRS reports for MS Dynamics AX. To verify that the BI Components
installed successfully and report servers are correctly configured in MS
Dynamics AX, Go to Tools -> BI tools -> BI reports servers. Configuration
form is shown below:
Verify that
the values are filled in and server name is the name of your machine.
2. Editing
existing SSRS Reports:
To edit any
existing SSRS Report, go to AOT\Visual
Studio Projects\Dynamics AX Model Projects. Under this node you will find
Visual Studio solutions and Projects that has been developed using Visual
Studio. To edit any solution, right click on it and select ‘Edit’. The selected
solution opens in Visual Studio. After making the desired changes in the
project, just select the project in solution explorer, use File -> Add ‘Project’
to AOT option to add the changes in Dynamics AX. Screen shot shown below:
3. Developing
a new SSRS report:
Before start developing SSRS
reports for Dynamics AX, you must have installed BI Components and Visual
Studio tools from AX setup. After installation of both these additional components,
open Visual Studio, click on File -> New Project. New Project dialog is open
as shown below:
From the above dialog select Microsoft Dynamics AX in Project types
and select Report Model under
templates section. The SSRS reports needs a data source from where the report
can grab the data and displayed it on the report. There are three types of data
source available to be used in SSRS report which is as follows:
Ø Query
Ø RDP
(Report data provider).
Ø Business
Logic.
When you use query as a data
source type for SSRS report then the report is called Query based report. Now,
I am going to explain you that how we develop SSRS reports based on Query.
4. Developing
Query Based SSRS reports using VS
Before you can
start developing query based reports in VS, you need an AOT query to be created
in Dynamics AX. Suppose you want to show all the records form CustTable table in an SSRS report. This
can be achieved through query based report. Create an AOT query in Dynamics AX
as shown below:
The Query name is SampleQuery.
Add CustTable under the data sources node of the query. All the fields of the CustTable are included
in the AOT query. Now you need to create an SSRS report which used this query
as a data source and display all the fields of CustTable on SSRS report. Open Visual Studio and goto File ->
New Project. New Project dialog form will be opened as shown below:
Select Microsoft Dynamics AX under Project types and Report
Model under Templates section. Specify the name of the report as ‘QueryBasedReporrt’ and press OK button.
A new SSRS project will be created as shown below:
Now you need to add a report under the above project. Right
click on the project, select Add and then select Report. A report will be added
to the project with the name Report1. Change the report name to QueryBasedReport. Now the solution
explorer should look like as shown below:
Double click on the newly created report. You will see report
items on the left side in Visual Studio as shown in the below screen shot:
The purpose of each report items is described below:
Ø Datasets: The report dataset retrieves data
from the AOT query. All fields which are available in AOT query are shown in
the report dataset which can be referred in the report design later on.
Ø Designs: The design or layout of the report
on which the data would be displayed after retrieving from AX.
Ø Images: Any embedded images that you want
to display on SSRS report.
Ø Data Methods: Business logic written in C#. Data
methods are no more recommended.
Ø Parameters: Report parameters which are to be
displayed on SSRS report and user can filter the report based on those
parameters.
You first need to create a dataset that will refer to an AOT
query created earlier. Right click on Datasets node and click ‘Add Dataset’. A
dataset will be created with name ‘DataSet1’. Rename it to
‘QueryBasedReportDS’. The newly created dataset should refer to the AOT query
from where the data can be picked. Right click on the newly created dataset and
select ‘Properties’. Set the data source type to ‘Query’ and click on the
‘Query’ property. An ellipses button appears, click on it. A new window will
open which shows the entire AOT queries present in Dynamics AX. Select the
query ‘SampleQuery’ which you have created earlier as shown below:
Select All Fields from the right
column to show all fields on the report dataset and then click ‘OK’ button. All
the fields which are added on AOT query are now showing on the report dataset.
You can see all the fields by expanding the Fields node under the dataset. You
can refer to any of these fields on the report design. Now we have created a
dataset which provides the data to the report, the next step is to create a
report design.
There are two types of report
designs available in SSRS report i.e. AutoDesign
and PrecisionDesign. First
preference when developing reports is to use AutoDesign unless you need a
custom placement of fields in a report and in that case you have to use
PrecisionDesign. Generally for complex report designs, we use PrecisionDesign. For
the sake of simplicity, we will use AutoDesign here for our first SSRS report.
Right click on the Designs node,
click on Add-> AutoDesign. An auto design is created with the name
AutoDesign1. Change its name to ‘Report’ by right clicking on the AutoDesign1
and click Rename. It is recommended that you set the name of the Design to
either ‘Design’ or ‘Report’. Now drag the data set of the report
‘QueryBasedReportDS’ on Report Design named ‘Report’. A table will be created
which contains all the fields that are shown in the report dataset. The value
of these fields will be shown on the report output in the same order as it is
showing in the data table. The final report design should look like as shown
below:
Now we execute the report to see
the output. The records will be fetched from Customer table so make sure that
the CustTable has some records in it
before opening the report otherwise a blank output will be shown. There are
three ways of opening an SSRS report which are as follows:
1) From Visual
Studio which is actually the preview of the report.
3) From
Dynamics AX through output menu item.
4.1.
Previewing SSRS reports from Visual Studio
You can easily preview SSRS reports
by right clicking on the design and select Preview option. For our example
right click on the Design ‘Report’ and select Preview as shown in the below
screen shot:
A separate preview page is open
which will show the report output. If your report has some parameters then the
parameters will be shown first and then the output will be shown based on the
values of the parameters. Following report output will be shown when you
preview the report from VS:
4.2.
Viewing SSRS report on browser
Before viewing report on browser
you need to deploy report on the report server. You can deploy SSRS reports in
two ways, first way is to right click the solution in Visual Studio and select
deploy solution option and the second way is to go to SSRS Reports/Reports node
under AOT, right click on the report you want to deploy and then select Deploy.
To deploy SSRS report from AX, you need to first add the report project to AOT.
Following screen shot shows how to do this:
Now right click the report solution
in VS and select deploy solution as shown below:
To view SSRS report from browser, go
to URL http://localhost/reports. When the
page is open you will see the Dynamics folder link shown on the page. By
clicking on the Dynamics folder you can see the list of all SSRS reports
deployed on the report server as shown below:
Click on the report which you want
to preview on browser. In our case, click on the SSRS report QueryBasedReport.Report to view it on
browser. Following output will be shown when you view the report from browser:
Opening SSRS report from AX
To open SSRS report from AX, you first need to create an
output menu item which refers to the SSRS report you have created earlier.
Below screen shot shows how to do this:
Step 1: Create an output menu item
Step 2: Set the object type and object properties as shown in the above screen shot.
Now right click on the output menu item QueryBasedReport and
select Open to launch the report. Following output will be shown when you run
the report from AX:
4.3.
Filtering a report with an specific parameter
Suppose you want to filter the report we have developed
earlier by customer group. In that case you need to show the parameter customer
group on the report dialog so that user can run the report for a specific
customer group. Steps are shown below:
Step 1: Adding range in a report query.
Step 2: Add CustGroup field under the Ranges
node.
Now compile the query
‘SampleQuery’ to save the changes. Run the report by right clicking the output
menu item and select Open. Following report dialog will be shown:
You can see the customer group
parameter in the above dialog which we have added through report query. Another parameter customer account is shown on
the above dialog. Just ignore this parameter, it is showing here because this
field is the part of unique index on CustTable.
Now click on the select button to specify a value for customer group parameter
shown above. Following screen shot will be shown:
Select the customer group 30 and click OK button. Customer group 30 will be shown selected on
the report dialog as shown below:
Again click the OK button on the report dialog to open the
report filtered by customer group 30. Following output will be shown:
As you can see that, only those records are shown, which
have customer group 30. In the same way you can apply as many filters as you
want on the report AOT query to filter the report based on that field’s value.
5. Developing
RDP based SSRS reports
RDP (Report data
provider) based reports are developed, when reports has a complex logic that
cannot be achieved by just using an AOT query. In RDP based reports, in addition to an AOT
query, an RDP class is developed which contains the report logic. After
processing all the report logic, the data is filled into the temporary table
which is used as a data source for SSRS report. Now, I am going to describe
about how to build an RDP based report. We will modify the previous AOT query
for this example to add the CustTrans data source. The query will now look like
as shown below:
Now create a new RDP class and
name it SampleReportDP. To create RDP based report, following objects needs to be
created:
Ø An
RDP class.
Ø An
AOT query.
Ø A
temp table.
The query has been modified for
this example and now RDP class and temp table needs to be created. Just for a
demo purpose, I have thought of a requirement where we need to display the transaction
amount based on the customer group. The logic is as follows:
·
If (customer group is 20) then multiply the
transaction amount by 20
·
If (customer group is 40) then multiply the
transaction amount by 40
·
If (customer group is 60) then multiply the
transaction amount by 60
Create a new class and
name it SampleReportDP. In class
declaration extend the class from SRSReportDataProviderBase
class. Add the following line before class declaration:
[SRSReportQueryAttribute(querystr(SampleQuery))]
This attribute specifies the AOT
query which is used by this SSRS report. Also declare some class variables
which are needed to store the values, retrieved after running the report query.
The class declaration should look like as shown below:
The second step is to create a
temp table which is used to store the report data. Create a new table and name
it SampleReportTmp. Set the
TableType property of the new table to InMemory. Create four fields in the temp
table as shown below:
Create a new method in RDP class
which will select the temp table records and return it afterwards to be used by
the SSRS report. The new method should look like as shown below:
Now we are going to implement the
logic discussed above in RDP class processReport
method. The processReport method in RDP class is the point where the report
processing begins. This method is called by the reporting framework. Actual
report logic needs to be written in this method. In our case, the code for
processReport method is as follows:
public void
processReport()
{
QueryRun
queryRun;
queryRun
= new QueryRun(this.parmQuery());
while (queryRun.next())
{
custTable = queryRun.get(tablenum(CustTable));
custTrans = queryRun.get(tablenum(CustTrans));
amountCur = custTrans.AmountCur;
voucher = custTrans.Voucher;
custGroup = custTable.CustGroup;
transDate = custTrans.TransDate;
switch
(custGroup)
{
case '20':
amountCur = amountCur * 20;
break;
case '40':
amountCur = amountCur * 40;
case '60':
amountCur = amountCur * 60;
default:
amountCur = custTrans.AmountCur;
}
this.insertSampleReportTmp();
}
}
Create a new Microsoft Dynamics
AX reporting project from VS 2010 and name it RDPBasedReport. Add a report item
to the project by right clicking on the Project. Change the report name to
RDPBasedReport. Create a new data set and name it SampleRDPReportDS. Now open
the properties page for the newly created dataset by clicking Ctrl + Enter. Set
the data source type to ‘Report Data Provider’. Select the query property and
click the ellipses to open the RDP class selection window. From the list of
classes, select your RDP class which was SampleReportDP and then click next.
Afterwards, select all the fields and then click OK. You report should match
with the following screen shot:
Notice the query mentioned in the
Query property of dataset. It is the syntax for mentioning RDP class and temp
table in the query property whenever you use the report data provider as data
source type. Add the new report to AOT by right clicking on the report project.
Now deploy the report by right click on the report solution and select deploy
solution to deploy the SSRS report on report server.
Create a new output menu item,
and name it RDPBasedReport. Set its properties as shown below:
Now right click on the menu item
and select Open to launch the SSRS report we have just developed. The report
output should match with the below screen shot:
6. Using
Controller Class
Following table describes the scenarios and suggests the
proper way of using controller class in SSRS reports:
|
Scenario
|
Suggested approach
|
|
Query is modified based on caller args before report dialog is
rendered.
|
Create controller class and override prePromptModifyContract().
|
|
Report contract need to be modified prior to running of the report.
|
Create controller class and override preRunModifyContract()
|
|
If you don’t want to show the report parameter UI.
|
Create controller class and in main method, before calling
startOperation method, call controller.parmShowDialog(false).
|
|
|
|
|
|
|
6.1.
Examples of controller class usage
Below, I will discuss some examples of controller class.
6.1.1. HcmEmployeeLeaveController
In this class the controller class is use to modify the
report query before the report is run. Below is the code in
preRunModifyContract() method:
Whenever you need to modify the report query before report
execution, you need to override this method in your controller class. This
method is called by the framework. In
the above example, the local method setRanges is called which will modify the
report query accordingly.
Note that the controller
class must extend from SrsReportRunController
class. In the main method of controller class, parmReportName method is used to specify the name of the report you
want to run. Support if an SSRS report has two designs, then you can run either
of them by specifying here the fully qualified report name. The startOperation()
method is used to run the report. Below is the main method of the HcmEmployeeLeaveController class:
The value of the
#ReportName macro is HcmEmployeeLeave.Report declared in classDeclaration.
6.1.2. BankDepositSlipController
This class uses the prePromptModifyContract
method to modify the report query before the report dialog is rendered. Following
is the code written in prePromptModifyContract method:
The setRanges method has
been called here which will modify the report query before the report dialog is
rendered.
6.1.3.
Suppressing the report dialog
If you want to suppress the report dialog and want to open
the report directly, add the following line of code in the main method of
controller class before startOperation()
method call.
Controller.parmShowDialog(false);
7. Data
Contract class for SSRS Reports
The contract class is written to
provide grouping and validation for report parameters. The contract class is
also needed when custom dialog fields need to be shown on the report dialog
i.e. the field is not a report parameter. Now I show you some of the existing
contract classes in AX and discuss them. Take a look at the LedgerEncumbranceReconciliationContract
class. Following is the screen shot which shows all the parm methods and a validate
method.
The parm methods represents the
report parameters and the validate method use to validate the report
parameters. Notice the contract class implements the SysOperationValidatable interface. It is necessary, if you are
going to provide validation on report parameters. After that you need to
override the validate method on the contract class.
7.1.
Defining label and help text for parameters
Label and help text for report parameters can be defined
through attributes on the data contract class. Before the definition of each
parm methods, label and help text attributest can be defined. Following code
snippet shows an example of defining label and help text attributes for report
parameters:
[
AifDataMemberAttribute('DifferencesOnly'),
SysOperationLabelAttribute(literalstr("@SYS57830")),
SysOperationHelpTextAttribute(literalstr("@SYS328295"))
]
public boolean
parmDifferencesOnly(boolean _differencesOnly = differencesOnly)
{
differencesOnly = _differencesOnly;
return differencesOnly;
}
The SysOperationLabelAttribute
attribute is used to define label for report parameter and SysOperationHelpTextAttribute is used to define help text for
report parameter.
No comments:
Post a Comment