I haven’t posted, or talked much, about my new work .. basically, I joined a company whose primary job is things, and most work focuses around an ERP, and its all about stabilizing and optimizing flows of information around processes. Edit/Update: I wrote this in, like, February. A lot happened since then.. CoViD19 especially. It is now June.
One of my new skillsets is Crystal Reports. To which my developer peeps say Ewww, usually. Eh, it works. The main thing is, the ERP system that everybody is logged into, has a way to embed Crystal Reports into it. That ERP System (Prophet 21) is also backed by a SQL Server database, and has a decent table structure to it, and a decent user-defined-field add-on strategy to it, making many tasks.. sane.
However, navigating the environment for what actually works has been hard. There were not a log of prior art examples which did things at the architecture I would like, so I’ve had to fumble around. Here’s what does NOT work:
- Using anything other than a particular driver (I forget which one) for connecting to SQL. Specifically, not to use the SQL Native driver. This is because when the ERP is hosting the report, they do a switcharoo on the connection to connect to the right “instance” of the ERP, and if you use the wrong driver, that doesn’t work. You don’t find out till you try to run the report from the ERP.
- Directly using a stored procedure to do heavy lifting for a report. You can, and it auto-creates report parameters for you, but those report parameter types lead to less-than-optimal user dialog (think dates plus times instead of dates).
- Using a parent crystal report to include a subreport to get around the previous thing. Works great for a crosstab, but page headers not so good in a grid style report. However, I am able to bind a parameter from the parent report through a calculated field to plug in to the subreport (and thus the stored procedure).
- Also, if you have a parent report that only calls sub-reports, and doesn’t actually connect to the database itself, the ERP system doesn’t like that because it cannot find the database connection to override.
- Not choosing a printer when designing the report. Apparently this affects font choices and Arial looks better than Device CPI 10.
Here’s what does seem to work:
- I can use Views to encapsulate business logic, example “The View of xxx customers” where xxx is a particular program that customers can enroll in.
- I can use stored procedures to D.R.Y., for example, the stuff to get the raw data of the number of designer frames sold per customer within a time period.
- I can call stored procedures from a “command” custom SQL block from Crystal Reports. In that block, I can: declare @t table (…) and insert into @t exec SPROC {?param1} {?param2} to get data from a stored procedure.
- For example, there are two reports: One is a CrossTab that breaks out customers across brands and # of frames, and another is a detail report. The detail report goes into how many DIFFERENT brands were sold + the number of frames sold — these numbers roll into a formula describing what % back the customer gets, per the rules of the contract(s). Both of these reports use the same stored procedure to get the underlying data.
- However, using this method, I have not yet been able to go (user input) to (calculated field) to (input into command) to (call stored procedure), yet. On the other hand, I can do a lot of manipulation in T-SQL, so that should be fine.
I’m continuing to learn a lot of things.. next week looks like it will be learning the ways of our EDI interfaces with some bigger customers, like the names you’ll find at a Mall. (I don’t know how much I can talk or not about our customers). (Edit: It is preferred that I do not.)
Side note – We have added datadog to our infrastructure, and monitoring is making our lives better, I think. Separate blog post, but in summary: immediate notify on errors, and notify on lack of success. Except on weekends.
Looking back at this post I wrote 4 months ago.. wow, there’s so much detail I could go into about all the little things that I’ve learned and tweaked. Like some powershell to inspect bad emails in an SMTP dead letter folder. And a powershell to automate connecting my Cisco VPN connection. Messing with Redhat 8.1 re-learining all the unix things including SMB shares. However, .. that’s another post; coming up shortly.
