SSRS Reports byPrecision Design in AX 2012

Today I will demonstrate you that How to create reports through precision design in AX 2012.

To define the queries

1. Open Microsoft Dynamics AX.
2. In the AOT, right-click the Queries node, and then click New Query.
3. Right-click the node for the query, click Rename, and then type InvoiceJournal.
4. Expand the node for the InvoiceJournal query.
5. Right-click the Data Dictionary node, and then click Open New Window.
6. In the new window, expand the Tables node.
7. Locate the VendInvoiceJour table and drag it onto the Data Sources node for the query.
8. Expand the node for the VendInvoiceJour data source.
9. Right-click the Ranges node, and then click New Range.
10. Right-click the VendGroup node and then click Properties. Select the node for the range, and in the Properties window, set the Field property to InvoiceId.
11. In the AOT, right-click the Queries node, and then click New Query.
12. Right-click the node for the query, click Rename, and then type InvoiceTransaction.
13. Expand the node for the InvoiceTransaction query.
14. Locate the VendInvoiceTrans table and drag it onto the Data Sources node for the query.
15. Expand the node for the VendInvoiceTrans data source.
16. Right-click the Ranges node, and then click New Range.
17. Select the node for the range, and in the Properties window, set the Field property to InvoiceId.
18. Save the queries.

Creating a Reporting Project

To create a reporting project

1. Open Microsoft Visual Studio.
2. On the File menu, click New, and then click Project. The New Project dialog box displays.
3. In the Project Types pane, click the Visual C# node. In the Templates pane, click Dynamics AX Reporting Project.
4. In the Name box, type SampleVendorInvoiceReport, and in the Location box, type a location.
5. Click OK.

A reporting project contains a model by default. A model is a file with the .moxl file name extension. When a reporting project first opens in Microsoft Visual Studio, the model automatically displays in Model Editor. It contains a default report named Report1.

Creating a Precision Design Report Using SQL Report Designer

To define datasets for the report

1. In Model Editor, right-click the Report1 node, and then click Rename.
2. Type VendorInvoiceReport.
3. Right-click the Datasets node for the report, and then click Add Dataset.
4. Select the node for the dataset.
5. In the Properties window, specify the following property values.

Property                     Value

Data Source                Dynamics AX

Data Source Type       Query

Name                           InvoiceJournalDataset

Query                          Click the ellipsis button (…). A dialog box displays where you can select a                                     query that is defined in the AOT and identify the fields that you want to                                           use. Select the InvoiceJournal query, and then select the DueDate,                                                 InvoiceAccount, InvoiceDate, InvoiceId, OrderAccount, and PurchId                                           fields. Be sure the All check box is not marked.

6. In Model Editor, right-click the Datasets node for the report, and then click Add Dataset.
7. Select the node for the dataset.
8. In the Properties window, specify the following property values.

Property                                 Value

Data Source                            Dynamics AX

Data Source Type                   Query

Name                                      InvoiceTransactionDataset

Query                                      Click the ellipsis button (…). A dialog box displays where you can                                                 select a query that is defined in the AOT and identify the fields that                                                 you want to use. Select the InvoiceTransaction query, and then                                                     select the ItemId, LineAmount, Name, PurchPrice, and Qty fields.                                                 Be sure the All check box is not marked.

To configure the parameters

1. In Model Editor, expand the Parameters node.
2. Select the InvoiceJournalDataset_InvoiceId parameter.
3. In the Properties window, type Invoice ID: for the Prompt String property.
4. Select the InvoiceTransactionDataset_InvoiceId parameter.
5. In the Properties window, set the Visibility property to Hidden. Click the ellipsis button for the Values property. In the dialog box that displays, click the Parameter radio button, select the InvoiceJournalDataset_InvoiceId parameter from the drop-down menu, and then click OK.

To define a precision design using SQL Report Designer

1. In Model Editor, right-click the Designs node for the report, point to Add, and then click Precision Design.

The Precision Design window displays with an empty report. The Datasets window containing the datasets that you defined in the model appears on the left.

In the Toolbox pane, select the Textbox element and drag it to the upper-left side of the report.

2. Select the text box, and in the Properties window, set the Font property to Normal, Tahoma, 14pt, Normal. In the Value property, type Invoice specification. You may need to adjust the size of the text box.
3. In the Toolbox pane, select the Line element and draw a line below the text box that you added in the previous step.
4. Select the line, and in the Properties window, set the LineWidth property to 2pt and set LineColor property to Blue.
5. In the Toolbox pane, select the Table element and drag it below the line. Align the left side of the table with the beginning of the line.
6. Select the table, and in the Properties window, specify InvoiceJournalDataset for the DataSetName property.
7. Right-click the left column of the table and click Table Header so that it is not selected. Repeat and then click Table Footer so that it is not selected.
8. Right-click the left column of the detail row, and then click Insert Row Below. Repeat this until there are five detail rows in the table.
9. Right-click the right column of the table and click Delete Columns so that there are only two columns.
10. In the first cell of the first detail row, type Invoice account:.
11. Type text into the first cell of the remaining detail rows. The text that should be added is: Vendor account:, Purchase order:, Invoice date:, and Due date:.
12. In the Datasets window, select the InvoiceAccount field from InvoiceJournalDataset, and drag it to the second cell in the first detail row. Do the same for the cells in the remaining rows but use the OrderAccount, PurchId, InvoiceDate, and DueDate fields.
13. Select the second cell in both the fourth and fifth detail rows, and in the Properties window, type d for the Format property. This will format the values as dates.
14. Select the first column in the table, and in the Properties window, set the Font property to Normal, Tahoma, 10pt, Bold.
15. Select the second column in the table, and in the Properties window, set the Font property to Normal, Tahoma, 10pt, Normal, and set the TextAlign property to Left.
16. In the Toolbox pane, select the Table element, and drag it below the previous table.
17. Select the table, and in the Properties window, set the BackgroundColor to Lavender, and set the DataSetName property to InvoiceTransactionDataset.
18. Right-click the left column of the header row and then click Insert Row Above. Right-click the left column of the footer row and then click Insert Row Below, and add columns to the table so that it contains a total of five columns. You may need to resize the column widths.
19. Select all the cells in the first header row, right-click the selection, and then click Merge Cells.
20. Select the merged cell, and in the Properties window, set the Font property to Normal, Tahoma, 10pt, Bold, set the TextAlign property to Center, and type Item details for the Value property.
21. Select both the last header row and the details row, and in the Properties window, set the BorderColor property to DarkGrey, set the BorderStyle property to Solid, and set the TextAlign property to Center.
22. In the second header row, type the following headings in the cells: Item, Description, Quantity, Unit price, and Amount. In the Properties window, set the Font property for each of these cells to Italic, Tahoma, 10pt, Normal.
23. Drag the ItemId, Name, Qty, PurchPrice, and LineAmount fields from InvoiceTransactionDataset in the Datasets window to the appropriate cells in the details row below the headings that you just created. The cell should display the value of the field. For example, the expression of the first cell in the details row should be =Fields!ItemId.Value. In the Properties window, set the FontFamily property for each of these cells to Tahoma.
24. Select the cells in the details row that display the values for unit price and amount, and in the Properties window, type c for the Format field. This will format the values in these cells as currency.
25. In the first footer row, merge the first four cells.
26. Select the merged cell, and in the Properties window, set the FontFamily property to Tahoma, set the TextAlign property to Right, and type Sales tax: for the Value property.
27. Select the last cell in the first footer row, type =Sum(Fields!LineAmount.Value*.07) for the Value property, set the FontFamily property to Tahoma, and type c for the Format property.
28. In the last footer row, merge the first four cells.
29. Select the merged cell, and in the Properties window, set the FontFamily property to Tahoma, set the FontWeight property to Bold, set the TextAlign property to Right, and type Total amount due: for the Value property.
30. Select the last cell in the last footer row, type =Sum(Fields!LineAmount.Value*1.07) for the Value property, set the Font property to Normal, Tahoma, 10pt, Bold, and type c for the Format property.
31. Select the cells that display the values for sales tax and total amount due, and in the Properties window, set the BorderColor property to DarkGrey, set the BorderStyle property to Solid and set the TextAlign property to Center.
32. Save the report.
33. To preview the report design, click the Preview tab in SQL Report Designer. Type an invoice ID in the Parameters tab, and then click the Report tab to view the report.