In this walkthrough, you will create a vendor invoice report. You will use SQL Report Designer to create a precision design for the report. The following illustration shows the report that you will create in this walkthrough. 
Note
The data that displays in your report may vary depending upon the sample data that is available to you.
This walkthrough illustrates the following tasks:
-
Defining queries
-
Creating a reporting project
-
Creating a precision design report using SQL Report Designer
To complete this walkthrough, you will need:
-
Microsoft Dynamics AX with sample data
Note In this walkthrough, you will use the VendInvoiceJour and VendInvoiceTrans tables. Therefore, these tables must be populated with data.
-
Microsoft SQL Server 2005
-
Microsoft Visual Studio 2008 with Service Pack 1
-
Microsoft Dynamics AX Reporting Tools for Microsoft Visual Studio
There are several ways to retrieve data for reports. In this walkthrough, you will create queries within the Microsoft Dynamics AX development environment. The following procedure explains how to define the queries that will be used to retrieve data for a vendor invoice report.
To define the queries
-
Open Microsoft Dynamics AX.
-
In the AOT, right-click the Queries node, and then click New Query.
-
Right-click the node for the query, click Rename, and then type InvoiceJournal.
-
Expand the node for the InvoiceJournal query.
-
Right-click the Data Dictionary node, and then click Open New Window.
-
In the new window, expand the Tables node.
-
Locate the VendInvoiceJour table and drag it onto the Data Sources node for the query.
-
Expand the node for the VendInvoiceJour data source.
-
Right-click the Ranges node, and then click New Range.
-
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.
-
In the AOT, right-click the Queries node, and then click New Query.
-
Right-click the node for the query, click Rename, and then type InvoiceTransaction.
-
Expand the node for the InvoiceTransaction query.
-
Locate the VendInvoiceTrans table and drag it onto the Data Sources node for the query.
-
Expand the node for the VendInvoiceTrans data source.
-
Right-click the Ranges node, and then click New Range.
-
Select the node for the range, and in the Properties window, set the Field property to InvoiceId.
-
Save the queries.
Next, you will create a reporting project in Microsoft Visual Studio. When you create a reporting project, you can choose from two project templates: Visual Basic and Visual C#. In this walkthrough, you will use the Visual C# template.
To create a reporting project
-
Open Microsoft Visual Studio.
-
On the File menu, click New, and then click Project. The New Project dialog box displays.
-
In the Project Types pane, click the Visual C# node. In the Templates pane, click Dynamics AX Reporting Project.
-
In the Name box, type SampleVendorInvoiceReport, and in the Location box, type a location.
-
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
Now that you have created a reporting project, you are ready to create a precision design report to display the data returned by the queries that you created in Microsoft Dynamics AX. To do this, you will first define datasets for the report. Then, you will configure the parameters that are created for the datasets. Finally, you will use SQL Report Designer to define the report layout.
To define datasets for the report
-
In Model Editor, right-click the Report1 node, and then click Rename.
-
Type VendorInvoiceReport.
-
Right-click the Datasets node for the report, and then click Add Dataset.
-
Select the node for the dataset.
-
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.
-
In Model Editor, right-click the Datasets node for the report, and then click Add Dataset.
-
Select the node for the dataset.
-
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.
Since the queries that are used for both of the datasets have a range based on the InvoiceId field, parameters are added to the report in Model Editor. Parameters of this type are automatically added as soon as you bind the query to a dataset. The name of the parameter is derived from the dataset name and the field name. In this case, both parameters represent the same field. Therefore, you will set up the report so that one of the parameters is visible to the users and the other parameter is hidden. The hidden parameter will get its value from what the user enters for the visible parameter.
To configure the parameters
-
In Model Editor, expand the Parameters node.
-
Select the InvoiceJournalDataset_InvoiceId parameter.
-
In the Properties window, type Invoice ID: for the Prompt String property.
-
Select the InvoiceTransactionDataset_InvoiceId parameter.
-
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
-
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.
Note If the Datasets window does not appear, a supported hotfix is available from Microsoft to fix this problem. To download the hotfix, see the MSDN Code Gallery for KB947173.
-
In the Toolbox pane, select the Textbox element and drag it to the upper-left side of the report.
-
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.
-
In the Toolbox pane, select the Line element and draw a line below the text box that you added in the previous step.
-
Select the line, and in the Properties window, set the LineWidth property to 2pt and set LineColor property to Blue.
-
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.
-
Select the table, and in the Properties window, specify InvoiceJournalDataset for the DataSetName property.
-
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.
-
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.
-
Right-click the right column of the table and click Delete Columns so that there are only two columns.
-
In the first cell of the first detail row, type Invoice account:.
-
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:.
-
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.
-
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.
-
Select the first column in the table, and in the Properties window, set the Font property to Normal, Tahoma, 10pt, Bold.
-
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.
-
In the Toolbox pane, select the Table element, and drag it below the previous table.
-
Select the table, and in the Properties window, set the BackgroundColor to Lavender, and set the DataSetName property to InvoiceTransactionDataset.
-
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.
-
Select all the cells in the first header row, right-click the selection, and then click Merge Cells.
-
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.
-
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.
-
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.
-
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.
-
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.
-
In the first footer row, merge the first four cells.
-
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.
-
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.
-
In the last footer row, merge the first four cells.
-
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.
-
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.
-
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.
-
Save the report.
-
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.
No comments:
Post a Comment