Category Archives: SSRS

SRSReportDataProviderPreProcessTempDB Class in Ax 2012

Hi All,

I have something to share on SRSReportDataProviderPreProcessTempDB which is robust feature for the long running report in terms of execution which I copied from different sources.

Preprocessed data providers first prepare all data and only then they call SSRS. Normally the report itself asked for data when needed and if it takes too long, a timeout will kill the execution. Preprocessing data is there to avoid this problem.

Use TempDB with SrsReportDataProviderPreProcessTempDB. Regular tables were used in a previous solution and it caused some performance issues.

Report Data Provider.

An RDP class is a X++ class that is used to access and process data for a Reporting Services report. The options for a data source type for a Microsoft Dynamics AX report are the query, business logic, and RDP. An RDP class is an appropriate data source type when the following conditions are met.

  1. You cannot query directly for the data you want to render a report.
  2. The data to be processed and displayed is from Microsoft Dynamics AX.

Preprocessing reports

SSRS uses the WCF (Windows Communication Foundation) to connect to the AOS for data access. This connection has a threshold limit and it might fail if a report takes a long time to execute. The report server execution waits for the RDP to process the data and return it. In the event where the RDP takes too long a time to execute the reporting, the service might fail. Preprocessing is a strategy to beat this issue. This recipe will help you understand how to enable preprocessing for any RDP report

How to do it

When a report processes a huge amount of data or is found to take considerable time during the execution, then you can decide to incorporate preprocessing. Follow these steps to enable preprocessing.

  1. Modify RDP base class:  SRSReportDataProviderBase -> SRSReportDataProviderPreProcessTempDB
  2. Update table type:  InMemory -> TempDB
  3. Perform a full compile of IL
  4. Restart the AOS
  5. Introduce Controller class to run the report
  6. Update Output Menu Item:  Report -> Controller class

These are pre-processing AX-SSRS reports. in these report user connection information is taken using method parmUserConnection()Yes, this method sets the User Connection object on the temp table(s) that are used in the RDP class to return data. As per documentation , for pre-processing RDP reports, you must set the User Connection before the tables can be used. It is a best practice to set it in the beginning of processReport() method.

Thanks for reading…

Till then Happy DaXing…

Common SSRS Expressions

Hi Axaptians, This is my first post in this month because of hectic schedule. Any how, I got some time to showcase some of my stuff related to SSRS reports. Here you go…..

2016-04-26_1759

2016-04-26_1801

2016-04-26_1802

2016-04-26_1803

2016-04-26_1805

2016-04-26_1807.png

2016-04-26_1807_001

These would be very helpful going forward.

Till then, Happy DaXing…

Expression to hide/unhide column and Text box in SSRS

Hi Axaptians,

This is second post on 5th Feb and eager to update regarding expressions used in SSRS reports to hide/unhide column or text boxes. In AX 5.0, reports are configured in Morphx but in AX 6.0, reports are being design in VS which is termed as SSRS reports. So, let’s begin this.

I would give an example where I would to like to hide a column or text box by using expression.

=iif(condition,True,false)

Above express will hide column depending on action applied. Suppose, if it is checked, will hide and if not, it will unhide. Following is the example below.

=iif(Parameters!BOMConsistOfDS_Item_Notes.Value = false,True,false)
or
=iif(Parameters!BOMConsistOfDS_Item_Notes.Value = false,1,0)

As simple as that, there are lot of things to be updated regarding expressions.

Please keep in touch for more updates.

Thanks & Happy DaXing…

Get Current Company in AX 2009

static void curExtExample(Args _arg)
{
    str curCompany;
    ;
 
    // Sets curCompany to the extension of the current company.
    curCompany= curExt();
    print “Current extension is ” + curCompany;
    pause;
}

Todays date in ax 2009 report

To Get the todays date in the Ax 2009 report write the following code in display method of the report

display public str ABC_TodaysDate()
{
    return date2Str(today(), 123, DateDay::Digits2, DateSeparator::Slash, DateMonth::Digits2, DateSeparator::Slash, DateYear::Digits4);
}

passing parameters between forms in AX

In dynamics AX, working with forms, there are times when you need to pass some information from current form to the opened form, so the question arrived is that what’s the best way to open the new form and pass information.

Answer: It depends upon the information that is needed in the new form; there is Args class that plays an important role to pass the information. Let’s take a look on some of the important methods of that class

Args class (Argument)

“The Args class is used to pass arguments such as a name, a caller, and parameters between application objects”

Some important methods are

Caller
Gets or sets the instance of the object that created this instance of the Args class.
name
Gets and sets the name of the application object to call.
parm
Gets or sets a string that specifies miscellaneous information for the called object.
parmEnum
Gets or sets the enumeration value of the enumeration type that is specified in the parmEnumType method.
parmEnmType
Gets or sets the ID value of any enumeration type.
ParmObject
Gets or sets an instance of any object to pass to the called object.
record
Gets and sets the record from the table on which the caller object is working.
There are four methods that can be used to pass extra information to the new class:
  1. The parm method – to pass strings
  2. The parmEnum and parmEnumType method – to pass enumeration values
  3. The parmObject method – to pass an object of any type.

Examples:

1.      If you need a data from the parent form main data source for the current record, so you don’t need to do anything in parent, just create a display menu item and give the form name that needs to be opened, create a menuItem button and assign the newly created menu item.
Override the Init method on opened form
And you get the parent dataset records as
element.args().record()
2.      Need to pass any object/string/Enum

Use the same approach for creating the button

Parent form
void clicked()
{
Args args;
FormRun formRun;super();args = new Args(formstr(FormName));

 // To pass any string value
args.parm(<string value>);
 // To pass any object
args.parmObject(<object>);
 // To pass any Enum
       args.parmEnum( EnumValue);
args.parmEnumType( EnumNum( <EnumName>) );
 formRun = classFactory.FormRunClass(args);
formRun.init();
formRun.run();
formRun.wait();
formRun.detach();
parenttable_ds.refresh(); // Refreshing parent table DataSourceTable
parenttable_ds.executeQuery(); // Refreshing Parent DataSourceTable
}
Child Form
void init()
{
args = element.args();
    // get string parameter
<string> = args.parm();
    // get object parameter
    <object> = args.parmObject();
    // get enum parameter
if( element.args().parmEnumType() == EnumNum( <EnumName>) )
{
<enum contol/variable>  =( element.args().parmEnum() );
}
}3.      There are many parameters that you need to pass to the child form.
               In that scenario, you need to create an extra class (parameter/contract class), you can first set the parameters in the init method for that class, use the parmObject for setting and gets the object on the child form
=======================================================================================

Argument Passing between Forms in Dynamics Ax 2009

Here a sample code to pass argument to one form to another form and using of Args() class.
Steps:
1) Create two Forms named FormA and FormB
2)Use the EmplTable as the Datasource of both forms
3)Design FormA with one Grid and add 4 data fields to the Grid(EmplId,DEL_Name,Grade,EmplStatus…..)
4)Assign the datasource for the grid and the data fields
5)Add a Button in FormA
6)Override the Clicked() method and write the below code:
void Clicked()
{
Args    _args;
FormRun _formRun;
EmplId   _empId;
;
_empId = EmplTable.EmplId;  // Selected employee id in the Grid is assigned to the variable which is pass to the next form
_args = new Args(); // creating a object for args class
_args.name(formstr(VA_FormB));  // Form Menuitem
_args.caller(this);  // Form Caller(Current Form is mentioned as this)
_args.parm(_empId); // Employee Number is passed to next form[but parm() is not a best practise]
_args.record(EmplTable); // Table name is passed
_formRun = ClassFactory.formRunClass(_args); //new FormRun(_args);   // Creating object for FormRun
_formRun.init();   // Form Initialization for Load
_formRun.run();  // Form Run for process
_formRun.wait(); // Form Wait for Display
}
7) Open the Second Form – FormB
8) Add one Grid Control and set the Data Source is EmplTable
9) Add 4 data fields as same in the Form A
10)Now Override the Init() of Form
public void init()
{
parmid      _parmId;
EmplTable   _EmplTable;
//     DictTable   _dictTable;    FormBuildDataSource   _ds;    FormBuildGridControl frmGrid; // These are for dynamicForm creation so leave it
_parmId =  element.args().parm(); // Getting the argument value from the Caller
//info(int2str(element.args().record().TableId));
if(!element.args().caller())   // Check the form is called by caller or directly, if directly it throw error
throw error(“Cant Run Directly”);
if(element.args().record().TableId == tablenum(EmplTable))   // check if the sent Table and the Current form table are equal or not
{
//        _EmplTable = element.args().record();  // Assign the Received Table name to Local Variable
//_dictTable = new DictTable(element.args().record().TableId);  // leave it , is used for Dynamic Form Creation
//_ds = form.addDataSource(_dictTable.name());  // leave it , is used for Dynamic Form Creation
//_ds.table(_dictTable.id());   // leave it , is used for Dynamic Form Creation
//frmGrid = form.addControl(FormControlType::Grid, “Grid”);   // leave it , is used for Dynamic Form Creation
//frmGrid.dataSource(_ds.name());   // leave it , is used for Dynamic Form Creation
//info(strfmt(“%1     %2″,_EmplTable.EmplId,_EmplTable.DEL_Name));
//frmGrid.addDataField(_ds.id(),fieldnum(EmplTable, DEL_Name));  // leave it , is used for Dynamic Form Creation
//        EmplTable_EmplId.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation
//        EmplTable_EmplId.dataField(fieldnum(EmplTable,EmplID));   // leave it , is used for Dynamic Form Creation
//        EmplTable_DEL_Name.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation
//        EmplTable_DEL_Name.dataField(fieldnum(EmplTable,EmplId));  // leave it , is used for Dynamic Form Creation
//        EmplTable_DEL_Email.dataSource(_EmplTable);   // leave it , is used for Dynamic Form Creation
//        EmplTable_DEL_Email.dataField(fieldnum(EmplTable,EmplId));   // leave it , is used for Dynamic Form Creation
super();  // Form Initialization
}
else
{
info(“DataSet Not Received”);  // throw error
}
}
11)Override the Init() of the DataSource
public void init()
{
switch(element.args().dataset())// get the table id sent by caller
{
case tablenum(EmplTable):  // check the table if matches with this tableid
{
_EmplID  =   element.args().parm();  // get the argument value
query   = new Query();            queryBuildRangeProj =                                          query.addDataSource(tablenum(EmplTable)).addRange(fieldnum(EmplTable,EmplId));          // query build for the form to display
queryBuildRangeProj.value(_emplId); // Criteria for the form
EmplTable_ds.query(query); // execution of the query
break;
}
}
super(); //datasource  initialization on the form based on the criteria
}
12) Save it, and create two menu items for each.
13) It is important to change the runon property of the FormB as CalledFrom.
14)Run the FormA and select an Employee Record and click the button.
15)The FormB opens with the Related information of the Selected Employee on form.
===========================================================================

Passing values between forms

For passing parameters from one form to another a special class Args is usually used.

Example:

The code of button click event of FormA which calls FormB and passes some parameters to that form.

void clicked()
{
    // Args class is usually used in Axapta for passing parameters between forms
    Args            args;
    FormRun         formRun;
    // Our custom made class for passing complex set of parameters
    FormBParams     formBParams = new FormBParams();
    Array           items = new Array( Types::String );
    int         i;
    ;

    args = new args();

    // Our values which we want to pass to FormB
    // If we want pass just simple string we can use 'parm' method of 'Args' class
    args.parm( strValue.text() );
    // We also can pass enum value to FormB
    args.parmEnum( NoYesEnumValue.selection() );
    args.parmEnumType( EnumNum( NoYes ) );
    // and also can pass a cursor pointing to some record (in our case it is EmplTable )
    args.record( EmplTable );

    // If we want pass more complex set of parameters we can develop our own class
    // just for passing our parameters.
    formBParams.parmSomeDate( someDate.dateValue() );
    formBParams.parmSomeTime( someTime.value() );
    for( i=0; i<ListBox.items(); i++ )
    {
        items.value( i+1,  ListBox.getText( i ) );
    }
    formBParams.parmItems( items );
    // Pass our object to FormB
    args.parmObject( formBParams );

    // Run FormB
    args.name( formstr( FormB ) );
    formRun = classFactory.formRunClass( Args );
    formRun.init();
    formrun.run();
    formrun.wait();

    if( formrun.closedOk() )
    {
        answerFromFormB.text( args.parm() );
    }
    super();
}

The code of init method of FormB

public void init()
{
    EmplTable       emplTableRecord;
    FormBParams     formBParams;
    Array           items;
    int             i;
    ;
    super();

    // Check for passed arguments
    if( element.args() )
    {
        // get string parameter
        strValue.text( element.args().parm() );

        // get enum parameter
        if( element.args().parmEnumType() == EnumNum( NoYes ) )
        {
            NoYesEnumValue.selection( element.args().parmEnum() );
        }
        // get object parameter
        if( element.args().parmObject() )
        {
            formBParams = element.args().parmObject();
            items       = formBParams.parmItems();
            for( i=1; i<=items.lastIndex(); i++ )
            {
                ListBox.add( items.value(i) );
            }
            someDate.dateValue( formBParams.parmSomeDate() );
            someTime.value( formBParams.parmSomeTime() );
        }
        // get record parameter
        if( element.args().record() && element.args().record().TableId == TableNum( EmplTable ) )
        {
            emplTableRecord =  element.args().record();
            emplName.text( emplTableRecord.Name );
        }
    }
}

The code of ok button click event of FromB

void clicked()
{
    super();
    element.args().parm( strAnswer.text() );
    element.closeOk();
}

The above code is cut out from a demo which you can download here.

===========================================================================

For passing parameters from one form to another a special class Args is usually used.

Example:

The code of button click event of FormA which calls FormB and passes some parameters to that form.

void clicked()
{
    // Args class is usually used in Axapta for passing parameters between forms
    Args            args;
    FormRun         formRun;
    // Our custom made class for passing complex set of parameters
    FormBParams     formBParams = new FormBParams();
    Array           items = new Array( Types::String );
    int         i;
    ;

    args = new args();

    // Our values which we want to pass to FormB
    // If we want pass just simple string we can use 'parm' method of 'Args' class
    args.parm( strValue.text() );
    // We also can pass enum value to FormB
    args.parmEnum( NoYesEnumValue.selection() );
    args.parmEnumType( EnumNum( NoYes ) );
    // and also can pass a cursor pointing to some record (in our case it is EmplTable )
    args.record( EmplTable );

    // If we want pass more complex set of parameters we can develop our own class
    // just for passing our parameters.
    formBParams.parmSomeDate( someDate.dateValue() );
    formBParams.parmSomeTime( someTime.value() );
    for( i=0; i<ListBox.items(); i++ )
    {
        items.value( i+1,  ListBox.getText( i ) );
    }
    formBParams.parmItems( items );
    // Pass our object to FormB
    args.parmObject( formBParams );

    // Run FormB
    args.name( formstr( FormB ) );
    formRun = classFactory.formRunClass( Args );
    formRun.init();
    formrun.run();
    formrun.wait();

    if( formrun.closedOk() )
    {
        answerFromFormB.text( args.parm() );
    }
    super();
}

The code of init method of FormB

public void init()
{
    EmplTable       emplTableRecord;
    FormBParams     formBParams;
    Array           items;
    int             i;
    ;
    super();

    // Check for passed arguments
    if( element.args() )
    {
        // get string parameter
        strValue.text( element.args().parm() );

        // get enum parameter
        if( element.args().parmEnumType() == EnumNum( NoYes ) )
        {
            NoYesEnumValue.selection( element.args().parmEnum() );
        }
        // get object parameter
        if( element.args().parmObject() )
        {
            formBParams = element.args().parmObject();
            items       = formBParams.parmItems();
            for( i=1; i<=items.lastIndex(); i++ )
            {
                ListBox.add( items.value(i) );
            }
            someDate.dateValue( formBParams.parmSomeDate() );
            someTime.value( formBParams.parmSomeTime() );
        }
        // get record parameter
        if( element.args().record() && element.args().record().TableId == TableNum( EmplTable ) )
        {
            emplTableRecord =  element.args().record();
            emplName.text( emplTableRecord.Name );
        }
    }
}

The code of ok button click event of FromB

void clicked()
{
    super();
    element.args().parm( strAnswer.text() );
    element.closeOk();
}

 

SSRS Report Configuration

1. System Admin–> setup–>Business Intelligence–>Reporting Service–>Reporting Server

The Following Images Shows :

 

ssrsconfg

1.1) Configuration Id = MSSQLSERVER

1.2) ReportServer

servername = MTSD-01 ( system name)

server instance name = MSSQLSERVER.

1.3)  Reporting Server URL:

1.1) Report Manager URL :http://systemname/Reports

1.2) Web service URL  : http://systemname/ReportServer

1.4) Click on Validate Setting

1.5)  if it is success

 

2.  start–>adminstratot tools–>power shell

type this:

Publish-  AXReport –Id  <SSRSCONFIG-ID>  -ReportName    *, Prescription, SOReport..

 

Simple UI Builder Class

This UI Builder class is simple class with less code and less effort. UI Builder Class is needed when you want to customize your dialog which pop ups when you open a Report. UI Builder Class helps you to add run time lookups and other controls on the dialog form.

Step1 : Your Class must extends SrsReportDataContractUIBuilder

class SimpleDemoUIBuilder extends SrsReportDataContractUIBuilder
{
DialogField dialogEmplId;
DialogField dialogName;
boolean enable;
SimpleDemoContract contract;

}// two fields emplId and Name will reflect in the lookup in dialog form at the time of report opening.

Step2 : Override the build method

public void build()
{
contract = this.dataContractObject();
dialogEmplId = this.addDialogField(methodStr(SimpleDemoContract, parmEmplId),contract);

}// this method used for adding the field  which is from contract class.

Step3 : Write this below code to get lookup

private void emplIdLookup(FormStringControl emplIdlookup)
{
Query query = new Query();
QueryBuildDataSource qbds_EmplTable;
SysTableLookup sysTableLookup;

// Create an instance of SysTableLookup with the current calling form control.
sysTableLookup = SysTableLookup::newParameters(tableNum(FilterDemo), emplIdlookup);
// Add fields to be shown in the lookup form.
sysTableLookup.addLookupfield(fieldNum(FilterDemo,EmplId));
sysTableLookup.addLookupfield(fieldNum(FilterDemo,Name));
qbds_EmplTable = query.addDataSource(tableNum(FilterDemo));
sysTableLookup.parmQuery(query);
// Perform the lookup
sysTableLookup.performFormLookup();
}

Step4 : Override this method

public void getFromDialog()
{
contract = this.dataContractObject();
super();
}

Step5 : Override this method

public void initializeFields()
{
contract = this.dataContractObject();
}

Step6 : Override this method

public void postBuild()
{
super();
dialogEmplId = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(SimpleDemoContract,parmEmplId));dialogEmplId.registerOverrideMethod(methodStr(FormStringControl, lookup),
methodStr(SimpleDemoUIBuilder,emplIdLookup), this);dialogEmplId.lookupButton(2);
}

the Contract Class code and DP class have already been added to the blog. please check it for further reference but one important part has to be added to the contract class i.e in class declaration, the following part has to be updated.

[DataContractAttribute,SysOperationContractProcessingAttribute(classstr(SimpleDemoUIBuilder))]

Print Date Time on Report

display str 70 datePage()
{;

return strfmt(element.design().lookupLabel(literalstr(“@SYS10092”)),
element.startDate(),
time2str(element.startTime(),-1,-1),
element.page());
}

 

Simple RDP Class with Contract Class in AX 2012

Firstly create contract class and specify the field which you want to provide as    parameter in the report.

Use this Contract class in RDP class as parameter.

[DataContractAttribute]
public class FilterDemoContract
{

EmplId emplId;

}

[DataMemberAttribute(“EmplId”)]
public EmplId parmEmplId(EmplId _emplId = emplId)
{

emplId = _emplId;
return emplId;

}

Now Declare Table buffers in the Class declaration of RDP Class including Temp Table Buffer.

[SRSReportParameterAttribute(classstr(FilterDemoContract))]
class ContractDemoClass extends SRSReportDataProviderBase
{

TempFilterDemo tempFilterDemo;
FilterDemo filterDemo;

}

[SRSReportDataSetAttribute(“TempFilterDemo”)]
public TempFilterDemo getTempFIlterDemo()
{

select * from tempFilterDemo;
return tempFilterDemo;

}
public void insertTmpTable()
{

tempFilterDemo.EmplId = filterDemo.EmplId;
tempFilterDemo.Name = filterDemo.Name;
tempFilterDemo.Salary = filterDemo.Salary;
tempFilterDemo.Gender = filterDemo.Gender;
tempFilterDemo.insert();

}

SysEntryPointAttribute(false)]
public void ProcessReport()
{

EmplId emplId;
FilterDemoContract contract;

;

contract = this.parmDataContract() as FilterDemoContract;
emplId = contract.parmEmplId();
select * from filterDemo where filterDemo.EmplId == emplId;
this.insertTmpTable();

}

Now you left with SSRS part, in the report, one Dialog Box will popped up, there, you would enter the emplid value and report will show you that particular record.