Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 16 days ago

    I want to build a report where the user can enter text, press a button (or text box just loses focus) and the report definitions would be searched for matching text. Any matches would be displayed in a list: report name, report path, and a preview of the matching text. I know how to search report definitions today and build the contents for the list. What i need is how to create a report with an input text box and how to trigger it to run a stored procedure that is passed the text box contents as an input parameter. 



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------


  • 2.  RE: How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 15 days ago

    Hi Brenda,

    In case you have access to the book "IBM Cognos 10 Report Studio Cookbook", Chapter 2 - "Advanced Report Authoring: Writing Back to the Database" - describes something similar. This method does require some work in Framework Manager if you choose to follow it. You will need to import the stored procedure as a "Stored Procedure Query Subject" and then define a macro around it.

    To be honest, I haven't personally tried this yet, but a colleague of mine has had success with it. Please send me a DM if you'd like more information.



    ------------------------------
    Martin Poulsen
    ------------------------------

    Original Message


  • 3.  RE: How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 12 days ago

    Hi Brenda, you could import the stored procedure into your Framework Manager model, ensuring the SP contains the prompt variable.  Create a data source query subject based on the SP and publish the model.  In Report Studio, create a query based on the query subject and use a prompt control (i.e. text box prompt, or value prompt), either on the report page or a prompt page; a value prompt could be configured to auto-submit whereas a text box prompt would require the use of a button to re-prompt, sending the entered value to the underlying SP.  You would need to make sure that your Report prompt name exactly matches that of the parameter name in your SP to ensure proper passing of the report prompt value to the SP parameter.

    It is a bit of work to setup, but once you have it in place, you can re-use it and/or the same process for other SPs that are similar in nature.



    ------------------------------
    Dion Paul
    Cognos Administrator
    Ascend SC
    Daytona Beach FL
    dpaul@scgts.com
    ------------------------------

    Original Message


  • 4.  RE: How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 14 days ago
    Edited by Dinesh K. Dhiman 14 days ago

    HI Brenda,

    We demonstrated it in one of our AMA sessions recently.

    I plan to publish article on this topic.

    In the meantime, if its urgent please feel free to DM me, I can plan and setup meeting to guide you on this.

    ------------------------------

    Thanks

    Dinesh K. Dhiman

    Product Manager (Tech.), Cognos Analytics Offering Management

    ------------------------------

    Original Message


  • 5.  RE: How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 9 days ago

    Hi Brenda,

    We have built reports that pass parameter values to SPs and use the results of those SPs.  As Dion Paul mentions, one way is to create query subjects in Framework Manager that reference the SP.  If you're using SQL you can also use the SQL query insertable object within Reporting [studio] to call the SP.   (FM of course is nice for re-usability). 

    We're not using any freeform text box prompting, but I did a quick example where I used a freeform text prompt along with a "Finish" button, and the technique of calling the SP from a SQL query object in Reports, and simply return the string I entered via an SP to a list.  In your case, the SP would be doing more work than that.   The key (as Dion noted) is to name the prompt in Cognos exactly the same as it is in the SP.  Here's an example of the syntax I use when calling the SP via a SQL query within Reporting [studio]:  {EXEC [DBname].[schema].[NameOfStoredProc] #prompt('@textbox1', 'nChar')#}

    ...and the name of the prompt in the Cognos report is "@textbox1"  [no quotes] 

    From your description it sounds like you have the content store query in hand; I presume that within that query you'd also handle any case-sensitivity or wildcards around the freeform text entered by the user, but yes this sounds do-able.



    ------------------------------
    Mike Comiskey
    ------------------------------

    Original Message


  • 6.  RE: How to build a report with an input text box that triggers a stored procedure and the text box contents is passed as a parameter.

    Posted 6 days ago

    I got pulled in another direction but plan on getting back to this. Just want to thank everyone for your replies and for reaching out. 



    ------------------------------
    brenda grossnickle
    BI Programmer Analyst
    FIS
    ------------------------------

    Original Message


Related Content