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);
}
}

Monday, 10 September 2012

EP lookup filterization with two ranges

protected void Item_Lookup(object sender, AxLookupEventArgs e)
    {
        // Boundfield is the sender
        AxBoundField field = (AxBoundField)sender;

        // The underlying control in this case is the lookup control
        AxLookup lookup = e.LookupControl;
        String lookupTable;
        string field_storeAreaId;
        string field_ItemId;
        string field_InventLocationId;
        string field_SiteId;
        string field_ItemName;
        lookupTable = "ItemStoreCombination";
        field_storeAreaId = "storeAreaId";
        field_InventLocationId = "InventLocationId";
        field_ItemId = "ItemId";
        field_SiteId = "SiteId";
        field_ItemName = "ItemName";



        using (Proxy.SysDataSetBuilder sysDataSetBuilder = Proxy.SysDataSetBuilder.constructLookupDataSet(this.AxSession.AxaptaAdapter, TableMetadata.TableNum(this.AxSession, lookupTable)))
        {
            lookup.LookupDataSet = new DataSet(this.AxSession, sysDataSetBuilder.toDataSet());

            // DataSet has to be init'ed before accessing the data sources
            lookup.LookupDataSet.Init();



            // Specify the lookup fields used
            lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields[field_ItemId]));
            lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields[field_ItemName]));
            lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields[field_SiteId]));
            lookup.Fields.Add(AxBoundFieldFactory.Create(this.AxSession, lookup.LookupDataSetViewMetadata.ViewFields[field_InventLocationId]));
            DataSetViewRow ItemTableRow = this.AxDataSource2.GetDataSourceView("InventRequestLine").DataSetView.GetCurrent();
            if (ItemTableRow!= null)
            {
                using (IAxaptaRecordAdapter record = ItemTableRow.GetRecord())
                {
                    if (record != null)
EP Lookup Filterization



                    {
                        // Filter the lookup
                        using (Proxy.Query query = lookup.LookupDataSet.DataSetViews[0].MasterDataSource.query())
                        {
                            using (Proxy.QueryBuildDataSource dataSource = query.dataSourceNo(1))
                            {
                                using (Proxy.QueryBuildRange range = dataSource.addRange(
                                    TableDataFieldMetadata.FieldNum(AxSession, "ItemStoreCombination", "InventLocationId")))
                                using (Proxy.QueryBuildRange range2 = dataSource.addRange(
                                    TableDataFieldMetadata.FieldNum(AxSession, "ItemStoreCombination", "storeAreaId")))
                                {
                                    range.status = (int)Proxy.RangeStatus.Hidden;
                                    range.value = (string)record.GetField("InventLocationId");
                                    range2.status = (int)Proxy.RangeStatus.Hidden;
                                    range2.value = (string)record.GetField("storeAreaId");
                                }
                            }
                          

                        }
                    }
                }

            }


        }
        // Specify the select field
        lookup.SelectField = field_ItemId;

    }

Tuesday, 28 August 2012

How to add image dynamically from Ax2009 database to SSRS report


i am using following method for employee signature printing in AX2009.

  1. In report design view, create a required table with a data source connection and a dataset with a field that contains binary image data. For more information, see .
  2. Insert a column in your table.
  3. On the Insert menu, click Image, and then click in the data row of the new column.
  4. On the General page of the Image Properties dialog box, type a name in the Name text box or accept the default.
  5. In Select the image source, select Database.
  6. In the Image Properties dialog box, click the expression (fx) button.
    Add following line in expression =System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!IMAGE.Value),8))
  7. In Use this MIME type, select the MIME type, or file format, of the image—for example, bmp.
  8. Click OK.


Insert Company logo in SSRS reports (AX)


following useful article for printing companylogo of Ax on SSRS reports of Ax .

http://www.hakasolutions.com/news/51/130/Insert-Company-logo-in-SSRS-reports-AX/

Sunday, 5 February 2012

Create Sales Quotation using X++ Code in Ax2012


static void CreateSalesQuotation(Args _args)
{
    AxSalesQuotationTable    AxSalesQttable;
    AxSalesQuotationLine     AxSalesQtline;
    SalesQuotationLine      SalesQuotationLine;
    salesQuotationTable     salesQuotationTable;
    CustTable     custTable;
    SalesQty  salesQty;
    ;
    salesQty = 2;
    AxSalesQttable =  new AxSalesQuotationTable();
    AxSalesQttable.parmCustAccount('C-20');
    AxSalesQttable.parmCurrencyCode('USD');        
    AxSalesQttable.save();
    AxSalesQtline =  new AxSalesQuotationLine();//::construct(smmQuotationLine);

    AxSalesQtline.axSalesQuotationTable(AxSalesQttable);

    AxSalesQtline.parmQuotationId(AxSalesQttable.parmQuotationId());
    AxSalesQtline.axSalesQuotationTable(AxSalesQttable);
    AxSalesQtline.parmItemId('1000');

    AxSalesQtline.parmSalesQty(salesQty);

    AxSalesQtline.parmInventDimId('00001005_069');
    //AxSalesQtline.par

    AxSalesQtline.parmcurrencyCode('USD');
    AxSalesQtline.save();
}

Wednesday, 25 January 2012

A call to the Microsoft Dynamics AX SRSFrameworkService service failed. No connection could be made because the target machine actively refused it

I am facing that problem but the help of following link. I have solved that problem
http://community.dynamics.com/product/ax/f/33/t/68553.aspx

with the help of  above link i have register the following both services

SRSFrameworkService
SSASFrameworkService
and also auto deploy the following services
BIServices
UserSessionService