Solved

Cognos 8 While loop

Posted on 2008-06-20
6
1,258 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 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
IF SQL Query 12 29
Sql case statement to calculate totals 5 36
ODBC settings not showng in FileMaker External Data Sources 6 23
SQL 2012 Instance Problem 3 59
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 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