Cognos Analytics

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Hey Brenda,

I use a SP to let users decide which kind of ETL job they want to perform during the day. The Sp has two parameters: pUSER and pJOB. Both are linked to macro functions in FM to achieve the desired behaviour: pUSER can't be modified as this is the user that clicks the button and pJOB is a prompt value that comes from a drop-down list.

Here's my setup in FM. As you can see, you "just" have to write the right macro function for each parameter. Of course you can use this short version or even type the default values right into the prompt macro. It's up to you (hope this helps):

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Hi Mr. Robert,

I created a value prompt (dropdown list) that references a querie that uses a very similar scenario that you a re showing here. My issue is that this only work when I execute the query itself, with the report don't works... do you have any idea what's could be happening in my case?

Thank you so much.

Hey Brenda,

I use a SP to let users decide which kind of ETL job they want to perform during the day. The Sp has two parameters: pUSER and pJOB. Both are linked to macro functions in FM to achieve the desired behaviour: pUSER can't be modified as this is the user that clicks the button and pJOB is a prompt value that comes from a drop-down list.

Here's my setup in FM. As you can see, you "just" have to write the right macro function for each parameter. Of course you can use this short version or even type the default values right into the prompt macro. It's up to you (hope this helps):

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Hi @Rudak Duarte Mendonça,

what I've forgotten to tell is that if you use a stored procedure you always have to add one OUT parameter to it. This is the case as Cognos always needs returned data from a query. If you only create IN parameters to be sent to the SP, then Cognos does not execute the query as there's no data coming back.

Here's an example of a very simple SP that inserts a row into a table and returns a string to the Cognos query:

In the Cognos query and on the report page (somewhere) you have to place the return data column so the query get's executed. Cognos then sends the data via IN parameters to your SP and the SP returns the data of the OUT parameter which Cognos than shows o the report page.

I use conditional blocks to change what is displayed. If the parameter is empty/null, the drop down box is shown. When a user selects and sends the value of the drop down box, the parameter is not null any more and the conditional block changes to show the returned data from the query.

Is that helpful?

Hi Mr. Robert,

I created a value prompt (dropdown list) that references a querie that uses a very similar scenario that you a re showing here. My issue is that this only work when I execute the query itself, with the report don't works... do you have any idea what's could be happening in my case?

Thank you so much.

Hey Brenda,

I use a SP to let users decide which kind of ETL job they want to perform during the day. The Sp has two parameters: pUSER and pJOB. Both are linked to macro functions in FM to achieve the desired behaviour: pUSER can't be modified as this is the user that clicks the button and pJOB is a prompt value that comes from a drop-down list.

Here's my setup in FM. As you can see, you "just" have to write the right macro function for each parameter. Of course you can use this short version or even type the default values right into the prompt macro. It's up to you (hope this helps):

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Hello Mr. Robert,

Thank you for your kindness. Let me explain the details. I think my SP already has a return value, because when I run the query inside Cognos Reports, I get "1" as a result. Let me share my SP source:

Explaining the scenario: the user will choose a month to open, but can close all months. By choosing "Close", all months will be closed; If you choose a single month, that one will be open and the others will be closed. Simple here.

The problem is: I created a dropdown prompt object that uses SP through Framework, but running the entire report doesn't work:

But when I run the "View Tabular Data" of the same query used in the dropdown object, the SP runs and updates the table normally:

The main question is: why, in my case, does it only work with "View Tabular Data" and does not work when running reports, if they both use the same query?

Thank you!

Hi @Rudak Duarte Mendonça,

what I've forgotten to tell is that if you use a stored procedure you always have to add one OUT parameter to it. This is the case as Cognos always needs returned data from a query. If you only create IN parameters to be sent to the SP, then Cognos does not execute the query as there's no data coming back.

Here's an example of a very simple SP that inserts a row into a table and returns a string to the Cognos query:

In the Cognos query and on the report page (somewhere) you have to place the return data column so the query get's executed. Cognos then sends the data via IN parameters to your SP and the SP returns the data of the OUT parameter which Cognos than shows o the report page.

I use conditional blocks to change what is displayed. If the parameter is empty/null, the drop down box is shown. When a user selects and sends the value of the drop down box, the parameter is not null any more and the conditional block changes to show the returned data from the query.

Is that helpful?

Hi Mr. Robert,

I created a value prompt (dropdown list) that references a querie that uses a very similar scenario that you a re showing here. My issue is that this only work when I execute the query itself, with the report don't works... do you have any idea what's could be happening in my case?

Thank you so much.

Hey Brenda,

I use a SP to let users decide which kind of ETL job they want to perform during the day. The Sp has two parameters: pUSER and pJOB. Both are linked to macro functions in FM to achieve the desired behaviour: pUSER can't be modified as this is the user that clicks the button and pJOB is a prompt value that comes from a drop-down list.

Here's my setup in FM. As you can see, you "just" have to write the right macro function for each parameter. Of course you can use this short version or even type the default values right into the prompt macro. It's up to you (hope this helps):

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Hello Mr. Robert,

Thank you for your kindness. Let me explain the details. I think my SP already has a return value, because when I run the query inside Cognos Reports, I get "1" as a result. Let me share my SP source:

Explaining the scenario: the user will choose a month to open, but can close all months. By choosing "Close", all months will be closed; If you choose a single month, that one will be open and the others will be closed. Simple here.

The problem is: I created a dropdown prompt object that uses SP through Framework, but running the entire report doesn't work:

But when I run the "View Tabular Data" of the same query used in the dropdown object, the SP runs and updates the table normally:

The main question is: why, in my case, does it only work with "View Tabular Data" and does not work when running reports, if they both use the same query?

Thank you!

Hi @Rudak Duarte Mendonça,

what I've forgotten to tell is that if you use a stored procedure you always have to add one OUT parameter to it. This is the case as Cognos always needs returned data from a query. If you only create IN parameters to be sent to the SP, then Cognos does not execute the query as there's no data coming back.

Here's an example of a very simple SP that inserts a row into a table and returns a string to the Cognos query:

In the Cognos query and on the report page (somewhere) you have to place the return data column so the query get's executed. Cognos then sends the data via IN parameters to your SP and the SP returns the data of the OUT parameter which Cognos than shows o the report page.

I use conditional blocks to change what is displayed. If the parameter is empty/null, the drop down box is shown. When a user selects and sends the value of the drop down box, the parameter is not null any more and the conditional block changes to show the returned data from the query.

Is that helpful?

Hi Mr. Robert,

I created a value prompt (dropdown list) that references a querie that uses a very similar scenario that you a re showing here. My issue is that this only work when I execute the query itself, with the report don't works... do you have any idea what's could be happening in my case?

Thank you so much.

Hey Brenda,

I use a SP to let users decide which kind of ETL job they want to perform during the day. The Sp has two parameters: pUSER and pJOB. Both are linked to macro functions in FM to achieve the desired behaviour: pUSER can't be modified as this is the user that clicks the button and pJOB is a prompt value that comes from a drop-down list.

Here's my setup in FM. As you can see, you "just" have to write the right macro function for each parameter. Of course you can use this short version or even type the default values right into the prompt macro. It's up to you (hope this helps):

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?

Re: Cognos Report Studio with prompt values passed to Stored Procedure which returns report data - is it possible?

I think it is, but having a hard time getting started and I don't know the correct terminology to use. I am an MS SQL developer so the SP is not the problem. It is the FM. 

So first step would be to import the SP into FM. Obviously the SP (MS SQL) would have three varchar parameters and have a single SELECT at the end (single result set returned) .

Second step is the prompts. Two of the prompts read the same table - RelationshipCode. Prompt1 - client selects each RelationshipCode that is a Sole Owner. Prompt2 - client selects each RelationshipCode that is a Joint Owner. Prompt3 - reads the account table with a DISTINCT needed on AccountType. If the client enters nothing in one of the prompts I want to be able to know that in my SP and will use default values. 

Do the prompts need to be in FM? And if the client selects multiple entries for a prompt will they be passed as a CSV string to the SP? Guess how to setup the prompts and then associate the selections to the SP is my primary stumbling block. 

Does anyone have a small example, any doc, links or even informal steps on how to get started?