Creating number sequence

http://swathidynamicsax.blogspot.ca/2012/07/simple-steps-to-create-number-sequence.html

A custom export to excel function with custom filters.

public void ExpotToExcel()
{
   

    CustInvoiceJour localcustInvoiceJour;
    CustInvoiceTrans localcustInvoiceTrans;
    SalesTable localsalesTable;
    SalesLine localsalesLine;
    InventDim localinventDim;
    ProdTable localprodTable;
    CustTable localcustTable;

    container line;
    CommaIo   file;

    FormRun     formrun;
    Args        args;
    ARG_InvoiceExportFilters invoiceExportFilters;

    FilePath filename = WinAPI::getSaveFileName(0, ["Comma Sperated Value .csv", "*.csv"], "", "Specify Path", "", "InvoiceInquiry"+ curUserId() + date2str(systemDateGet(), 321, 2, DateSeparator::None, 2, DateSeparator::None, 4) + ".csv");

    args = new Args(formStr(ARG_InvoiceExportFilters));


    formRun = new FormRun(args);
    formRun.init();
    formRun.run();
    formRun.wait();

    select invoiceExportFilters;

    file = new CommaIo(filename, 'W');
    if( !file || file.status() != IO_Status::Ok)
    {
        throw error("File Cannot be opened");
    }

    line = ["Invoice", "Date", "Item Id", "Main Account", "Sales Order", "Delivery Name", "Sales District", "Sales Taker", "Production Order",
            "Location", "Size", "Resource Group", "Prod Line", "Shipped", "Booking", "Extended Cost", "Commission", "Profit %",
            "Actual Item Costs", "Actual Process Costs", "Actual Service Costs", "Actual Indirect Rate Costs", "Actual Costs Total",
            "Estimated Item Costs", "Estimated Process Costs", "Estimated Service Costs", "Estimated Indirect Rate Costs", "Estimated Costs Total",
            "Standard Costs"];
    file.writeExp(line);

    if(subStr(invoiceExportFilters.InvoiceDate, 0, 1) == "<"){

        while select localCustInvoiceJour where localcustInvoiceJour.InvoiceId like (invoiceExportFilters.InvoiceId ? invoiceExportFilters.InvoiceId : "*")
                                             && localcustInvoiceJour.InvoiceDate < str2Date(subStr(invoiceExportFilters.InvoiceDate, 2, strLen(invoiceExportFilters.InvoiceDate)-1), 123)
                                             && localcustInvoiceJour.SalesId like (invoiceExportFilters.SalesId ? invoiceExportFilters.SalesId : "*")
            outer join
            localcustInvoiceTrans where localcustInvoiceTrans.SalesId == localcustInvoiceJour.SalesId
                                        && localcustInvoiceTrans.InvoiceId == localcustInvoiceJour.InvoiceId
                                        && localcustInvoiceTrans.InvoiceDate == localcustInvoiceJour.InvoiceDate
                                        && localcustInvoiceTrans.numberSequenceGroup == localcustInvoiceJour.numberSequenceGroup
                                        && localcustInvoiceTrans.ItemId like (invoiceExportFilters.itemId ? invoiceExportFilters.ItemId : "*")
            {


                localsalesTable = localcustInvoiceJour.salesTable();

                localSalesLine = localcustInvoiceTrans.salesLine();

                select localinventDim where localinventDim.inventDimId == localSalesLine.InventDimId;

                select localprodTable where localprodTable.ProdId == localSalesLine.InventRefId && localprodTable.ProdId like (invoiceExportFilters.ProdId ? invoiceExportFilters.ProdId : "*" );

                select localcustTable where localcustTable.AccountNum == localsalesTable.InvoiceAccount;

                if(((localsalesLine.showProdLine() like (invoiceExportFilters.ProdLine ? invoiceExportFilters.ProdLine : "*" ))||(localsalesLine.showProdLine() == "" && invoiceExportFilters.ProdLine == "*"))&&
                   ((localsalesLine.showLocation() like (invoiceExportFilters.Location ? invoiceExportFilters.Location : "*" ))||(localsalesLine.showLocation() == "" && invoiceExportFilters.Location == "*"))&&
                   ((localsalesLine.showSize() like (invoiceExportFilters.Size ? invoiceExportFilters.Size : "*" ))||(localsalesLine.showSize() == "" && invoiceExportFilters.Size == "*"))&&
                   ((localsalesLine.showResGrp() like (invoiceExportFilters.ResourceGroup ? invoiceExportFilters.ResourceGroup : "*"))||(localsalesLine.showResGrp() == "" && invoiceExportFilters.ResourceGroup == "*"))){
                        line = [localcustInvoiceJour.InvoiceId, localcustInvoiceJour.InvoiceDate, localcustInvoiceTrans.ItemId, localcustInvoiceTrans.LedgerDimension,
                        localcustInvoiceTrans.SalesId, localsalesTable.DeliveryName, localcustTable.SalesDistrictId, HcmWorker::find(localsalesTable.WorkerSalesTaker).name(),
                        localprodTable.ProdId, localcustInvoiceTrans.showLocation(localcustInvoiceTrans), localcustInvoiceTrans.showSize(localcustInvoiceTrans), localcustInvoiceTrans.showResGrp(localcustInvoiceTrans),
                        localcustInvoiceTrans.showProdLine(localcustInvoiceTrans), localcustInvoiceTrans.showShipped(localcustInvoiceTrans), localcustInvoiceTrans.showBooking(localcustInvoiceTrans), localcustInvoiceTrans.showExtendedCost(localcustInvoiceTrans),
                        " ", localcustInvoiceTrans.showProfit(localcustInvoiceTrans),
                        localProdTable.showActualCostItem(), localProdTable.showActualCostProcess(), localProdTable.showActualCostService(), localProdTable.showActualCostIndirectRate(), localProdTable.showActualCostTotal(),
                        localProdTable.showEstimatedCostItem(), localProdTable.showEstimatedCostProcess(), localProdTable.showEstimatedCostService(), localProdTable.showEstimatedCostIndirect(), localProdTable.showEstimatedCostTotal(),
                       localProdTable.showStandardCostTotal()];
                        file.writeExp(line);
                }
        }
    }
    else if(subStr(invoiceExportFilters.InvoiceDate, 0, 1) == ">"){


        while select localCustInvoiceJour where localcustInvoiceJour.InvoiceId like (invoiceExportFilters.InvoiceId ? invoiceExportFilters.InvoiceId : "*")
                                             && localcustInvoiceJour.InvoiceDate > str2Date(subStr(invoiceExportFilters.InvoiceDate, 2, strLen(invoiceExportFilters.InvoiceDate)-1), 123)
                                             && localcustInvoiceJour.SalesId like (invoiceExportFilters.SalesId ? invoiceExportFilters.SalesId : "*")
            outer join
            localcustInvoiceTrans where localcustInvoiceTrans.SalesId == localcustInvoiceJour.SalesId
                                        && localcustInvoiceTrans.InvoiceId == localcustInvoiceJour.InvoiceId
                                        && localcustInvoiceTrans.InvoiceDate == localcustInvoiceJour.InvoiceDate
                                        && localcustInvoiceTrans.numberSequenceGroup == localcustInvoiceJour.numberSequenceGroup
                                        && localcustInvoiceTrans.ItemId like (invoiceExportFilters.itemId ? invoiceExportFilters.ItemId : "*")
            {


                localsalesTable = localcustInvoiceJour.salesTable();

                localSalesLine = localcustInvoiceTrans.salesLine();

                select localinventDim where localinventDim.inventDimId == localSalesLine.InventDimId;

                select localprodTable where localprodTable.ProdId == localSalesLine.InventRefId && localprodTable.ProdId like (invoiceExportFilters.ProdId ? invoiceExportFilters.ProdId : "*" );

                select localcustTable where localcustTable.AccountNum == localsalesTable.InvoiceAccount;

                if(((localsalesLine.showProdLine() like (invoiceExportFilters.ProdLine ? invoiceExportFilters.ProdLine : "*" ))||(localsalesLine.showProdLine() == "" && invoiceExportFilters.ProdLine == "*"))&&
                   ((localsalesLine.showLocation() like (invoiceExportFilters.Location ? invoiceExportFilters.Location : "*" ))||(localsalesLine.showLocation() == "" && invoiceExportFilters.Location == "*"))&&
                   ((localsalesLine.showSize() like (invoiceExportFilters.Size ? invoiceExportFilters.Size : "*" ))||(localsalesLine.showSize() == "" && invoiceExportFilters.Size == "*"))&&
                   ((localsalesLine.showResGrp() like (invoiceExportFilters.ResourceGroup ? invoiceExportFilters.ResourceGroup : "*"))||(localsalesLine.showResGrp() == "" && invoiceExportFilters.ResourceGroup == "*"))){
                        line = [localcustInvoiceJour.InvoiceId, localcustInvoiceJour.InvoiceDate, localcustInvoiceTrans.ItemId, localcustInvoiceTrans.LedgerDimension,
                        localcustInvoiceTrans.SalesId, localsalesTable.DeliveryName, localcustTable.SalesDistrictId, HcmWorker::find(localsalesTable.WorkerSalesTaker).name(),
                        localprodTable.ProdId, localcustInvoiceTrans.showLocation(localcustInvoiceTrans), localcustInvoiceTrans.showSize(localcustInvoiceTrans), localcustInvoiceTrans.showResGrp(localcustInvoiceTrans),
                        localcustInvoiceTrans.showProdLine(localcustInvoiceTrans), localcustInvoiceTrans.showShipped(localcustInvoiceTrans), localcustInvoiceTrans.showBooking(localcustInvoiceTrans), localcustInvoiceTrans.showExtendedCost(localcustInvoiceTrans),
                        " ", localcustInvoiceTrans.showProfit(localcustInvoiceTrans),
                        localProdTable.showActualCostItem(), localProdTable.showActualCostProcess(), localProdTable.showActualCostService(), localProdTable.showActualCostIndirectRate(), localProdTable.showActualCostTotal(),
                        localProdTable.showEstimatedCostItem(), localProdTable.showEstimatedCostProcess(), localProdTable.showEstimatedCostService(), localProdTable.showEstimatedCostIndirect(), localProdTable.showEstimatedCostTotal(),
                       localProdTable.showStandardCostTotal()];
                       file.writeExp(line);
                }
        }
    }

    else if(invoiceExportFilters.InvoiceDate == "*"){
        while select localCustInvoiceJour where localcustInvoiceJour.InvoiceId like (invoiceExportFilters.InvoiceId ? invoiceExportFilters.InvoiceId : "*")
                                             && localcustInvoiceJour.SalesId like (invoiceExportFilters.SalesId ? invoiceExportFilters.SalesId : "*")
            outer join
            localcustInvoiceTrans where localcustInvoiceTrans.SalesId == localcustInvoiceJour.SalesId
                                        && localcustInvoiceTrans.InvoiceId == localcustInvoiceJour.InvoiceId
                                        && localcustInvoiceTrans.InvoiceDate == localcustInvoiceJour.InvoiceDate
                                        && localcustInvoiceTrans.numberSequenceGroup == localcustInvoiceJour.numberSequenceGroup
                                        && localcustInvoiceTrans.ItemId like (invoiceExportFilters.itemId ? invoiceExportFilters.ItemId : "*")
            {


                localsalesTable = localcustInvoiceJour.salesTable();

                localSalesLine = localcustInvoiceTrans.salesLine();

                select localinventDim where localinventDim.inventDimId == localSalesLine.InventDimId;

                select localprodTable where localprodTable.ProdId == localSalesLine.InventRefId && localprodTable.ProdId like (invoiceExportFilters.ProdId ? invoiceExportFilters.ProdId : "*" );

                select localcustTable where localcustTable.AccountNum == localsalesTable.InvoiceAccount;

                if(((localsalesLine.showProdLine() like (invoiceExportFilters.ProdLine ? invoiceExportFilters.ProdLine : "*" ))||(localsalesLine.showProdLine() == "" && invoiceExportFilters.ProdLine == "*"))&&
                   ((localsalesLine.showLocation() like (invoiceExportFilters.Location ? invoiceExportFilters.Location : "*" ))||(localsalesLine.showLocation() == "" && invoiceExportFilters.Location == "*"))&&
                   ((localsalesLine.showSize() like (invoiceExportFilters.Size ? invoiceExportFilters.Size : "*" ))||(localsalesLine.showSize() == "" && invoiceExportFilters.Size == "*"))&&
                   ((localsalesLine.showResGrp() like (invoiceExportFilters.ResourceGroup ? invoiceExportFilters.ResourceGroup : "*"))||(localsalesLine.showResGrp() == "" && invoiceExportFilters.ResourceGroup == "*"))){
                        line = [localcustInvoiceJour.InvoiceId, localcustInvoiceJour.InvoiceDate, localcustInvoiceTrans.ItemId, localcustInvoiceTrans.LedgerDimension,
                        localcustInvoiceTrans.SalesId, localsalesTable.DeliveryName, localcustTable.SalesDistrictId, HcmWorker::find(localsalesTable.WorkerSalesTaker).name(),
                        localprodTable.ProdId, localcustInvoiceTrans.showLocation(localcustInvoiceTrans), localcustInvoiceTrans.showSize(localcustInvoiceTrans), localcustInvoiceTrans.showResGrp(localcustInvoiceTrans),
                        localcustInvoiceTrans.showProdLine(localcustInvoiceTrans), localcustInvoiceTrans.showShipped(localcustInvoiceTrans), localcustInvoiceTrans.showBooking(localcustInvoiceTrans), localcustInvoiceTrans.showExtendedCost(localcustInvoiceTrans),
                        " ", localcustInvoiceTrans.showProfit(localcustInvoiceTrans),
                        localProdTable.showActualCostItem(), localProdTable.showActualCostProcess(), localProdTable.showActualCostService(), localProdTable.showActualCostIndirectRate(), localProdTable.showActualCostTotal(),
                        localProdTable.showEstimatedCostItem(), localProdTable.showEstimatedCostProcess(), localProdTable.showEstimatedCostService(), localProdTable.showEstimatedCostIndirect(), localProdTable.showEstimatedCostTotal(),
                       localProdTable.showStandardCostTotal()];
                        file.writeExp(line);
                }
        }
    }
    else if(strContains(invoiceExportFilters.InvoiceDate, "..")){
        while select localCustInvoiceJour where localcustInvoiceJour.InvoiceId like (invoiceExportFilters.InvoiceId ? invoiceExportFilters.InvoiceId : "*")
                                             && localcustInvoiceJour.InvoiceDate >= str2Date(subStr(invoiceExportFilters.InvoiceDate, 1, 10), 123)
                                             && localcustInvoiceJour.InvoiceDate <= str2Date(subStr(invoiceExportFilters.InvoiceDate, 13, strLen(invoiceExportFilters.InvoiceDate)-1), 123)
                                             && localcustInvoiceJour.SalesId like (invoiceExportFilters.SalesId ? invoiceExportFilters.SalesId : "*")
            outer join
            localcustInvoiceTrans where localcustInvoiceTrans.SalesId == localcustInvoiceJour.SalesId
                                        && localcustInvoiceTrans.InvoiceId == localcustInvoiceJour.InvoiceId
                                        && localcustInvoiceTrans.InvoiceDate == localcustInvoiceJour.InvoiceDate
                                        && localcustInvoiceTrans.numberSequenceGroup == localcustInvoiceJour.numberSequenceGroup
                                        && localcustInvoiceTrans.ItemId like (invoiceExportFilters.itemId ? invoiceExportFilters.ItemId : "*")
            {


                localsalesTable = localcustInvoiceJour.salesTable();

                localSalesLine = localcustInvoiceTrans.salesLine();

                select localinventDim where localinventDim.inventDimId == localSalesLine.InventDimId;

                select localprodTable where localprodTable.ProdId == localSalesLine.InventRefId && localprodTable.ProdId like (invoiceExportFilters.ProdId ? invoiceExportFilters.ProdId : "*" );

                select localcustTable where localcustTable.AccountNum == localsalesTable.InvoiceAccount;

                if(((localsalesLine.showProdLine() like (invoiceExportFilters.ProdLine ? invoiceExportFilters.ProdLine : "*" ))||(localsalesLine.showProdLine() == "" && invoiceExportFilters.ProdLine == "*"))&&
                   ((localsalesLine.showLocation() like (invoiceExportFilters.Location ? invoiceExportFilters.Location : "*" ))||(localsalesLine.showLocation() == "" && invoiceExportFilters.Location == "*"))&&
                   ((localsalesLine.showSize() like (invoiceExportFilters.Size ? invoiceExportFilters.Size : "*" ))||(localsalesLine.showSize() == "" && invoiceExportFilters.Size == "*"))&&
                   ((localsalesLine.showResGrp() like (invoiceExportFilters.ResourceGroup ? invoiceExportFilters.ResourceGroup : "*"))||(localsalesLine.showResGrp() == "" && invoiceExportFilters.ResourceGroup == "*"))){
                        line = [localcustInvoiceJour.InvoiceId, localcustInvoiceJour.InvoiceDate, localcustInvoiceTrans.ItemId, localcustInvoiceTrans.LedgerDimension,
                        localcustInvoiceTrans.SalesId, localsalesTable.DeliveryName, localcustTable.SalesDistrictId, HcmWorker::find(localsalesTable.WorkerSalesTaker).name(),
                        localprodTable.ProdId, localcustInvoiceTrans.showLocation(localcustInvoiceTrans), localcustInvoiceTrans.showSize(localcustInvoiceTrans), localcustInvoiceTrans.showResGrp(localcustInvoiceTrans),
                        localcustInvoiceTrans.showProdLine(localcustInvoiceTrans), localcustInvoiceTrans.showShipped(localcustInvoiceTrans), localcustInvoiceTrans.showBooking(localcustInvoiceTrans), localcustInvoiceTrans.showExtendedCost(localcustInvoiceTrans),
                        " ", localcustInvoiceTrans.showProfit(localcustInvoiceTrans),
                        localProdTable.showActualCostItem(), localProdTable.showActualCostProcess(), localProdTable.showActualCostService(), localProdTable.showActualCostIndirectRate(), localProdTable.showActualCostTotal(),
                        localProdTable.showEstimatedCostItem(), localProdTable.showEstimatedCostProcess(), localProdTable.showEstimatedCostService(), localProdTable.showEstimatedCostIndirect(), localProdTable.showEstimatedCostTotal(),
                       localProdTable.showStandardCostTotal()];
                        file.writeExp(line);
                }
        }
    }

    else{

        while select localCustInvoiceJour where localcustInvoiceJour.InvoiceId like (invoiceExportFilters.InvoiceId ? invoiceExportFilters.InvoiceId : "*")
                                             && localcustInvoiceJour.InvoiceDate == str2Date(invoiceExportFilters.InvoiceDate, 123)
                                             && localcustInvoiceJour.SalesId like (invoiceExportFilters.SalesId ? invoiceExportFilters.SalesId : "*")
            outer join
            localcustInvoiceTrans where localcustInvoiceTrans.SalesId == localcustInvoiceJour.SalesId
                                        && localcustInvoiceTrans.InvoiceId == localcustInvoiceJour.InvoiceId
                                        && localcustInvoiceTrans.InvoiceDate == localcustInvoiceJour.InvoiceDate
                                        && localcustInvoiceTrans.numberSequenceGroup == localcustInvoiceJour.numberSequenceGroup
                                        && localcustInvoiceTrans.ItemId like (invoiceExportFilters.itemId ? invoiceExportFilters.ItemId : "*")
            {


                localsalesTable = localcustInvoiceJour.salesTable();

                localSalesLine = localcustInvoiceTrans.salesLine();

                select localinventDim where localinventDim.inventDimId == localSalesLine.InventDimId;

                select localprodTable where localprodTable.ProdId == localSalesLine.InventRefId && localprodTable.ProdId like (invoiceExportFilters.ProdId ? invoiceExportFilters.ProdId : "*" );

                select localcustTable where localcustTable.AccountNum == localsalesTable.InvoiceAccount;

                if(((localsalesLine.showProdLine() like (invoiceExportFilters.ProdLine ? invoiceExportFilters.ProdLine : "*" ))||(localsalesLine.showProdLine() == "" && invoiceExportFilters.ProdLine == "*"))&&
                   ((localsalesLine.showLocation() like (invoiceExportFilters.Location ? invoiceExportFilters.Location : "*" ))||(localsalesLine.showLocation() == "" && invoiceExportFilters.Location == "*"))&&
                   ((localsalesLine.showSize() like (invoiceExportFilters.Size ? invoiceExportFilters.Size : "*" ))||(localsalesLine.showSize() == "" && invoiceExportFilters.Size == "*"))&&
                   ((localsalesLine.showResGrp() like (invoiceExportFilters.ResourceGroup ? invoiceExportFilters.ResourceGroup : "*"))||(localsalesLine.showResGrp() == "" && invoiceExportFilters.ResourceGroup == "*"))){
                        line = [localcustInvoiceJour.InvoiceId, localcustInvoiceJour.InvoiceDate, localcustInvoiceTrans.ItemId, localcustInvoiceTrans.LedgerDimension,
                        localcustInvoiceTrans.SalesId, localsalesTable.DeliveryName, localcustTable.SalesDistrictId, HcmWorker::find(localsalesTable.WorkerSalesTaker).name(),
                        localprodTable.ProdId, localcustInvoiceTrans.showLocation(localcustInvoiceTrans), localcustInvoiceTrans.showSize(localcustInvoiceTrans), localcustInvoiceTrans.showResGrp(localcustInvoiceTrans),
                        localcustInvoiceTrans.showProdLine(localcustInvoiceTrans), localcustInvoiceTrans.showShipped(localcustInvoiceTrans), localcustInvoiceTrans.showBooking(localcustInvoiceTrans), localcustInvoiceTrans.showExtendedCost(localcustInvoiceTrans),
                        " ", localcustInvoiceTrans.showProfit(localcustInvoiceTrans),
                        localProdTable.showActualCostItem(), localProdTable.showActualCostProcess(), localProdTable.showActualCostService(), localProdTable.showActualCostIndirectRate(), localProdTable.showActualCostTotal(),
                        localProdTable.showEstimatedCostItem(), localProdTable.showEstimatedCostProcess(), localProdTable.showEstimatedCostService(), localProdTable.showEstimatedCostIndirect(), localProdTable.showEstimatedCostTotal(),
                       localProdTable.showStandardCostTotal()];
                        file.writeExp(line);
                }
        }
    }

    info("Data exported to file: " + filename);


}

Refresh vs Reread vs Research

https://community.dynamics.com/ax/b/mafsarkhan/archive/2010/05/26/refresh-reread-research-executequery-which-one-to-use.aspx

Creating a new SSRS report

When creating a new SSRS report, there are some things you need to keep in mind:

• Security, you will need to set up a privilege that not only has the menu items you will be using, but also the TMP tables that the report uses.  The error you get when you leave off the TMP tables looks similar to a Management Reporter security issue so you might end up running in circles when the answer is right in front of you.
• When editing a report in Visual Studio, the checkbox in the Table properties to repeat headers on a new page does nothing.  You will need to use the advanced view on the groups list to see the static headers, click on the and set RepeatOnNewPage to True.