command object does not refresh correctly

[dtboy]

Hello,
I'm a pretty experienced Crystal user but I've never had to use a command object. In this case, what I'm using it for is to call a stored procedure, which then pre-aggregates a very complex dataset in the database (Oracle 9i). I don't know why Crystal doesn't give you a simple option to just execute a SP before running a report, they want you to create a package and actually draw the results out of it, very convoluted and unncessary.

So I've created a parameter and the command object, here is the command object syntax:

BEGIN EE_SETMASTERDATE_SP (to_char({?NewDtParam},'YYYYMMDD')); END;

FYI, if you ever have to do this one of the tricks is that you can't have returns in the box, everything has to be on 1 line even though it will wrap.

Now the incredibly frustrating part: it will refresh correctly once, when the report opens and you choose a date. Then even if you press the refresh button, the main report will refresh but the command objects will not call the database again. I can be sure it is not the DB's fault because I can mess up the syntax of this SP and it doesn't catch it until you either restart crystal or close and reopen the report, which also makes a real refresh happen.

I've tried every possible combo of the "refresh on open" etc. buttons. This is Crystal Reports XI on Win XP. This is just such silliness, I feel like there must be a secret registry setting or something to make it work.

I know this is kind of out there...not many people use command objects...TIA for any ideas.

 

[synapsevampire]

Try:

execute EE_SETMASTERDATE_SP (to_char({?NewDtParam},'YYYYMMDD'))

I didn't try it but it makes more sense to me.

Also curious why you don't just use the SP as the data source within crystal and eliminate the Command Object?

I also assume that the command object is the only data source within the report.

I see that you're changing the data type in the Command Object, but you could do this within the SP anyway using a to_char against the date being passed and just pass it a date, which seems to make more sense anyway long term rather than forcing code to pass a properly formatted string.

-k

 

[dtboy]

synapsevampire,
Thanks for the quick reply. That seems like SQL server syntax, I got this error msg when I tried it...

Failed to retrieve data from the database.
Details: ORA-00900: invalid SQL statement

I think it expects you to put exactly into that box what the command would be in sqlplus.

I don't just use the SP as the datasource because I've not done it before and I don't know what to expect, for one thing I don't know if I can just set datasource location from the table I have now to the new stored procedure cursor. Also I don't know how the joins to other tables are going to behave. As I said that is a pretty kludgy option for someone who just wants to use a stored procedure to rebuild a small table and then report off of it.
Thanks again.

 

[synapsevampire]

Try:

EXEC EE_SETMASTERDATE_SP (to_char({?NewDtParam},'YYYYMMDD'))

-k

 

[synapsevampire]

I sould also have mentioned that it doesn't expect exactly what you palce i SQL Plus, it expects what the database driver can properly process in the Command Object.

And EXEC is Oracle syntax, but you're right, I used SQL Server syntax, too many databases syntax floating around in this tired ole noggin...

-k

 

[dtboy]

No problem I mix them up sometimes too because I also develop in SQL server.
EXEC is oracle syntax in SQL plus to run a sql script that is a local file but I've never used it to run a procedure residing on the database.
BEGIN procedure_name END;
is what I've used to call procedures inline in SQLPLUS or in another procedure.

 

[Turkbear]

Hi,
If you already have the SP just be sure it returns a REFCURSOR and accepts a parameter.

Use the SP as your datasource and Crystal will prompt you for the parameter..


SPs in Oracle are the best ( IMHO) way to handle complex queries/joins/subqueries ....





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Did you try it and did it resolve the issue?

I should have also addressed the connectivity type, use the native connection for the best performance, and if you elect to use the slower ODBC type, select the Crystal supplied Oracle ODBC driver, not Oracles, and make sure that you turn on the ODBC option of procedure returns results.

SPs need to be in a specific format when using them directly within Crystal.

-k

 

[dtboy]

Turkbear, synapsevampire,
Thanks. exec didn't work. I'm sure I have the right syntax to call the sp because it does work the first time. I am using native oracle drivers.

Turkbear, I will have to try it this way, I suppose. But like I said it's totally kludgy if they make you do something like that. I have numerous concerns and hate to burn up client time when it might not work. For example, Will I be able to point all of the fields at the new datasource or will I have to completely recreate the report? Can I form a link between a cursor from an SP and another table? It seems to be Crystal will have do that join itself. I'm concern about that because of performance slowing.

Also I had to go through all the work and have the same goofiness happen with the updated approach i.e., it only runs the stored procedure when it first opens. Hope not! You'll certainly get an update from me either way. Thanks.

 

[dtboy]

BTW another thing I'm thinking of doing is changing the existing view that the report runs off of (which would have its data updated by the stored procedure) to do a cartesian product of all possible results by date. (time granularity is only per day, thank goodness)
The total number of possible rows would be an alarming 9 million on so if I give them a reasonable time frame (10 years X 2400 rows), but I think in oracle whether or not I indexed the underlying date field, when you use a where clause in a view it will not have to do the full cartesian product. It should only have to scan the master table once which it is going to have to do anyhow. This is a more comfortable approach to me since I'm not confident crystal is going to behave after I go to the hassle of satisfying its conditions for the use of stored procedures.

 

[dtboy]

Oops...I forgot about all of the other aggregation that goes on even to get to the point where you'd have 9 million rows. Not sure it will work. I mean, I could definitely precalculate all 9 million rows and it would work, but then you deal with the physical storage requirements changing drastically - and the time it takes to parse 9 million rows. Also the underlying data still changes every day so you'd have to refresh it every day.

 

[Turkbear]

Hi,
As far as this part is concerned,

But like I said it's totally kludgy if they make you do something like that. I have numerous concerns and hate to burn up client time when it might not work. For example, Will I be able to point all of the fields at the new datasource or will I have to completely recreate the report? Can I form a link between a cursor from an SP and another table? It seems to be Crystal will have do that join itself. I'm concern about that because of performance slowing.

'Kludgy' is in the mind of the beholder, since this is a recommended method of optimizing Reports when using Oracle, I might disagree..

A SP that returns a REFCURSOR is 'seen' by Crystal like any other datasource ( Table, View,Recordset)..
The Join can be in your SP, ideally.
( Crystal always does the Joins defined in the Database Links, so joining the SP to Table should not be any different - assuming same database instance)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[dtboy]

Hhhmmm. I AM one to argue and I'd have to stay on the side of kludgy. A table is a table is table - that is the great thing about a database. Someone else can come along and change the join/link in the future as needed. If I have to write the join within the SP, that will require someone in the future who can edit that SP. Also I suspect it might not allow Oracle to optimize the query the same way...I've read horror stories of SQL running much slower inside a stored procedure than outside.

My theory is that Crystal/BOBJ wants to discourage people from using stored procedures in the highly convenient way I wanted to. Obviously from a coding perspective (I've written application database code in VB and Java as well) - if you can send a parameter to an SP and get the results back as a cursor, it would be the work a moment to just allow someone to arbitrarily activate an SP of their choice and still get the results from tables. (and this reminds me of a last "trick" I plan to try to outwit my friends at BOBJ...to embed a do-nothing subreport that gets a very simple cursor of trivial values back, but still performs the needed operation on the data)

I think they want to try to force developers to put as much of the aggregation/logic/assorted goobledegook into the report itself to make it hard to port to another reporting platform. Because if you are reporting off of pre-computed tables or views with little logic in your reports it is easy to do the same reports in another product. Oh well. I still prefer it to Cognos, and, I can't really hold it against them because I've had a lot of career growth from knowing BOBJ/Crystal.

Anyhow...thanks for contributing everyone. It was appreciated.

 

[synapsevampire]

Sounds a tad paranoid to me...

They've supported SPs for many years, and I've used them throughout, but not in a Command Object, and written to the standards defined by Crystal.

SPs can run slower than SQL because it has a precompiled execution plan, which if not updated, can cause this, as can other factors.

The fact that it doesn't work the way you want it to is a shame, however they do work, and I use them often.

Consider reading up on the requirements for them in crystal and writing and invoking them accordingly.

-k