How to: Find the Data Source For a Report [AX 2012]


To find the data source

  • In the AOT, expand the SSRS Reports node, expand the Reports node, right-click the report for which you want to find the data source, click Add-ins, click Cross-reference, and then click Using (instant view). The data source for the report will display in the Complete cross-reference for the selected nodes form.

To determine the report that is using a data source

  • In the AOT, right-click the data source. For example, in the Data Dictionary > Tables node, right-click the CustTable table, click Add-ins, click Cross-reference, and then click Used By. The reports that use the CustTable table as a data source display.

Exception handling and transactions


The runtime semantics of exceptions is peculiar in X++. The difference from the semantics from other languages is that exception handling considers any catch blocks that are in a scope that contains a running transaction as ineligible to handle the exception. Consequently the behavior of exceptions is quite different in the situation where a transaction is running and in the situation where no transactions are running. This makes the exception semantics different from exception semantics of other languages, even though the syntax is very much the same. This is a perfect setup for confusion even for seasoned X++ programmers.
Please consider the example below for the following discussion:
 1: try 
 2: { 
 3:     MyTableType t; 
 4:     ttsBegin;    // Start a transaction 
 5:     try  
 6:     { 
 7:         throw error(“Something bad happened”); 
 8:     } 
 9:     catch 
10:     { 
11:         // Innermost catch block 
12:     } 
13:     update_recordset t … // Must run within a transaction 
14:     ttsCommit;    // Commit the transaction 
15: } 
16: catch 
17: { 
18:     Info (“In outermost exception handler”); 
19: } 
20:  
20: 
When the exception is thrown in the code above, the control is not transferred to the innermost catch block, as would have been the case if there had been no transaction active; Instead the execution resumes at the outermost exception handler, i.e. the first exception handler outside the try block where where the transaction started (This is the catch all exception handler in line 19).
This behavior is by design. Whenever an exception is thrown all transactions are rolled back (except for two special exceptions namely Exception::UpdateConflict and Exception::UpdateConflictNotRecovered, that do not roll back the active transactions). This is an inseparable part of the semantics of throwing exceptions in X++. If the exception is not one of the two exceptions mentioned above, the flow of control will be transferred to the handler of the outermost block that contains a TTSBegin. The reasoning behind this is that the state of the database queries would not be consistent when the transactions are rolled back, so all code potentially containing this code is skipped.
Another issue is balancing of the transaction-level.
Please consider an amended example:
 1: try 
 2: { 
 3:     MyTableType t; 
 4:     ttsBegin;      // Start a transaction – Increment TTS-level to 1 
 5:     try  
 6:     { 
 7:         ttsBegin;  // Start a nested transaction – Increment TTS-level to 2 
 8:         throw new SysException(“Something bad happened”); 
 9:         ttsCommit;     // Decrease TTS-level to 1 
10:     } 
11:     catch 
12:     { 
13:         // Innermost catch block 
14:         // What is the tts level here? 
15:         info (appl.ttslevel()); 
16:         // Something bad happened -> abort the transaction 
17:         ttsAbort; 
18:     } 
19:     update_recordset t … // Must run within a transaction 
20:     ttsCommit;       // Decrease TTS-level to 0, and commit 
21: } 
22: catch 
23: { 
24:     Info (“In outermost exception handler”); 
25: } 
Let’s examine the case where we allowed the inner catch block to run as the result of throwing the exception. The question is: What should the TTS-Level be in the innermost catch block?
TTSlevel is 1: This won’t work, as the innermost catch block must be able to compensate (and commit) or abort the level-2 transaction. TTS Level 1 would mandate that the system has taken the decision to either abort or commit the level-2 transaction.
TTSlevel is 2: This won’t work either, as the innermost catch block should be able to abort the level-2 transaction. A ttsabort inside the innermost catch block will also abort the level-1 transaction, and render the code following the catch block void. This in turn requires that the innermost catch block throws a new exception, and then we end up in the outermost catch block anyway.
I hope this provides a little clarity in these murky waters.

SQL improvements in the next version of Dynamics AX



Ordering and grouping Data

Previously, the order by and group by clauses acted on the table in the current join or select clause. So, the following

CustTable ct;
CustTrans transactions;

 select  *  from ct  order by AccountNum
 join transactions  order by AmountCur
 where ct.AccountNum == transactions.AccountNum;

would order by the account number, then on the amount. Note that the order by syntax doesn't include the table to which the field belongs, because it is implicit in the order by clauses position. This way of doing things works fine so long as you do not have requirements to order in a different order than what is mandated by the join structure. For instance, if you wanted to order by amount and then by accountnum you'd have to reverse the select/join hierarchy. To get rid of these problems, we have introduced a way of specifying an order by (and a group by) list that is not tied to a particular place in the join hierarchy. This is done by specifying a list of qualified names of fields in a single order by or group by clause:

 select *  from ct  join transactions  order by transactions.AmountCur, ct.AccountNum
 where ct.AccountNum == transactions.AccountNum;

There can be only one such augmented order by clause in a selection.


Improvements to the update statement

The update statement may now have a join clause attached to it, as shown in the code snippet below:

 // Three Table Join

  update_recordset salestable setting currencycode = 'USD' join custtable
  where  custtable.accountnum = salesTable.custaccount   join custgroup
  where custgroup.name == 'Retail' && custgroup.custgroup == custTable.custgroup;

We have also added some diagnostics that will cause the compiler to complain if the user tries to use the value of fields coming from tables that are given in exists and non exists joins (The database backend is not required to fetch values for these, merely to find out if there are matching records or not).

The Insert statement.

The insert statement is generally used to insert data from one table into another. The programmer typically writes a selection of fields matching the fields to insert into the target table and delimits the data by using suitable where and join clauses:

 insert_recordset MyTargetTable(targetField1, targetField2)  select sourceField1, sourceField2 from MySourceTable where ...

This is indeed suitable for the case where all the data to be inserted into the target table is already stored in the source table, but that is not always the case. The new feature allows the user to insert values that are given by variable references, not field references.

Create first SSRS report in Dynamics AX 2012


Hi All,
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.

SSRS reports with RDP Classes in DAX 2012



Hi All,

Today, I will be covering the Report data provider. An RDP class is an X++ class that is used to access and process data for a Reporting Services report. An RDP class is an appropriate data source type when the following conditions are met.

First things first: The following elements are required to set RDP as your data source type.
step 1: Query
step 2: Temporary table – RDP class fills a temporary table with data that will be used by Reporting Services to display the report.
[msdn help]
step 3:Data contract class – To define the parameters in the report.
step 4:Report data provider class – processes business logic based on parameters and a query, and then returns the tables as a dataset for the report. [msdn help]
Let me explain all the steps mentioned above with an example
For step 1: Create a new query by name SR_CustTable and add the dataasource as CustTable as shown below

Next step 2: Let us create a new temporory table by name TmpSR_CustTable and add 4 new fields as shown below. Ensure that you set the Table type propert as TempDB or InMemory
Use an InMemory temporary table if the dataset is small, for reports that will display fewer than 1000 records.
Use a TempDB temporary table for large datasets to improve performance.
In the below Table: I have set the Table type property to TempDB to improve the performance.Also, I have dragged dropped 4 fields AccountNum, Blocked, priceGroup, Currency as shown below.
Step 3: Now we need to Data Contract class – which will help to define parameters in the report.
Create a new class by name "SRSRDPCustTableContractClass" and add a AccountNum global variable as shown below
class SRSRDPCustTableContractClass
{
AccountNum accountNum;
______________________________________________
Add one more parm method to it as shown below
[DataMemberAttribute("AccountNum")]
public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)
{
accountNum = _accountNum;
return accountNum;
________________________________________________
Done…Lets move to Step 4
Step 4:
Now we need to create Report Data provider class
Create a new class by name "SR_CustTableRDP" that should extend SRSReportDataProviderBase class.
Attach the SRSReportQueryAttribute attribute to specify the query to use to get the data for the report.
For this example, set the attribute to the SR_CustTable query.
Attach the SRSReportParameterAttribute attribute to specify the data contract class that defines the report parameters for the report.
For this example, set the attribute to the SRSRDPCustTableContractClass.
[ SRSReportQueryAttribute (querystr(SR_CustTable)),
SRSReportParameterAttribute(classstr(SrsRDPCustTableContractClass))
]
class SR_CustTableRDP extends SRSReportDataProviderBase
{
TmpSR_CustTable tmpSR_CustTable;
_________________________________________________________
Now we need 3 more methods to be added
/// 
/// Processes the report business logic.
///

///
/// Provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be
/// returned to SSRS.
///
[SysEntryPointAttribute(false)]
public void processReport()
{
QueryRun queryRun;
Query query;
CustTable custTable;
SRSRDPCustTableContractClass srsRDPCustTableContractClass;
AccountNum accountNum;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange queryBuildRange;
query = this.parmQuery();
srsRDPCustTableContractClass = this.parmDataContract() as SRSRDPCustTableContractClass;
accountNum = srsRDPCustTableContractClass.parmAccountNum();
// Add parameters to the query.
queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));
if(accountNum)
{
queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountNum));
if (!queryBuildRange)
{
queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum));
}
// If an account number has not been set, then use the parameter value to set it.
if(!queryBuildRange.value())
queryBuildRange.value(accountNum);
}
queryRun = new QueryRun(query);
while(queryRun.next())
{
custTable = queryRun.get(tableNum(CustTable));
this.insertTmpTable(CustTable);
}
________________________________________________
/// 
/// This method inserts data into the temporary table.
///

///
/// Table buffer of CustTable table.
///
private void insertTmpTable(CustTable _custTable)
{
tmpSR_CustTable.AccountNum = _custTable.AccountNum;
tmpSR_CustTable.Blocked = _custTable.Blocked;
tmpSR_CustTable.PriceGroup = _custTable.PriceGroup;
tmpSR_CustTable.Currency = _custTable.Currency;
tmpSR_CustTable.insert();
____________________________________________
[SRSReportDataSetAttribute("Tmp_SRCustTable")]
public tmpSR_CustTable getTmpSR_CustTable()
{
select * from tmpSR_CustTable;
return tmpSR_CustTable;
________________________________________________
wow…we are done with all the steps mentioned above and I am excited to help you guys understand how to use the RDP class as a datasource for the dataset. For this we need to create a new report in the visual studio .
Follow me friends…[I am assuming that all the visual tools have been installed and you have report model template available to created the reports]
Open visual studio. Click on File >> New project and follow the below process.
Now add a new report to the report model by right clicking and selecting new report as shown below
Rename the report to "SR_CustTableRDPReport" as shown below by going to the properties of the report
Now the next thing what we have to do [the most awaited...] is to create a dataset for this report.
Right click on the DataSet and add new dataset as shown below. Rename it to CustomerDataSet as shown below
Go to its properties and rename it to "CustomerDataSet"
Now the real trick, we have a datasource type property on the dataset properties. Select report data provider as the datasource type as show below.
Select the query property and Click on the ellipsis (…) button to selct the RDP class which you have created in the step 4 as shown below
This will come up with all the RDP classes available in AX. Select SR_CustTableRDP Class and click on next button to select the fields from the tmpTable to shown it on the report.
Follow the below screen shot to select the fields and click on Ok button
wonderful..we are done with dataset..Only few steps now..Now we need to create design. Drag and drop this CustomerDataSet to Designs node. It will automatically create the Autodesign1 as shown below
Few more properties for good look and feel of reports : Right click on the Autodesign1 and set the
LayOutTemplate as ReportLayoutStyleTemplate as shown below
Then set the style template as TableStyleTemplate as shown below
Also, since I dont have data in default DAT Company, I would like to use company parameter as well. so I am unhiding the company parameter to select the company parameter along with the Account number [step 3 parameter]
To do this, follow me and change the hidden property to visible as shown below for company parameter

Thats it..Now let us run this report.
Right click on the Autodesign1 and select option Preview and select the parameters as shown below
Select the report Tab to view the report. It will take some time to render data.
Here comes the final data on the report – from the RDP class by inserting in to temporary table and showing the same on to report.

Animation in DAX 2012


1) Create a Form in AOT.Go to design done and add a new control called Animate.
2) Now override the run() method on the form.

               public void run()
               {
                           int cnt;
                           #AviFiles
                          ;
                          super();
                         for(cnt=1;cnt<=500;cnt++)
                        {
                            Animate.animateFile(#AviPrint);
                           Animate.play();
                        }
                        Animate.visible(true);
              }
3) Save the Form and run the Form.Enjoy the Animation.

Interesting Jobs



static void SGX_timeConsumed(Args _args)
{
    FromTime startTime = timeNow();
    int i;
    str dummyStr;
    ;
   
    for (i = 1 ; i <= 500000; i++)
    {
        dummyStr += int2str(i);     
    }
       
    info(strFmt("Total time consumed is  %1", timeConsumed(startTime, timeNow())));
}




static void mandatoryFieldsOfATable(Args _args)

{
SysDictTable sysDictTable;
SysDictField sysDictField;
TableId tableId;
Counter counter;
;
sysDictTable = new SysDictTable(tablenum(CustTable));

for(counter = 1;counter <= sysDictTable.fieldCnt(); counter++)
{
sysDictField = new sysDictField(sysDictTable.id(), sysDictTable.fieldCnt2Id(counter));

if(sysDictField.mandatory())
info(sysDictField.name());
}
}




static void ShowFileNameinDirectory(Args _args)
  {

      int           handle;
      FilePath      filePath;
      FileName      FileName;
      ;

      filepath = "E:\\";

      [handle,filename] =
       WinAPI::findFirstFile(filepath + "\\*.doc");

      while (filename)
      {
          info(filepath + "\\" + filename);
          filename = WinAPI::findNextFile(handle);
      }

      WinAPI::findClose(handle);
  }




static void Date(Args _args)
{
 
   date d;

   d= dateMthFwd(systemdateget(), 120); //Adding 2 years to the System Date
 
   info(d);

    info(date2str(today(),123,2,2,2,2,4));

    info(date2str(systemDateGet(),123,2,2,2,2,4));

    info(date2str(DateTimeUtil::date(DateTimeUtil::getSystemDateTime()),123,2,2,2,2,4));

}


static void TableData_without_TableBuffer(Args _args)
{

    print (select MyTabble7).FirstName;
    print (select MyTabble7).LaStNAme;
    pause;
}



static void Job_To_GetTableIds(Args _args)
{
    Dictionary d1;
    TableId t1;
    ;
    d1=new Dictionary();
    t1=d1.tableNext(0);

    while(t1)
    {
     info(int2str(t1));
        t1= d1.tableNext(t1);
    }
}



static void JobToCountTables(Args _args)
{
    Dictionary dict;
    int totalTables;
    dict=new Dictionary();
    totalTables=dict.tableCnt();
    info(strfmt("%1",totalTables));
}