Triggers and Javascript

[toddbutt]

I have a report that needs to run via a trigger and ad-hoc.

The trigger is set up and I am able to run the report by running trigger.bat. 
When I run the report via ad-hoc I have javascript that sets the dates and it runs.

The problem I have is that when I run the report via the trigger, the dates do not get set.  It's like the javascript does not run.  If I set the date parameters to optional, it runs from the beginning of time to the end of time.  When I set the date parameters to required, it throws an error because the parameters do not have a value.

Surely there is a way to pass the parameters to the report when is report is fired via a trigger.

I've seen where people say you can do this in Event Studio but I have yet to find where that is in the documentation.  I've searched the ES user guide for the word parameter and found nothing.

I've also hear you can do this via the SDK.  I don't have the SDK right now.  If you have documentation showing this is the only way, point me to where it is.  That will help my case in getting the SDK.

Has anyone done this before?  Ad-hoc seems to be fine.  The javascript runs and sets the dates.  Click Finish and the report runs.  That same javascript doesn't seem to run when the trigger fires the report and I can't figure out how to get the date values I need to the report through the trigger process.


Thanks for any help.

Todd

[CognosPaul]

As you said, the JavaScript isn't triggered. What's actually happening is that Cognos is skipping the prompt page entirely, effectively leaving the date parameters empty.

There is a fairly easy way to fix this. The prompts in the filter can be modified to add some logic to the default value.

Assuming you need the date to default to yesterday, you could try the following:

[Date] = #prompt('ReportDate','date',_add_days($current_timestamp,-1))#

Alternatively, you could work with Event Studio. To begin, design the query to return the values you need to populate the parameters. When you create a run report step, you'll be able to pass the values from that query directly to the parameter. Then you would just schedule the event studio agent with the trigger.

[toddbutt]

Thanks alot.  Seems to work fairly well.

Is this documented anywhere?  I'd like to read more, if it exists.

One more question.  If the prompt you have in your report is pointing to a stored procedure with parameters, does the default value code go in the report's prompt, the stored proc's prompt or both?  Seems to be both but I'm not sure.

Thanks for your help.  I've been looking for the documentation on this for months.

[Grim]

Is this documented anywhere?  I'd like to read more, if it exists.

I LOL'd.  :P

[CognosPaul]

As Grim implied, there isn't much documentation when it comes to triggers.

Parameters obey the last most populated field. Let's take your stored procedure, for example.

In your database, you have a procedure that returns the parameter provided:

Code Select Expand

create proc test
@text nvarchar(1024)
as
select @text as "returnedText";

You import the procedure, and give the @text parameter the macro:

Code Select Expand

#prompt('myText','string',sq('This is a test'))#

On the first run, if you don't enter anything the framework will resolve the macro to 'This is a test', passing that to the sp and returning a single row 'This is a test'.

Publish the package and open RS.

In RS, create a list and drag in returnedText from the sp. When you run it, you'll get a generated optional text prompt. If you don't enter anything, it will use the default value from the macro.
Create a render variable on the list with

Code Select Expand

paramValue('myText') is not null

This ensures the sp isn't run until the myText parameter has a value (remember, if it doesn't have a value, it defaults to whatever is in the macro).

Next, drag in a text prompt and a refresh button. When you run the report, you'll see the prompt and the button. Type something into the prompt and click refresh, and the list will appear with "something". That's because the parameter myText is now populated with the value from the text prompt.

Give that text prompt a default value of whatever. When you run the report, Cognos will pull the default value from the prompt and use that to populate the parameter. So now the myText parameter has a value of whatever, which renders the list, which sends the myText parameter to the sp, which returns "whatever".

We can get even more complex, create another list, fill that with product line, and drag the sp list inside that. Now use a master/detail relationship on the sp list to populate product line to myText. When you run the report, each row in the outside list will populate myText with another value. The master detail is overriding the value selected in the prompt.