Solved

Cognos 8 While loop

Posted on 2008-06-20
6
1,220 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Closing Comment

by:ac_davis2002
Comment Utility
thanks again
0
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
Comment Utility
Hi nmcdermaid

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

10 Experts available now in Live!

Get 1:1 Help Now