March 26, 2008 at 6:12 pm
Info:
Embedded SELECT Query written as command type text in the data type.
Environment: SSRS 2005
Data Source: Oracle
Q:
I am trying to implement a custom Report Execution log to my .rdl file where a row will be inserted into an Oracle TABLE Whenever the report is RUN. Is there a way to do this without writing a stored proc. Basically, Add code to the .rdl file to insert a row into the table.
This is what I want to insert into a table----->
INSERT INTO ktran.QC_EXCEPTION_REPORTING VALUES
((Select Nvl(Max(Row_Id),0)+1 From ktran.QC_EXCEPTION_REPORTING),
'Wells',
'WELLID',
'I_PPDM UWI of wells in I_PPDM not found in R_Master', count(*), 'Wells QC Report - Integration Layer And Summary Layer.rdl',
sysdate);
-------------> DDL of the oracle table where the row is inserted.
CREATE TABLE ktran.QC_EXCEPTION_REPORTING
(
ROW_ID NUMBER(10),
SUBJECT_AREA VARCHAR2(80 BYTE),
CATEGORY VARCHAR2(80 BYTE),
REPORT_RULE VARCHAR2(400 BYTE),
Number_of_Rows NUMBER(10),
REPORT_NAME VARCHAR2(180 BYTE),
ROW_CREATED_DATE DATE
)
Thanks in advance for any help.
Regards,
Sarod
March 26, 2008 at 9:06 pm
The data you are logging seems (to me) to be very similar to the logs that are kept by reporting services already. Have a look at the table "ExecutionLog" in the ReportServer database.
Whilst you might need to write a little bit of code to extract the details that you need, why not use the existing logging which you get without having to do anything extra
March 31, 2008 at 7:33 am
That is a great point, but if you have to do what you are asking to do, I would suggest you add a second data source to your report and call to a procedure that does just the logging in the second data set and return something simple like the current date.
Not only will this allow you to return your data set without any trouble caused by the logging, but it is a solution that you could re-use in all of your reporting without having to mess with the complexities of changing all of your data sets. You can also use a second data source making it possible to log data to a single server regardless of where your report data is coming from.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply