Create Quick first SSRS report in Dynamics AX 2012

Friends,

Today, let us learn how to quickly create report models and report in Visual studio and add to AOT[Dynamics AX 2012] and see a running report.

Some information:

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.

Also, for this post I am assuming that all report services are configured in the system.

Let us create a query in AOT as a dataset source for our report.

Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it. I will let you know the significance of adding the range as I proceed further..

So, your new query should like below :

Please note: we can use already existing queries which are in AOT for report as datasource, for better understanding I have created new query above.

Lets proceed further. Open visual studio 2010 and lets us create a new Dynamics AX project.

Once visual studio is launched >> click on file menu >> New project as shown below

Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_ReportNewModel as shown below

Now let us add a new report to the newly created report Model as shown below. Right click on the SR_ReportNewModel from the solution explorer, Add >> Report

Rename the report to SR_InventTable by right click and rename option on the newly added report.

Then we need to add the dataset to the newly created report. Right click on the datasets node and chose the option New datset. Rename it to InventTable and go to query property and click on the ellipsis (…) button to select the query which we have created as shown below [picture explains better than 1000 words]

It will open with list of Dynamics AX Queries from which we should select our query “SR_InventTableQuery” and click on next button as shown below

Now , you can select the list of fields and display methods you want to see on your report.
I have selected few fields form the fields and also couple of display methods like site Id and location Id as shown below and click on Ok Button.

wow..There you go… we are done with the datasets part and lets work on the design part real quick now…

Its very simple..Select the InventTable dataset and drag and drop on to your designs node as shown below. It will create autodesign for you 🙂

In my case, when I expand the designs node, i see my fields and the data methods added in the data nodes. we will look in to other nodes in detail later.

Well there are now some important [not mandatory] properties but beautification properties which make your report look good with style. Once you expnad the designs node, you will find InventTableTable with the dataset name. Right click on it and go to properties and set the style template to “TableStyleTemplate” as shown below.

On to autodesigns, we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below

Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it is hidden. Lets unhide or make it visible it as we want to display the items based on the company [dataaread id] selection by the user.

See the screen shot below

wow..we are getting closer. Now we can preview the data by right clicking the autodesign and by chosing option preview as shown below

Note: you can select the company parameter and click on the report tab to view the report. But our main aim is to deploy back this report model to AX.

To deploy the report to AOT, we have a very simple option. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.

We are done with visual stuido development part. Now lets us switch to AX and see whether the report model has been saved to AOT or not. To do so, open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects . you should see SR_ReportNewModel project.

Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventTable report.

Now we are left out with final thing, creating menuitem for this report. This can be easily done by following hthe below process.

Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.

Cool.. we are done and hope you are excited to view the report now. Well you can add this menu item to relevant menu and I hope you knw this process.

Now , lets open the report, Right click on the newly created menu item and select open.[You should see the following as shown below]

Since we have made the company parameter visible- we have option of generating the report based onthe dataareaid and since we have added range ItemId to the query – we get twow ranges as shown above.

I have selected ceu as my company and I am leaving ItemId as blank to view all the items in the report. Now lets run the report and see how it renders the data. [Below is the report]

Note: AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration. we shall look in to details in next posts.

Happy Dax6ng,
Sreenath

Valid Time State Tables in Dynamics AX 2012 [ValidTimeStateFieldType]

Friends,

In DAX 2012, we can let the system manage data relationships that are valid only during a specified date range.

This can be achieved by setting “ValidTimeStateFieldType” property on the table.The system then adds the ValidFrom and ValidTo columns that track a date range in each row. The system guarantees that the values in these date fields remain valid by preventing overlap among date ranges, based on the primary key value.

Let me explain with an example. Let us create a new table with a new string field “Name” which extends Name EDT.

Right click on the table and set the ValidTimeStateFieldType property to utcdateTime as shown below

After setting this property, system will automatically create ValidFrom and ValidTo fields in your table as shown below

Create a new Index by name NameIdx and drag drop Name, ValidFrom, ValidTo fields on to the index and set the following properties on your newly created index as shown below.

You can set the NameIdx as replacement Key on the table.

Now open the table and start entering the data. you will notice that ValidTo will be automatically defaulted to maxdate() 12/31/2154[Never] and validFrom to system date and time now as shown below.

Now let us select all the records in the table starting from min date to max date with the new keyword validTimeState in the SELECT statement as shown below. [Note, Give your own from date time and To datetime Ranges and it work perfctly fine by filtering only those records within the range]


static void SR_ValidState(Args _args)
{
SR_ValidTimeState sr_validTimeState;
utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();

While SELECT
validTimeState(minDateTime, maxDateTime)
* from sr_validTimeState
order by sr_validTimeState.ValidFrom
{
info(SR_ValidTimeState.Name);
}
}

we can also achieve the same thing by using Queries. The following code example uses the Query class to read all of the rows from a valid time state table. Use a new validTimeStateDateTimeRange method to achieve this.

utcdatetime minDateTime = DateTimeUtil::minValue() , maxDateTime = DateTimeUtil::maxValue();
qry = new Query();
qry.validTimeStateDateTimeRange(minDateTime, maxDateTime);

There are 4 methods intotal which are introduced new in the query framework.
validTimeStateDateTimeRange
validTimeStateDateRange
validStateAsOfDate
validStateAsOfDateTime

The same concept will be used on the forms to show only relevant records based on the system date and time if the ValidState is enable on the tables. [I believe so – will post in detail soon]

Happy Dax6ng,
Sreenath

Full Text Index in Dynamics AX 2012 – X++

Friends,

Full text index supports to quickly query words that are embedded in the middle of a string field of a table. Well, this is a very nice enhancement to query on Database table fields for the developers who work with the latest vesion [Microsoft Dynamics AX 2012]

Good thing is we can use this Index on Memo fields and also Extended data type.

Let me explain with an example. Create a new table as shown below and add a new field of type string “Name” to it – On the field use EDT – Name. In the below example, my table Name is SR_FullTextExample.

Once you are done with your table, Go to FullTextIndex Node >> Right click and create a new FullTextIndex.
Rename it to NameFullTextIndex. Drag and drop Name field from the fields to the newly created index.

The table with index should look like below.

I have added some dummy records/data for testing purpose as shown below.

Now, let us see how to use this FullTextIndex for searching a field with the words.

Please Note: X++ select statements cannot use a full text index

A full text index can improve the speed of queries that search for words that are embedded in string and memo fields on tables. [MS help]

The QueryRange class has a rangeType method. You can direct a QueryRange object to use the full text index by passing the QueryRangeType::FullText enum value to the rangeType method. [MS help]

If the value you are searching for is a two word phrase with a space between the words, the system treats the space as a Boolean OR [MS Help]

Below job will help to search the strings with in a full string in the fields.

static void SR_FullTextQuery(Args _args)

{

    Query                   query;

    QueryBuildDataSource    qbds;

   

    QueryBuildRange         queryBuildRange;

    QueryRun                queryRun;

    SR_FullTextExample      sr_FullTextExample;

 

   

    query = new Query();

    qbds = query.addDataSource(tableNum(SR_FullTextExample));

    queryBuildRange = qbds.addRange(fieldNum(SR_FullTextExample, Name));

 

    queryBuildRange.rangeType(QueryRangeType::FullText);

 

    // The space character is treated as a Boolean OR.

    queryBuildRange.value(“Sreenath Kumar”);

 

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        sr_FullTextExample = queryRun.get(tableNum(SR_FullTextExample));

        info(sr_FullTextExample.Name);

    }

 

}

 

 


As you can see, the results displayed is purely based on the search using ‘OR’ condition.

Happy Dax6ng,
Sreenath