After a recent upgrade from GP 9.0 to GP 2010 R2 I ran into an interesting problem. The client is running on SQL Standard 2005 SP4 and I was aware that the Charts and KPI’s in GP were not going to work since they are only supported with SQL 2008 or above. See an excerpt from the upgrade documentation below:
During my tests after the upgrade, I selected the Customer Navigation List and I was confronted with this error message:
rsItemNotFound400The item '/xxxxxx/Sales/Charts And KPIs' cannot be found.http://go.microsoft.com/fwlink/?LinkId=20476&EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&EvtID=rsItemNotFound&ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&ProdVer=9.00.5057.00Microsoft SQL Server Reporting Services9.00.5057.00127OsIndependent1033ReportingServicesLibraryThe item '/xxxxxx/Sales/Charts And KPIs' cannot be found.
The first thing my mind jumped to were the new Business Analyzer panes in GP 2010 R2. How could I turn them off? The BA pane is looking for the Charts and KPI’s on the SQL Reporting services server and they of course are missing since they are not supported on SQL 2005.
I discovered that I could hide the BA pane and the error message would then not show up. Once you click past all the error messages, the navigation list will load. You can then click the List Name settings dropdown >> Show/Hide >> and uncheck Business Analyzer.
This is great but what if I have many users and I want to hide the BA pane for every user? Using the Manual Logging feature in the Support Debugging Tool (written by David Musgrave from Microsoft, a must have if you’re a GP admin, available only on Partnersource, ask your partner to get it for you), I was able to determine which table holds this setting for the Navigation Lists. Below is a SQL script that will hide the BA pane for every Navigation List.
The above SQL code is great but there’s one problem. Every time a user clicks on a Navigation List that they’ve never clicked on before, a record will get inserted into the table and of course the BA pane will be visible by default. To overcome this problem, I used the above mentioned Support Debugging Tool to create a table trigger. When GP reads data from the List View Options table (DYNAMICS..SY07225), this trigger catches that read and changes the data that is getting returned to hide the BA pane. You can download the trigger here. Download the SDT_HideBAinNavigationList.xml.dbg.xml file. In Support Debugging Tool (I am using build 15, not sure if this file is compatible with earlier versions of SDT), select Options >> Configuration Export/Import and import the downloaded file. This will create an Automatic Debugger Mode trigger which you will need to enable for all users. Since this is not an SDT tutorial, please check out the Debugger pdf on the SDT page for instructions on how to use the Automatic Debugger Mode and the other features of the tool.
PS. This was a quick and dirty debugging job. I did NOT thoroughly test every bit of code in this example. Use at your own risk.
Of course, I gotta say, you really shouldn’t be on SQL 2005 because you’re missing out of all the new features. I should go tell the customer this…