zooming in cognos (drilltrue?)

[Devasto]

ive been searching for some time now for a way but so far i havent been succesfull. i know that within cognos you can drill in your data, like going from day to week to month ect.

But is there wat to drill? or more exactly focus more on a specific data?

For example, when you have a report that generates a list of orders you make it contain, the client and the ordernumber and total price. can you then make it possible that if you click on lets say the ordernr, you scope in and get all the details of just that order? is there a way to do this by drilling, or forward the selected ordernr to a new report and juse the selected ordernr as a criteria?

thx for reading and possible youre respons.

 

[blom0344]

Drilling would require a dimensional modelled datasource and you typically stay within a hierarchy. In other words, the nature of the information does not change, just the level at which you are looking.

Example is years - year - month - day.

Getting all the details on a specific order would not be a typical case of drilling but would require starting a second report that includes all the details.

Ties Blom

 

[almeids]

There are 2 drilling concepts, drill-down and drill-through. Ties' description of "starting a second report that includes all the details" is drill-through and is one way of doing what you describe (showing more details for an order). In your example, you would set up drillthrough in your summary report on your order number data item, which would then show as a clickable link in the report. Your drillthrough target would be a second report which accepts order number as a parameter and reports the details. Note that the drillthrough target can be any report, not necessarily a different one (so you can have recursive drillthrough).
Read up on drill-through in your Report Studio User Guide for details on how to set it up.

 

[blom0344]

Good addition Almeids, my description did fit the drill-up/drill-down mechanism. the drill-through in Cognos is quite a new concept to me (as a drill-through in Business Objects constitutes a new database query and not a new report)

Ties Blom

 

[Devasto]

first of all thanks for replaying,

iam starting to get the picture of drillthrough and drill up/down, and indeed for the first raports i will need to build i will juse the drillthrough option, gonna try it this afternoon.

i also have another question if you think about the drill up/down you will need to have several levels in your table.

I didnt build up my timetable with a hierachy and it just contains Day, Week, Month and year, is there any way of still jusing this in a drill up/down report?

 

[Devasto]

ive managed to create a nice drillthrough and it works fine, now the next Q. has come up, as the hyperlink iam using a document ID as this is a ugly and long ID i would like to hide this one and have it replaced by a small icon is this possible so the icon will become the clickthrough?

 

[almeids]

Yes, just insert an Image in your list, you can set up drillthrough on pretty much anything I think as all of your source data items are available when defining the "linkage" to the target report.

 

[Devasto]

my current report is a list and i want my document id replaced by and image, but this must be for every record does that work the same as a drill-through image without multirecords?

 

[almeids]

Huh?
Not sure what you're asking...the image goes in your list instead of the doc ID and repeats with each row, the drillthrough is still passing doc ID (doc ID stays in the query) for the row. Without doc ID in the list, and with an identical image in every row, I don't know how the user will know which one they're selecting but assume you have other distinguishing characteristics displayed in the list since this was your idea!

 

[Devasto]

already found it was simple indeed
thanks.

next problem:
in one of my reports i want to return an end date easy
but wenn the record is empty and it doesnt contains an end date my dwh will automaticly enter 1-1-9999, now i want to edit my expression so it will always show the end date Except wenn the date equels 1-1-9999

How does this work in cognos as i cant use normal expressions.

 

[blom0344]

Empty records? I'm sure you mean something else. Did you lookup the Coalesce function and what it can do for you?

Ties Blom

 

[Devasto]

sorry for the late reply ive been having a really busy weekend,

Let me refraze the Question,

I have some Data shown in my report wich is good, but there is one value i do not want to show, what way do you write expressions in Cognos?

i was thinking about something like

IF [DATUM] = '1-1-9999'
THEN ' '
ELSE [DATUM]

But these types of statements give several errors,

 

[blom0344]

Just one way to solve this:

CASE WHEN
extract(year,[DATUM]) >= 9999
THEN NULL
ELSE [DATUM] END

If you evaluate a date then returning a string will probably not be valid, null values should be valid though




Ties Blom

 

[Devasto]

awesome it works thanks

 

[Devasto]

i cant edit posts on this board?
O well i got another small Q.
in my reports iam using several Value Prompts
but the Wenn you run the report it will show the value prompt box with the name of the datafield (as this selection also stands for ALL results, can i change this name to something else?


P.S, Ties Blom are you dutch?

 

[blom0344]

Yep, 100% Dutch.

You can pretty much changes names everywhere as long as the reference in the report expression remains valid..

Ties Blom

 

[Devasto]

oke so we could talk dutch if needed, but lets keep english for now so others will be able to read this

At the moment ive got the reports and wenn you run it for the first time it will show the value prompts with the field names,

Can i change these names into for example 'Alles weergeven'?
and if so were do i do this as i cant find it...

 

[blom0344]

Cognos uses the dataitem definition of the display-value. No idea if you can change this without changing the FM definition.

Ties Blom

 

[Devasto]

FM definition?

 

[blom0344]

Framework manager = FM.
The business layer in the Framework will dictate the actual naming of data-items..

Ties Blom