Solved

Cognos passing paramters from Cognos to sql stored proc

Posted on 2008-06-24
7
4,051 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
ETL Sql Server OLE Source  displays preview data - but exports nothing 4 340
tableau restapi 7 232
SSRS and Visual Studio Setup 7 89
SCCM Microsoft Report 2 79
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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