How to run a report, WAIT untill it's finished before another report is started

Cognos 8 Business Intelligence Report Studio
1

Hi all,

We want to run a report from DataManager, but we do not want to run the reports simultaniously.

How should we do that?

Regards,

Rob

2

Hi Rob,

can you give some more details please?

like what would you like to achieve exactly?

thx

3

Hi CognosGirl,

I want to run a report, wait untill it’s finished before I’ll do the next report.

Now I fire off a trigger, a lot of report runs at-the-same-time, causing some reports to fail.

Than I made multiple triggers for each report an own trigger, but still some reports run together, despite the wait-commands…

Regards and thanks,

Rob

4

Hi Rob,

Why do the reports fail?

With a timeout error ie. All low affinity connections of the reportserver(s) are used for report building and some get a queue timeout?

If # of reports <= low affinity connections available
Use 1 job and execute all reports “at once” (for a given schedule)

If # of reports > low affinity connections available
Use N jobs – where N is the # of low affinity connections available
Divide the # of reports to run by N and assign each group to a job
Set each job to run on the same schedule
Execute all jobs “in sequence”

:wink:

5

Hi Guru,

Execute all jobs “in sequence”… that’s the problem… There is no return value to know if the first step of that job is finished…
So kicking off, results in all report running in the same minute.

Regards,

Rob

6

Rob,

maybe this helps… Found it on my computer but i never have used this. Because you are working with the content store database, do NOT forget to make a backup first! ;D

Using Event Studio, a data-modification stored procedure can be used to update a table within the database. The ETL can monitor this table for the update, and proceed with the next task.

Steps:

  1. Create a table that will store the flag when the job is complete. Example DDL is:
    create table isComplete (Complete int);
  2. Insert a value of 0 into the isComplete table.
  3. Create a stored procedure that updates the table. Example Stored Procedure (written for MSSQL) is:
    CREATE PROCEDURE dbo.isCompleteSP
    @inp_someval int
    AS
    update isComplete set Complete = 1 where Complete = 0;
    GO
  4. Create a new Framework Manager Model, and import the isComplete table and the isCompleteSP stored procedure.
  5. Doubleclick on the isCompleteSP stored Procedure, change the Type from Data Query to Data Modification.
  6. Create a package and publish
  7. Create a new Event Studio event.
  8. For the Event, drag in the [Complete] query item and set the Detail expression to [Complete] = 0
  9. Click on the Add a task drop down, and go to Advanced -> Update a database
  10. Edit the new Task, and set the Package to the new package, and select the ‘Data modification stored procedure’ to isCompleteSP.
  11. Save the Event
  12. Create a new Job within Public Folders
  13. Set add all the desired reports, and set the Report Output defaults
  14. Add in the Event as the last object in the job, and set the ‘Submission of steps’ to ‘In Sequence’ and ‘Continue on error’
  15. Schedule the job (such as a Triggered event)

Now, whenever the job is executed, the last step would be to update the isComplete table to have the value of one. At this point, you can have your ETL polling the table looking for this flag.

Let me please know if it works for you. :slight_smile:

7

Hi,

Isn’t this simply a case of:
a) Defining a ‘Job’ on the Cognos 8 server to run all the selected reports, with the job configured to run the reports in sequence and continue on failure.
b) Schedule the job based on a trigger.
c) Fire off the job by launching the batch file for the trigger from within Data Manager (use a System() function).

MF.