Wednesday, October 1, 2014

My 2nd Dynamics AX MVP Award



I don't like to brag, it makes me feel awkward, but today I received the so much awaited email from Microsoft announcing my renewal of the MVP Award for Dynamics AX. I feel like I've been holding my breath for the last weeks and trying not to get my hopes too high. I am humble and thankful for the Award and the support by the AX community, fellow MVPs and Microsoft employees. From the bottom of my heart, THANK YOU!

The last year has been increasingly interesting and fun, and at times perhaps a bit exhausting. The number of requests from AX professionals have increased and I try my best to give valuable answers to those who reach out. I love to share the knowledge and my passion for Dynamics AX, and I do have high hopes for Dynamics AX as a great platform for building the best ERP solution out there.

A lot of the AX giants out there have been extremely valuable for me when I've been searching forums and blogs for AX content. Being an MVP has made it a lot easier to get to know more people who share the same passion and glow for AX. The AX community is still growing at a fast pace, and all the time new amazing professionals are discovering Dynamics AX, which is great. This makes me humble for having the pleasure of getting this Award, and I probably can't emphasize that enough.

I aim to keep pushing what I hope is valuable content to the community, and if you also have a sharing mentality, you should consider create a site or blog and share. At least join our Dynamics AX Community Site.

Thank you for reading, and thank you for your support!

Monday, September 8, 2014

Installing Management Reporter 2012 for AX2012

In this post I will go through the process of installing Management Reporter 2012 using its own Setup experience. I will also add a Data Mart for Dynamics AX 2012. You know you can install Management Reporter 2012 using Dynamics AX Setup, but you can also install it using the regular MR 2012 Setup. The Dynamics AX Setup experience is a bit easier because it hides some options which the regular installer doesn't. Hence this little blog post.

Installing the Management Reporter Server

Start by downloading, extracting and running Management Reporter Setup.


When the binaries are installed, choose to let the Configuration Console open after pressing Finish.

In this example I will opt for only installing the Application Service and the Process Service. I could select the Dynamics AX Data Mart now, but I'll set it up afterwards to keep this guide a bit simpler.


I don't need the Access Database engine, so I'll ignore this warning.

You need to define a service account for the service and I have already setup and defined an account in the Active Directory for this.


I will create a new database so I need to provide the SQL Server Instance, the name of the database, a passphrase for encrypting the content of the MR database and finally the account that will be automatically added as first admin of the Management Reporter installation.

By default, the Application Service will listen on 4712 by default. I'll stick with the defaults for the sake of everyone's sanity.


Grab yourself a cup of coffee and let it configure.


 When it is done, you have two running services but no configured applications yet. From File menu, choose Configure and run the Configuration wizard.


Choose the proper application, in my example the Dynamics AX 2012 Data Mart.


It will need information on how to connect to the right AOS, so provide the name and port for the AOS, and also the SQL Server instance and database name.

It also wants to know where it can put the Data Mart database. If you're running this wizard in the context of a user that can create the necessary database artifacts you can simply let it do so using Windows authentication.

 When it is done, I recommend restarting both services from the Configuration Console.

You will notice a new task pops up in the Task List asking you to enable the integration.
Before doing so, you need to make sure the Management Reporter 2012 service account has the necessary permissions to Dynamics AX, so the recommended step is to add the service account to AX as a user with System Administrator permissions. Not only will it read out financial data, but also information about companies and users.


When clicking the "Enable Integration" you will be prompted for some credentials. If you user context has sufficient permissions to enable the integration, you can choose Use Windows authentication.

When the integration is enabled, it will immidiately start collecting data from the Dynamics AX database. The next important step is to install the client and apply the Management Reporter license. Otherwise, only two users will be able to play with the reporting.


Installing the client is super easy. The only thing you may need to know is what to put in as server connection for the client.

When you have installed the client, open the designer and open the Tools and Registration to paste in your license key.

Now that was pretty easy and straight forward, wasn't it? :-)

Saturday, August 30, 2014

Permission error when setting up additional SSRS instances

Perhaps you read my article on setting up additional SSRS instances and now you notice each time the Report Server instance is restarted it causes reports to throw a weird error like this:

"Error while setting server report parameters. Error message: The DefaultValue expression for the report parameter 'AX_CompanyName' contains an error:"


You can reproduce this by restarting the SSRS instance and try run any report. The first time it fails, but the second run goes through ok. Now, we can't have that can we?

Back in 2012 Microsoft blogged about this issue and the solution so far is to make some minor adjustments on one of the configuration files.

So let's try that and see if that fixes it.

Head over to the location where the rssrvpolicy.config file is and see if you can open and edit it. My preference is NotePad, but any text editor will obviosuly work.



This file may contain a lot of content, so try search for "Report_Expressions_Default_Permissions" and you should only find one occurrence of that text. Now the value you're looking for is "Execute" and you want to change that to "FullTrust".



Save the file and restart SSRS. Head back to AX and observe the reports run perfectly on the first run!

Good job!

Setting up multiple instances of Report Server for AX2013 R3

The documentation for setting up multiple SSRS instances covers the steps I will illustrate in this post. I did this on AX2012 R3, and in this version it is made a bit easier due to the new PowerShell command for setting up the SSRS configuration files. If you're using a version prior to AX2012 R2 CU7, I highly recommend using the freely available PowerShell scripts from this codeplex project.

Let's begin!

So I prepared by installing three named instances of SSRS, running SQL Server Setup for each instance.


Furthermore I completed the initialization of each of them by making sure they all run under business connector proxy account and had the databases and sites ready.


Next I ran AX2012 R3 Setup and installed the Reporting Extensions, which would make my first SSRS instance prepared for reporting. I opted for having the reports deployed as well.

However, the remaining instances remain without a complete configuration, so let us go ahead and prepare them as well.

Using the PowerShell command introduced after AX2012 R2 CU7 makes this easy. Simply open the Dynamics AX 2012 Management Shell and run this command:

Install-AXReportInstanceExtensions –ReportServerInstanceName AX2012_DEV -Credential contoso\daxbc

Notice that after running this command, the configuration files will be modified and they will have the necessary changes to support Dynamics AX reporting.



Run the command for all the additional instances you're setting up.

So how will each instance know what AOS they bound to? We will drop an AX configuration file into the bin-folder for each Reporting Server. The guide tells us the file needs to have a specific name, Microsoft.Dynamics.AX.ReportConfiguration.axc, so all you need to do is to either create a configuration file or pick one you've already made earlier.

I always create configuration files for all environments, so I just copied the appropriate file for each environment to the bin-folder and renamed it according to the instructions. If you have to create new configuration files using the Dynamics AX Configuration from Administrative Tools, just make sure to leave it pointing at the first original configuration when you're done. If you remember, it was pointing to the same AOS as your first SSRS instance was, and you want to keep it that way. :-)


Notice the filesize. Typically if I see a configuration file that is less than 5kB, it tells me that file does not have an updated WCF-configuration. You know the huge chunk of XML that holds the WCF-configuration inside the AX-configuration file. You really want to make sure your configuration file has a proper WCF-configuration before you continue.

Finally restart the Report Server instances and they should be good to go.

Before deploying reports, you should prepare the settings inside AX. Open a client to each of the environments still missing reporting. Head over to System Administration, Setup, Business Intelligence, Report Services, Report servers. Either create a new configuration or change the one already there. My extra environments where copies from other environments, so I simply changed the existing one. Make sure it points to the right SSRS instance, has the right URLs and is associated with the right AOS (at the bottom of that dialog - so scroll down). If the settings are correct, you should be able to press the "Create report folder"-button and observe the folder be created successfully. That is a good sign! You're ready to deploy reports now.

Deploying reports is just a matter of running another PowerShell command. The whole process may take a while, so spend the time documenting your work and feel good about your achievement.

Publish-AXReport -ServicesAOSName MYAOSSERVER -ServicesAOSWSDLPort 8103 -ReportName *

Notice the name of the server running the AOS is used, along with the WSDL-service port. You'll find the correct WSDL from the Dynamics AX Server Configuration under Administrative Tools on the AOS server.

Finally, you can test the reporting by opening a client, and try run any of the reports under System Administration, like the "Database Log". Don't worry about the report not having any data, as long as it loads.

If you encounter any issues or problems, feel free to comment on this post, or head over the community site and post your concerns on the forum.

Wednesday, August 20, 2014

Upgrade Management Reporter 2012 for ERP

So you installed Management Reporter and have it running like expected. And then later down the road you've been asked to apply the latest (cumulative) upgrade. If you, like me, installed Management Reporter using AX2012 Setup, you will notice the Management Reporter upgrade story involves using Management Reporter Setup, and not AX2012 Setup experience.

No problem! This post by Jill Carter on MSDN Blogs explains the highlights, and I will show you some screenshots from the process. Without any hick-ups this upgrade should over in less than an hour, at least the server bits.

First head over to download page and grab the latest setup with the latest updates. You'll find an upgraded version of the documentation from there as well. At the time of writing this post, the latest documentation is from May 2014. Run, extract and run setup.

Upgrade the Server Components

As Jill explains, you need to upgrade the server bits first, so choose the Server install and run through the setup that will upgrade the services for you. After it is complete, let it open the configuration console as recommended. I highly recommend using the same user for upgrading as you used when you installed in the first place. That way the permissions should already be the properly defined and you won't get into trouble.


When the Configuration Console has loaded you will notice in the bottom left hand side the "tasks" necessary to complete for Management Reporter to be ready on the latest updated version.


So click the "Update the Management Reporter database"-text and let it help you upgrade the database schema to the latest version. A dialog will pop up and you should be able to update the schema in the context of your current user. Hit Update and wait.


Next click the final task to make sure the integration to the Dynamics AX transaction database is correctly updated. First you will provide the credentials to the Management Reporter integration user. If you remember when setting this up to begin with, you needed to have a dedicated account for Management Reporter and this account would be injected as a user within AX. Dig up your documentation and provide the credentials.


Secondly it will ask for credentials for updating the Datamart database schema. Using your current user should be sufficient.


After it is complete, start the Processing Service and open the logs for the Data Mart Integration. You may need to wait a couple of minutes and hit refresh just to have a check if things look promising (as in no horrible errors).

Upgrade the Client Components

Next we will upgrade the client bits, and I tend to install them on the same server as the server bits so I can test and see things look like expected before I install client bits on terminal servers or client machines.

Unless you closed the Management Reporter Setup, you can continue just by clicking the Client install and run through that simple upgrade process.


And again, finally open the designer and check the version number and test any reports if that is your swag.


In order to upgrade the clients, you will redo the client upgrade on any necessary client machine or terminal server, but as you just saw it was a quick and easy experience.

The upgrade I just showed you was CU9 which contains a lot of new and improved features. If you installed Management Reporter using AX2012 R2 Setup which came with CU7, you are most likely on Management Reporter CU7 and you should really consider upgrade to the latest version. Obviosuly, AX2012 CU is not the same as Management Reporter CU - but you knew that already.

Tuesday, June 10, 2014

Synchronization error when setting up AX2012 R3

This will be a quick post on an error I had when setting up a fresh AX2012 R3 installation. I was getting a bit ahead of myself and got stuck with an error when synchronizing the database.


And to help search engines find this post, the error in clear text:

Cannot create a record in Inheritance relation (SysInheritanceRelations). MainTableId: 12345.
The record already exists. 

The error is due to the fact that I didn't restart the AOS after compiling the application. Lesson is; don't rush and do it the right way the first time.
Furthermore, if you DO get stuck, try do a quick search on Life Cycle Services for a solution:
https://fix.lcs.dynamics.com/Issue/Results?q=SysInheritanceRelations

Restarted the AOS and continued the installation.

Thursday, June 5, 2014

Delete Company in AX 2009 using SQL

One of the potential tasks when upgrading to a new version of Dynamics AX (like from AX2009 to AX2012) is getting rid of obsolete companies. Microsoft shared a SQL for this a few years back. I enhanced it a little bit and added some additional statements.

Just one important remark - DO NOT RUN THIS AGAINST AX2012!

In the interest of sharing, here it is:

/********************************************************
 REMOVE COMPANYID IN DYNAMICS AX 2009

 USE AT OWN RISK! 

 MAKE SURE YOUR TRANSACTION LOG IS PERMITTED TO GROW
 
 Inspired by: 
 http://blogs.msdn.com/b/emeadaxsupport/archive/2010/12/09/how-to-delete-orphaned-data-remained-from-deleted-company.aspx

 Tommy Skaue http://yetanotherdynamicsaxblog.blogspot.com/
*********************************************************/ 

DECLARE @_TABLENAME NVARCHAR(40)
DECLARE @_COMPANYID NVARCHAR(4)

SET @_COMPANYID = N'TST';  -- COMPANY TO DELETE

DECLARE CURSQLDICTIONARY CURSOR FOR
SELECT A.SQLNAME
 FROM SQLDICTIONARY A
  INNER JOIN SQLDICTIONARY X ON X.TABLEID = A.TABLEID AND X.FIELDID = 61448
   WHERE A.FIELDID = 0
    AND A.FLAGS = 0

OPEN CURSQLDICTIONARY

FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME

WHILE @@FETCH_STATUS = 0
BEGIN
 DECLARE @_SQL NVARCHAR(4000)
 SET @_SQL = N'DELETE FROM ' + QUOTENAME(@_TABLENAME) + N' WHERE DATAAREAID = @_DATAAREAID'
 PRINT (CHAR(13) + 'Removing ' + @_COMPANYID + ' from ' + @_TABLENAME + '...')
 EXEC SP_EXECUTESQL @_SQL, N'@_DATAAREAID NVARCHAR(4)', @_DATAAREAID = @_COMPANYID  
 
 FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME
END

PRINT (CHAR(13) + 'Finalizing...')
DELETE FROM DATAAREA WHERE DATAAREA.ID = @_COMPANYID
DELETE FROM COMPANYDOMAINLIST WHERE COMPANYDOMAINLIST.COMPANYID = @_COMPANYID
DELETE FROM VIRTUALDATAAREALIST WHERE VIRTUALDATAAREALIST.ID = @_COMPANYID
PRINT (CHAR(13) + 'Done!')
CLOSE CURSQLDICTIONARY
DEALLOCATE CURSQLDICTIONARY

Use at own risk (of course), and let me know if you find any issues with it.