Dr. BI Tip: SQL Server 2008 R2 & Report Builder 3 for AX 2009
As the title of this post suggest, we have Dr. BI, Michael Stashwick back for another round of tips, tricks and information around reporting with Microsoft Dynamics AX 2009 / BI.
For Michael's latest article below, he dives into SQL Server 2008 R2 and the benefits of Report Builder 3 for Ad-Hoc reporting use. This tool is great, and as he will show has great improvements over earlier versions. He also talks us through how to upgrade to Report Builder 3 with SQL Server 2008 R2.
---
I can’t think of any single subject clients ask me about more than self-service Business Intelligence (BI) and ad hoc reporting. There seems to be no ceiling to the demand that has increased daily for the past few years, and it’s mostly due to the very robust BI stacks available.
Unfortunately, the primary ad hoc tool that comes packaged with Dynamics AX, Report Builder 1.0, is a bit archaic and leaves users underwhelmed. Fortunately, the latest version, 3.0, is rich with functionality and pleases every super user that I’ve introduced it to.
Implementing Report Builder 3.0 requires SQL Server 2008 R2, and the upgrade path, which I’m going to discuss in this article, should be anything but intimidating.
To begin, there is a free downloadable Upgrade Advisor that removes all compatibility guesswork. After running the Analysis Wizard against all installed SQL Server components, the Upgrade Advisor Report Viewer will provide a list of all issues with links to MSDN for assistance. Even in the most perfect situation, there will always be one issue--the custom extensions and assemblies from the AX installation.
This is nothing to worry about and should be expected. Once all else checks out, run the R2 upgrade executable. After a successful upgrade, the first logical verification step is to run Report Manager and a deployed report.
Immediately accessing Report Manager will result in a cryptic error message that you’ve probably never seen before:
"The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version."
After giving the computer a verbal snarky response, gather more meaningful information by logging into Report Server. You will get an error message that the ‘Microsoft.Dynamics.Framework.ReportsExtensions’ could not be loaded. This is exactly what the Upgrade Advisor warned of in the beginning of the procedure.
The reason this happens is that in the upgrade process, a new sub-folder is made, as opposed to files being appended, in your Microsoft SQL Server directory. The necessary information to run AX Reporting Extensions can’t be located due to this move.
The simplest fix is to re-install Reporting Extensions from your AX install media. Of course, it must first be uninstalled before it can be re-installed.
There is one last step in upgrade process. If you tried to run a report now, it would fail as the system would be “unable to find the file specified.”
Because of this, all reports must be deleted and re-deployed to the Report Server. This realization made me so happy to have Report Libraries in AX 2009, because the countless number of reports, including any custom AX 2009 SRS reports, can be deployed in a single action.
Now, Report Builder 3.0 will launch by default when accessed by Report Manager. The additional functionality provided (compared to 1.0) is staggering, and can be seen in the MSDN article, Comparing Report Authoring Environments. Not every piece of functionality may be relevant to AX, but the UI is grossly improved with the familiar ribbon interface, and more in-depth querying is available.
---
I would like to thank 'Dr. BI' for taking the time and sharing with us yet another reason why Microsoft Dynamics AX is so powerful.: It's Continued use of the rest of the Microsoft Stack
Also, this ties right into the use of tools like Globe Software Atlas, and Microsoft PowerPivot (Microsoft Dynamics AX 2012 and the importance of PowerPivot) for Ad-Hoc and Self-Service BI needs.
That's all for now, till next time!
"Visit the Dynamics AX Community Page today!"
For Michael's latest article below, he dives into SQL Server 2008 R2 and the benefits of Report Builder 3 for Ad-Hoc reporting use. This tool is great, and as he will show has great improvements over earlier versions. He also talks us through how to upgrade to Report Builder 3 with SQL Server 2008 R2.
I can’t think of any single subject clients ask me about more than self-service Business Intelligence (BI) and ad hoc reporting. There seems to be no ceiling to the demand that has increased daily for the past few years, and it’s mostly due to the very robust BI stacks available.
Unfortunately, the primary ad hoc tool that comes packaged with Dynamics AX, Report Builder 1.0, is a bit archaic and leaves users underwhelmed. Fortunately, the latest version, 3.0, is rich with functionality and pleases every super user that I’ve introduced it to.
Implementing Report Builder 3.0 requires SQL Server 2008 R2, and the upgrade path, which I’m going to discuss in this article, should be anything but intimidating.
To begin, there is a free downloadable Upgrade Advisor that removes all compatibility guesswork. After running the Analysis Wizard against all installed SQL Server components, the Upgrade Advisor Report Viewer will provide a list of all issues with links to MSDN for assistance. Even in the most perfect situation, there will always be one issue--the custom extensions and assemblies from the AX installation.
This is nothing to worry about and should be expected. Once all else checks out, run the R2 upgrade executable. After a successful upgrade, the first logical verification step is to run Report Manager and a deployed report.
Immediately accessing Report Manager will result in a cryptic error message that you’ve probably never seen before:
After giving the computer a verbal snarky response, gather more meaningful information by logging into Report Server. You will get an error message that the ‘Microsoft.Dynamics.Framework.ReportsExtensions’ could not be loaded. This is exactly what the Upgrade Advisor warned of in the beginning of the procedure.
The reason this happens is that in the upgrade process, a new sub-folder is made, as opposed to files being appended, in your Microsoft SQL Server directory. The necessary information to run AX Reporting Extensions can’t be located due to this move.
The simplest fix is to re-install Reporting Extensions from your AX install media. Of course, it must first be uninstalled before it can be re-installed.
There is one last step in upgrade process. If you tried to run a report now, it would fail as the system would be “unable to find the file specified.”
Because of this, all reports must be deleted and re-deployed to the Report Server. This realization made me so happy to have Report Libraries in AX 2009, because the countless number of reports, including any custom AX 2009 SRS reports, can be deployed in a single action.
Now, Report Builder 3.0 will launch by default when accessed by Report Manager. The additional functionality provided (compared to 1.0) is staggering, and can be seen in the MSDN article, Comparing Report Authoring Environments. Not every piece of functionality may be relevant to AX, but the UI is grossly improved with the familiar ribbon interface, and more in-depth querying is available.
I would like to thank 'Dr. BI' for taking the time and sharing with us yet another reason why Microsoft Dynamics AX is so powerful.: It's Continued use of the rest of the Microsoft Stack
Also, this ties right into the use of tools like Globe Software Atlas, and Microsoft PowerPivot (Microsoft Dynamics AX 2012 and the importance of PowerPivot) for Ad-Hoc and Self-Service BI needs.
That's all for now, till next time!
Labels: Adhoc, Dr. BI, Dynamics AX, Dynamics AX 2009, Michael Stashwick, Microsoft, PowerPivot, Report Builder, Report Builder 3, Reporting, SQL Server 2008 R2, Upgrade
posted by brandon | 6:58 AM
1 Comments:
I have Report Builder 3 installed and functioning, but I have been trying to find inforamtion about how to use it with Dynamics AX. When I attempt to add the Dynamic AX datasource from the Report Server, I receive an Anonymous Login error. Are there resources available that can help me use this tool with Dynamics AX 2009?
Post a Comment
<< Home