Friday 7 March 2014

SSRS report in Dynamics AX 2012

 

SSRS report in Dynamics AX 2012

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.

Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it.
we can use already existing queries which are in AOT for report as datasource.


Open visual studio 2010 and lets us create a new Dynamics AX project.
click on file menu >> New project as shown below





Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_ReportNewModel as shown below


Now add a new report to the newly created report Model as shown below. Right click on the SR_ReportNewModel from the solution explorer, Add >> Report




Rename the report to SR_InventTable by right click and rename option on the newly added report.

Right click on the datasets node and chose the option New datset. Rename it to InventTable and go to query property and click on the ellipsis (…) button to select the query which we have created as shown below.





It will open with list of Dynamics AX Queries from which we should select our query “SR_InventTableQuery” and click on next button as shown below


Select the list of fields and display methods you want to see on your report.
and click on Ok Button.


Work on the design part.

Select the InventTable dataset and drag and drop on to your designs node as shown below. It will create autodesign for you .



Set the style template to “TableStyleTemplate” as shown below.


On to autodesigns, we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below


Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it is hidden. Lets unhide or make it visible it as we want to display the items based on the company [dataaread id] selection by the user.




Preview the data by right clicking the autodesign and by chosing option preview as shown below




To deploy the report to AOT. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.




Open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects .

Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventTable report.


Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.




Well you can add this menu item to relevant menu.

Open the report, Right click on the newly created menu item and select open.


we have option of generating the report based onthe dataareaid and since we have added range ItemId to the query – we get twow ranges as shown above.

Below is the report.





AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration.



Thursday 6 March 2014

Get Dimension Combination values

 

Get Dimension Combination values

 These functions used to read the individual ledger dimension combinatio values.
private void Krishh_getDimensionValues(LedgerDimensionAccount _dimensionRecid)
{
 
    DimensionAttributeLevelValueAllView dimAttrView;
    DimensionAttribute                  dimAttr;
 

        while select DisplayValue from dimAttrView
            where dimAttrView.ValueCombinationRecId == _dimensionRecid
            join BackingEntityType from dimAttr
                where dimAttr.RecId == dimAttrView.DimensionAttribute
        {
            switch (dimAttr.BackingEntityType)
            {
                case tableNum(DimAttributeMainAccount):
                    info(strFmt(“Main Account: %1″, dimAttrView.DisplayValue));
                    break;
             
                case tableNum(DimAttributeOMBusinessUnit):
                    info(strFmt(“Business Unit: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeCustTable):
                    info(strFmt(“Customer: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeOMDepartment):
                    info(strFmt(“Department: %1″, dimAttrView.DisplayValue));
                    break;

                case tableNum(DimAttributeHcmWorker):
                    info(strFmt(“Worker: %1″, dimAttrView.DisplayValue));
                    break;
            }
        }
 
 
}

private DimensionValue krishh_getDimensionValueByName (LedgerDimensionAccount _dimensionRecid, Name _name)
{
    DimensionAttributeLevelValueAllView dimAttrView;
    DimensionAttribute                  dimAttr;

 
        Select DisplayValue from dimAttrView
            where dimAttrView.ValueCombinationRecId == _dimensionRecid
            join BackingEntityType from dimAttr
                where dimAttr.RecId == dimAttrView.DimensionAttribute
&& dimattr.Name==_name;

return dimAttrView.DisplayValue;
}


Get dimension values In AX 2012

 

Get dimension values In AX 2012


To get the master Data for Dimension attributes we can use two service classes.

1. DimensionService
2. DimenisionValueService

we will see what Dimension Service class is providing

1. getActiveDimensionsFromLedger()- Gets active dimensions in current company which will return the DimensionContract class collection List.
2. getDimensions(AccountStructureContract _accountStructureContract)-Processes an incoming account structure contract and return a list of financial dimensions for a specific account structure.
3. getDimensionsAll()-Gets a list of all financial dimensions, which returns a A String list of dimension values using DimensionContract class.

we will see what DimensionValue Service class is providing
using this class we can create/Get DimensionValues for the specific dimension attribute.
Methods:

1. createDimensionValue(DimensionValueContract _dimensionValueContract)-Processes an incoming dimension value contract and creates a financial dimension value for a specific financial dimension.

2. getDimensionValues(DimensionContract _dimensionContract)- processes an incoming dimension contract and returns a list of financial dimension values for a specific financial dimension. and returns the list of the dimension values for that specific dimension attribute.
we can use this service classes and consume in any external application and call this service methods.

by using these two services we can get the dimension values easily.


static void Krishh_getDimensionValues(Args _args)
{
    List                            dimensionAttributeList;
    ListEnumerator                  listEnumerator;
    DimensionService                dimensionService;
    DimensionContract               dimensionContract;
    AccountStructureContract        accountStructureContract;
   
    dimensionService            = new dimensionService();
    accountStructureContract    = new AccountStructureContract();
    accountStructureContract.parmName("Account Structure - P&L");
    dimensionAttributeList      = dimensionService.getDimensions(accountStructureContract);
    listEnumerator  = dimensionAttributeList.getEnumerator();
    while (listEnumerator.moveNext())
    {
        dimensionContract   = listEnumerator.current();
        info(strFmt("%1, %2",dimensionAttributeList.elements(), dimensionContract.parmDimensionName()));
    }
}


Wednesday 5 March 2014

WORKFLOW in MS Dynamics AX 2012




Workflow Configuration


There are three batch jobs that manage workflow processing. These jobs are all run on the Application Object Server (AOS) using the Batch system. To set this up, run System Administration > Setup > Workflow > Workflow

infrastructure configuration. Enter batch groups for each process. Batch groups can be used to control which AOS instance each workflow batch job is run on.


1) Create a Workflow Category


1.  Open the AOT.


2.  Expand the Workflow node. 
3. Right-click on the Workflow Categories node and select New Workflow Category. A new workflow category called Workflow Category1 will be created. 
4.  Right-click on the newly created workflow category and select Properties.

5.  Change the name property to SalesCategory.
6.  Change the label property to Sales workflows.
7.  Change the Module property to SalesOrder.
8.  Right-click on the newly created workflow category and select Save.

Create a Workflow Category (Step by Step with screen shot)
 





2)  Create a Query    

1.  Open the AOT.
2.  Right-click on the Queries node and select New Query.
3.  Rename the query to SalesCreditLimitApproval.
4.  Expand the newly created query.
5.  Open another AOT window.
6.  Expand Data Dictionary > Tables.
7.  Find the table SalesTable

8. Drag the SalesTable table to the Data Sources node of the SalesCreditLimit Approval query.

9.  Expand the SalesTable_1 node
10.   Right-click on the Fields node and select Properties.
11.   Set the Dynamics property to Yes and close the property sheet. 
12. Right click on the SalesCreditLimitApproval query and select  Save.
Create a Workflow Category (Step by Step with screen shot)
 
3)  Create a Workflow Type    

1.  Open the AOT.
2.  Expand the Workflow node. 
3.Right-click on the Workflow Types node and select Add-ins > Workflow type wizard.
4.  Click Next.
5.  Enter SalesCreditLimitAppr in the name.
6.  Enter SalesCategory in the Category.
7.  Enter SalesCreditLimitApproval in the query.
8.  Enter SalesTable in the Document menu item.

9.  Click Next
10.Click Finish. A development project with a number of newly created elements will be displayed.

Create a Workflow Type (Step by Step with screen shot)



Enable Workflow on a Form

Add a WorkflowState Field  
1.  Open the AOT.
2.  Expand Data Dictionary.
3.  Right-click on Base Enums and select New Base Enum.
4.  Rename the new enum to SalesCreditLimitApprovalStatus
5.Add four elements to the Enum called NotSubmitted, Submitted, Approved, Rejected.
6.  Expand Tables > SalesTable.
7.  Right-click on Fields and select New > Enum.
8.  Right-click on the newly created field and select Properties.

9.  Change the Name property to CreditLimitApprovalStatus
10.Change the EnumType property to SalesCreditLimitApprovalStatus.

11.   Right-click on the SalesTable node and select Save.
 


Add a WorkflowState Field(Step by Step with screen shot)


Enable Workflow on the Form 

1.  Open the AOT.

2.  Expand Tables > SalesTable.

3.  Create a new method and add the method in the following code. 


boolean canSubmitToWorkflow(str _workflowType = '')

{

amountMST creditBalance; custTable custTable;

;

if (!this.CreditLimitApprovalStatus = = SalesCreditLimitApprovalStatus::NotSubmitted) return false;

custTable = this.custTable_InvoiceAccount(); if (!custTable.CreditMax)

return false;

creditBalance = custTable.CreditMax - custTable.balanceMST();

if(this.amountRemainSalesFinancial()+this.amountRemainSalesPhysical() < creditBalance) return false;
return true;
}
 


4.  Save the changes made to the table.
5.  Expand Forms > SalesTableListPage > Designs.
6.  Right-click on the design node and select Properties.
7.  Change the WorkflowEnabled property to Yes
8.  Change the WorkflowDatasource property to SalesTable.
9.  Change the WorkflowType property to SalesCreditLimitAppr 
10. Save your changes to the form  


Enable Workflow on the form(Step by Step with screen shot)


 
Create a Submit to Workflow Class

1.  Press Ctrl+Shift+P to open the development projects window.

2.  Expand Private.

3.  Double-click on SalesCreditLimitApprWFType development project. 
4.In the development project find the class SalesCreditLimitApprSubmitManager.
5.Create a new method called submit, and copy the following code for this method.


void submit(Args args)

{

// Variable declaration.

recId recId = args.record().RecId; WorkflowCorrelationId workflowCorrelationId; // Hardcoded type name

WorkflowTypeName workflowTypeName =workflowtypestr(SalesCreditLimitAppr);

//  Initial note is the information that users enter when they

//  submit the document for workflow. WorkflowComment note =""; WorkflowSubmitDialog workflowSubmitDialog; SalesTable SalesTable;

//  Opens the submit to workflow dialog.

workflowSubmitDialog = WorkflowSubmitDialog::construct(args.caller().getActiveWorkflowConfiguration()); workflowSubmitDialog.run();

if (workflowSubmitDialog.parmIsClosedOK())

{ 
recId = args.record().RecId; SalesTable = args.record();

//  Get comments from the submit to workflow dialog. ttscommit;
}
catch(exception::Error)
{
info("Error on workflow activation.");
}
}
args.caller().updateWorkFlowControls();
}

note = workflowSubmitDialog.parmWorkflowComment(); try

{

ttsbegin;

workflowCorrelationId = Workflow::activateFromWorkflowType(workflowTypeName,recId,note,NoYes::No); SalesTable.CreditLimitApprovalStatus = SalesCreditLimitApprovalStatus::Submitted;

//  Send an Infolog message.

info("Submitted to workflow.");



6.  Modify the code for the main method as shown in the following code.

7.  Press F8 to save and compile the code.
8.  Find the menu item SalesCreditLimitApprSubmitMenuItem.
9.  Change the Label property to Submit
10.Right-click on the SalesCreditLimitApprSubmitMenuItem menuitem and select Save.

Create a submit to Workflow Class (Step by Step with screen shot)



 
Create a Workflow Approval

1.  Open the AOT.

2.  Expand the Workflow node.

3.  Right-click on Approvals and select Add-ins > Approval wizard.

4.  Click Next.

5.  Enter SalesCLApproval in Name.

6.  Enter SalesCreditLimitApprDocument in Workflow document.

7.  Enter Overview in Document preview field group.

8.  Enter SalesTableListPage in Document menu item.
9.  Click Next
10.Click Finish. A development project with a number of newly created elements is displayed.
11.Drag SalesCLApproval approval to the Supported elements node on the SalesCreditLimitAppr workflow type.
12.   Save your changes to the SalesCreditLimitAppr workflow type 


Creating a Workflow Approval (Step by Step with screen shot)
 











Create Event Handlers 

Write code for Event Handler

1.  Press Ctrl+Shift+P to open the development projects window.
2.  Expand Private.

3.  Double-click on SalesCreditLimitApprWFType development project.
4.  In the development project find the class SalesCreditLimitApprEventHandler

The following code needs tobe added to the completed method of the

SalesCreditLimitApprEventHandler class.


public void completed(WorkflowEventArgs _workflowEventArgs)

{
  SalesTable SalesTable;

  select forupdate SalesTable where SalesTable.RecId ==   _workflowEventArgs.parmWorkflowContext().parmRecId(); if(SalesTable.RecId)

{

SalesTable.CreditLimitApprovalStatus = SalesCreditLimitApprovalStatus::Approved; SalesTable.write();

}

}
 



Add Element Level Event Handlers



1.  In the AOT locate the class SalesCLApprovalEventHandler. This class was created by the Approval element wizard.
2.  Add the following code in the returned method 


public void returned(WorkflowElementEventArgs _workflowElementEventArgs)

{

SalesTable SalesTable; ttsbegin;

select forupdate SalesTable where SalesTable.RecId ==
_workflowElementEventArgs.parmWorkflowContext().parmRecId()

;

SalesTable.CreditLimitApprovalStatus = SalesCreditLimitApprovalStatus::Rejected; SalesTable.update();

ttscommit;

}
 

3.  Save your changes to the class.

4.  In the AOT, right-click on the AOT node and select Incremental CIL generation from X++





Author a Workflow


1.The workflow category we created in the first procedure needs to be added to the main menu. Create a new display menu item called WorkflowConfigurationSales.

2.  Set the label to Sales workflows.
3.  Set the object to WorkflowTableListPage.

4.  Set the EnumTypeParameter to ModuleAxapta.

5.  Set the EnumParameter to SalesOrder.

6.  Add the new menu item to SalesAndMarketting > Setup menu. 
7.In the property sheet for the new node in the menu, set the property IsDisplayedInContentArea to Yes.

8.  Save your changes to the menu.

9. Open the main menu and select Sales and Marketting > Setup > Sales workflows.
10.  Click New.

11.  Select SalesCreditLimitApprType and click Create workflow

The workflow editor window opens.

12.  Drag SalesCLApprovalApproval approval from the Workflow elements window to the Workflow window.

13.  Drag the bottom node of the Start box to the top node of the Approval box.
14.  Drag the bottom node of the Approval box to the top node of the End box.

15.  Double click on the Approval box.

16.  Click Step 1

17.  Click Assignment.

18.  On the Assignment type tab, select User

19.  On the User tab, double click on Sammy

20.  Click on Basic Settings and then enter a subject and instructions for the approval
21.  Click on Close

22.  Click on Save and Close

23.  Enter some notes for the new workflow if you wish.

24.  Select to Activate the new version

25.  Click OK


Authoring a Workflow (Step by Step with screen shot)








Test the Workflow


1.  In the application workspace, navigate to Accounts receivable > Common > Customers > All customers.
2.  Select a customer and click Edit.

3.  In the Credit and collections fasttab, set a credit limit.
4.  Close the Customers form.

5.  Go to Sales and marketting > Common > Sales orders > All sales orders.

6.  Click New sales order. Enter the customer account modified that

you modified the credit limit for and click ok.

7.  Enter items and quantities in the sales lines so that the balance of the customer plus the total amount on the lines is greater than the credit limit.
8.  The workflow Submit button and dialog should appear.

9.  Click the Submit button and enter a comment.

10.  Wait for the batch job to process the workflow request. This should take one to two minutes.

11.  Select Actions > History. You will see that the document is waiting for approval by the person you assigned to approve it.
12.  Logon to windows as the Sammy using the Switch User option on the Start menu.

13.   Start the Microsoft Dynamics AX client.
14.  Open the Sales order form.
15.   Click the workflow Actions button and select Approve.
16.  Wait one to two minutes for the workflow engine to process the approval.
17.  The workflow has now been approved.