Building a Simple 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 again use the same example of printing Customer Id, Customer name and Balance but this time instead of creating an Auto Design we will use 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.

Here we will use the same AOT query created in previous example Build and Deploy Simple Report–Queries: SKL_SampleCustomer

Create the Required Dataset

  • Open the previously created Report Model

image_thumb26

  • Right click on report model, select Add –> Report. Name the report SKL_SampleReportPrecision
  • Set the report title to “Customer balance”

image

  • Now in the report, go to the Datasets section and create new Dataset using the name Customer
  • In the query property, click the button image_thumb6
  • This opens up the query selection box to select a Microsoft Dynamics AX Query
  • From the list select “SKL_SampleCustomer” and click Next
  • image_thumb20

  • Here you can select required fields. Either you can select all fields or a subset
  • Expand All Fields nodes under the CustTable node to select “AccountNum”
  • Expand Node “All Display Methods” and select “name” and “openBalanceMST”
  • Click “Ok”
  • Now Right click on the designs node, select Add and then “Precision Design”. This creates a new Precision Design and name it as PrecisionReport

image

  • Now right click on new Design and select “Edit Using Designer”

image

  • 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

image

  • 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”

image

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

image

  • In the expression editor, you can see lot of sections for Variables, Parameters, Fields, Datasets etc.
  • 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”

image

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

Specifying the data for the table

  • 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

image

  • Click the button and it displays the list of fields from the dataset, select required fields

image

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

image

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

image

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:

image

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

image

image

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.

image

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

image

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

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

image

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

Create a new menu item

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

image

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

image

Advertisements

Building a simple report – Using Report Data Provider

In my previous post, I explained how we can build a simple report using just the AOT queries. Now what if we have some logic that needs to be implemented and cannot be achieved using AOT queries?

This is where Report Data Providers plays a significant roles. Let us take the same example of displaying the Customer Id, Name and Balance that was used in post “Build and Deploy Simple Report–Queries”.

We can have this report using Report Data Providers as well. For this, we will keep the query and create three more artifacts, RDP, Report and a new Output menu item.

First we create a Report Data Provider class named “SKL_SampleReportDP”. Do the following:

To create an RDP for a report, we also need a temporary table (if it is Base Provider) or a permanent table (if it is pre process provider).

For this sample, we will use CustTmpAccountSum table that is present in base product.

Here are the class methods

/// <summary>

/// The <c>SKL_SampleReportDP</c> class is the report data provider class for the

/// SKL_SampleSimpleReportQuery report.

/// </summary>

/// <remarks>

/// This is a sample class. Author: Sumit Loya

/// </remarks>

[ SRSReportQueryAttribute (querystr(SKL_SampleCustomer))]

class SKL_SampleReportDP extends SRSReportDataProviderBase

{

    CustTmpAccountSum   tmpAccountSum;

}

 

The class declaration contains one attribute “SRSReportQueryAttribute”. This attribute specifies the query that will be used for this report. In case no query is required, this attribute can be removed.

There is one other attribute that can be specified on the RDP class and that is SRSReportParameterAttribute. This attribute defines the contract class that will be used to display report parameters.

processReport method

The processReport method is the entry point for calculating the report data for dataset. Here is the method for our sample class

[SysEntryPointAttribute(false)]

public void processReport()

{

    this.insertTmpAccountSum();

}

 

insertTmpAccountSum method

This is a private method that uses the report query to insert data into the temporary table

/// <summary>

/// This method processes the report query and inserts data into the CustTmpAccountSum temporary table

/// </summary>

private void insertTmpAccountSum()

{

    QueryRun            queryRun = new QueryRun(this.parmQuery());

    CustTable           custTable;

   

    while (queryRun.next())

    {

        custTable = queryRun.get(tableNum(custTable));

       

        tmpAccountSum.AccountNum    = custTable.AccountNum;

        tmpAccountSum.Txt           = custTable.name();

        tmpAccountSum.Balance01     = custTable.openBalanceMST();

        tmpAccountSum.insert();

    }

}

 

getCustTmpAccountSum method

This method is mandatory as it returns the table buffer that contains the processed report data. The Dataset uses this buffer to bind the table to the dataset.

/// <summary>

/// This method returns the table buffer that contains processed data

/// </summary>

[SRSReportDataSetAttribute(tableStr(CustTmpAccountSum))]

public CustTmpAccountSum getCustTmpAccountSum()

{

    select * from tmpAccountSum;

 

    return tmpAccountSum;

}

 

After creating the class, go ahead and add a new report the existing report model.

  • Open the previously created Report Model

image

  • Right click on report model, select Add –> Report. Name the report SKL_SampleReportDP
  • Now in the report, go to the Datasets section and create new Dataset using the name CustomerDP
  • The parameters for the new Dataset should be as shown below. The Data Source Type is “Report Data Provider”

image

  • In the query property, click the button image_thumb6
  • This opens a box to select a data provider class
  • Select the class we created before, SKL_SampleReportDP and click “Next”

image

  • In the next tab, Deselect all fields and just select “AccountNum, Balance01 and Txt” fields
  • Click Ok

image

  • This is how the data set looks like

image

  • Now create a new Auto Design as before and name it as CustDP, Select a Layout Template for the report design in Parameters window
  • Now right click on newly created design select “Add” and “Table”
    • Set following properties
      • Name – CustDP
      • Dataset – CustomerDP
      • Style Template – TableStyleAlternatingRowsTemplate
  • You will also notice that the fields are included automatically from Dataset to the “Data” node of the table

image

  • Now right click on the report model and select option “Add SKL_SampleReportProject to AOT”
  • Once the report is created back to AOT, go to AX, find the report in AOT –> SSRS Reports –> Reports, right click and deploy using “Deploy Element” option
  • Once the deployment is successful, create an output type menu item for the report
  • Go to AOT –> Menu Items –> Output, Right click and select “New Menu Item”. Set following properties

image

Now using menu item open and run report. You will notice the same dialog

image

The report looks like as shown below:

image

Build and Deploy Simple Report–Queries

In this post, I will explain about creating a simple AX SSRS Report using the queries from AX AOT. This is what you need to do:

  • Login to AX and open the development workspace
  • Go to AOT –> Queries, right click and select “New Query”
  • Rename the query to “SKL_SampleCustomer”
  • Go to Query’s data source node and right click and select “New Data Source”
  • Rename the data source to “CustTable” and set property table to “CustTable”
  • Now go to the “Fields” node of this query and set property: Dynamic to “Yes”
  • The query looks as shown below

image

  • Save the query
  • Now open visual studio and create a new reporting project by name SKL_SampleReportProject
  • On the reporting model, right click, select “Add” and then Report
  • This will add new report to the project

image

  • Name this report as “SKL_SampleSimpleReportQuery”
  • In the report, Right click on the Dataset node and select “Add Dataset”

image

  • Name the data set as customer
  • On the properties window, ensure following properties are set by default

image

  • In the query property, click the button image
  • This opens up the query selection box to select a Microsoft Dynamics AX Query
  • From the list select “SKL_SampleCustomer” and click Next

image

  • Here you can select required fields. Either you can select all fields or a subset
  • Expand All Fields nodes under the CustTable node to select “AccountNum”
  • Expand Node “All Display Methods” and select “name” and “openBalanceMST”
  • Click “Ok”

image

image

image

  • This will generate the required fields for the dataset

image

  • Now Right click on the designs node, select Add and then “Auto Design”. This creates a new Auto Design and name it as Report

image

  • Select a layout template say “ReportLayoutStyleTemplate”

image

  • Now right click on newly created design select “Add” and “Table”

image

  • Set following properties
    • Name – Cust
    • Dataset – Customer
    • Style Template – TableStyleAlternatingRowsTemplate
  • You will also notice that the fields are included automatically from Dataset to the “Data” node of the table

image

  • Click on Preview once to view the report

image

Note: It may not be possible to preview all reports from VS, especially when there are some internal parameters etc.

  • Now right click on the report model and select option “Add SKL_SampleReportProject to AOT”

image

  • Once the report is added, go to AX, find the report in AOT –> SSRS Reports –> Reports, right click and deploy using “Deploy Element” option
  • Once the deployment is successful, create an output type menu item for the report
  • Go to AOT –> Menu Items –> Output, Right click and select “New Menu Item”. Set following properties

image

  • Now using menu item open and run report.

When you open the menu item, a dialog is automatically made and shown and you will notice that there is also a select button

image

This happens as we had kept the Dynamics Filter property on Dataset in report to “True”. Hence the Reporting framework adds a select button.

The displaying of dialog box can be handled as well. We will cover this topic when we learn about controller classes.

The report will look like this:

image

Note: The name has been intentionally removed from image

Hope this helps you in building your first simple report.