We help IT Professionals succeed at work.

CRYSTAL REPORTS EXECUTE PROCEDURE BEFORE RUNNING REPORT

Cree
Cree asked
on
Medium Priority
586 Views
Last Modified: 2012-05-12
is it possible to have crystal reports execute a simple command line or stored procedure before running the report?

i just need this run:

DELETE FROM udVerified
WHERE Vendor > 0
INSERT udVerified (Vendor)
select Vendor from APVC
where APCo = 1 and Verify = 'N'

before data is populated into the actual report.

I've attempted to add it in the Add Command Line .. but i just get errors.  IDEAS?

Comment
Watch Question

Gary ColtharpSr. Systems Engineer

Commented:
Why not add it to the beginning of the stored procedure that runs your report.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Does that SQL run in the database?

mlmcc

Author

Commented:
The report was created by the vendor that has pretty complicated equations in it  It would be easier for me to add a table to the report rather than rewrite the entire report.

The SQL does run in the database.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Are you running the report using a SP or a COMMAND?

IF an SP then I don't think you can get anything to run before te SP because the SP is run before the report is opened

mlmcc

Author

Commented:
not running the report of a sp.  all tables..  just want a sp to run before report is populated.  

I've created my own sp seen here:

Create Procedure udNotVerified AS
DELETE FROM udVerified
WHERE Vendor > 0
INSERT udVerified (Vendor)
select Vendor from APVC
where APCo = 1 and Verify = 'N'

As you can see from the image, the "udVerified" Table is used in the report.  I just need the table to be updated directly before the report is populated.  

Our program doesn't allow that to happen, so to avoid adding an additional step of having to remember to hit a button (run the sp) before running the report, I was hoping CR would do it.

SCREENSHOT019.jpg
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
How do they run the reports?

Is it through an application you can modify?
If so you could modify the code that runs the report to runthe SP

You might be able to add the SP to the report as part of the data source just don't link it to the tables and don't select anything from it.  It might run first.

Another way would be to use  UFL that exposes a founction to run the SP.  The function could be used as a formula in the report header.

mlmcc
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
There are a couple of ways to do this:

1)  Rewrite the report to be based on a SQL Command and make sure you include this functionality at the beginning of the command. This is cumbersome, but doable.

2)  Use some sort of front end that executes the proc before the report is called
CERTIFIED EXPERT
Top Expert 2011

Commented:
What program do you use to run this report ?
 
CERTIFIED EXPERT
Top Expert 2011

Commented:
BTW can you create a view ( or query)  udVerified to replace the table udVerified? Then you don't need to refresh the table before to run the report.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
A view is treated just like a table as far as Crystal Reports is concerned, except that it's materialized at runtime.  So, in order for it to pull in the correct data, the proc would still have to be run prior to the report.
CERTIFIED EXPERT
Top Expert 2011
Commented:
rhinok I am not sure I understand, is this a problem ?

It is not necessary to change anything in Crystal reports, the change is in database.
If the table udVerified is deleted from the database and a view (or query) is added (to the database) then the data in the object udVerified  will be always current.

Keeping udVerified as a table is not necessary since it is recreated before each use.

The call to the database in case there is a view will be :
select Vendor from APVC where APCo = 1 and Verify = 'N'
In case there is a table the call will be:
select Vendor from udVerified
No difference for Crystal.

I can see an issue with naming convention though.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect
Commented:
That's true, but it doesn't really have anything to do with the original question, which is "how to execute a stored procedure before the report?"  So, it doesn't really matter whether or not the OP uses a table or a view as the data source, the results are the same and both still rely upon the proc.

Author

Commented:
thanks guys for the suggestions.  I talked to the creators of the application and they told me that what i wanted to do could not be done  the way i wanted it to.. So i created a schedualed job in SQL studio to run the procedure on a semi hourly basis.  I alerted the users that any recent changes in a vendor's compliance would not be reflected on the report but would show up with in an hour.  
The good news is that this report is not run often.. and the compliance verification is not overly crucial.  For as little as verification changes happen, I estimate it to be incorrect very little. Plus they would have to verify the it anyways.  

Thanks for all your help, I will spread out the points between all of you.
CERTIFIED EXPERT
Top Expert 2011

Commented:
rhinok, are you saying that the view will not work without running a proc ?

I agree that is not an answer for the question "how to run a procedure before to run a report" but the solution will not require to run procedure at all. The report will work even better because will avoid inserting records in a semi-temp table and the view will use the existing indexes.
CERTIFIED EXPERT
Top Expert 2011

Commented:
If you prefer to use an application. Check the viewers here: http://www.kenhamady.com/bookmarks.html#viewers

At least one application from this list (http://r-tag.com/ReportManager.aspx ) will allow you to run a command before to run a report. This looks pretty basic feature to me so there should be other viewers too.

However switching to another application is not necessary. Do you have vendors with Vendor <= 0? If you don't then replacing the table with a view will resolve your issue and will make the report even faster.

Author

Commented:
Thanks Vasto, Didn't think about the view.  Was making it a lot more complicated that it had to be.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
I still don't understand the need for the view.  Isn't the original requirement that you have to actually delete and update a table that's used by the report? The assumption is that if this is what you're trying to do then you have a legitimate business reason for doing so.  

If all you need to do is filter the report further, then a view might be a good idea. Personally, I like using Views as the basis for as many of my reports as I can use.

Author

Commented:
rhinok.  I'm not deleting the table. I'm deleting all rows on the table and then inserting ones that fit that filter.  A view allows me to run the query without ever having to use an additional table.
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
Right - that's what I meant.  The point being you were deleting and reinserting data.  So there wasn't ever a legitimate business reason for that?  I can see why a view would be fine, then.