SSRS Reports in D365 FnO

 

SSRS Reports

 

SSRS (SQL Server reporting services) - SSRS provides a tools and services to help you to create, deploy, and manage reports for your organization. By this programming features that enable you to extend and customize your reporting functionality.
It is a report generation software that is a part of Microsoft SQL Server.
It enables users to execute reports using a web interface or even as embedded reports in applications
by using a web services interface

 

 

Report Type & Designs

Report Types: In AX there are two types of Reports

·       Query Report

·       RDP Report

Query Report: We can create reports using AOT Query (static query). This approach is suggested when data is coming direct from tables.

RDP Report: RDP report involves business logic according to the requirement.  This logic may be calculations or getting values from different tables based on conditions.


Report Designs: Reports in AX are used to display data to users in a format which is suitable for the way that the information is read.
A report can have multiple designs so that the code can decide at run-time which design to use. The designs can be totally different, but they would share the same data sources and form methods.

There are two common design patterns

·       Auto Design

·       Precision Design

Auto Design: This report is automatically generated based on the query in the data sources of the report. It limits the possibilities for the developer as developer cannot make any changes in the design of report.

 

Precision Design: The developer has full control over the design, he can modify the design according to the requirement.

 

 

Report Nodes:

The purpose of each report items is described below:

·       Datasets: The report dataset retrieves data from the AOT query. All fields which are available in AOT query are shown in the report dataset which can be referred in the report design later on.

·       Designs: The design or layout of the report on which the data would be displayed after retrieving from AX.

·       Images: Any embedded images that you want to display on SSRS report.

·       Parameters: Report parameters which are to be displayed on SSRS report and user can filter the report based on those parameters.

 

 

Menu & Menu Items D365 FnO

 

 Menu & Menu Items

You can create menu & menu items to provide access to forms, reports, classes, jobs, and queries in Microsoft Dynamics 365.

There are 3 types of Menu Items:

Display Menu item - This object is used to contain menu items that reference runnable objects which are primarily for presenting data to the user such as forms.

Output Menu item - An output menu item should have the sole purpose to print a result, mostly used for referencing classes. Used for reports.

Action Menu item - As the name says it, you should create a menu item under this folder for your runnable object has an action to perform, for example creating or updating data.

So choosing between these three is on the developer’s sole discretion and I hope now you’ll be able to make a better choice for your scenario. A basic thumb rule is:

Display – for Form
Output – for Report
Action – for classes

Important Properties of Menu
Label
Menu Item Name
Menu Item type

Important Properties of Menu Items
Name
Label
Object
Object Type

 

 

From Methods

 

Forms Method

These methods are used to retrieve and validate calling parameters. Do not write any code that effects the database.

Example: Change properties of control, Like Visibility, Enable, Editable.

·       init () - These methods are used to retrieve and validate calling parameters, Change properties of control, Like Visibility, Enable, Editable

·       run ()

·       close ()

 

Forms Data Source Method

These methods are used to perform operation on database fields. And also for improving performance by clearing cache data.

Also used for Sorting, filtering and creating ranges.

Example: Set Datasource properties – AllowEdit, AllowCreate, AllowDelete.

·       Init () - These methods are used to perform operation on database fields. And also for improving performance by clearing cache data. Set Datasource properties – AllowEdit, AllowCreate, AllowDelete.

·       Executequery() – This method is frequently used for applying ranges and filters.

 

·       Active () – Updating design depending on selected records, updating other datasource queries.

·       Create() – Add supplementary rules to validate the creation. Example while creation of record open some new form to enter values.

·       Initvalue() – Assign default value

·       Validatewrite () – Validate business rules and data consistency. Example mandatory fields that are form specific.

·       Write () – Override the normal saving process with specific rules. Updating datasource in same form.

 

 

 

Form Method Calling Sequence

 

Sequence of Methods calls while opening the Form

Form --- init ()
Form --- Datasource --- init ()
Form --- run ()
Form --- Datasource --- execute Query ()
Form --- Datasource --- active ()

 

Sequence of Methods calls while closing the Form

Form --- canClose ()
Form --- close ()

 

Sequence of Methods calls while creating the record in the Form

Form --- Datasource --- create ()
Form --- Datasource --- initValue ()
Table --- initValue ()
Form --- Datasource --- active ()

 

Sequence of Method calls while saving the record in the Form

Form --- Datasource --- ValidateWrite ()
Table --- ValidateWrite ()
Form --- Datasource --- write ()
Table --- insert ()

 

Sequence of Methods calls while modifying the fields in the Form

 

Table --- validateField ()

Table --- modifiedField ()

 

 

 

 

FormRun formRun = sender.formRun() as FormRun;

You can even call custom methods
formRun.myCustomMethod();

Get the selected datasource record
TableName tableBuffer = sender.cursor();

Get datasource variable
FormDataSource salesTable = sender.formRun().dataSource(SalesTable);

 

Edit Property

salesTable_ds.object(fieldNum(SalesTable, SalesId)).allowEdit(allowEdit);

 

Get Value

salesTable_ds.object(fieldNum(SalesTable, SalesId)).getValue();

 

Set Value

            salesTable_ds.object(fieldNum(SalesTable, SalesId)).setvalue(“Pass your value”);

 

Enable/disable a control

DataSourceName_ds.object(fieldnum(Table, Field)).visible(true/false);

 

Make a control mandatory

DataSourceName_ds.object(fieldnum(Table, Field)).mandatory(true/false);

 

 

Create new form D365 FnO

                                                             Create new forms

Create the form

·       Right click on project and add new form “MyNewForm” in your project, a form is added to the project. Open the project to see designer

·       Add a Data Source to the Form, Expand MyNewForm so that the Data Sources and Designs nodes are visible. Drag Sales Table onto the Data Sources node of the form.

·       You can also create a form data source by right-clicking the Data Sources node of MyNewForm, and then clicking New Data Source. Use the Table property to select Sales Table.

Form controls belong to one of three groups, depending on their data source as follows:

·        Bound control – associated with a field in an underlying table. Use bound controls to display, enter, and update values from fields in the database.

·        Unbound control – does not have a data source. Use unbound controls to display pictures and static text.

·        Calculated controls – uses a method as the data source. An example of a calculated control is the sum of two fields on a form.

Add a control and fields to the Form

·       Right click the Design node of MyNewForm. In the properties window, click Caption and type Sales. The value in the Caption property appears in the title bar of the form.

·       Click Title Datasource and select salesTable from the drop-down list.

·       Right-click Design, click New Control, and then click Grid.Click the grid in the Design node. In the Properties window, click Width and select Column width. Click Height and select Column height.

·       Expand the Data Sources > SalesTable > Fields node of MyNewForm.

·       Press Ctrl and then click AssetId, Name, SerialNum, and Model. Drag the highlighted fields to the Grid in the Design node of MyNewForm.

 

Add a Button to the Form

·       Right-click Design, click New Control, and then click Button.

·       In the Properties window, click Text and type Sales order.

·       Expand the node for the new button, right-click Methods, click Override method, and then click clicked.

·       Right-click the clicked node, and then click View Code. The clicked method opens in the code editor.

 

 

Add the action pane

·       Go to Designs in forms, right-click Design, point to New Control, and then click ActionPane. An action pane control is added to the Design node of the form.

·       Properties

Name - Enter a name that identifies your action pane.

Style - Standard

Vertical Spacing - 0

Width - Column width

Add an action pane tab

·       Right-click the action pane control, point to New Control, and then click ActionPaneTab. An action pane tab control is added to the action pane.

·       Properties

Name - Enter a name that identifies your action tab.

Caption

Add an action pane button group

·       Right-click the action pane tab, point to New Control, and then click ButtonGroup. A new button group control is added in the AOT.

 

Add an action pane button

·       Right-click the button group control, point to New Control, and then click the type of button you want to add to the group. The new button is added to the button group.

·       For example, you click CommandButton to add a button that performs a specified Microsoft Dynamics AX command.

 

 

 

 

Forms in D365FnO

 

                                                            Forms

A form is a window that you open from the Microsoft Dynamics AX client workspace that shows information and enables you to perform actions.

There are seven different predefined form templates.

§  ListPage

§  DetailsFormMaster

§  DetailsFormTransaction

§  SimpleListDetails

§  SimpleList

§  TableOfContents

§  Dialog

§  DropDialog

ListPage                                   Main entry in to a module.
DetailsFormMaster                  View and edit master data.
DetailsFormTransaction           View and edit transactional and worksheet data.
SimpleListDetails                     Reference and setup data.
SimpleList                                Basic inquiry.
TableOfContents                     Module configuration parameters.
Dialog                                      Quick user interaction.

ListPage – A list page is a form that displays a list of data related to a particular entity or business object. A list page provides provisions for displaying data and taking actions on this data. Every module has at least a couple of list pages. List pages are further classified as primary and secondary list pages. A secondary list page will only display a subset/Details of data from the primary list page. Example, CustTableListPage, VendTableListPage, ProjProjectsListPage. Best practice is to have ListPage as a suffix in the name of the form for all list pages.

DetailsFormMaster: This template is used for forms which display data for stand-alone entities or business objects. Example includes Customers, Vendors, and Projects etc. If you look at the forms for these, i.e. CustTable, VendTable, ProjTable, their style property will be set to DetailsFormMaster.

DetailsFormTransaction: This template is used for forms which display data for entities which have child records associated with it. In other words, if the data being displayed is header-lines type in nature, use this template. Example, Sales orders, Purchase orders etc. If you look at the style property of SalesTable, VendTable, their properties will be set to DetailsFormTransaction.

 

SimpleListDetails – This template is used to display primary fields in a list and detailed data in a tab page. This style is useful to view all records in a form and selecting a particular record will bring up their details. Example includes HcmSkillMapping, etc.

SimpleList – This template is a very basic form which displays data in a grid. No extra or fancy detail is displayed. This style is best suited for forms where data being shown is not very detailed in nature or has limited fields. Example includes AifAction, etc.

TableOfContents – This template is the new style which should be adopted for all parameter forms in Dynamics 365. Take a look at any parameters form and its style property will be set to TableOfContents. This style sets all the tabs as a hot link in the left hand side navigation pane. Clicking on the link will bring up the controls on that tab page. This style is a very neat and appealing UI design which is surely welcome. Example includes Custparameters, vendparameters, etc.

Dialog – This template is used on forms which show static data or are intended to capture some user input for further actions. These forms are mostly modal in nature and should be dismissed before any further actions can be taken.

DropDialog – This template is used for forms that are used to gather quick user inputs to perform an action. Drop dialog forms are generally attached to an action pane button. They appear to be dropping from the menu button when clicked. Example includes HcmWorkerNewWorker,  .

 

 

 

Examples:

ListPage           Accounts receivable > Common > Customers > All Customers
DetailsFormMaster      Accounts receivable > Common > Customers > All Customers > Edit
DetailsFormTransaction           Sales and marketing > Common > Sales Orders > All sales orders > Edit
SimpleListDetails         General ledger > Setup > Currency > Currencies
SimpleList        Master planning > Inquiries > Processes > Planned orders log
TableOfContents         Accounts payable > Setup > Accounts payable parameters
Dialog  Product information management > Products > Products > New product

 

.

 

Queries in D365FnO

 

Queries

Views have limitation, they can be used only for inner joins. You can create queries for other joins and use them in your views. Queries are faster, compiled and reusable.

There are two Types of Queries:

·       AOT/Static queries

·       Dynamics queries

Static Queries:

Create a new query in your project >> Give the name as "ATTCustTable"

Go to datasources >> drag and drop ATTCustTable to the datasource

In field’s property: Set the dynamic property to "Yes" if you want all the fields. If you make it "No", only user selected fields will come.

Go to ranges node in the datasource and add new ranges on - AccountNumber, Name

On the AccountNumber range - set the value to 805..810.

In the order by - select Name field - descending order

How to run this query, Create a new runnable as shown below

static void RunAOTQuery(Args _args)
{
    Query q = new Query(querystr(ATTCustTable)); //Intrinsic/ Inbuild function

    QueryRun queryRun;

    ATTCustTable attCusttable;

    queryRun = new QueryRun(q);

    if (queryRun.prompt())
    {
        while (queryrun.next()) // 805 t0 810
        {
            g = queryRun.getNo(1);
            //g = queryRun.get(tablenum(attCusttable));

            info(g.AccountNumber +',' + g.Name);

        }
    }
}

Run time Queries:

Query Framework classes have four classes

Query, QueryRun, QueryBuildDataSource, QueryBuildRange

Example:
static void RunTimeQueries(Args _args)
{

    Query q = new Query();

    QueryBuildDataSource     qbds;

    QueryBuildRange     qbrAccNo, qbrName;

    QueryRun     queryRun;

    ATTCustTable attCustTable;

   

    qbds = q.addDataSource(tablenum(ATTCustTable));

    qbrAccNo = qbds.addRange(fieldnum(ATTCustTable, AccountNumber));   

    qbrAccNo.value('805..810'); // queryvalue will convert anytype datatype

   

  queryRun = new QueryRun(q);

    if (queryRun.prompt()) // This will open user interactive form – sysqueryForm
    {
        while (queryrun.next()) // 803 t0 806
        {
            g = queryRun.getNo(1);

            //g = queryRun.get(tablenum(ATTCustTable));

            info(g.AccountNumber +',' + g.Name);

        }
    }
    else
        throw error("User has cancelled the operation");
}

Qbds2 = qbds.addDatsaource – To add another datasource

Qbds.addlink to add relation with another database