Cognos 8 While loop

Hi

Are there any Cognos experts out there that can provide me with the syntax to do a while loop in cognos. It is to be used in a prompted report prompted on dates start date = ?START? and end date ?END?

The query is as follows

While ?START?<  ?END?
BEGIN
                        Select  startdate, EndDate , NoEmployees as noemp
                  from CTContracts
                  Where Fromdate  <= ?END? And  EndDate >= ?START?
                  and LKContractTypeID = 8
         --increment ?START?
      Set ?START? = dateadd(m,1,@startdate)
END



Thanks Adam
ac_davis2002Asked:
Who is Participating?
 
nmcdermaidConnect With a Mentor Commented:
Cognos 8 doesn't have any kind of procedural language like that (except for one MDX function). If you need something like that you would generally write a stored procedure and call it from Cognos.

But there is probably a way to do what you want in a non-procedural way, for example, joining to a calendar table.

What are you trying to do? Enter start and end dates in one prompt, then populate another prompt based on that, or is that While loop meant to populate a list?


The psuedo code that you have is going to give you duplicates.

For example if your prompts have a start date of 1 Jan 08 and an end date of 10 Jan 08, and you have a single contract with a start and end date of 6th Jan 08, you are actually going to get 6 rows back. Is that what you want? Six iterations of that loop are true between 1st and 6th Jan.
0
 
ac_davis2002Author Commented:
Hi mmcdermaid

I need to populate a list and yes I need the duplicates, Basicall most of our contracts last one year. for that year the contract is availble to say, 1000 staff on that contract. so effectively we can service 1000 peaple a day, month or year. In this instance i am trying to 'sum' all employees per month on an active contract. So in this example 1000 staff per month.

What i would like to do is use a procedure but pass the start prompt and end prompt from the report to the procedure so I can 'sum' employees between the prompt dates. Are you able to advise?
0
 
ac_davis2002Author Commented:
Hi nmcdermaid

I think I have solved this now but thanks for your interest. I have raised another question about passing cognos report prompts to a sql query if you have any thoughts

Cheers
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.

 
ac_davis2002Author Commented:
thanks again
0
 
nmcdermaidCommented:
Hi,

Do you want to post a link to your new question in here and maybe I can take a look.
0
 
ac_davis2002Author Commented:
Hi nmcdermaid

My other question has been resolved but thanks for your interest
0
All Courses

From novice to tech pro — start learning today.