I recently ran into a scenario that I’ve seen a few times before that I thought I’d document in today’s blog article.
We’ll document the problem we’re trying to solve, the troubleshooting steps, the underlying issue, and then the solution.
The scenario is this:
Customer has created a summary report in SmartList Builder that does <something> and they really like this report. It’s so great, all the companies use it individually.
Figure 1: Fabulous and simple summary on the GL10001 table
In this summary report, the Journal Entry is set to “Group By”, the Sequence Line is set to Count (we want to know how many journal lines there are for some reason), and the Debit & Credit Amounts are set to SUM. This is set in the Options window (not shown)
Figure 2: Flawless!
The next thought is – it would be great if we could have this report print for ALL the companies so that our accountants don’t have to manually combine each company report.
Figure 3:Now with multicompany!
Easy enough, just go back to the Options window and mark the Multicompany SmartList checkbox and mark all the companies it applies to as above.
Figure 4: Now showing multicompany summary data
Tougher to see that this DID work successfully but since we now have 1000 Records (default max SmartList query) vs 434 with one company. Also we see that I have 3 duplicate journals for 1543 (my data is not-coincidentally the same in all 3 companies!) so we can tell this report works correctly and everyone seems happy.
The business comes back to you and notes that they cannot tell WHICH company each journal entry belongs to.
OK, we can do that – SmartList Builder allows us to create our own calculations and even has a “special field” that will retrieve the Company Name or Database ID for us.
Simple enough, we think.
Running the report:
Figure 5: Missed something
Why is “CompanyID” a number and not “TWO”?
Because we forgot about the summary report option – “string” fields are automatically set to Count. It isn’t a coincidence that CompanyID and Sequence Line both show the same value because both are set to Count.
No problem, just set the Summary Type of this calculated field in the Options window to “Group By” and re-run the report.
Figure 6: There was an earth shattering kaboom!
And nothing – no data? What could possibly be wrong?
From an end user perspective, you have no idea. I mean it HAD to be that summary option since it was just working before the change. But we need to see “TWO” and not “2” – is this even possible?
So the first step is always – you need to know what is happening and why.
And for SmartList Builder reports that is almost always starting with a DexSQL.log. I’m a big fan of SQL Profiler and so we COULD start there – but anyone can run a dexsql.log since it is just a Dex.ini change and a lot quicker for something like this.
Microsoft has a KB on enabling a DexSQL.log in GP
I would always recommend turning that one. Getting to the point of reproducing the “issue” and then delete the dexsql.log. Now repro the issue again. Makes finding the relevant SQL a LOT easier.
Looking in ours, we find the last company query near the bottom– the SQL Query and then the SQL error.
Figure 7: SQL statement and error
The error given by SQL is:
Each GROUP BY expression must contain at least one column that is not an outer reference.
How SmartList Builder adds in the Company ID or the Company Name to a report is to just hard code the ID or Name as a static string field – so GP2 in this case.
If we suspect that this was really a “SmartList Builder issue”, we can always run that same SQL Query in SSMS.
Yup – same error for the same reason.
After a bit of thought, it seems that perhaps SQL is just unhappy that this was a static text field. What if we made it “Dynamic” in that we ask SQL to pull the current SQL db? This might work because each company is in the correct context when SLB runs the report and then the dataset name won’t be a hard coded string field.
To make it a quicker test, we can just modify the SQL above to replace ‘GP2’ with the SQL function DB_NAME(). Nope – same error.
After a LOT more research, I found a few references to using SQL “Case” statements. Trying that next, I modified my calculation and crossed my fingers.
Here I made a case statement where the “true” clause won’t fail since there shouldn’t be negative or 0 numbered journals. For those wondering why I didn’t choose something simpler that would always work such as “when 1=1 then” I actually DID try that – same SQL error. I assume that since SQL does know that it cannot fail it isn’t any different than static text and fails in the same way.
Figure 8:Success!
That did it but it sure wasn’t as easy as I thought it would be due to “because it’s a SQL thing” conditions. I’m sure if I was a SQL guru it would make sense to why it doesn’t work that way but I’ll leave it up to my readers to tell me why – I’m just happy I found a way to make it work.
In today’s blog post, we:
- Created a SmartList Builder Summary report and tested with one company as ok
- Made it multicompany and tested ok
- Added a “Special Field” calculated field to get current GP company INTERID
- Discussed setting fields to be set to Count, SUM, or Group By on a summary report
- How to work around the error from SQL when using this type of calculation on a multicompany summary report.
Best regards and until next time,
Patrick Roth
eone