Cree
asked on
CRYSTAL REPORTS EXECUTE PROCEDURE BEFORE RUNNING REPORT
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?
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?
Gary Coltharp
Why not add it to the beginning of the stored procedure that runs your report.
Does that SQL run in the database?
mlmcc
mlmcc
ASKER
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.
The SQL does run in the database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
What program do you use to run this report ?
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.
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.
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.
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.
ASKER
Thanks Vasto, Didn't think about the view. Was making it a lot more complicated that it had to be.
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.
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.
ASKER
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.
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.