Creating SSRS report using ‘Query’ and ‘Auto Design’

In this example we will create a SSRS report that shows all the records in ‘VendTable’

1. Create a new query.

     a) Create a new query “SSRS_VendTable” in the AOT and add “VendTable” as the data source and rename it to “VendTable_DS”.

s1

     b) Now set the “Dynamic” property of ‘Fields’ to “Yes” (in order to include all the fields from the table).

s2

You can manually select the fields from the table “VendTable” and drag and drop them to the data source “VendTable_DS”.

     c) Set the range of the query.

The main purpose of setting a range is to filter the data that is to be displayed on the report. Let set field “AccountNum” as range

s3

2. Create a new report project: The next step is to create a SSRS report that will use this query to display the records from “VendTable”.

     a) Launch Visual Studio, click on “File” menu and select “New >> Project”. It will open the following dialog box:

s4

Here select the option “Report Model” under “Microsoft Dynamics AX” and specify the name of the project “SSRS_VendTable” and press “OK”. This will create a new SSRS project.

     b) Add “Report” to the newly created project by referring the screenshot below.

s5

     c) Add the dataset to the newly added report.

s6

Rename the dataset to “VendTable”.

     d) Right click on the dataset “VendTable” and select properties.  When you select the property “Query” a dialog box will pop up.

This dialog box will list all the queries in AOT. Select the query “SSRS_VendTable” and click on “Next”.

s7

The next dialog box will list all the fields in the query “SSRS_VendTable”. Click on “All the fields” and select “OK”. You can manually select the fields that you want to include in this report.

s8

     e) Add “Design” to the report.

In SSRS reports there are two kinds of designs “Auto Design” and “Precision Design”. We generally use “Precision Design” when the report is complex in nature.

So continuing with our example, simply select the dataset “VendTable” and drag and drop it on the “Design” node, it will create a new “Auto Design”.

 s9

Change the name of the design to “Design”.  Here we need to set an important property Layout Template set it to “ReportLayoutStyleTemplate”.

3. Our report is now complete. In order to view the report right click on report and select “Preview”

s10

 You will get the following screen:

s11

In the step “1.c” we specified “AccountNum” as the query range. So click on “Select” and specify the range of the query. Now click on “Report” you will be able to view the report:

s12

Written By: Shikha Satija (Dynamics AX Team, Cipher Dynamics) 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s