Error when installing Reporting Extensions for AX2012 R3 on SQL 2014 SSRS

Hi

I could not really find any posts on this out there, so I decided to just share this here.

You may experience installation errors when you try to install Reporting Extensions for AX2012 R3 on SQL 2014. The setup crashes internally, rolls back the installation and fails.
In the installation log you will see the error "Version string portion was too short or too long".

The solution is available on LCS as a downloadable hotfix KB3216898 (released 10th of January 2017) here:
https://fix.lcs.dynamics.com/Issue/Resolved?kb=3216898&bugId=3800976

Unpack the content of the hotfix and slipstream it as part of your AX2012 R3 installation and run the installation again. Now it will work.

Just to make this sure people find this post if they search for the errors, I'll add the full call stack below:

An error occurred during setup of Reporting Services extensions.
Reason: Version string portion was too short or too long.
System.ArgumentException: Version string portion was too short or too long.
  at System.Version..ctor(String version)
  at Microsoft.Dynamics.AX.Framework.Reporting.Shared.SrsWmi.get_ReportManagerUrl()
  at Microsoft.Dynamics.Setup.ReportsServerInstaller.GetOrCreateServerConfiguration(String instanceName, String sharePointServiceApplicationSite, Boolean& createdConfiguration)
  at Microsoft.Dynamics.Setup.Components.ReportingServicesExtensions.InstallConfigurationFiles(String instanceName, String sharePointServiceApplicationSite, Boolean& createdConfiguration)
  at Microsoft.Dynamics.Setup.Components.ReportingServicesExtensions.RunReportingSetupManagerDeploy()

Reporting Server Subscription does not run on Schedule

I've setup a SSRS report that pulls data from two SQL Server databases across two different SQL Server instances. It runs under a SQL Server user and works like charm when run manually. However, when I try to setup a Subscription for this report, the report never runs according to the schedule. I don't even get an error in the Reporting Services log.

In order to understand what is going on, I need to go back to the SQL Server Agent and check the logs there. When you create a Report Server Subscription on a Schedule it creates a new Job for this Schedule and this Job initiates the Event that triggers the scheduled report(s) to run.You can read more about it here.

First I need to identify what Job ID is behind the schedule I want to investigate. The Jobs are created using a Unique ID (GUID) and in order to link the actual report to its schedule ID I run this SQL on the Reporting Server Database (Normally called "ReportServer_INSTANCENAME"):

select s.Description, us.UserName, s.LastStatus, s.LastRunTime, c.Path, uc.UserName, rs.ScheduleId,
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId

Going back to the Job, I can see from the Job History it doesn't even get to the first step of the Job. Since this is AX, the Job is run under the same account as the Business Proxy Account. That is how SSRS normally is configured in relation to AX. The error from the history log actually says "the owner of job does not have server access". The job is actually set to run in the context of a user that does have server access, but somehow (a bug maybe) this is ignored by the SQL Server Agent.

The solution is:

1. Change owner of the job to the SQL Server User that has the necessary permissions to run the report
2. Grant the SQL Server User permissions to operate on the the Report Server Database (It does needs permissions to inject data, however I gave it full db_owner permissions)
3. Test the job by doing a manual run

If the job still fails, you can investigate the Job history for any errors. 

Reduce SSRS deployment time for static reports in AX2012

Are you wasting minutes deploying and redeploying static SSRS reports in all the languages provided with AX2012? If you only need a handful of them, you might just as well consider disabling the licenses for the unwanted languages. You can enable them back if you need them later.

Now disabling one language at a time manually might not be your cup of tea, so I would like to share a small job that disables all languages except the ones you want to keep enabled. Just create a new job and paste in the code below. Use at own risk of course, take backups and backups of the backups etc (you know the drill).

// Remove licence codes for unwanted languages
static void AdLanguageRemover(Args _args)
{
    SysConfig                   sysConfig;
    SysRemoveLicense            remLic;
    
    Query                       query;
    QueryBuildDataSource        qbd;
    QueryBuildRange             qbr;
    QueryRun                    queryRun;
    
    FormRun                     confirmForm;
    Set                         languagesToKeep = new Set(Types::String);
    Set                         licenseCodeSet  = new Set(Types::Integer);
    SetEnumerator               it;
    int                         confCount       = 0;
    boolean                     licenseChanged  = false;
    Args                        args            = new Args(formStr(SysLicenseCompareForm));    
    boolean                     proceed         = false;
    SysLicenseCodeDescription   codeDescription;
    str                         currentLanguageId;
    int                         pos, sysConfigId;    
    
    // List of languages to keep. Add, remove, change to fit your preference
    languagesToKeep.add('nb-no');
    languagesToKeep.add('en-us');
    languagesToKeep.add('sv');
    languagesToKeep.add('nl');
    languagesToKeep.add('fr');
    languagesToKeep.add('da');
    languagesToKeep.add('de');    
    
    query = new Query();
    qbd = query.addDataSource(tableNum(sysConfig));
    
    qbr = qbd.addRange(fieldNum(SysConfig,ConfigType));
    qbr.value(enum2Value(ConfigType::AccessCodes));
    
    qbr = qbd.addRange(fieldNum(SysConfig,Id));
    qbr.value(SysLicenseCodeReadFile::rangeLanguage());
    
    queryRun = new QueryRun(query);
    
    delete_from remLic;
        
    while (queryRun.next())
    {
        if (queryRun.changed(tableNum(sysConfig)))
        {
            sysConfig = queryRun.get(tableNum(sysConfig));
        }
        
        codeDescription     = SysLicenseCodeReadFile::codeDescription(sysConfig.Id);
        pos                 = strFind(codeDescription,'(',strLen(codeDescription),-strLen(codeDescription));
        currentLanguageId   = subStr(codeDescription,pos+1,strLen(codeDescription)-pos-1);
        
        if (!languagesToKeep.in(currentLanguageId))
        {
            warning(strFmt('Removing language %1',SysLicenseCodeReadFile::codeDescription(sysConfig.Id)));
            licenseCodeSet.add(sysConfig.Id);
            remLic.clear();
            remLic.LicenseCode = sysConfig.Id;
            remLic.Description = SysLicenseCodeReadFile::codeDescription(sysConfig.Id);
            remLic.insert();
        }
        else
        {
            info(strFmt('Keeping language %1',SysLicenseCodeReadFile::codeDescription(sysConfig.Id))); 
        }
    
    }
    
    if (licenseCodeSet.elements())
    {
        // if not valid code, then we should display the warning            
        confCount   = SysLicenseCodeReadFile::findConfigKeysFromLicenseCodeSet(licenseCodeSet);

        confirmForm = classfactory.formRunClass(args);
        confirmForm.init();
        confirmForm.run();        
        confirmForm.wait();  
        
        if (confirmForm.closedOk())
        {
            it = licenseCodeSet.getEnumerator();
            while (it.moveNext())
            {
                sysConfigId = it.current();
               
                update_recordSet sysConfig 
                    setting value = '' 
                    where sysConfig.id == sysConfigId;
                
            }
            
            SysLicenseCodeReadFile::codesModified();
        }
    }
}

Allow for a synchronization to run through after the licenses are modified. Remember that this may impact the database schema, but if you really do not want the (ie.) Norwegian language to be enabled, it should be safe to disable. Thanks for reading!

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.

Warm up Reporting Services for quicker reporting

You might already know AX2012 R2 CU7 brought a new helper class for warming up Reporting Services so it doesn't "fall asleep" during long periods of inactivity. The new class has already been blogged about, but in this post I will show to set up the batch and also give you some hints on how to find potential reports you can target if you want to extend the class.

Setting up the batch

Now, I'm going to set this up for every 10 minutes, and not every minute as some might suggest.

The class is dependent on a SSRS report that needs to be deployed, so if you haven't done so already, find the report and deploy it.

After making sure the report is available, you need to locate the class in the AOT. Open the class to run it. Choose batch and define recurrence and alerts.

When the report runs, it will save the result as a PDF to the temporary folder on the AOS. You may open the report and view it, if you're really interested. The point is not the report as much as keeping the service "warm" and also provide a pattern for warming up other potentially slow running reports.

You may want to make sure you don't get a log entry for each successful batch run. I prefer to keep Errors Only for these types of frequent jobs.

Finding potential reports for extending the warmup

Reporting Services logs the execution of the reports and there are a lot of good statistics you can use to investigate potential performance issues. For this example I will run a SQL Query that gives me frequently run reports and load some metrics. I'm looking for reports run the last month with more than 25 runs and where there are more zero rows. These reports could be reports I would want to keep warm.

SELECT 
  COUNT(REPORTPATH) AS RUNS, 
  REPORTPATH, 
  MAX(TIMEDATARETRIEVAL) AS MAX_DATA ,
  MAX(TIMEPROCESSING) AS MAX_PROCESSING,
  MAX(TIMERENDERING) AS MAX_RENDER,
  MIN(TIMEDATARETRIEVAL) AS MIN_DATA ,
  MIN(TIMEPROCESSING) AS MIN_PROCESSING,
  MIN(TIMERENDERING) AS MIN_RENDER
FROM EXECUTIONLOG2 
  WHERE 
     STATUS IN ('RSSUCCESS') 
  AND REPORTPATH NOT IN ('UNKNOWN')
  AND TIMESTART > DATEADD(m,-1,GETDATE())
  AND BYTECOUNT > 0 AND [ROWCOUNT] > 0
GROUP BY REPORTPATH
HAVING COUNT(REPORTPATH) > 25
ORDER BY COUNT(REPORTPATH) DESC

Here are a snippet of the results. You can clearly tell even the warmup report has different metrics for MAX and MIN. These are milliseconds, but if you see reports spending several thousands of milliseconds processing or rendering just a few rows, you may want to investigate why.

Coming from pretty quick and performant MorphX reports to SSRS might be painful both for us AX consultants and AX end users, but then we also see some reports perform stunningly if they were run just minutes ago. You would think they should run just as slow each time. They don't, so maybe we should figure out why that is. Keeping Reporting Services warm is part of the solution.

Get the element name of an AX2012 Report from the preview

This is going to be a short post, but hopefully it will help someone still.

One of the essential tasks when delivering an Dynamics AX solution to a customer will always be report customization. There are more than one thousand SSRS reports deliveres with Dynamics AX out of the box. I would say it is closed to guaranteed the customer wants some of them adjusted.

With the release of AX 2012 R2, more of the central reports are now split up into new reports dependent on various configurations being active or not. So what report is run can be a result of license, configuration or simply the state of the entity you are trying to report. This often results in frustration for the developer or consultant who is trying to figure out what exact SSRS Report element is actually being used. You don't want to spend time changing the wrong layouts.

The solution is actually very simple. You could even share this with the super user when asked what exact report is bugging them.

While in the preview of a SSRS Report, click the Save-button and choose PDF (other formats works as well):

Have the consultant or super user note down the suggested file name. This will be the name of the element within the AOT. After the dot, you will have the name of the design.

In order to figure out why one layout is chosen over another, you will still have to step through the code, but you know an easy way to find the correct Report element name.

UPDATE:
I realize this does not always work as intended. Some reports in some languages will rather suggest the title of the report as filename.

In those cases, you want to do what Lurker so kindly suggests in the comment below.

"\Forms\SrsReportViewer\Methods\init add a breakpoint
In the call stack open Classes\SrsReportRunController
this.reportname will have the report name and design."

Deploying Reporting Services Reports dependent on X++ helper classes

Today I was having trouble with deploying a custom Reporting Services Report. One of the changes I made to the report was adding a helper class in the AOT and calls a method on this class from one of the data methods within the report.

The deployment was successful, but when I tested the report it threw a Dynamics AX error stating it could not call my custom method. Lucky for me, this customer had Visual Studio installed on the server running SSRS, so I was able to load the Report Library and test it within Visual Studio. The report ran perfectly.

I tried first to delete the report in the Report Manager Web interface, and then redeploy it from scratch. The documentation clearly states:
"Reports often rely on code that is defined in business logic assemblies. During deployment, business logic assemblies, as well as satellite assemblies containing translated resources, are built and deployed to the report server. After the assemblies have been deployed to the report server, the security policy file for the report server is updated to allow.NET Business Connector secure access to the business logic assemblies."
http://msdn.microsoft.com/en-us/library/cc519490.aspx

The report still failed with the same error. But since the report worked within Visual Studio I started thinking about whether or not the added X++ class was truly available for any managed code in SSRS. Those of us who work with Enterprise Portal development knows this always isn't true, and you are some times forced to refresh managed code generated from the AOT. So I pulled up the Reporting Services Configuration Manager and picked my instance, connected to it, and restarted it.


Now the report worked perfectly!


I hope this helps someone.

Property value is not valid when setting datamethod in Dynamics AX SSRS Report

UPDATE January 2012: This is resolved in version 6 of JetBrains Resharper.

I was working with building Reporting Services reports for Dynamics AX 2009 in Visual Studio and when I tried to set the Query property of the Dataset I got this very strange and not very helpful error:
"Property value is not valid" with the details containing "Object reference is not set to an instance of an object".

I was pretty sure my Data Method was valid, but I spent some time trying to strip out any possible problems with my code. In the end I tried to test my report in a different environment (which I was lucky enough to have set up earlier). The report worked perfectly in my second environment. I then figured out it had to do with Visual Studio and went back to my development environment for a closer look. Lucky for me, one of the first things I did was trying to suspend ReSharper (www.jetbrains/resharper) and that was it.  After suspending ReSharper, the DataMethod returned the DataTable as expected and everything was back to normal. I have notified the ReSharper developers about this problem, so maybe one day this will get fixed. In the meantime, I hope this post will sort things out for any other developers out there struggling with the same problem. This is version 5.1.2, and I know there is a 5.1.3 version out there. If any later versions of ReSharper fixes this I will update this post (if I remember).

As a side note, ReSharper is my favorite addon for Visual Studio, and if you're working with SharePoint development (Enterprise Portal to be specific), then I'd highly recommend ReSharper. Try it out, you won't regret it! Then again, when working with the Dynamics AX Report Model for Visual Studio, you will simply have to suspend it.

Dimension Permission Error after successfully processing the Dynamics AX OLAP

Introduction

I have recently been involved in hunting down a strange "bug" in all the Reporting Services reports which reported data from the General Ledger Cube. The error message itself was "The 'Measure' attribute for the 'Measure' dimension has generated dimension security expression that is not valid. DimensionPermission (1, 209) the member '[Cheque Transaction Amount - Bank]' was not found in the cube when the string, [Measures].[Cheque Transaction Amount - Bank], was parsed.". Any users who were not members of the Local Administrators Group on the SSAS server would get this error on reports trying to report from the General Ledger Cube.

Configuring the Default OLAP cubes

Those who have worked with the deployment of standard OLAP cubes for Dynamics AX knows the main job is to make the OLAP process. This can only be achieved by removing any elements in the Analysis database that still reference missing elements from the OLTP (the Dynamics AX relation database). You probably also know the fact that if you turn off configuration that represents database elements like tables and/or fields, these elements will effectively be removed upon database synchronization. So basically any table or field removed from the OLTP will result in possible errors when processing the OLAP. If you can process the OLAP without errors, you're good to go (well, almost).

The 'Measure' referenced in my error was in fact based on the "BankCheque" Configuration, which was turned off in this particular installation. I could verify the measure missing from the OLAP as expected. So why did the report throw this error?

The solution

I'm not entire sure why, but the solution was in the definition of each Role connected to the General Ledger Cube. With the default OLAP there are numerous predefined Roles. These roles more or less resembles the default installed Roles in Dynamics AX. They have a semantic relation, but that's all. Adjustments to role membership in Dynamics AX are not automatically copied to the Roles in the OLAP. They are separate. The Roles in the OLAP contains various settings for limiting the access to the analysis data. There are settings for limiting access to cubes and all the way down to limiting access to specific dimensions. Given all this, there was indeed a setting on several Roles naming this particular dimension in its security context.

Under Properties for the CFO role and under Dimension Data, you'll find a dropdown for available dimensions you may specify security restrictions for. These dimensions are limited to those common for the entire OLAP, and those specific for any cubes this role has access to. Under each Cube there is even a Measures Dimension which allows you to define security context down to attributes. This is the source for the reference to the invalid 'Measure' attribute, namely the [Measures].[Cheque Transaction Amount - Bank]. After taking this reference out the "Allowed member set", and saving the Role, everything was working perfectly. Well, I actually had to remove this reference from all Roles granting access to the General Ledger Cube.

Finally...

Why the users in the Local Administrators Group are not effected by this, and why the OLAP may process without errors even when the Roles reference invalid dimensions, that I do not know. Please feel free to message me or comment under this article.

I hope this helps someone!