Cognos passing paramters from Cognos to sql stored proc

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?
ac_davis2002Asked:
Who is Participating?
 
RWrigleyCommented:
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
 
ac_davis2002Author Commented:
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
 
RWrigleyCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ac_davis2002Author Commented:
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
 
ac_davis2002Author Commented:
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
 
ac_davis2002Author Commented:
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
 
ac_davis2002Author Commented:
first class Thanks again!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.