Solved

Cognos passing paramters from Cognos to sql stored proc

Posted on 2008-06-24
7
4,013 Views
Last Modified: 2012-06-22
Hi

I have a cognos reports that uses start and end prompts. The report also calls a sql stored procedure to return number of employees to a list report.

Can anyone advise how I can pass the start and end prompts to the procdure paramters please?
0
Comment
Question by:ac_davis2002
  • 5
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 21856604
Probably a prompt macro:

{exec myProc(#prompt('StartDate','timestamp')#,#prompt('ENdDate','timestamp')#)}

I'm not sure about the 'timestamp' part (might need to use 'datetime' instead', but I dont' have a stored procedure handy to test this.
0
 

Author Comment

by:ac_davis2002
ID: 21857408
Hi RWrigley

I have atually made some progress with this but have one small step to make.

I have gone into framework manager and emported the stored procedure into the database view. I have then gone into the querysubeject definition and set the input parameters to be the ?START? and ?END? prompts. When I then run in test within framework manager I get the result. I have republished the package.

But when I try to validate the execute comand within the report in cognos connection it times out. I have taken the query I use to execute the command straight from frame work manager and they are as follows, can you confirm they are correct? I have also attached a screenshot from the query subject definition, could you give me your opinion please

Cognos sql

select
       Executeproc.StartDate  as  StartDate,
       Executeproc.EndDate  as  EndDate,
       Executeproc.noemp  as  noemp
 from
       (call "ICAS DB".ICAS.dbo.Empcount(:P0 timestamp IN,:P1 timestamp IN)) Executeproc

Native SQL
{call "ICAS"."dbo".Empcount(?,?)}
0
 
LVL 12

Expert Comment

by:RWrigley
ID: 21858327
The only potential problem I can forsee is if the system decides it nees to locally process the results (which it would need to do if you attempted any sort of grouping or sorting on the data, somethign that Cognos does automatically unless you turn that off in the query object in Report Studio).  And this would only be a problem if the stored procedure is returning a lot of data, since Cognos would need to retrieve the entire dataset into a local tempary file, and then apply the grouping and sorting as local processing.
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:ac_davis2002
ID: 21858646
ok I see, so the execute commands look ok to you? the procedure is only retuning one row but one of those is a sum of number of emloyees.
0
 

Author Comment

by:ac_davis2002
ID: 21858901
Hi Rwrigley

I now have a differnt problem, the strange thing is i have not done anything different. I am using the following execute command

select
       Executeproc.StartDate  as  StartDate,
       Executeproc.EndDate  as  EndDate,
       Executeproc.noemp  as  noemp
 from
       (call "ICAS DB".ICAS.dbo.Empcount(:P0 timestamp IN,:P1 timestamp IN)) Executeproc

and i am getting the following error, can you give me any pointers?

The table or view "Empcount" was not found in the dictionary. UDA-SQL-0192 A description of the resultset returned from the stored procedure "Empcount" could not be found. RSV-SRV-0042 Trace back: RSReportService.cpp(644): UDASQLException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(177):
0
 

Author Comment

by:ac_davis2002
ID: 21864523
Hi RWrigley

Still making small progress


using your command {exec myProc(#prompt('StartDate','timestamp')#,#prompt('ENdDate','timestamp')#)}

and query profiler I can see the procedure is now being called in the database, my last small hurdle is the procedure is expecting the date formatt 'Jan 01 2007' using your query I am sending 2000-01-01 00:00:00.000000000 so getting a sytax error

do you have anysuggestions on how I can resolve this last bit!!
0
 

Author Closing Comment

by:ac_davis2002
ID: 31470071
first class Thanks again!!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
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.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

10 Experts available now in Live!

Get 1:1 Help Now