Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Cognos passing paramters from Cognos to sql stored proc

Posted on 2008-06-24
7
Medium Priority
?
4,321 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
RWrigley earned 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 …
How to increase the row limit in Jasper Server.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

604 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