Business Intelligence and Reporting using Power View in Microsoft Dynamics AX 2012 R2/R3

Business Intelligence and Reporting using Power View in Microsoft Dynamics AX 2012 R2/R3

Most of us must be already aware of the new Power View reporting capabilities which is now fully integrated with Microsoft Dynamic AX 2012 R2, starting with the CU6 release.

Getting real time access to your key business data anywhere, anytime, in any device is the real key to success and can help manage your business more efficiently by allowing the staff and executives to take more informed business decisions on time.

Furthermore, the capability of being able to create, modify and view key business reports online anywhere in any device, can prove to be a real differentiator, in how your staff react to business situations, by getting most recent data from their Dynamics AX ERP system with few clicks.

With Microsoft Dynamics AX 2012 R2(CU6 Onwards) and R3, Microsoft SQL Server Analysis Services Cube and the Power View tool, your organizations BI reporting capabilities just got much easier.

In today’s discussion, we will take a look at the three key scenarios of the Power view reporting.

View a Pre-built Report in Power View online:

Once a Power View report has been designed, it can displayed on the employee role centers. Additionally, employees can add quick links to the Power View reports on their role center pages.

For this demonstration, I will use one of the Region Wise Sales Performance Report, which was already built and is displayed on my role center as well as I have added a quick link to the report on my role center page. Follow the steps below.

  1. Launch Enterprise Portal and navigate to the My Links area of the home page.
  2. Click Sales Performance report.

image

3. Notice that system launches the Region Wise Sales Performance report online in Power View. The report shows the different regions of sales in Bing Maps and the details of the sales are displayed on the right hand side, by customer group, sales category etc.

image

4. The screenshots below shows another layout of the Sales Performance report in different layouts which the Power View tool allows to configure.

image

image

Create or Modify a Report in Power View online:

  1. If you are provided with the required access and security permissions, you can create your own reports using the existing cubes or by adding additional data source as you need. Additionally, you can click the Analyze date button most of the list pages in AX rich client, which will launch the Power Viewer report designer and you can design your own reports by selecting the fields, design layouts, and several other options.
  2. In this case, I will select the Project Accounting cube and design a simple report of comparison of Project actuals vs budgeted by project category.
  3. Select the Project Accounting Cube and Launch Power View tool. You can create the design layout of your report by simply selecting and arranging the data fields you want. Refer to screenshot below, in which I have prepared the simple actual Vs. budgeted amounts by project category for each of the Projects.

image

4. From AX client, you can navigate to any list page such as HRM > Workers and click Analyze Data button on the action pane, to launch the Power View Report designer tool. In the screenshot below I have designed a very basic report of the worker skill/education details by launching the Power View from Workers list page.

image

Short and simple for today’s post. Till next time !!

-Sandeep

Lifecycle Services for Microsoft Dynamics – More Control over your ERP implementation from Presales to Go-Live

Lifecycle Services for Microsoft Dynamics – More Control over your ERP implementation from Presales to Go-Live

Microsoft Dynamics Lifecycle Services provides a cloud-based collaborative work space that customers and their partners can use to manage Microsoft Dynamics AX projects from pre-sales to implementation and operations.

Based on the phase of your project and the industry you are working in, the site provides checklists and tools that help you manage the project. It also provides a dashboard so that you have a single place to get up-to-date project information.

Click HERE to get started.

Another Cumulative Update 6 For Microsoft Dynamics AX 2012 R2

Another Cumulative Update 6 For Microsoft Dynamics AX 2012 R2

Microsoft continues its commitment to customers success through regular release of cumulative updates.

CU 6 is now released which primarily introduces two types of enhancements and updates.

1. Business Processes.

2. Regulatory Updates.

CU7 is slated to be released during end of OCT, 2013.

Note: Note the change in numbering for the cumulative updates which will be released for AX 2012 R2 going forward.

Read the complete details of the enhancements and updates here on Microsoft Dynamics AX Product Team Blog.

Regards

Sandeep

Delta Airlines takes off with Microsoft Dynamics for Retail and Windows phone platform

Delta Airlines takes off with Microsoft Dynamics for Retail and Windows phone platform

Do not get surprised when you get to see “Microsoft Dynamics for retail” in ACTION in a Windows phone mobile device(Nokia Lumia 820) by the Customer service crew of Delta Airlines on the flight.

It is great to see the major technologies coming together to offer simple and high end platform for customers.

I am glad to be aware of this news and I am sure everyone in the Dynamics community will like this. Thanks to all the technology partners who worked together to make Microsoft Dynamics “LIVE” in the sky. :)

Read the complete news here on the Dynamics Community blog.

New enhancements in Cash and Bank Management Module of Microsoft Dynamics AX 2012 R2

New enhancements in Cash and Bank Management Module of Microsoft Dynamics AX 2012 R2

Hello,

Today, I wanted to discuss few of the enhancements which have been introduced in AX 2012 in the Cash and Bank Management module. Most of us must be already aware of these, but I just want to discuss these in this forum as I found these enhancements very useful.

AX 2012 Icon

Follow the points below which describes the major enhancements.

  1. Additional validation on the Bank account number and routing number(USA and Canadian Bank accounts):
  • Earlier in AX 2009, there was no real-time validation on the bank account number and routing number fields and users could enter any random values into those fields.
  • In AX 2012, new validation have been introduced where only valid bank account numbers  and routing numbers are accepted.
  • These validation are triggered based on the “Address” which you select for the bank account. If the Country of the address is USA, then the bank account number and routing number validation are thrown accordingly. If the Country of the address is CANADA, then the bank account number and routing number validation are thrown as per Canadian bank validation.
  • To verify this go to Cash and Bank Management > Common > Bank Accounts.
  • Select USA OPER bank account.
  • Verify that routing numbers and bank account number fields accept random values with no validation.
  • Now specify an address for the bank under the Address fast tab and System will throw the necessary validation errors/warning for the bank account number and routing number fields when you save the record(In Conjunction with routing number type).

image

image

image

2. Bank Credit Limit check during vendor payments with bank as the offset:

  • We can now define credit limits and credit limit tolerance thresholds for a bank account in AX 2012. These credit limit check gets triggered during making a vendor payment(with bank as offset).
  • System will check for the available balance in the bank account and will throw required tolerance message as per the credit limit specified for the bank.
  • We can define Credit limit related parameters in the Cash and Bank Management parameters form.

image

  • Define the credit limit for a selected bank account under General fast tab.

image

  • Note that the available balance in the USSI OPER bank(CONTOSO demo data) is $72771678.46. We will now record a vendor payment journal with an amount $7277378.46(Bank balance + Credit Limit) and validate the journal. System will successfully validate the journal and say It is Ok.

image

  • So system now allows you pay an amount which is within the (Available balance+Credit limit) range of for the bank. If we try to make a payment for an amount $727737946, this will exceed the credit limit and system will stop us from validating and posting it.

image

       3. Inactivate a Bank account:

  • In AX 2009, there was no way to deactivate or close a bank account from the Bank sub ledger. The only way possible was to close the GL account(Cash account) related to the bank account.
  • Now in AX 2012, we can inactivate bank accounts from the bank sub ledger. The steps below explains this change.
  • Go to Cash and Bank Management and Select a bank account.
  • Expand the General fast tab.
  • You can provide a from and To dates for which the back account will be active.
  • Additionally you can set bank account status to 3 different options such as

Active for all transactions – Bank account is active and transactions are allowed.

Inactivate for new transactions – Bank account is inactive and no new transactions are allowed.

Inactivate for all transactions – Bank account is inactive and no transactions are allowed

BankAccountStatus

    

   4. Bank Account Statement Import and advanced reconciliation

  • A much-needed functionality. AX 2012 now allows importing electronic bank statements and then performing advanced reconciliations.
  • To be able to use the back statement import and reconciliation functionality, you will need a series of one time configurations. Below are the high level steps to do all required configurations .
  • The first step is to configure the setup required for the electronic bank statement import which includes the parameters, bank statement format, transaction code mapping and bank reconciliation matching rules etc.

image

  • The next step is to set up the required AIF inbound ports(Services) and import formats etc.

image

  • After all the setups are done, finally we can import the electronic bank statements(XML/XSLT) formats and perform advanced reconciliation.

image

In my next post, I will explain in detail the step by step procedure for setting up bank accounts for statement import and advanced reconciliation  procedure.

Till next time !! keep exploring.

Regards

Sandeep

Time and Attendance Registration functionality of Microsoft Dynamics AX 2012

Time and Attendance Registration functionality of Microsoft Dynamics AX 2012

Hello Everyone,

I had been away from my blog since over a month time because of my work and long vacation. It feels nice again to write here and share knowledge with you all. We all thrive to learn new things always and so do I. Today, I decided to get my hands dirty on the new “Time and Attendance registration” functionality which is introduced in Microsoft Dynamics AX 2012 and share what I have learnt so far. Hope everyone finds this post useful.

Dynamics AX 2012 has been amazing with its all new exciting range of features. I found the Time and Attendance registration feature pretty useful and decent and I am sure Microsoft will make it more powerful in the coming versions. Let me first explain you what this functionality is and how it can benefit organizations.

Organizations of all sizes use Time and attendance systems to record when their employees start and stop work, and the department where the work is performed. It’s also a  common practice to track meals and breaks, the type of work performed, and the number of items produced. The Time and attendance functionality of AX 2012 enables organizations to track the time and attendance including the jobs on which the workers are working on a given day. Using this functionality the workers can register various types of times such as Clock in, Clock out, Absence, Breaks, Flex time, Overtime etc. This functionality will find a lot of traction in the manufacturing industries where lot of the workers are paid based on their working profiles and make time registrations.

The below diagram depicts the overall flow of the worker’s time registration process in Dynamics AX 2012.

image

Now that we know an overview of the Time and attendance functionality, let us look at the setups which we need to make in order to use this functionality.

Basic setup for T&A:

There are a bunch of setups present under the Time and attendance form in the human resource module. Each of these setups as a significance and I will not explain those here as I will primarily highlight the process of the time and attendance recording in this post.

image

Calculation and Approval groups:

  • The calculation groups are a way to categorize the workers. For example, workers working in ‘same shifts’ or workers working in ‘same teams’. This helps the team lead/manager who calculates the time registrations for approval, to calculate the time registrations for a bunch of workers falling in same calculation group and this ensures reduced time in calculation.
  • Navigate to HR > Setup > Time & Attendance > Groups > Calculation groups and create a new record.
  • Optionally, you can password project the calculation groups by providing a password under the General tab.

image

  • An approval group is logically same as the calculation group and typically consists of a set or group of workers.
  • Navigate to HR > Setup > Time & Attendance > Groups > Approval groups and create a new record.

image

Indirect activities and Absence codes:

  • Dynamics AX 2012 enables workers of an organization to register time on several indirect activities which they may work on a given day and it also allows to register hours on absence codes if they are absent from working hours. Few examples of indirect activities are “Meetings”, “Travel” etc. .(Activities which are not directly related to projects, jobs)
  • Four different types of time registrations are allowed for the indirect activities which are “Job”, “Break”, “Switch code” and “On call”. Indirect activities are ideally configured under an activity group.
  • Go to HRM > Setup > Time & Attendance > Indirect activities.
  • Create a new record and select the Registration type from the four option as per your requirement. In my example, I will select Job.

image

  • Click Activities button.
  • Create new indirect activities such as Test1 and Test 2.
  • Check the Registration check box under General tab and specify account and offset account if needed.

image

  • You can also setup cost for the indirect activities by clicking the Cost button. Refer to screen below.

image

  • Similarly absence groups and codes can be configured for recording time for absence from work. If “Education break” is an example of absence group, then absence codes under it could be “ Training”, “Workshops”, “Long term education” etc.
  • Configure the absence groups and codes under HRM >  Setup > Absence > Absence groups.

image

Setup Worker/Employee for Time Registration:

  • Now that we have done most of the setups required for the Time and attendance function, let us setup the a worker and enable him for time registrations.
  • AX 2012 allows the workers to make time registrations in two forms. The Electronic timecard form and the Job registration form. In this case, we will just enable a worker to make registrations in Electronic time card form.
  • To do this, go to HR > Common forms > Workers > Workers.
  • Select any worker and double-click.
  • Click the Employment tab and expand the Time registration fast tab to set up parameters related to activating the worker for time registrations.

image

Setup Work Time Profiles:

  • Different work time profiles can be configured for workers working in different working hours and tasks. The worker time profile basically identifies the nature of time registrations which the workers is supposed to make in a given day. For example a worker can clock in, work for standard working hours, take a break, work overtime and clock out. The work time profile basically will contain these profile types.
  • The work time profiles are configured and setup for group of workers usually. For example, workers working on Day shifts may be allocated with a  “day shift” profile and workers working in “evening shift may be allocated with a “evening shift” profile.
  • When the work time profiles are setup in the worker time registration, then the time and attendance system automatically detects the profile depending on the clock-in time of the employee. The work time profile functionality and concept is large and I will cover this in detail in one of my upcoming posts.

image

Time and Attendance Registration Process in AX 2012:

  • In this post of mine, I will cover only the time registration process using the Electronic time card functionality. I will try to discuss the Clock-In/Clock-Out time registration functionality in posts later.
  • As we have set up Ahmed to register electronic time cards, we will now see the steps which he will follow to make time registrations.
  • Go to Home > Time and Attendance > Electronic Timecard
  • Select the worker. (Ahmad in this case)

image

  • System will automatically show the profile for the worker and the profile date will default to the current date/day. You can modify the profile date.
  • If you want your registrations not to be modified by team lead or your manager, then you can Lock the profile date by clicking the Lock button.
  • See the screen below in which I have entered a standard day’s time registrations.

image

  • After filling in the time card, click Check button. System will validate that all your time registrations are fine and checked for any violations.
  • Finally, Click Transfer to, transfer the time registration for calculation and approval.

Calculate and Approve Time & Attendance Registrations:

  • The next task is for the supervisors or the team members to calculate the time registrations done by their workers.
  • During the calculation, the worker time registrations are calculated against the work time profile. If there are any missing registrations, then it is automatically detected during the calculation and system will generate errors indicating these. The team lead or supervisor can then correct and recalculate the registrations.
  • To do this go to HRM > Common > Time and Attendance > Calculate.

image

  • Select the calculation group and date. The calculation group will enable the supervisor to mass calculate the time registrations for the workers here.
  • Click Ok button.
  • Note that, system may prompt for password if you have configured in the calculation group setup.
  • On the Calculate form, verify all the information and click Update > Calculate button and then click Ok on the

image

  • After the time registrations are calculated and verified, it needs to be approved. Approval is ideally done by the HR manager/Payroll manager. This is to ensure that the time registrations looks all correct before they are committed and posted in other modules.
  • To approve the time registration, Go to HRM > Common > Time and Attendance > Approve.

image

  • On the Approve form, click Update > Approve and then click Ok on the batch job.

image

  • You will get a confirmation that the registration for approved without errors.
  • The next task for the payroll manager or whoever approves the time registrations is to transfer the time registrations. This will ensure that all the transactions are posted successfully to the jobs on which registrations were made.
  • To transfer the time registration click Update > Transfer and then click Ok button on the dialog.

image

  • Notice that the TR lines will get transferred from the Approve screen.

image

  • Not let us verify the postings in the different modules. For example in this time registration Ahmed had entered time on the project 10001. So if you go to project 10001 and then to Posted transactions, you will find the the transactions created by the TR system. See screen below.

image

In this post , I covered,

  • The overall set up required to use the time and attendance system in AX 2012.
  • How to enable workers to make time registrations.
  • The actual time registration process.
  • And the calculation, approval and transfer process for the time registrations. Consider these as the first step to learn this new functionality. I am breaking it down as this is getting way too longer now. Smile

In my next post, I will cover the remaining part of this functionality.

  • More about the absence management.
  • Time and attendance workflow.
  • And the complete integration with pay agreements and payroll process.
  • Time registration using the Clock in/out punch clock
  • Which industries types can use and benefit from this ?

Well ! That is it for now.

Till next time…Keep exploring.

Thanks

Sandeep