If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

(!!) Problem with filter in FM

Started by Rosanero4Ever, 09 Oct 2012 09:35:45 AM

Previous topic - Next topic

Rosanero4Ever

Hi all,

in my query subject I have an element defined as follows:
cast(_days_between ( getdate();[Logic view].[My query subject].[LastPaymentDate])/365; int)

LastPaymentDate is defined as follows:
maximum[Logic view].[My query subject].[PaymentDate])

LastPaymentDate tells me the latest date of a payment, while the other element defines how many years are spent from the last payment.

These definitions work well but, when I use the filter pane of the query subject, the filter doesn't work and zero rows are returned  >:(

Does anybody can help me to solve this issue?
Please!

Thanks in advance

bdbits

Remember that a filter must return a boolean (true/false) value. The expressions you have listed are data items. What is your filter expression?

blom0344

Quote from: Rosanero4Ever on 09 Oct 2012 09:35:45 AM
Hi all,

LastPaymentDate is defined as follows:
maximum[Logic view].[My query subject].[PaymentDate])

LastPaymentDate tells me the latest date of a payment


I would be careful in assuming this.  The query context will determine the value of the aggregate.  If you add , say, customer and year to the report query you will end up with the latest payment date for each set of [customer,year]  by default.

The aggregate behavior can be manipulated by means of the regular aggregate property, you do not need to hardcode it !!

Rosanero4Ever

Quote from: bdbits on 09 Oct 2012 01:59:59 PM
Remember that a filter must return a boolean (true/false) value. The expressions you have listed are data items. What is your filter expression?

Right! In my filter I use a booelan expression on the element defined by
cast(_days_between ( getdate();[Logic view].[My query subject].[LastPaymentDate])/365; int)

Rosanero4Ever

Quote from: blom0344 on 09 Oct 2012 02:08:43 PM
The aggregate behavior can be manipulated by means of the regular aggregate property, you do not need to hardcode it !!

Thanks for your reply, but, sorry,  I don't understand.
Can you explain better your advice?
Thanks a lot

blom0344

Each data item in FM has a set of properties.  One of them is the regular aggregate.  You can set this to maximum by default instead of coding it as you did.  The enables a report writer to change it should he wish it.  The default is set to sum for usage type 'fact' ..

Rosanero4Ever

Thank you very much for your good explanation.
So, I'll try to remove maximum() function replacing it with the aggregation property of the element.
Regards.


ps
but....this will resolve the problem I wrote about the filter?  ???

blom0344

No, but bdbits already pointed out that there is a distinct difference between a data item expression and a filter expression.  My input was concerned with you hard coding an aggregate.  Perhaps some formal FM training would clear things up?

Rosanero4Ever

I know the difference between a data item expression and a filter expression, but avoiding hardcoding don't solve my filtering problem.

blom0344

Please reread bdbits post..    ::)

cognostechie

Actually, the expression to determine the no. of years is wrong too. Dividing by 365 will not give accurate result because the leap year has an additional day. Moreover, that additional day is not equally divided among all 4 years so until the leap year is reached, it is dividing by a higher average.