Filters in Framework Manager Model

[pravin.cognos]

Dear Gurus

We have reports based on predefined reporting periods. I have a scenario where I have to create 2 reports, one to show current year and previous year difference in the data (i.e 2009 data - 2010 data) and another to show current quarter and same quarter in the previous year, difference in the data (i.e Q1 2009 data - Q1 2008data). The user selects the reporting period ex: 01/01/2010 and 31/12/2010 for an annual report and 01/04/2009 and 31/06/2009 for quarterly reports. We have to generate the previous year/quarter reporting period (ex: _add_months_(date), -12). We use relational model. I have a faint idea that a filter can be put into the model itself to filter the data but I am not sure how can it be achieved. Please let me know if any more details are needed. Please help me on this !!

Thanks
Pravin

[blom0344]

Do not try to solve this through model filters.  Filters in a model should be kept simple, like a selection on a status field to distinguish between - example -  'active' and 'archived'

Your requirement calls for creating 'buckets' of data to calculate with within a reports.

So , the first calculation between years would be something like:

total(case when extract(year,[somedate]) = 2010 then [somevalue] else (0) end)  -
total(case when extract(year,[somedate]) = 2009 then [somevalue] else (0) end)

You can make this dynamical by using prompts and associated parameters to work with instead of absolute years

[pravin.cognos]

Dear Blom

Thanks for the reply. Yeah, What you said is correct about the data buckets.
I have the following questions.

1. Which is best place to create these buckets, FWM or Report Studio? I am asking about FWM again because we have atleast 8-10 yrs of data but 90% of the times we need data only for 2 years i.e Year XXXX(need not be current year always) and XXXX - 1.

2. I tried to create the value for previous year in RS by giving the condition as (_Add_Months(?date?, -12) but this resulted in a value which has timepart in it, though the original value in Date column doesnot have any timepart in it. So i ended up with "Result set Mismatch" error.

Forgot to mention that we dont have a DWH i.e I use the SQL Server 2005 DB tables directly for modelling.

Regards
Praveen

[blom0344]

I have already answered the first question.   ;)   Try _months_between  as an alternative to solve the issue from 2nd question..

[pravin.cognos]

Thanks a lot for your time Blom :)

As you said, I have created the buckets in RS and its working.

I used the same approach u mentioned ;)

Thanks again !!!!

Cheers
Pravin

[cognostechie]

Here is another way to do it.

Create Relative Time Categories in FM which you can drag and drop in either the columns of the report or in the Query as a filter. Those are called stand-alone filters in Cognos terminology. There are another set of filters called 'embedded' filters which are applied to the Query subjects so they become filters for the whole query.

You can create a folder in FM, right click on the folder and it will let you create a filter which will then be available in RS as an independent filter. Ofcourse, it has to be related to some Date column and it is preferred to use it on a Time Dimension in FM though not neccecary.

Here is the syntax for YTD:

extract(year, [Database View].[Query Subject Name].[Date]      ) = extract(year, current_date) and
[Database View].[Query Subject Name].[Date]     <= _add_days(current_date,0)

Prior YTD (ending till same period of this year). So if you apply this today, it will give you data for 1/1/2011 till 2/23/2012:

extract(year, [Database View].[Query Subject Name].[Date]     ) = extract(year, current_date) - 1 and
  [Database View].[Query Subject Name].[Date]    <= _add_months (  current_date , -12)

QTD and PQTD depends on whether your Fiscal Quarter is the same as Calendar Quarter.

I have it for all of that and also for Rolling Weeks, Months etc. You can also create a generic filter that will
let the user select the no. of periods going backward so you can call it Rolling N Months. It works
with the prompt macro.

The YTD and PYTD filters can be applied to columns in the report with an If condition or Case statement.

If
( YTD )
then (measure)
else (0)

If you need to make the report to show YTD vs PYTD then also apply a filter to the query itself to get the data for  only 2 years. That way the report will read data for only 2 years and the columns will seperate the data for YTD and PYTD.

[pravin.cognos]

Dear cognostechie

Thanks you very much for your detailed suggestion. I have delivered the report with the previous approach but this knowledge will be verymuch useful for me as well as others who have this scenario.

Request you to guide me with my other queries in this forum. Thanks a lot again for sharing your knowledge with us.

Have a happy week :)

Cheers
Pravin

[siyad]

Hi all, the filters created in FM model wont work in Analysis studio. Would any body know any work to get this worked in Analysis studio ?

[blom0344]

Multidimensional analysis works with using slicers , not with predefined filters.  You can drag a level to the right top of the analysis screen in the context filter area and then select a member to use as a slicer

[siyad]

Thanks for your quick reply. I may miss to mention that I am looking this for a DMR model

[blom0344]

Does not matter, it's the way Analysis Studio works..