Solved

Cognos how to use the prompt dates in queries

Posted on 2008-06-16
3
1,228 Views
Last Modified: 2010-04-29
Hi

I have a report that uses date prompts to select data from contracts. (cognos8)

Basically you enter a start date and an end date and I have created queries that select data between those dates.

What I need to do though is count the number of emplyees on a contract for each month between the dates entered. for example if I enter the start prompt as 01/12/07 and the end prompt as 31/12/08 I want the count of employees from 01/12/07 to 31/12/07 and then the 01/01/08 to the 31/01/08 and then the same for Feb, March, April etc all the way to Dec 08 (end prompt date) and then add them all together at the end! Any ideas welcome!!
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
  • 2
3 Comments
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 21794827
I'm assuming you're using a relational model.  I'd probably create a data item that uses the "extract" function to pull the "month" out of the date, and group by that.  I'd probably have another dataitem called "OnContract" that determines if an employe was on contract on a given date, and has a value of 0 or 1.  Then , we can simply use the automatic aggregation to figure out how many employee's were on contract in any given grouping.
0
 

Author Comment

by:ac_davis2002
ID: 21795004
I am using a relational model yes.

That sounds like what I need.

Are you able to send me any example of the "extract" function?

This is the select statement I can use to extract the infomation for just one month

Select CPCompanyid,NoEmployees as noemp from CTContracts where Fromdate  
<= '20080430' and  EndDate >= '20080401' and CTcontracts.LKContractTypeID in (0,8) Order by CPCompanyid,ctcontractid  

The bit I am struggling with is using this statement in conjunction with the start and end date prompts and moving the dates forward to meet the time scales I need.
0
 

Author Closing Comment

by:ac_davis2002
ID: 31467577
Cheers!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

738 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