I’m not sure why, but I always seem to find the really obscure bugs in GP, the ones that really don’t affect many others. I guess it’s called a gift. :)
Today, I found another one that affects GP10 (although apparently 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).
- Create an Excel Report Builder report - use anything (I used Year To Date Transaction Open).
- Set some fields for display.
- On the Account Index field, use the field options to set “Show Account Number” instead of account index.
- Use the Summary button (GP10) or Options button (GP2010) to select multiple companies. This doesn’t matter if you consolidate into one workbook or not (I didn’t).
- Publish.
Now, take a look at the SQL view it creates, which will be prefixed, if you didn’t change it, 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, it’s 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 part that is wrong is the select script that is to obtain the account number for a given account index. Look for 2 (or more depending on what you have 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 db).
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 you make the 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!