Solved

Modify SQL query to display only current data in Cognos report

Posted on 2013-01-23
4
377 Views
Last Modified: 2013-02-04
What's happening: A Cognos report is being generated with inaccurate data.
Users are adding 'future' data into the database via another app, this works for them and this app, but it's causing the Cognos report to display inaccurate information since the query coming from Cognos is unable to filter this 'future' data.

What needs to happen: I need the report to display only the accurate/current data, not the 'future' data. I've tried modifying the table view the query references and I've tried altering the query, with only partial success. The back-end is SQL Server 2005.

There's a column in another table that holds release data (future and current). Y = released (this is the info I need), N = not released (future data I don't want).

In another table and column there is a sequence column. This is used to identify the newest information. Sequence 1 would be the first record entered, any updates to this particular data would then get a 2 and so on, with the highest number being the most current data. Unfortuanltey this usually means Future data that we don't want in the report.

My thought was to use a CASE statement something like this in the query coming from Cognos:
CASE WHEN SeattleZoo.Release = 'N' THEN SeattleZoo.Sequence -1 ELSE SeattleZoo.Sequence END AS Sequence  

I thought this would iterate through the 'N' (not released data) and find the 'Y' released. But it's not working.

SeattleZoo is a table view that references the two tables with the Release and Sequence columns. I'd like to avoid creating a stored procedure and user defined function if possible.

 Sample query (coming from Cognos report)


SELECT   Distinct
         BostonZoo.Mammals AS Mammals,
         BostonZoo.Reptiles AS Reptiles,
         BostonZoo.Source AS Source,
         BostonZoo.Food AS Food,
         BostonZoo.Meds AS Meds,
         BostonZoo.Enclosure AS Enclosure,
         SeattleZoo.Primates AS Primates,
         SeattleZoo.Marine AS Marine,
         SeattleZoo.Mammals AS Mammals,
         SeattleZoo.Reptiles AS Reptiles,
         SeattleZoo.Insects AS Insects,
FROM     ZooWorld.dbo.zoo_boston BostonZoo
         LEFT OUTER JOIN ZooWorld.dbo.vw_SeattleZoo SeattleZoo on BostonZoo.Mammals = SeattleZoo.Mammals and BostonZoo.Reptiles = SeattleZoo.Reptiles
WHERE    
BostonZoo.Food in ('Alp-003')
AND      BostonZoo.Enclosure in ('Alp')
AND      BostonZoo.Source <> 'C2'
OR       BostonZoo.Enclosure in ('Alp')
AND      BostonZoo.Source = 'C2'
ORDER BY 1 asc, 2 asc
0
Comment
Question by:letstryplanb
[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
  • 2
4 Comments
 
LVL 8

Expert Comment

by:SNeupane
ID: 38811003
Do you have a date field in the database?
You should have one that tells future vs current.
All you have to do is add a filter in Cognos report for that date <= current_date or in sql with date format.
0
 

Author Comment

by:letstryplanb
ID: 38815582
I tried <= GETDATE() in the query and it removed all of the record(s) from the report with a date greater than today instead of just the highest sequence. So I tried GETDATE() against the release indicator and sequence and it's still not working.
0
 

Author Comment

by:letstryplanb
ID: 38829738
I'm trying to use this in a table view and it's telling me that there's an error near <.

CASE WHEN SeattleZoo.release_ind = 'N' THEN SeattleZoo.eff_date <= CONVERT(VARCHAR(10),GETDATE(),120) ELSE price.eff_date END AS price.eff_date
0
 
LVL 8

Accepted Solution

by:
SNeupane earned 400 total points
ID: 38833021
Not sure but looks like formatting may be the issue.
Try a simple hard coded date instead of convert getdate function and see if that works first.
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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.

734 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