Solved

Using SQL within Cognos

Posted on 2008-06-19
2
1,591 Views
Last Modified: 2012-06-21
Hi

I have a query in sql that uses temp tables and table variables.  I would like to use this to generate a report through Cognos. Firstly can anyone tell me if you can use queries containing temp tables and variables in Cognos?

Cheers
0
Comment
Question by:ac_davis2002
2 Comments
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 21821709
Your best bet for that is to create a stored procedure.  You can then either import the stored procedure into Framework Manager, or put the exec command in a SQL object in your report.  You would need to use prompt macros to insert variables into the SQL or Exec statment.

Note that Cognos has some limitations on what kinds of SP's it'll accept.  The SP has to return only one dataset; less and we'll error, more and we'll ignore the data.  There should be some information on this in the Framework Manager Users Guide.

0
 

Author Comment

by:ac_davis2002
ID: 21821841
Thanks RWrigley

Cheers
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now