How to launch a SSRS report (with some parameters) from AX 2009

Hi folks!

I will show you how to create a CommandButton in a form able to launh a SSRS report in Report Viewer from AX 2009 with parameter passing.

Picture yourself that we have a SSRS report with a parameter called "test", and this parameter must be taken from the datasource table of the form.

All you have to do is create a CommandButton control in your form and override the "clicked" method with this code:

void clicked()
{
    //declare the parameter
    str          _test;
    ;

    //assign to the parameter the value of the datasource field
    _test      = YourTableName.YourFieldName;
    infoLog.urlLookup(
    strfmt("http://<yourServername>/<yourReportWebServiceName>/Pages/ReportViewer.aspx?/<yourReportName>&rs:Command=Render&<Test>=%1",_test));

    super();
}

This command will open your browser and automatically launch the report with the specified parameter.

Sometimes I noticed visualization problems with browser different from Internet Explorer.
If Internet Explorer isn't the default browser, just change the command "infolog.urlLookup()" with:

WinAPI::shellExecute("iexplore.exe",strfmt("http://<yourServername>/<yourReportWebServiceName>/Pages/ReportViewer.aspx?/<yourReportName>&rs:Command=Render&<Test>=%1",_test),"","",1);

That's all!

How to send a report via email from AX 2009

Hi all!

Today I want to show you how to send a custom report via email from AX (it works with an AX client installation and it's tested for Microsoft Outlook and Novell GroupWise).

In AX there is a class that allows you to do this: "SysINetMail".

Here's the code:

void printPDFMail()
{
    SysINetMail     SysINetMail;
    str             FileName;
    str             user;
    str             Body;
    str             Subject;
    str             cc1;
    str             mailAddressFrom;
    str             mailAddressTo;
    UserInfo        userInfo;
    SysINetMail mail = new SysINetMail();
    ;

    select firstonly userInfo where userInfo.id==curUserId(); {
    user= userInfo.networkAlias; }

    FileName = strfmt('C:\\Users\\%1\\Desktop\\AXReportName.pdf',user);

    reportRun.printJobSettings().setTarget(PrintMedium::File);
    reportRun.printJobSettings().format(PrintFormat::PDF);
    reportRun.printJobSettings().fileName(FileName);
    reportRun.run();

    //assign the values ​​of sender, recipient, subject and message body
    Subject     = 'Insert here the email subject';
    Body        = 'Insert here the email body';
    cc1         = 'example@example.com';

    //assign the values ​​to send email
    mailAddressFrom     = SysUserInfo::find(curUserId()).Email;
    mailAddressTo       = email@addresstosend.com
   
    //Send Mail
    mail.sendMailAttach(mailAddressTo,cc1,Subject,Body,false,FileName);
   
    //delete the temporary pdf file
    winAPI::deleteFile(FileName);


    //info("Email sent.");
}

I have included this method in a class that creates the report to be sent, but if you want to use it as a job, you have to use "Args" function to pass the name of the report to be printed.

Calculation of the item average availability

Hi all!

This time I will talk to you about the way to calculate average availability over time in AX 2009.

The calculation of the average availability requires the availability of every item code on a specific date (usually at the end of month), but this information seems not available directly in AX 2009.

I found a way to retrieve this information: I create a batch class that runs every day and check if the current day is the last day of the month.
For doing this check I have used the "endMth" function, that works also with leap years

Here's the code:

void Run()
{
    Table1                         table1;
    TransDate                   t = today();
    TransDate                   lastdayofMth;
    ;

    lastdayofMth = endmth(t);
    if (t==lastdayofMth)
    {
       //Insert your code here for populate a custom table with item availability
    }
}

This is part of a custom project I developed for get the Inventory Turnover Index in AX 2009.
(if anyone is interested please do not hesitate to contact me).

See you next time!...

Passing values between two forms

Hi all!

Today i will talk to you about passing values between forms in AX...
We have to open a form with data filtered by specific parameters coming from another form.

In this specific case I have to pass ItemId and InventDimId from InventDimCombination (formA) to a custom-made form (formB).

First I created a MenuItemButton on formA which calls FormB and I override the clicked method with this code:

void clicked()
{
    Args            _args;
    FormRun         _formRun;
    str             _filterValue;
    ;

    //Assign at _filterValue a string that contains ItemId and InventDimId in a unique field which is
    //pass to the next form
    _filterValue = (InventDimCombination.ItemId + '-' + InventDimCombination.InventDimId); 
    _args   = new Args();
    // _filterValue is passed to next form
    _args.parm(_filterValue);
    _args.name(formstr(formB));
    _args.caller(this);

    // Creating object for FormRun and initialization for load
    _formRun = ClassFactory.formRunClass(_args);
    _formRun.init();
    _formRun.run();
    _formRun.wait();
}

Then I override the init method of the formB datasource:

public void init()
{
    QueryBuildRange     rangeItemId;
    QueryBuildRange     rangeConfigId;
    QueryBuildRange     rangeColorId;
    str                 Config;
    str                 Color;
    str                 s,t;
    int                 i;
   
    super();
   
    //check if value is arrived from formA
    if(element.args().parm())
    {
     //retrieve ItemId and InventDimId as two separate field from the passed value
     i      = strlen(element.args().parm());
     s      = substr(element.args().parm(),i-7,8);//inventDimId
     t      = strDel(element.args().parm(),i-8,9);//ItemId
     Config = InventDim::find(s).configId;
     Color  = InventDim::find(s).InventColorId;
   
    //create the query to initialize the form
    rangeItemId   = this.query().dataSourceTable(tablenum(formBtable)).addRange(fieldnum(formBTable, ItemIdfield));
    rangeConfigId = this.query().dataSourceTable(tablenum(formBtable)).addRange(fieldnum(formBtable, ConfigIdfield));
    rangeColorId  = this.query().dataSourceTable(tablenum(formBtable)).addRange(fieldnum(formBtable, InventColorIdfield));
    rangeItemId.value(t);
    rangeConfigId.value(Config);
    rangeColorId.value(Color);
    rangeItemId.status(RangeStatus::Hidden);
    rangeConfigId.status(RangeStatus::Hidden);
    rangeColorId.status(RangeStatus::Hidden);
    }
}

That's all, when you press the button in formA, the formB will open showing only data related to the passed parameters

See you next time!

Use of the "periods-to-date" function in OLAP cube

Hi all!

Today we will see one of the possible way to use the "periods-to-date" function (mdx language) in a OLAP cube...

If in AX 2009 we want to see a summarized requirements plan, perhaps with item details in row and the calendar days in column, we have two possibilities.....

one is to write al lot of code and create a new matrix-like form to be filled with data from ReqTrans table (with all the connected difficulties.....)

the second way is to create an OLAP cube using BIDS (Business Intelligence Development Studio).
I followed this way a few month ago and I have created a cube that shows the variation of the availability for each item in the future...(if anyone is interested in this cube can contact me).

The "heart" of this cube is exactly the "periods-to-date" function...
here's an example:

SUM((PERIODSTODATE([Time].[Data].[Anno],[Time].[Data].CurrentMember)),[Measures].[Quantità fabbisogno])

this function allows to calculate the variation of quantity from the first member of the time dimension to the current member of the time dimension. So we can simply change the value of the time dimension filter and we have the updated value...

In the example we have:

- [Time].[Data].[Anno]: time dimension hierarchy
- [Time].[Data].CurrentMember: identifies the current value of the time dimension filter
- [Measures].[Quantità fabbisogno]: the measure field in the cube

This is all......! see you next time!

How to post multiple picking list journals

Hi all!

For my first post I decided to write an article with the instructions for posting multiple picking list journals.

Well, let's start!

For real it's all pretty simple, the idea is to create a button in ProdJournalTable form and override its clicked method...

here's the code:

void clicked()
{
    DialogButton                            dialogBtn = DialogButton::Yes;
    ProdJournalCheckPostBOM     prodJournalCheckPostBOM;
    ProdJournalId                           journal;

    //here I entered a second level of confirmation before start with posting
    dialogBtn = Box::yesNo("Registrare tutti i giornali di distinta di prelievo?", DialogButton::No);
       
        if(dialogBtn != DialogButton::No)
        {
        prodJournalTable = prodJournaltable_ds.getFirst(true);
        while (prodJournalTable)
        {
            journal                 = prodJournalTable.JournalId;
            ProdJournalCheckPostBOM = ProdJournalCheckPostBOM::newPostJournal(journal,false);
            try
            {
            ProdJournalCheckPostBOM.run();
            }
            catch
            {
            info("Il giornale contiene errori");
            }
            prodJournalTable = ProdJournalTable_ds.getNext();
        }
        }
        info("Fine");
}

this code runs every selected journal and post it if there are no errors (in case of journal with errors, the procedure try to post next journal).
Don't forget to set "Multiselect" button property to "Yes"...

see you next time...!