Trial balance report export to share point using x++

Trial balance report export to share point using x++.

using Microsoft.Dynamics.Platform.Integration.SharePoint;

[SRSReportParameterAttribute(classStr(TrialBalanceMainAccountReportContract))]
public class TrialBalanceMainAccountReportDP extends SrsReportDataProviderPreProcessTempDB
{
    #Define.BusinessUnitDim('BusinessUnit')
    #Define.DepartmentDim('Department')
    #Define.ExcelExtension('.xlsx')

    TrialBalanceMainAccountReportTmp        TrialBalanceMainAccountReportTmp;
    LedgerTrialBalanceTmp                   ledgerTrialBalanceTmp;
    TrialBalanceMainAccountReportContract   contract;
    Name                                    primaryDimensionFocus;
	
   
    public void setTrialBalanceTmpTable(TrialBalanceMainAccountReportTmp _TrialBalanceMainAccountReportTmp)
    {
        TrialBalanceMainAccountReportTmp.linkPhysicalTableInstance(_TrialBalanceMainAccountReportTmp);
    }

    [SRSReportDataSetAttribute(tablestr(TrialBalanceMainAccountReportTmp))]
    public TrialBalanceMainAccountReportTmp getDocAgreementHeader()
    {
        select TrialBalanceMainAccountReportTmp;
        return TrialBalanceMainAccountReportTmp;
    }

    public void processData()
    {
        FiscalCalendarPeriod                fiscalCalendarStartPeriod;
        GeneralJournalAccountEntry          generalJournalAccountEntryHeader, generalJournalAccountEntryLine;
        GeneralJournalEntry                 generalJournalEntryHeader, generalJournalEntryLine;
        DimensionAttributeValueCombination  dimensionAttributeValueCombination;
        LedgerJournalTrans                  ledgerJournalTrans;
        LedgerTrialBalanceTmp               ledgerTrialBalanceTmpReference;
        MainAccount                         mainAccount;
        AmountMST                           openingBalance, creditAmount, debitAmount, closingBalance;
        Name                                dimensionAttributeName;
        DimensionValue                      dimensionValue;
        boolean                             insertData = false;
        boolean                             displayOnlyBalances = true;
        CurrentOperationsTax                postingLayer;

        contract = this.parmDataContract() as TrialBalanceMainAccountReportContract;

        TransDate               FromDate            = contract.parmFromDate();
        TransDate               ToDate              = contract.parmToDate();
        TransDate               periodStartDate     = LedgerFiscalCalendar::findOpeningStartDateByDate(Ledger::fiscalCalendar(CompanyInfo::current()), FromDate);

        delete_from TrialBalanceMainAccountReportTmp;

		//  Based on date range looping records in 'generalJournalAccountEntryHeader' table.(Voucher transactions)
        while select MainAccount, LedgerDimension from  generalJournalAccountEntryHeader
            group by generalJournalAccountEntryHeader.LedgerDimension ,generalJournalAccountEntryHeader.MainAccount
        join generalJournalEntryHeader
            where generalJournalAccountEntryHeader.GeneralJournalEntry == generalJournalEntryHeader.RecId
                && generalJournalEntryHeader.AccountingDate >= fromDate
                && generalJournalEntryHeader.AccountingDate <= toDate
        {
            FiscalCalendarPeriod fiscalCalendarPeriod;
           
            //Debit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount  from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::No
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            debitAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Credit balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
                    && generalJournalAccountEntryLine.LedgerDimension == generalJournalAccountEntryHeader.LedgerDimension
                    && generalJournalAccountEntryLine.IsCredit == NoYes::Yes
                join generalJournalEntryLine
                where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                    && generalJournalEntryLine.AccountingDate >= fromDate
                    && generalJournalEntryLine.AccountingDate <= toDate
                    && generalJournalEntryLine.Ledger == Ledger::current()
                exists join fiscalCalendarPeriod
                where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                    && fiscalCalendarPeriod.Type == FiscalPeriodType::Operating;

            creditAmount = generalJournalAccountEntryLine.AccountingCurrencyAmount;

            //Opening balance
            select sum(AccountingCurrencyAmount), MainAccount, LedgerAccount from generalJournalAccountEntryLine
                group by generalJournalAccountEntryLine.MainAccount, generalJournalAccountEntryLine.LedgerAccount
                where generalJournalAccountEntryLine.MainAccount == generalJournalAccountEntryHeader.MainAccount
            join generalJournalEntryLine
            where generalJournalAccountEntryLine.GeneralJournalEntry == generalJournalEntryLine.RecId
                && generalJournalEntryLine.Ledger == Ledger::current()
                && generalJournalEntryLine.AccountingDate >= fromDate
                && generalJournalEntryLine.AccountingDate <= ToDate
                 exists join fiscalCalendarPeriod
            where fiscalCalendarPeriod.RecId == generalJournalEntryLine.FiscalCalendarPeriod
                && fiscalCalendarPeriod.Type == FiscalPeriodType::Opening;

            openingBalance  = generalJournalAccountEntryLine.AccountingCurrencyAmount;
            closingBalance  = openingBalance + debitAmount + creditAmount;

            str  mainAccountId, MainAcountName;

            mainAccountId = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).MainAccountId;
            MainAcountName = MainAccount::find(generalJournalAccountEntryHeader.MainAccount).Name;

            //info(strFmt("Id %1, Name %2, Debit: %3, Credit%4, LedgerDim:%5, opnBl%6, CloseBl%7", mainAccountId, 
                    MainAcountName, drAmt, CrAmt, generalJournalAccountEntry.LedgerAccount,opnBl, CloseBl ));

            TrialBalanceMainAccountReportTmp.FromDate              = FromDate;
            TrialBalanceMainAccountReportTmp.ToDate                = ToDate;
            TrialBalanceMainAccountReportTmp.MainAccount           = str2Int64(mainAccountId);
            TrialBalanceMainAccountReportTmp.Name                  = MainAcountName;
            TrialBalanceMainAccountReportTmp.OpeningBalance        = openingBalance;
            TrialBalanceMainAccountReportTmp.ClosingTransactions   = closingBalance;
            TrialBalanceMainAccountReportTmp.AmountCredit          = creditAmount;
            TrialBalanceMainAccountReportTmp.AmountDebit           = debitAmount;

            // Fetching Dimension Values
            if (generalJournalAccountEntryHeader.LedgerDimension)
            {
                TrialBalanceMainAccountReportTmp.BusinessUnit       = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #BusinessUnitDim);
                TrialBalanceMainAccountReportTmp.Department         = this.getDefaultDimensionValue(generalJournalAccountEntryHeader.LedgerDimension, #DepartmentDim);
            }
            TrialBalanceMainAccountReportTmp.insert();
        }
        this.uploadOnSharePoint();
    }

    public DimensionValue getDefaultDimensionValue(DimensionDefault   _defaultDimension, Name  _dimensionName)
    {
        DimensionAttributeValueSetStorage   dimStorage;

        dimStorage = DimensionAttributeValueSetStorage::find(_defaultDimension);

        return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(_dimensionName).RecId);
    }

    public void uploadOnSharePoint()
    {
        System.IO.MemoryStream      memoryStream;
        int                         row;
        int                         col;
        Filename                    fileNameExt;
        System.Exception            ex;
        str                         errorMessage = '';

        try
        {
            ttsbegin;
            memoryStream = new System.IO.MemoryStream();

            row =   1;
        
            using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
            {
                var sheet = package.get_Workbook().get_Worksheets();
                var worksheet = sheet.Add("Hyperion trail balance"); // adding new work sheet.
                var cells = worksheet.get_Cells();
                OfficeOpenXml.Style.ExcelStyle style;
                OfficeOpenXml.Style.ExcelFont font;
				
				//adding colum names
                Col = 1;
                var cell = Cells.get_item(row,Col);
                cell.set_Value("Main Account");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Business Unit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Department");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Name");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Opening Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Debit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Credit");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                cell = Cells.get_item(row,Col);
                cell.set_Value("Closing Balance");
                style = cells.style;
                font  = style.font;
                font.Bold = true;
                col++;

                while select TrialBalanceMainAccountReportTmp
                {
                    row++;
                    Col = 1;
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.MainAccount);
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value((TrialBalanceMainAccountReportTmp.BusinessUnit));
                    col++;
                
                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Department);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.Name);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.OpeningBalance);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountDebit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.AmountCredit);
                    col++;

                    cell = Cells.get_item(row,col);
                    cell.set_Value(TrialBalanceMainAccountReportTmp.ClosingTransactions);
                    col++;
                }
                package.save();
            }

            memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
			
			// File name
            fileNameExt = 'Hyperiod Trail balance';
            fileNameExt =  subStr(fileNameExt, 1, 30) + #ExcelExtension;
			
			str fileContentType = System.Web.MimeMapping::GetMimeMapping(fileNameExt);
			
			//path
            str folderPath = "/Shared Documents/Integration/Input";

            ISharePointProxy    proxy = null;
            str                 src = '';
            str                 hostName = '';

			src = ' https://'domain name'.sharepoint.com';// 
				
			System.UriBuilder builder = new System.UriBuilder(src);
			hostName = builder.Host;
			str externalId = xUserInfo::getCurrentUserExternalId();

			try
			{
				proxy = SharePointHelper::CreateProxy(hostName, '/', externalId);
			}
			catch(Exception::CLRError)
			{
				proxy = null;
			}

            if(proxy)
            {
                Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider prov =
                    new Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(proxy, folderPath);
                    
                prov.SaveFileWithOverwrite(newguid(), fileNameExt, fileContentType, memoryStream);
            }
            else
            {
                throw Error("Sharepoint Connection Error");
            }
            ttscommit;
        }
        catch (ex)
        {
            System.Exception e = ex;
            while (e != null)
            {
                errorMessage += e.Message;
                e = e.InnerException;
            }

            if (appl.ttsLevel() > 0)
            {
                ttsabort;
            }

            checkFailed("Process failed");
            error(errorMessage);
        }
    }

}

Trial balance using Standard class:

    LedgerTrialBalanceListPageBalanceParms 	balanceParameters;
    DimensionHierarchy 			        dimHier;
    LedgerTrialBalanceTmp   			LedgerTrialBalanceTmp;
    TransDate                                   fromDate,toDate;
    List 					selected = new List(Types::Enum);
 
    selected.addEnd(CurrentOperationsTax::Current);

    dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, 'Main account set');
	
    balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
	
    balanceParameters.parmDateCode('');
    balanceParameters.parmDimensionSetHierarchy(dimHier);
    balanceParameters.parmStartDate(fromDate);
    balanceParameters.parmEndDate(toDate);
    balanceParameters.parmPostingLayers(selected);

    select firstOnly RecId from LedgerTrialBalanceTmp;

    LedgerTrialBalanceTmp::calculateBalances_V2(
						LedgerTrialBalanceTmp,
						dimHier.Name,
						balanceParameters.getStartDate(),
						balanceParameters.getEndDate(),
						balanceParameters.getIncludeOpeningPeriods(),
						balanceParameters.getIncludeClosingAdjustments(),
						balanceParameters.getIncludeClosingTransactions(),
						balanceParameters.getPostingLayers(),
						true,//ShowErrors,
						true,// runUpdateBalancesInNewSession,
						Ledger::primaryLedger(CompanyInfo::findDataArea('other company').RecId),//Ledger::current(),// current comapny.
						balanceParameters.getDisplayMainAccountCategory());


	while select LedgerTrialBalanceTmp
	    where LedgerTrialBalanceTmp.EndingBalance != 0
	{
	    info(strfmt('%1:%2', LedgerTrialBalanceTmp.DimensionValues[1],LedgerTrialBalanceTmp.EndingBalance))
	}

Keep Daxing!!

How to add fields to the form DirPartyQuickCreateForm in D365FO

 I got a requirement to add few fields while creating customer and those fields data should be auto populated from customer group.

• To create customer/vendor the standard system will call the  DirPartyQuickCreateForm. 
• We can't add fields directly to this form. It will not work and we can't get the control's in logic.

Solution :

•     From form inti method the below will trigger.

        

• Here They are calling field groups in table.
• If you open the table you can find that groups.

            

•  You can drag the required fields to the above mentioned groups.

• For my requirement I have add the field in cust table and cust group and dragged in to that group.

• For auto population I have added the below code in modified method

[ExtensionOf(tableStr(CustTable))]
final class custTable_Extension
{    
   public void modifiedField(FieldId _fieldId)   
   {        
	next modifiedField(_fieldId);

        switch(_fieldId)
        {
            case fieldNum(CustTable, CustGroup):
                this.MarkGUA = CustGroup::find(this.CustGroup).MarkGUA ;
                break;
        }
    }
 }

OuptPut :

Keep daxing!!

Add multiple fields data in one control in Report using x++

 I got a requirement to add multiple fields data in one control in Report

• Create new control in design and select the expression.
• Write the below code based on your fields.

=First(Fields!Field1.Value, "MyTable") & vbcrlf &  
First(Fields!Field2.Value, "MyTable")  & vbcrlf &
First(Fields!Field3.Value, "MyTable")

• Check the output.

Keep daxing!!

How to add a new dataset to SalesInvoice report in D365FO.

 Steps for adding new data source in salesInvoice report.

• created New temp / regular table. Set the CreateTransactionId property to Yes in table and sync it.
• For DP class we have 2 ways.

• Way 1 : Using COC.

[ExtensionOf(classStr(SalesInvoiceDP))]
final class MySalesInvoiceDP_Extension
{   public MySalesInvoiceTmp     mySalesInvoiceTmp;

    [SRSReportDataSetAttribute(tableStr(MySalesInvoiceTmp))]
    public MySalesInvoiceTmp getMySalesInvoiceTmp()
    {
        select mySalesInvoiceTmp;
        return mySalesInvoiceTmp;
    }
}  

• Way 2 : Using Extends

[SRSReportParameterAttribute(classStr(SalesInvoiceContract))]
class mySalesInvoiceDP extends SalesInvoiceDP
{
      public MySalesInvoiceTmp     mySalesInvoiceTmp;
    [SRSReportDataSetAttribute(tableStr(MySalesInvoiceTmp))]
    public MySalesInvoiceTmp getMySalesInvoiceTmp()
    {
        select mySalesInvoiceTmp;
        return mySalesInvoiceTmp;
    }
}

Methods:

protected void populateSalesInvoiceTmp(CustInvoiceJour _custInvoiceJour,
        CustInvoiceTrans _custInvoiceTrans,
        TaxSpec _taxSpec,
        CustPaymSchedLine _custPaymSchedLine,
        CustTrans _prepaymentCustTrans,
        TaxTrans _prepaymentTaxTrans)
    {
	// For extends
        super(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);
	
	//COC you use
	next populateSalesInvoiceTmp(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);

	//Write the code here If your added new fields in salesInvoiceTmp table
	//If some data exist in salesInvoiceTmp table this method will not trigger
	//So you need to delete existing records   
    }

   protected void populateSalesInvoiceHeaderFooterTmp(CustInvoiceJour _custInvoiceJour, 
                                                        CompanyInfo _companyInfo)
   {
      // For extends
      super(_custInvoiceJour, _companyInfo);

      //COC you use
	next populateSalesInvoiceTmp(_custInvoiceJour, _custInvoiceTrans, _taxSpec,
		 _custPaymSchedLine, _prepaymentCustTrans, _prepaymentTaxTrans);
    
     //Write the code here If your added new fields insalesInvoiceTmp
   }

 

•  Duplicate standard SalesInvoice report and create custom design.
• Right click on dataSets and add a new dataset. set the datasource type as Report data provider, dynamic filters = yes. select SaleSInvoiceDP in options and click next. 
• Select your data source.
• Add that data source in design and your fields. 

Print Management :

•     PrintmgmtDocType

final static class MyPrintMgmtDocType_EventHandler
{     
    [SubscribesTo(classstr(PrintMgmtDocType), delegatestr(PrintMgmtDocType, getDefaultReportFormatDelegate))]
    public static void getDefaultReportFormatDelegate(PrintMgmtDocumentType _docType, EventHandlerResult _result)
    {   switch (_docType)
        {
            case PrintMgmtDocumentType::SalesOrderInvoice:
                _result.result(ssrsReportStr(MySalesInvoice, Report));
                break;
        }  
    } 
}  

If you want add new element in print management write below code.

[ExtensionOf(classstr(PrintMgmtReportFormatPopulator))]
final class MyPrintMgmtReportFormat_Extension
{
    protected void addDocuments()
    {
        this.addStandard(PrintMgmtDocumentType::SalesOrderInvoice);
        next addDocuments();
    } 
}    

Run the below line in job.

PrintMgmtReportFormatPopulator::construct().populate();    

Keep daxing!!

Row visibility based one field in report using X++

Row visibility is based on one field in the report using X++.

• Select the whole row and right click and select properties.
• Select row visibility properties and add the below code.

=IIF(Fields!AccountNum.Value = "", true, false)   

AccountNum is my field. Use your field.

Column visibility 

• Select the whole column and right click and select properties.
• Select visibility properties and add the below code.

=Parameters!parmcompany1.Value = "" 

Keep daxing!!