Creating Reports Out Of Pure SQL 3

[Tama]

I've just started using Crystal Enterprise 8.5 for connecting to an 300+ table Oracle DB via ODBC.

For the past couple of years I've been using coded SQL queries to extract data from DBs for analyse/ reports.

My initial investigation into using SQL in Crystal have hit a wall. I can code and save a SQL Query using "Crystal SQL Designer" - and then use that query for the basis of a Crystal Report in the Crystal Reports client.

However - if I upload my report to the Enterprise server I get error messages, I have been informed that this is because the query is being used as the data source rather than the ODBC connect - is this right?

What I'm interested in is the steps I need to take to
1. Write a piece of SQL code
2. Format the query results into a Crystal Report
3. Upload it to the Crystal Enterprise server so it works.

I really really really really (really) don't want to use all that point and click stuff - my DB is over 300 tables with many many many fields and point and click drives me nuts. I've also got a pile of SQL queries I want to turn into reports and make available via Crystal Enterprise to users.

Any suggests, comments etc. are much appreciated.

Cheers
Tama

 

[synapsevampire]

First, even Crystal doesn't suggest using their Query Designer, so scrap that idea.

However it's very simple to migrate any existing queries into a Crystal Report, especially if you have access to the database, just add:

Create or replace view <name it> as
<paste in your SQL>

Now you have an easily maintained, reusable data object available across the enterprise, and more importantly, to your report.

You should also consider that ODBC is the slow means to connect to Oracle from Crystal, instead use More Data Sources->Oracle Server

If you're dba's are so stewpud that they won't allow you to convert your sql into Views on the database, then your options aren't great unless you update to CR 9. You can also use an ADO connection (More data sources->Active Data)and paste th SQL in there, but I generally advise against it.

Getting usable data objects on the database just makes good sense, and having Views even more because you insulate applications from any database changes, if something changes on the backend, go into the view and change it back, all processes will continue along.

Good luck.

-k

 

[Tama]

Thank you synapsevampire

Unfortunately I do not have write access to my database - which I believe I would need to create views (please tell me I'm wrong on this one.) My database is in a country-wide consortium with many layers of PHB - so there's more chance of me climbing Everest in my underwear than getting write access.

Regarding my ODBC connection - I think I might have a chance in getting permission to connect via Oracle, so I'll look into this.

Why would you advise against ADO? Are there any particularly ugly problems I could encounter?

Does Crystal 9 have a better interface for SQL? It might be a bit of a mission to get the powers that be to spend extra money on it - perhaps I should hold my breath...

Cheers
Tama

 

[mdwyer]

Why your DBA's wouldn't create your own schema for you to write views I can't understand. That schema can be insulated against the rest of the database so that you can't do any more than you can now - and perhaps less, since they would have to grant specific access to the database objects of interest.

Then again, maybe the ODBC connection you are currently using is already a controlled-access account. If you can write SQL against it, putting it into a View inside that schema doesn't compromise the database or affect performance any more that running it from outside.

But there are corporate politics and turf wars to deal with, and probably questions about who has the responsibility for maintenance. But 300 Oracle tables is pretty small potatoes to get worked up about.

 

[hkim]

Hey Tama,

Since you're using CE 8.5, I assume you're also using Crystal Reports 8.5 Developer. In CR8.5 you can create reports directly from SQL statements within CR (since you have no rights on the database).
Here's how: select new report (as a blank report), under the data explorer select MORE DATA SOURCES, ACTIVE DATA(ADO), with the ODBC(ADO) option selected, use the drop down menu to select your ODBC/DSN name, click ok and when the data explorer shows again, just add the "ADO" table, select the SQL option box, and enter your SQL statement there. That's it.

Hope that helps.

 

[synapsevampire]

A seperate schema sounds reasonable, but to address your questions:

Writing to tables isn't required to create Views, it's a separate privilege.

As for using native connectivity, you don't need anyone's permission, just switch the connectivity by using Database->Set Location

As for ADO connections, this means that you embed the SQL into the report, again, the wrong place for it. Plus you lose the ability to modify the SQL using the record selection.

But if your SQL is efficient, and you don't have a large rowset being returned as a result of it, you'll be OK.

Crystal 9 also allows you to paste SQL in, but you can still pass the record selection along to the database as SQL.

-k

 

[Tama]

Thanks for the input guys - I'm going to give hkim's instructions a crack on Monday. It looks like I'll have to read up on ADO (still have no idea what that stands for) before plunging in, so I don't restrict myself to much - thanks for the head's up synapsevampire.

Mdwyer it's one of those sprawling Dilbert-esque set ups with about a dozen Admins of various flavours spread around the country trying to swim through the corporate treacle. You should have seen how much dancing we had to do to get "them" to let us to use Crystal instead of (wait for it...) MS Access for generating reports. The joys of responsibility coupled with the inability to make "big picture" changes - you know the drill

Will let you know how I go on Monday.

Cheers
Tama

 

[synapsevampire]

ADO is Active Data Object, one of the MS standards.

Try to push for doing things on the database though, this is a stopgap measure and I would write it up that you're using a method not suggested because of inadequate database support.

-k

 

[Tama]

Progress (or lack of) so far...

The ADO solution worked well on the Crystal Designer end of things but Crystal Enterprise throws a tantie if I try to run any of my created reports and spits up the following error:

Error Message: Cannot find database DLL. File C:\Program Files\Crystal Decisions\Shared\Temp\procSched\[SERVER_NAME].report\~tmpb5034a05060880.rpt.

A searching on the Crystal Support pages suggest that this error should only occur on 64-bit AIX boxes... when our Crystal server is Wintendo... sigh...

Attempts to connect directly to the Oracle Server through Crystal are met with "ORA-12514: Message 12514 not found; product=RDBMS80; facility=ORA" - which I'm guessing is the firewall in action.

SV - I'm interested in your view idea but unsure how to implement it/ test my ability to create views. Is there some SQL syntax I could use via something like Oracle SQL Plus 8?

Sorry to be so useless with this - the majority of my database experience has been with MySQL with total control - so while I can write basic to intermediate SQL queries in my sleep I'm far too used to total control on a simpler system. Getting there though.

Cheers
Tama

 

[hkim]

The error message from CE when using the "ADO" solution maybe due to this: make sure their is an identical ODBC/DSN name on the CE server machine (or check what ODBC/DSN that the server uses, and create an identical one on the CR machine and stick with that one for all the reports).

I have been using CE8.0/CR8.5 with the "ADO" solution with no problems.

 

[mdwyer]

ORA-12514 is a names mapping error, as though your tnsnames.ora file does not contain and entry for your database name.

ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor

Cause: The SERVICE_NAME in the CONNECT_DATA was not found in the listener's tables.

Action: Check to make sure that the SERVICE_NAME specified is correct.


SQL*Plus can be used to create views, with the general, simple syntax being:

CREATE OR REPLACE VIEW name_of_view AS your_sql_statement;

Simple example: CREATE OR REPLACE VIEW CURRENT_TIME AS SELECT SYSDATE NOW FROM DUAL;

(Pseudo-columns not actually in the table, or columns modified by functions, will need alias names, like "NOW" in the example.)

 

[Tama]

Someone smother me with a pillow and put me out of my misery!

I talked with the Oracle Admin and he said "No create view access, or extra schema - get Crystal upgraded to 9", so I talked with the Crystal Admin and he said "Use the GUI designer."

So I'm back to square one - but at least I've had my Crystal Reports patched so it can connect to the AFS. I went back to looking at ADO and am now getting the following error:

Microsoft OLE DB Provider for ODBC drivers:
The request properties can not be supported by this ODBC driver.
Which seems to relate to this known error in Crystal:

http://support.businessobjects.com/library/kbase/articles/c2006320.asp

which suggests the following "workaround"

A workaround is to create the report using a Data Definition File. 

If data from the Oracle data source is required to assist with the creation of the report at design time the report can be created using an ODBC driver (P2sodbc.dll) or OLE DB driver (P2soledb.dll). After the report is complete convert the report to the Active Data driver (P2smon.dll) connect to a Data Definition File. 

For information on converting to the Active Data driver refer to Knowledge Base article c2002653

Eh? This seems to put me back in my original situation of not being able to use "pure" SQL. I'm beginning to think that begging for a schema might be my best course of options.

I desperately want to avoid having people up on high say "Use the GUI" - I'm a competent SQL coder and find the GUI clicky clicky thing very difficult to work with. Since a significant part of my role is to product reports for people the idea of not being able to use SQL gives me the creeps. I also have access to a library of SQL queries designed specifically for the Oracle system I'm using.

All the help you've given me so far is really appreciated.

Tama

 

[hkim]

Are u pasting the sql from SQL-PLUS directly into the CR? FYI some oracle sql commands need to be "re-worded" when going thru ODBC. One example is the "LOWER" function: in SQL-PLUS you would use "Select LOWER(fieldname) from ..." while connecting to ORACLE through ODBC you would use "Select {fn LCASE(fieldname)} from ..."

 

[Tama]

Yep - I've already run into some syntax changes but the SQL query I'm using as my test is really really simple - no joins or anything - just a few tables:

SELECT p.LAST_NAME, 
    p.FIRST_NAME, 
    l.LOCATION_NAME, 
    iv.CALL_NO, 
    bt.TITLE_BRIEF, 
    i.ITEM_ID, 
    hi.HOLD_RECALL_STATUS_DATE, 
    hs.HR_STATUS_DESC
FROM LOCATION l, 
    ITEM_VW iv, 
    BIB_TEXT bt, 
    ITEM i, 
    HOLD_RECALL_ITEM_ARCHIVE hi, 
    MFHD_ITEM mi, 
    BIB_MFHD bm, 
    HOLD_RECALL_STATUS hs, 
    HOLD_RECALL_ARCHIVE ha, 
    PATRON p
WHERE l.LOCATION_ID=i.TEMP_LOCATION AND 
    i.ITEM_ID=iv.ITEM_ID AND  
    i.ITEM_ID=mi.ITEM_ID AND 
    mi.MFHD_ID=bm.MFHD_ID AND 
    bt.BIB_ID=bm.BIB_ID AND 
    hi.ITEM_ID=i.ITEM_ID AND 
    hi.HOLD_RECALL_STATUS=hs.HR_STATUS_TYPE AND 
    ha.HOLD_RECALL_ID=hi.HOLD_RECALL_ID AND 
    ha.PATRON_ID=p.PATRON_ID AND
    hs.HR_STATUS_DESC<>'Charged'
;

 

[synapsevampire]

I would write up the case for permissions to build Views/SP's and submit it to Management.

Oracle dba's are notoriously old fashioned and fascists, I usually don't deal with them directly, rather I write up a justification for permissions and submit it to the Management.

If the Management denies the request, you have it in writing from that they prefer not to use best practices for whatever BS reason (there isn't a reasonable excuse for this), and hopefully at some point when asked by their Management you can demonstrate that the CIO needs to be replaced, possibly by you

Good tools for creating PL/SQL are:

Toad
PL/SQL Programmer

I prefer PL/SQL Programmer, but there's a freebie version of Toad (as opposed to the shareware version of PL/SQL Programmer).

Each sports a GUI for building SQL, so you should be able the generate Views immediately.

-k

 

[hkim]

Hi Tama,

Is that the SQL u posted into CR? if so, remove the semi-colon (";") at the end of the sql statement!!

Hope that works.

 

[Turkbear]

Hi,
To synapsevampire :
As an Oracle DBA I take exception to your statement:
Oracle dba's are notoriously old fashioned and fascists, I usually don't deal with them directly, rather I write up a justification for permissions and submit it to the Management.

If anyone does that in our org, management would refer the request to me ( or someone in my DBA group) to insure that the enterprise environment is not adversely affected by the requested task.
If we are held responsible for the performance of the database environment, we need to have some degree of control over what is done with ( and to) it..Many managers ( even IT managers) have little knowledge of the possible unintended consequences for seemingly simple actions.

As to being fascists, we have ways to make you change your mind..

 

[Tama]

I'm happy to report that I installed a shiny new copy of Crystal Reports 10 on my PC at work today. Crystal Enterprise should be upgraded to 10 a bit after Easter (but I'm not holding my breath.) It just took a bit of manoveuring to get me there.

From what I've seen today Crystal 10 is just what I needed - nicely adaptive with SQL - and endless options for report display.

Thank you for all of your advice with this.

Cheers
Tama

I do my sums on fingers and thumbs.

 

[synapsevampire]

Well you're clearly an exception, Turk, so you're exempted, exiled and exalted.

I've worked as a DBA at many sites, though only once as an Oracle dba. Generally I'm a SQL Server dba (I know, that doesn't count...).

But please don't try to convince the world that Oracle dba's/coders aren't old fashioned and fascists, just look at the tools the majority use and suggest (SQL Plus, Baaabeee!!!).

And that's only because Larry no longer sports a punch card interface.

I understand the concern to evaluate all processes which impact the database, but the common thing from every Oracle dba's smirking maw is "No!".

Then they ask what the question was... Once I explain what I meant, they go read for a few days, and then come back and pretend that they inveted the concept, and start pushing it as their idea. Today it was a question I had regarding GUIDs on Oracle. The dba pretended that he knew what they were, and when he said something absurd, I gently corrected him and nudged him into the RTFMing to prevent a major ego meltdown.

I've done PL/SQL programming for about 10 years, and I agree with the assessment that Larry does 2 things well, builds databases, and creates jobs

<duckin' and runnin'>

-k