Newbie: Trying to use a calculated value to filter back 7 days (on DB stor date)

[littlevoices]

Hi,

I'm just experimenting with Cognos and I'm struggling with the following;
I want to show the last 7 days of data (and have read all about the _add_days function), but based around the last days loaded into my database (rather than an offset on the current date/time).
I was having some problems getting this, so was wondering what the best practice would be to accomplish this.

I thought the following, however I'm having problems.
1 - Create a Calculation Definition in Framework Manager called 'Last Day'.
Code:

Code Select Expand

cast(head( order ( [Physical Database].[tblDates].[Date],[Physical Database].[tblDates].[Date],DESC), 1), date)
This works fine in framework manager, or in a singleton, and gives a single date as a result (the last date in my table)

2 - Put in a simple query filter to do an equals in report studio (step 3 would be to change this to an _add_days, but I haven't got this far yet)...

Code Select Expand

[My Package].[Time Dimension].[Time (By Day)].[Day of Month].[Date] = [My Package].[Last Day] -- fails with
'QE-DEF-0459 CCLException
GEN-ERR-0015 Initially in data source type(s) D2, the function 'olap_order' is no supported in 'RelationalQueryProvider'. After decomposition, in data source type(s) PC, the function 'sql99_cast' is not supported... etc

Code Select Expand

[My Package].[Time Dimension].[Time (By Day)].[Day of Month].[Date] = _make_timestamp(2008,10,26) -- works

After this I thought 'OK, so it doesn't like my calculation definition, so I created the calculation from Step 1 directly in the filter, but it didn't actually do the filtering. I was trying to play around with 'caption' and the likes without much understanding of how to get a single value to do a comparison as above (I had errors about invalid coercion from memberset to value when doing this). I even then tried to create a union query which would not run at all.

As such, to go back to square one, how should I be trying to accomplish this particular task? I'd appreciate guidance on the best approach as much as the actual configuration/code.

Thanks in advance