I'm not sure why, but I always seem to find the obscure bugs in GP, the ones that don't affect many others.  I guess it's called a gift. 😄

Today, I found another one that affects GP10 (although has been resolved - I can't verify that yet) and a slight variation of the same for GP2010.

The Issue

When creating an Excel Report Builder report and publishing it for multiple companies at once, the SQL views are not pointing at the correct database (or in some cases are only partially correct).

To reproduce this issue

In the environment I am testing, the client has GP10 but hasn't put in SP5 or anything newer so admittedly this is an out-of-date environment at the moment.  Microsoft confirmed it's a known issue that is resolved in SP5 (10.00.1701 or later).  I just don't have that handy to test.  For fun, I also tested this in GP2010, with SP2 (11.00.1752).

  1. Create an Excel Report Builder report - use anything (I used Year To Date Transaction Open).
  2. Set some fields for display.
  3. On the Account Index field, use the field options to set "Show Account Number" instead of Account Index.
  4. Use the Summary button (GP10) or Options button (GP2010) to select multiple companies.  This doesn't matter if consolidating into one workbook or not (I didn't).
  5. Publish.

Now, take a look at the SQL view it creates, which will be prefixed with "erb".  I use the right-click Script View As context menu to display it in a query editor window in SQL Mgmt Studio. Look for the "FROM" clause(s).

The results

On GP10: the company I am logged into, its SQL views were correct; however the SQL views on the other databases I published to are selecting "from" the same logged-into company database. Everything else appears as normal but of course, when the user runs the Excel Report, it will be pulling data from a different company than they want.

On GP2010: the only wrong part is the select script that is to obtain the account number for a given account index. Look for 2 (or more depending on what has been created) "select" scripts. One of them is "select top 1 ACTNUMST from [XXXX]..GL00105 where ACTINDEX = T1.[ACTINDX]".  The XXXX in this case is pointing at the wrong company database. So, once again, the users would be pulling a report, and it would be displaying Account Numbers from another company's database (or erroring I suppose if the account index does not exist in the other company's database).

Quick Fix

The quick fix is to alter the SQL view directly by changing the referenced database to the correct one. I haven't tested any further so beware if making a change, and then republish the report for some reason - I don't know if it "re-creates" the SQL view at that time or not.

What's next

I've submitted both of these to Microsoft. The GP10 one they can't reproduce on SP5 so that is likely resolved. The GP2010 variation they haven't responded to me yet, they are still testing it.  I'll try to post updates as I get them on this!