AX 2012 Creating the SSRS Report– Precision Design

In this post, we will see how we can build a report using precision design. A precision design is like a pre-printed format where the placing of report controls, the design and the spacing matters a lot.

A precision design is the standard SSRS report designer following the same principles, rules and requirements as are found in a SQL Server Business Intelligence Studio SSRS designer.

We will  print Customer Id, Customer name and Balance by Precision Design.

I will also show how we can use some inbuilt parameters to display some generic information like page number, execution date and time etc.

Create the Queries from AOT 

Step 1 : Open the AX -> AOT -> Queries

Queries: RBCustomer

Step 2 : Drag and drop the  Table: CustTable  to the Query Datasource of the RBCustomer Queries , and the change the field properties 'Dynamic'  to Yes as shown above and save it.

Step 3 : Open Visual Studio , Click on "New Project"  ,  Expand the Templates - > Select Microsoft Dynamics AX -> Select "Report Model"

Step 4: Enter the report name as "RB_CustomerReportProject" and Click on the Ok button. The new visual studio project will be created.

Step 5:  Right Click on the project , Move your cursor to "Add" -> Select  the  "Report " menu. The new report will be created as shown below.

Step 6: Goto the properties and rename the report as RB_CustomerReportQuery.

Step 7: Click on Add Dataset

Step 8:  Change the Name as "Customer" in the property window.

Step 9: Click on the small button available on the  properties "Query"

Step 10:  List of Queries available in the AOT will be opened , select the query "RBCustomer " created for this demo as shown below.

Step 10 : Here you can select required fields. Either you can select all fields or a subset.

Step 11 : Expand All Fields nodes under the CustTable node to select “AccountNum”

Step 12: Expand Node “All Display Methods” and select “name” and “openBalanceMST” and Click “Ok”

Step 13: This will generate the required fields for the dataset

Step 14: Now Right click on the designs node, select Add and then “Auto Design”. This creates a new Precision Design 

                                

Step 15: Now right click on new Design and select “Edit Using Designer”

• This opens up the SSRS Designer (same as standard SSRS)
• You can see the standard Report Item tool bar from where you can drag and drop the controls onto the designer surface

Step 16 : Now from the controls tool bar, drag and drop the Table control onto the Report Designer , Select the Tablix and then set the DataSetName property = “Customer”

Step 17 : Now let us specify headers, Select one text box in the header section and right click, then select “Expression…”  

                            

Step 18 : In the expression editor, you can see lot of sections for Variables, Parameters, Fields, Datasets etc.

Step 19 : Here write the following in the space “Set expression for: Value” –  =Labels!@SYS316441
This displays the header text using the labels in AX. This label is “Customer”

Step 20 : Now again specify the values in Second and third text boxes in header row
Step 21 : 2nd Column Header(Name) : =Labels!@SYS117778
Step 22 : 3rd Column Header (Amount): =Labels!@SYS62867

Specifying the data for the table

Step 23 : Now in the data row, move the mouse to first text box and you should see a button on the right side that looks as shown below

Step 24: Click the button and it displays the list of fields from the dataset, select required fields

Step 25 : Alternatively, you can right and use “Expression…” box to specify the data fields. The expression box looks as shown below:

Now that the data is ready, we will go ahead and change the look and feel of the report

Formatting the table

You can specify the lots of formats using the properties windows like Background color, Font type color etc. You can also use the formatting tool bar above to format the data

Go ahead and specify the borders for the table for each row. Then change the background of the Header row. Make the text in the header row as center aligned and make the font as bold

Select the table rows one by one and change the font to “Segoe UI” and Font Size to “8pt”. Now your table looks as shown below:

Step 26 :Now we will go ahead and a Page Header and Page Footer. In Page Header, we will add Report name, Report Title and Execution Date and Time. In the Page Footer, we will add the Page Numbers in the format (Page of Total Pages)

To enable Page Header and Footer, in the Report menu select “Add Page Header” and “Add Page Footer” options

Step 27: Now drag three text boxes from tool box and drop onto the Page Header area. Place two text boxes on the left hand corner and one on the right hand corner as shown below. Drag and drop one text box in the center of the Page footer as shown below.

Step 28 : Select the first text box, Open the expression box and in the Category section, select “Built-in Fields. Now double click on “ReportName” field and it should add the report name to value section

Step 29 : In the same way select “ExecutionTime” in third text box. For report title, Type in “Customer balances” in the second text box

Step 30 : In the footer section, select the 4th text box and type the expression ‘=Globals!PageNumber & ” of ” & Globals!TotalPages’

Apply formatting as required. Now you should see the design as follows

Step 31 : Now close the designer, add the report back to AOT (along with the model) and deploy the report from within AOT.

Step 32 :Create a new menu item

Go to AOT –> Menu Items –> Output, Right click and select “New Menu Item”. Set following properties

Step 33 : Now run the report and the report will be seen as shown below:

Based on your select , you will get the output as shown below.