Dynamic filter of relativ, static timestampt ranges ?!

[charon]

 :'( hi community,

i am a bit ashamed to post my cognos issue, i think it should not be that complicated.but my approach does not work, therefore...

Goal: What i wanne do is, to create a chart/ list that is filtered by a prompt. that prompt has 3 options,(2 day, 10 days, 45 days). It should filter me my measure for the last day (10 days, 45 days).
I Have: I have the servertraffic (my measure) and the related timestamps.
Model: Dimensional
Cognos: Report Studio 8.2

What i tried: 2 approaches
1) created 3 data items, that should deliver me the data between the timestamp (e.g. 2 days ago) and today.
like     [server].[db].[timestampt]<trunc(_add_days({sysdate},-1)'dd'))

that alread does not work. the plan was, after geting this create value prompt, edit static choises for my options as display and add this data item as value.

2) No data items and put in my timestamp filter in the value of the static choices...

Both does not work  :'(
I admit, im not a big sql genius, therefore it might be the case that im doing crazy **** here :D

Any ideas how i can achieve my goal?
Thanks and cheerz
charon :P

[MFGF]

You say this is a dimensional package? I think filtering is probably not a good idea with dimensional sources (but you can get away with it if your package is based on DMR)

I would try a different approach - I am on my iPhone so this is all from vague memory - there are some dimensional functions that will help - lastperiods, closingperiod. ClosingPeriod() will give you the latest member from your day level, then you can use the LastPeriods() function to return the n members from the day level up to and including the latest member identified above. You can prompt for the n value.

Good luck!

MF.


Snet form my fumblefingers
iPhon 5 usig Tapatalk

[CognosPaul]

8.2? I've got two clients using that. Upgrade upgrade upgrade! The problem you're facing is a snap to fix in 8.4 and above.

As The Muppet said you should use dimensional functions, and as he mentioned you could use the closing period function to get the last day. That would only work if your time dimension closes on the last day and that the cube processes on a daily basis. If you have any future dates you're out of luck with that. The problem you're facing is how to get today's mun. Once you have that, then the lastPeriods will be exactly what you need.

Another method to try is to use the filter function to find the last day with data, and to extract the member from it:
item(tail(filter([cube].[Time].[Time].[Day],[Cube].[Actual]>0)),0)

You could use lastPeriods on that.

But that would only work in cases where Actual exists for the current day. If your cube ever has a current date with no Actual, maybe after a weekend or a holiday, this method won't work. So then we can get to have some fun with Cognos even more complicated:

First take a look at one of the date MUNs. It might look something like

Code Select Expand

[cube].[Time].[Time].[Day]->:[PC].[@MEMBER].[20120101]

The trick we're going to do is to recreate that with macro.

You might have tried using my method I wrote in this post, but discovered it doesn't work. That's the penalty for using 8.2. The trick is to create a parameter map with the current date in the format that you can use inside a MUN.

Now, open the framework package you used to publish the cube.

What? Didn't save it?! Shame on you! Create a new model, import the cube, and create a package with the same name as the existing one.

Create a new query and use whatever functions it takes to return the value of today's date in the MUNs format. Add another field to the query with a static value of 1.

Create a parameter map, base it on the query you just built, call it paulsMagicalDateLookup.

Publish the package, overwriting the existing one. Or as a new package if you're really paranoid.

In the report create a new query and put the following in a data item:

Code Select Expand

#'[cube].[Time].[Time].[Day]->:[PC].[@MEMBER].'+sb($paulsMagicalDateLookup{1})'#

Now run the query - if it works you should see the current date.

You can then use that method with lastPeriods to get the results you need:

Code Select Expand

lastPeriods(#prompt('Days','integer','10')#,#'[cube].[Time].[Time].[Day]->:[PC].[@MEMBER].'+sb($paulsMagicalDateLookup{1})'#)

[charon]

hi ,

thx for your awesomly fast reply. Great to feel not beeing alone against cognos  ;D
quote: "to have some fun with Cognos"

Well, after all, your advises sounds reasonable, but also a bit sophisticated. I didnt even consider using dimensional functions yet. So thanks for that idea   ;)
Another approach: Would it be easier, if i would have a relational model? Because if it would, i think i would consider creating a new relational package...

sincerly your cognos-rookie
charon :P

[CognosPaul]

In terms of ease of use, speed, and flexibility I'd rank the following (only from what I've used):

1. MSAS OLAP
1.5 Essbase (Technically both at number 1, but Essbase loses the top spot due to annoyances with Global Variables, and no level identifiers by default)
3 Cognos PowerCubes
4 Oracle
5 SQL Server
6 DB2 or AS/400
7 SAP B/W (yes, it looks like a cube, but the set up is a nightmare.)
8 Anything DMR. This is from DMR on 8.2 and 8.4, but in my experience it's never been good. With a small caveat, when used only for simple filtering and drills it may work, but most dimensional functions will make the SQL smell like overripe limburger.

The learning curve on olap is fairly steep, but once you get it you'll find it much easier to work with than a relational model.

[charon]

 ;D made me lulz

Alright, gonne try dimensional then...thx a lot guys :)
have a good night and..
cheerz  :P

[MFGF]

In terms of ease of use, speed, and flexibility I'd rank the following (only from what I've used):

1. MSAS OLAP
1.5 Essbase (Technically both at number 1, but Essbase loses the top spot due to annoyances with Global Variables, and no level identifiers by default)
3 Cognos PowerCubes
4 Oracle
5 SQL Server
6 DB2 or AS/400
7 SAP B/W (yes, it looks like a cube, but the set up is a nightmare.)
8 Anything DMR. This is from DMR on 8.2 and 8.4, but in my experience it's never been good. With a small caveat, when used only for simple filtering and drills it may work, but most dimensional functions will make the SQL smell like overripe limburger.

The learning curve on olap is fairly steep, but once you get it you'll find it much easier to work with than a relational model.

You don't mention TM1, but from your observations I would imagine it would slot in above Essbase. You need to be TM1ified, Paul!! :)

[CognosPaul]

I'll admit I haven't much experience with TM1. The only time I used it was against a TM1 cube where the developer created about 500 separate measures instead of a single measure against a hierarchy. That wasn't fun, especially since the users wanted all the normal cube functions (drill up/down,set functions, ordering) capabilities that just can't be done on measures.

Other than that, from the perspective of an author it's just another standard cube, right? Then yeah, it would be 1.4 (levels also aren't enabled by default dammit).

[MFGF]

If you get to use Cognos Insight, you will find yourself suddenly exposed to a lot of TM1 technology! It's quite like Essbase but a whole lot better**. And you can write back to it too.

** The opinions expressed by the muppet are often random thoughts, planted there by green-eyed Martians while he sleeps at night. Best taken with a healthy dose of beer. Consumption of these opinions may affect your sanity.

[charon]

jea, ive seen the webcast 2day...looking awesome :D