Solved

Cognos 8 While loop

Posted on 2008-06-20
6
1,232 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
  • 4
  • 2
6 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Slabs 9 40
Help with Sorting Full Text results 2 14
MSDN Licensing query 5 55
ebay table structure 2 14
How to increase the row limit in Jasper Server.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now