CODEWITHSUNDEEP
axaptamicrosoft-dynamicsx++dynamics-ax-2009
Luntri
Luntri

Reputation: 620

How to bind select query as datasource in report?

I have a select statement that I want to bind as data source in a report.
I have not found a way to design an appropriate AOT query.

This is how it looks like in X++

public void insertData(date data = today())
{
    BHNEmployeesOnDay       ins;
    EmplTable               tbl;
    CompanyInfo             info;
    BHNEmplAgreements       Agreemnt;
    BHNEmplAgreements       Agreemnt2;
    BHNEMPLHISTORYCOMPANY   history;
    BHNEMPLHISTORYCOMPANY   history_test;
    BHNDIVISIONTABLE        division;
    BHNPOSITIONTABLE        position;

    SysCompanyUserInfo      sys1;
    SysUserInfo             sys2;
    UserInfo                usrInfo;
    Date                    infinity = mkdate(1,1,1900);
    ; 

    delete_from ins;
    while select * from tbl
        join Info   where info.dataAreaId == tbl.dataAreaId && info.BLX_companyForDW == 1
        join sys1   where sys1.EmplId==tbl.EmplId && sys1.dataAreaId == tbl.dataAreaId
        join sys2   where sys1.UserId==sys2.Id
        join usrInfo where usrInfo.id==sys1.UserId
        exists join history_test
                        where history_test.EmplId==tbl.EmplId && history_test.dataAreaId==tbl.dataAreaId
        join Agreemnt   where Agreemnt.HistoryId==history_test.HistoryId
                        && (agreemnt.DateTo >= data || agreemnt.DateTo==infinity)

    {
        select firstonly *
        from history  order by history.DateFrom desc, Agreemnt2.DateFrom desc
                        where history.EmplId==tbl.EmplId                && history.dataAreaId==tbl.dataAreaId
        join Agreemnt2   where Agreemnt2.HistoryId==history.HistoryId
                        &&  Agreemnt2.DateFrom<=data && (Agreemnt2.DateTo >= data || Agreemnt2.DateTo==infinity)
        join division   where division.DivisionId==agreemnt.DivisionId
        join position   where position.PositionId==agreemnt.PositionId;


        ins.adddRecord(tbl.EmplId, tbl.Name_BHN, tbl.BirthDate, division.Name, position.FullName);

    }
}

Currently I generate data into a table [during run() method of the report], then simply select from that table. So far only 1 person uses this report so it's not a problem, but if two people run the same report simultaneously, I'm gonna get dirty reads.

I know it's bad approach, but I'm out of ideas. I thought of making a View on T-SQL side and try to select from it - but I was told that it might not be detected or simply not transferred to other instances of our AX during export, so it has to be done on AX side.

How can I solve this?

Just in case this is query in T-SQL SQL query on pastebin

Upvotes: 0

Views: 938

Answers (1)

DAXaholic
DAXaholic

Reputation: 35408

You could overwrite the report's fetch method and just use your X++ code as is to get the records and then use the report's send method to process them.

See here for an example.
The example uses a query object but you could easily swap that with your own X++ code - you just eventually have to call send for the records you want to be processed by the report.

Update:

For example you could just fetch any record of SalesTable and call send.
In this example a member variable salesTable is assumed so that you can access the current record in a display method in case you need it. 

public boolean fetch()
{
    boolean ret;
    //ret = super();
    ;

    select firstOnly salesTable;

    this.send(salesTable);

    return true;
}

Upvotes: 3

Related Questions