How to Create SSRS report

-

 Today we will see how to create a ssrs report:-


1) Create a temp table

2) Create a contract class

3) Create a controller class

4) Create a DP class

5) Create a action menu item


========================================================================


1) Create a temp table 



2) Create a contract class


/// <summary>

/// Data Contract class for <c>ALBD_PrintPaymentConfirmation</c> SSRS report.

/// </summary>

/// <remarks>

/// This is the Data Contract class for the <c>ALBD_PrintPaymentConfirmation</c> SSRS Report.

/// </remarks>

[DataContract]

final class ALBD_PrintPaymentConfirmationContract implements SysOperationValidatable

{

    RecId  recId;

    PurchaseOrderId confirmationNum;

    PaymTermId  paymTermId;

    Notes       notes;


    /// <summary>

    /// parmRecID

    /// </summary>

    /// <param name = "_recId"></param>

    /// <returns></returns>

    [DataMemberAttribute('RecId')

    ]

    public RecId parmRecID(RecId _recId = recId)

    {

        recId = _recId;

        return _recId;

    }


    /// <summary>

    /// parmConfirmationNum

    /// </summary>

    /// <param name = "_confirmationNum"></param>

    /// <returns></returns>

    [DataMemberAttribute('POConfirmationNum')

    ]

    public PurchaseOrderId parmConfirmationNum(PurchaseOrderId _confirmationNum = confirmationNum)

    {

        confirmationNum = _confirmationNum;

        return _confirmationNum;

    }


    /// <summary>

    /// parmPaymtermID

    /// </summary>

    /// <param name = "_paymTermId"></param>

    /// <returns></returns>

    [DataMemberAttribute('Payment term'),

     SysOperationLabelAttribute(literalStr('Payment term')),

     SysOperationDisplayOrderAttribute('1')

    ]

    public PaymTermId parmPaymtermID(paymTermId _paymTermId = paymTermId)

    {

        paymTermId = _paymTermId;

        return _paymTermId;

    }


    /// <summary>

    /// parmNotes

    /// </summary>

    /// <param name = "_notes"></param>

    /// <returns></returns>

    [DataMemberAttribute('Notes'),

     SysOperationDisplayOrderAttribute('2')

    ]

    public Notes parmNotes(notes _notes = notes)

    {

        notes = _notes;

        return _notes;

    }


    /// <summary>

    /// Indicates whether the SSRS report parameters are valid.

    /// </summary>

    /// <returns>

    /// true if the parameters are valid; otherwise, false.

    /// </returns>

    public boolean validate()

    {

        boolean ret = true;


        if (!paymTermId)

        {

            //Payment term must be specified.

            ret = checkFailed(strFmt("@SYS50104", "Payment term"));

        }


        return ret;

    }


}


=======================================================================


3) Create a controller class


/// <summary>

/// The <c>ALBD_PrintPaymentConfirmationController</c> class opens the <c>PrintPaymentConfirmation</c> report.

/// </summary>

public class ALBD_PrintPaymentConfirmationController extends SrsReportRunController

{

    /// <summary>

    /// Main

    /// Main function for report controller

    /// </summary>

    /// <param name = "_args"></param>

    public static client void main(Args _args)

    {

        ALBD_PrintPaymentConfirmationController controller = new  ALBD_PrintPaymentConfirmationController();

        controller.parmReportName(ssrsReportStr(ALBD_PrintPaymentConfirmationReport,Report));

        controller.parmArgs(_args);

        controller.parmShowDialog(true);

        controller.startOperation();

    }


    /// <summary>

    /// prePromptModifyContract

    /// Set contract parms

    /// </summary>

    protected void prePromptModifyContract()

    {

        PurchTable                          purchtable;

        VendPurchOrderJour                  vendPurchOrderJour;

        ALBD_PrintPaymentConfirmationContract     contract;

        ;

       

        purchtable = this.parmArgs().record();

        contract = this.parmReportContract().parmRdpContract() as ALBD_PrintPaymentConfirmationContract;


        select firstonly RecId, PurchOrderDocNum,PurchId from vendPurchOrderJour

                order by PurchId desc

            where vendPurchOrderJour.PurchId == purchtable.PurchId;


        contract.parmRecID(vendPurchOrderJour.RecId);

        contract.parmConfirmationNum(vendPurchOrderJour.PurchOrderDocNum);

        

    }


}


========================================================================


4) Create a DP class


[SRSReportParameterAttribute(classstr(ALBD_PrintPaymentConfirmationContract))]

class ALBD_PrintPaymentConfirmationDP extends SrsReportDataProviderPreProcessTempDB

{

    ALBD_PrintPaymentConfirmation     printPaymentConfirmationTmp;

    Lits_AssignWarehouse              lits_AssignWarehouse;

    Lits_DesignationCode              SignaturePosition1,SignaturePosition2,SignaturePosition3,SignaturePosition4,

                                    SignaturePosition5,SignaturePosition6,SignaturePosition7,SignaturePosition8,SignaturePosition9;

    

    

    /// <summary>

    ///    Processes the business logic that is used to populate a <c>ALBD_PrintPaymentConfirmation</c> temporary

    ///    table.

    /// </summary>

    public void processReport()

    {

        ALBD_PrintPaymentConfirmationContract     contract = this.parmDataContract() as ALBD_PrintPaymentConfirmationContract;

       

        RecId                                       recID;

        str                                         description;

        VendPurchOrderJour                          vendPurchOrderJour   = vendPurchOrderJour::findRecId(contract.parmRecID());

       

        

        printPaymentConfirmationTmp.CompanyLogo     = FormLetter::companyLogo();

        printPaymentConfirmationTmp.ReportPrintDate = today();

        printPaymentConfirmationTmp.VendorName      = vendPurchOrderJour.purchTable().vendorName();

        printPaymentConfirmationTmp.ProjectName     = this.getDimensionDisplayValue(vendPurchOrderJour.purchTable().DefaultDimension, 'C_Project');


        printPaymentConfirmationTmp.PaymentTerms    = contract.parmPaymtermID();

        printPaymentConfirmationTmp.Notes           = contract.parmNotes();

        printPaymentConfirmationTmp.PurchId         = vendPurchOrderJour.PurchId;

        printPaymentConfirmationTmp.CurrencyCode    = vendPurchOrderJour.purchTable().CurrencyCode;


        printPaymentConfirmationTmp.CurrencyDecimalDenomination = Currency::find(vendPurchOrderJour.purchTable().CurrencyCode).Lits_Denomination;

        printPaymentConfirmationTmp.AmountCurrency  = trunc(vendPurchOrderJour.Amount);

        printPaymentConfirmationTmp.AmountDecimalDenomination = frac(vendPurchOrderJour.Amount) *100;

        printPaymentConfirmationTmp.AmountInwords = ALBD_PrintPaymentConfirmationDP::numeralsToTxt_AR(vendPurchOrderJour.Amount);


        while select  lits_AssignWarehouse

                order by CreatedDateTime asc

            where lits_AssignWarehouse.InventLocationId == vendPurchOrderJour.purchTable().InventLocationId

                && lits_AssignWarehouse.DataAreaId  == vendPurchOrderJour.purchTable().DataAreaId


        if(lits_AssignWarehouse.RecId)

        {


            if(!SignaturePosition1)

            {

                SignaturePosition1 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition2)

            {

                SignaturePosition2 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition3)

            {

                SignaturePosition3 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition4)

            {

                SignaturePosition4 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition5)

            {

                SignaturePosition5 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition6)

            {

                SignaturePosition6 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition7)

            {

                SignaturePosition7 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition8)

            {

                SignaturePosition8 = lits_AssignWarehouse.DesignationCode;

                continue;

            }


            if(!SignaturePosition9)

            {

                SignaturePosition9 = lits_AssignWarehouse.DesignationCode;

                continue;

            }

        }


        // Insert Assign Warehouse to Designation Details

        printPaymentConfirmationTmp.SignaturePosition1 = SignaturePosition1;

        printPaymentConfirmationTmp.SignaturePosition2 = SignaturePosition2;

        printPaymentConfirmationTmp.SignaturePosition3 = SignaturePosition3;

        printPaymentConfirmationTmp.SignaturePosition4 = SignaturePosition4;

        printPaymentConfirmationTmp.SignaturePosition5 = SignaturePosition5;

        printPaymentConfirmationTmp.SignaturePosition6 = SignaturePosition6;

        printPaymentConfirmationTmp.SignaturePosition7 = SignaturePosition7;

        printPaymentConfirmationTmp.SignaturePosition8 = SignaturePosition8;

        printPaymentConfirmationTmp.SignaturePosition9 = SignaturePosition9;



        printPaymentConfirmationTmp.insert();


    }


    /// <summary>

    ///    Retrieves the temporary table for SQL Server Reporting Services reports.

    /// </summary>

    /// <returns>

    ///    The <c>ALBD_PrintPaymentConfirmation</c> temporary table.

    /// </returns>

    [SRSReportDataSetAttribute(tableStr(ALBD_PrintPaymentConfirmation))]

    public ALBD_PrintPaymentConfirmation  getTmp()

    {


        select * from  printPaymentConfirmationTmp;

        return  printPaymentConfirmationTmp;


    }


    /// <summary>

    ///    Get the fin.dim on the based of default dimension & name of it.

    /// </summary>

    /// <returns>

    ///    The Financial dimension.

    /// </returns>

    public  str getDimensionDisplayValue(RecId defaultDimension, Name dimName)

    {

        DimensionAttributeValueSetStorage dimStorage;


        dimStorage = DimensionAttributeValueSetStorage::find(defaultDimension);


        return dimStorage.getDisplayValueByDimensionAttribute(DimensionAttribute::findByName(dimName).RecId);

    }


    /// <summary>

    /// Method created for converting numbers to text in Arabic currency format.

    /// </summary>

    /// <param name = "_num"></param>

    /// <returns></returns>

    static TempStr numeralsToTxt_AR(real _num)

    {

        int     numOfPennies = any2int(decRound(frac(_num), 2) * 100) mod 100;

        real    test         = _num - frac(_num);


        int64   numOfTenths;

        str 20  ones[19], tenths[9], hundreds, thousands, millions, billions, trillions;


        int64   temp;

        str 200 returntxt;

        int64   testLoc;

        int     fils;


        real modOperator(real a1, real a2)

        {

            int tmpi;

            real tmp1, tmp2;

            tmp1 = a1 / a2;

            tmpi = real2int(tmp1);

            tmp2 = tmpi;

            return (tmp1 - tmp2)*a2;

        }


        real checkPower(real  _test, int64 _power)

        {

            int64   numOfPower;


            if (_test >= _power)

            {

                testLoc = any2int64(_test);

                numOfPower = testLoc div _power;


                if (numOfPower >= 100)

                {

                    temp = numOfPower div 100;

                    returntxt = returntxt + ' ' + ones[temp] + ' ' + hundreds;

                    numOfPower = numOfPower mod 100;

                }

                if (numOfPower >= 20)

                {

                    temp = numOfPower div 10;

                    returntxt = returntxt + ' ' + tenths[temp];

                    numOfPower = numOfPower mod 10;

                }

                if (numOfPower >= 1)

                {

                    returntxt = returntxt + ' ' + ones[numOfPower];

                    numOfPower = numOfPower mod 10;

                }

                switch(_power)

                {

                    case 1000000000000 :

                        returntxt = returntxt + ' ' + trillions;

                        _test = modOperator(_test, 1000000000000.00);

                        break;

                    case 1000000000 :

                        returntxt = returntxt + ' ' + billions;

                        _test = modOperator(_test, 1000000000);

                        break;

                    case 1000000 :

                        returntxt = returntxt + ' ' + millions;

                        _test = modOperator(_test, 1000000);

                        break;

                    case 1000 :

                        returntxt = returntxt + ' ' + thousands;

                        _test = modOperator(_test, 1000);

                        break;

                    case 100 :

                        returntxt = returntxt + ' ' + hundreds;

                        _test = modOperator(_test, 100);

                        break;

                }

            }

            return _test;

        }




        ones[1] = "@SYS26620";

        ones[2] = "@SYS26621";

        ones[3] = "@SYS26622";

        ones[4] = "@SYS26626";

        ones[5] = "@SYS26627";

        ones[6] = "@SYS26628";

        ones[7] = "@SYS26629";

        ones[8] = "@SYS26630";

        ones[9] = "@SYS26631";

        ones[10] = "@SYS26632";

        ones[11] = "@SYS26633";

        ones[12] = "@SYS26634";

        ones[13] = "@SYS26635";

        ones[14] = "@SYS26636";

        ones[15] = "@SYS26637";

        ones[16] = "@SYS26638";

        ones[17] = "@SYS26639";

        ones[18] = "@SYS26640";

        ones[19] = "@SYS26641";


        tenths[1] = 'Not used';

        tenths[2] = "@SYS26643";

        tenths[3] = "@SYS26644";

        tenths[4] = "@SYS26645";

        tenths[5] = "@SYS26646";

        tenths[6] = "@SYS26647";

        tenths[7] = "@SYS26648";

        tenths[8] = "@SYS26649";

        tenths[9] = "@SYS26650";


        hundreds    = "@SYS26651";

        thousands   = "@SYS26652";

        millions    = "@SYS26653";

        billions    = "@SYS26654";

        trillions   = "@SYS101697";



        test = checkPower(test, 1000000000000);

        test = checkPower(test, 1000000000);

        test = checkPower(test, 1000000);

        test = checkPower(test, 1000);

        test = checkPower(test, 100);


        if (test >= 20)

        {

            testLoc = any2int64(test);

            numOfTenths = testLoc div 10;


            returntxt = returntxt + ' ' + tenths[numofTenths];

            numOfTenths = numOfTenths mod 10;

            test = real2int(test) mod 10;

        }

        if (test >= 1)

        {

            numOfTenths = real2int(test);

            returntxt = returntxt + ' ' + ones[numOfTenths];

        }


        if (numOfPennies)

        {

            returntxt = returntxt + ' ' + "Dirhams" + ' ' + "@SYS5534";

            fils = numofPennies;

            if(fils)

            {

                if(fils >=20)

                {

                    numofTenths= fils DIV 10;

                    returntxt=returntxt + ' '+tenths[numofTenths];

                    numofTenths = numofTenths MOD 10;

                    fils=fils MOD 10;

                }

                if(fils >=1)

                {

                    numOfTenths=fils;

                    returntxt=returntxt+ ' '+ones[numOfTenths];

                }

                returntxt = returntxt + ' ' + 'Fils';

            }

        }

        else

        {

            returntxt = returntxt + ' ' +"Dirhams" + ' ' + "@SYS5534" + ' ' + '0 Fils';

        }


        return returntxt;

        

    }


}


====================================================================


5) create action menu item 





Comments

Post a Comment

Popular posts from this blog

Customization on Sales invoice Report in D365 F&O

-
 We have a requirement to create a new design and show some new fields in the sales invoice report.  So for that we need to consider the below steps :-  1) Controller class than extends standard class and create alogic. 2) Create a print management class to call the new design. 3) Extension of DP class to write a logic. 4) Report design. ==================================================================== 1) Controller class : -  /// <summary> /// Extension of the controller class to call the custom design of report "MMSSalesInvoice.report" for MMS legal entity. /// </summary> class MMSSalesInvoiceController extends SalesInvoiceController {        public static MMSSalesInvoiceController construct()         {             return new MMSSalesInvoiceController();         }         public static void main(Args _args)         { ...
Read more

75) COC - Create a coc of the table modified method

-
 /// <summary> /// The <c>AvaEntAssetChecklistVariableOptionsDbt_Extension</c> contains the TransMountain extending logic for the <c>EntAssetChecklistVariableOptions</c> table. /// </summary> [ExtensionOf(tableStr(EntAssetChecklistVariableOptions))] final class AvaEntAssetChecklistVariableOptionsDbt_Extension {     /// <summary>     /// Processes the modified field event for TransMountain fields.     /// </summary>     /// <param name = "_fieldId">reference to field id</param>     public void modifiedField(FieldId _fieldId)     {         EntAssetChecklistVariableOptions entAssetChecklistVariableOptions;         next modifiedField(_fieldId);         // Default must be marked for only one check list line for the varaible.         if (_fieldId == fieldNum(EntAssetChecklistVariableOptions,...
Read more

46) D365 FO: SHAREPOINT FILE UPLOAD USING X++

-
Image
Requirement : Uploading a file in Sharepoint online using X++ and inside from Microsoft Dynamics 365 finance and operations. Prerequisites: The current user id of Microsoft Dynamics 365 Finance and operations should have proper rights and privileged in Sharepoint Online. Sample Code: In this scenario i am creating a text file in Microsoft Dynamics 365 finance and operations and then uploading its memory Stream in Sharepoint online using X++ . You can modify the X++ code in D365 finance and operations as per your need. class RunnableClassRun { /// /// Runs the class with the specified arguments. /// /// The specified arguments. public static void main(Args _args) { System.UriBuilder builder = new System.UriBuilder(“ https://XXXXXX.sharepoint.com&#8221 ;); str extId = xUserInfo::getExternalId(); Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider provider; Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation documentLocation ...
Read more