?
Solved

Cognos 8 While loop

Posted on 2008-06-20
6
Medium Priority
?
1,311 Views
Last Modified: 2010-04-29
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
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
  • 4
  • 2
6 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 21842766
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
 

Author Comment

by:ac_davis2002
ID: 21844281
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
 

Author Comment

by:ac_davis2002
ID: 21853586
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
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 Closing Comment

by:ac_davis2002
ID: 31469297
thanks again
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 21922454
Hi,

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

Author Comment

by:ac_davis2002
ID: 21924182
Hi nmcdermaid

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

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

649 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