Beginner Question - Get data between Time Range

[1nsyz1on]

Good day Guys

I am new to this forum, and would like some assistance if you could

We have an application which uses Cognos as the reporting Mechanism

I have a simple requirement to build a report which every time it runs, only brings back the last 2 hours data

I though this would be something simple to do, but cannot manage to get it to work.

The model does not have a "last hour, or last 2 hour, or last day" etc. Time option to be used with the reports so will have to do a query

Could any of you please assist me with this, would much appreciate it

Cognos 10

Ciao

[bdbits]

You have not provided enough information.

First off, is this a relational or dimensional package? There are many ways to process dates, but it is quite different between the two.

Second, are we dealing with a star schema (dimensions and facts)? Do you have a time dimension linked to the fact tables? If not, how will the date be referenced - is it a column on a table? Which gets back to dimensional/relational.

Has anyone given you information about BI reporting with a tool like Cognos? It's not like traditional reporting tools, e.g. Crystal Reports and such. You need at least a basic understanding of concepts like facts and dimensions (assuming the data is modeled as such, which it should be).

[1nsyz1on]

Thank you very much for assisting

As far as I know its relational

I am fairly new to Cognos and building up some skils

I have attached a screenshot of the DB Tables for the time options it stores data in

it stores it e.g

Hour of day
Minute of day
half hour of day etc.

It just needs to use the current time when it runs and bring back last 2 hours data

Apologies if I missed anything

[bdbits]

OK relational, so you will want to use a detail filter in the query section of the report. If you want to base it on e.g. the "Hour" column, something like this (untested, off the top of my head):

[Hour] between _add_hours(current_timestamp,-2) and current_timestamp

Assuming [Hour] is the thing you want to use for the data's hour, and that it is a datetime datatype.

As a new-to-Cognos report author, I'd recommend looking through the Report Studio user guide / help. It has some tutorial-style information using the Cognos sample data.

[1nsyz1on]

Thank you so much

The query that worked was

[Interface_Health].[f_Virtual_InterfaceMetrics].[Hour] > _add_hours([Interface_Health].[f_Virtual_InterfaceMetrics].[This Hour]; -3)

I just could not get the timestamp one to work, we using Sybase database

Thank you so much for assisting me

Will start doing some training on Cognos reports soon

Ciao