Solved

Cognos passing paramters from Cognos to sql stored proc

Posted on 2008-06-24
7
4,172 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

717 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