Monday, 24 December 2012

Retrieve Signature image of workflow approval user using SQL query in SSRS Report AX




SELECT     TOP (1) COMPANYIMAGE.REFRECID, COMPANYIMAGE.IMAGE, COMPANYIMAGE.REFRECID AS Expr1, EMPLTABLE.EMPLID, SYSCOMPANYUSERINFO.USERID,
                      DIRPARTYTABLE.NAME, EMPLTABLE.TITLE
FROM         COMPANYIMAGE INNER JOIN
                      EMPLTABLE ON COMPANYIMAGE.REFRECID = EMPLTABLE.RECID INNER JOIN
                      SYSCOMPANYUSERINFO ON EMPLTABLE.EMPLID = SYSCOMPANYUSERINFO.EMPLID INNER JOIN
                      WORKFLOWTRACKINGTABLE ON SYSCOMPANYUSERINFO.USERID = WORKFLOWTRACKINGTABLE.USER_ INNER JOIN
                      DIRPARTYTABLE ON EMPLTABLE.PARTYID = DIRPARTYTABLE.PARTYID INNER JOIN
                      PURCHTABLE ON WORKFLOWTRACKINGTABLE.CONTEXTRECID = PURCHTABLE.RECID
WHERE     (COMPANYIMAGE.DATAAREAID = ‘Ceu’) AND (PURCHTABLE.PURCHID = ‘12345’) AND (WORKFLOWTRACKINGTABLE.CONTEXTTABLEID = 345) AND
                      (WORKFLOWTRACKINGTABLE.TRACKINGCONTEXT = 5) AND (WORKFLOWTRACKINGTABLE.TRACKINGTYPE = 4)
ORDER BY WORKFLOWTRACKINGTABLE.DUEDATETIME DESC

Assigned to me filtration of workflow on Purchase Oder Form




public void executeQuery()
{


 QueryBuildRange         rangeCompanyId;
    QueryBuildRange         rangeUserId;
    QueryBuildRange         rangeId;
    QueryBuildRange         rangeStatus;
 Query query;
 QueryBuildDatasource    datasource;
 WorkflowWorkItemTable   workflowWorkItemTable;
 QueryBuildDataSource    qbdsWorkFlowItemTable;
;


 if(Filter.valueStr() == "Assigned To Me" )
                    {

query = new Query();
                 
                    datasource = query.addDataSource(tableNum(PurchTable));

                    // Add child datasource "WorkflowWorkItemTable" to previously created DS
                    qbdsWorkFlowItemTable = datasource.addDataSource(tableNum(WorkflowWorkItemTable));

                    // Set the join mode
                    qbdsWorkFlowItemTable.joinMode(JoinMode::ExistsJoin);

                 
                    qbdsWorkFlowItemTable.relations(true);

                 
                    qbdsWorkFlowItemTable.addLink(fieldNum(PurchTable, RecId),
                    fieldNum(WorkflowWorkItemTable, RefRecId));

                    qbdsWorkFlowItemTable.addLink(fieldNum(PurchTable, TableId),
                    fieldNum(WorkflowWorkItemTable, RefTableId));
                    rangeStatus = qbdsWorkFlowItemTable.addRange(fieldnum(WorkflowWorkItemTable, Status));
                    rangeStatus.value(queryValue(WorkflowWorkItemStatus::Pending));
                    rangeStatus.status(RangeStatus::Locked);
                    rangeCompanyId = qbdsWorkFlowItemTable.addRange(fieldnum(WorkflowWorkItemTable, CompanyId));
                    rangeCompanyId.value(queryValue(curext()));
                    rangeCompanyId.status(RangeStatus::Locked);

                    rangeUserId = qbdsWorkFlowItemTable.addRange(fieldnum(WorkflowWorkItemTable, UserId));
                    rangeUserId.value(queryValue(curuserid()));
                    rangeUserId.status(RangeStatus::Locked);

                    //The Id-range is only used to lock the query from the Ui
                    rangeId = datasource.addRange(fieldnum(WorkflowWorkItemTable, Id));
                    rangeId.status(RangeStatus::Locked);
                    rangeId.enabled(false);
                    // info(query.dataSourceNo(1).toString());
                    this.query(query);
}
}