Thursday, November 10, 2011

Fix: Crystal Reports 2008 Database Explorer Doesn't Show All Schemas [Field Notes]

Issue
In Crystal Reports 2008, I am attempting to add a ODBC datasource (connecting to an Oracle 11g database) so that I can change field links, etc.

However, when I go into the Database Explorer and create an ODBC connection, certain system and user schemas show up, but mine is not in the list, despite the fact that I can view and query that schema/user in many other Oracle clients such as SQL Developer or DBVisualizer.

Resolution
This seems to be a strange bug. According to this helpful (but obscure) forum post, the way to resolve the issue is to:

  • On the menu bar, select File --> Options.
  • Click the "Database" tab.
  • Uncheck the "Stored Procedures" option.
I have no idea why this fixes the issue, but it certainly did. Very happy to have this one behind me.

Crystal Reports 2008 / Business Objects XI: Differing ODBC DataSource Issue [Field Notes]

Issue
In Crystal Reports 2008, I create a report using an Oracle connection or an ODBC connection with name x. When I upload the report BO XI, it doesn't work with the ODBC connection added to it.

Cause
While annoying, this appears to be because Business Objects doesn't automatically apply its own Data Source, and so any difference that doesn't match up in the server will result in a database error or repeated/endless login dialogs

Resolution
When using ODBC connections on your BO Server, prior uploading your report, be sure to create an ODBC connection with the same name as the server and set the data source location to it.

This should allow the server connection to stand in for the local connection and remove the Database issues when you run the report in Business Objects XI

Tip: Always Write Out Your SQL Queries in Plain English First [Field Notes]

I recently ran into an issue where constructing a SQL query took me much longer than I would have liked. While my need to dust off some cobwebs and my lack of familiarity with an enterprise system certainly contributed, I think the biggest factor was that -- because I didn't write out what I wanted to accomplish in plain English -- I was attempting to think about my data in the way that the database was thinking.

Example

Consider the difference between just talking about what you want to return and how you will go about doing it. I believe you should do both, but separately, and they should inform each other.

Example: What You Want To Return

  • Summaries for the costs and labor of a work order's child tasks (which are just a special type of work order having the original work order as a parent and a designation of a task)

Example: What Your Process Will Be To Get it

  • I'll get the hours summary, labor cost summary, and item cost summary by task workorder number, by joining the workorder number across workorder, labor, and items
    • I'll Constrain this by making sure the work order is a task
    • I'll call this Work Order Summary
  • Once I have that working, I'll put it on the inside of another set of summaries, by parent so that I can sum up the summaries and have them for each parent work order.
  • Once that works, I'll make sure that all potential null values (summaries) return zero if they come back null.
Suggested Key Phrases
  • "I'll call this... " -- Helps you to decide what your Aliases  / "As" statements should be
  • "I'll get the..." -- Indicator of types of field information you want to return in each record
  • "I'll constrain this by making sure" -- helps define your WHERE clause
  • "Summary" -- helpful to see if you'll be aggregating / grouping
  • "Once that works, ..." -- Helps you to see a testing point, or a place that can stop. 
Additional Benefits

Consider the other practical benefits that writing your queries in plain English will bring:
  • Firstly -- you lose nothing by doing this. That's the biggest thing.
  • You'll find what I call your "test points" -- places where you can stop and insert some hard code for the purposes of testing. 
  • If you can't write it out using plain language, you will be informed early on that you don't understand the data you're dealing with.
  • You can always start over from scratch if the query design doesn't quite work, knowing that you won't lose what you were trying to do.
  • You can ask for help much more easily if you need it, and help your rescuers understand your situation quickly and clearly
  • It automatically self-documents the business language, which means that someone smart on the business side can read it and tell you if you're not understanding the business rules quite right. This can be very helpful if your queries aren't returning what the end-user needs.

Does anyone know where I might find more guides along these lines? Do you have suggestions or improvements? Sound off in the comments!

Wednesday, November 09, 2011

Fix: Crystal 2008 Doesn't Refresh After Adding Columns [Field Notes]

Issue

Crystal Reports 2008 Designer does not reflect changes made to views or stored procedures, even after refreshing the database and those views/procedures within Crystal.

Resolution

While a horrible user experience, there is a proper method to accomplish this.

Based on trips from this helpful article:
  • From the "Database" menu, click "Verify Database".
  • If dealing with a stored procedure, you will be asked to enter parameters; enter correct ones.
  • Crystal Reports will undergo a verification process, during which time it will find the extra fields and adjust the report accordingly.

No idea why this is separate from (and ambiguously named compared to) a right-click and "refresh", but it does the job.

Fix GMail 707 error by disabling Background Send in Google Labs

For anyone who has had an issue with a GMail 707 error in the browser recently, a potentially useful tip via Brad Feld over at FeldThoughts:

The answer was a simple one once I figured it out. I disabled all the Google Labs and it magically started working again. I then re-enabled Labs until I found the one that was causing the 707 error – the “Background Send” Labs. Apparently something broke over the weekend with Background Send and the newest browser version of Gmail.
If you are getting a 707 error, just turn off Background Send. That should fix it.

Thought it might be useful to some.

GMail 707 Error [FeldThoughts]