Query Filter Question

[Former Member]

Hello All,

I need help with a filter condition in my WebI report.  I have a prompt in my report for Season Year which is in this format "YYYY", and I need to create another filter with this condition.

"Retrieve all the data for which the Object[Year of Expiration Date] (MM/dd/yyy  - format) should be equal to season year from prompt."

Can someone please tell me how I can achieve this in WebI, is this something I can apply at query filter level, or do i need to create a variable object for Year of Expiration date and make Year equal to Season year.

Thanks in advance.

[Former Member · Accepted solution]

Have you tried creating a variable to parse out the year of your object and then compare the prompt value to the variable?

ie if the object is a date object:

[Var Object Year] = FormatDate([Year of Expiration Date];"yyyy")

or if the object is a string:

[Var Object Year] = FormatDate(ToDate([Year of Expiration Date];"MM/dd/yyy");"yyyy")

Then set a filter variable: [VarReportFilter]= IF [Var Object Year] = UserResponse("name or your prompt") Then 1 Else 0

Then just apply filter where [VarReportFilter]=1

[CarlyThomas]

Hi Syed,

What version are you using?  And are your reports based on a Universe?

If  a Universe, I would recommend creating an object at the Universe level that's formatted the way you would like and then use that object in your WebI Report query filter.

The most efficient way for filtering data in WebI is via the Semantic Layer and query - report level variables will still allow all data to be returned, but filtered at rendering time which is a bit more resource intensive.

If you have a Universe level object for Expiration Year, then WebI has query filter options that you can set the Expiration Year to filter based on the Season Year filter.  Once you click the prompt options, you'll be able to see  how to set that.

I hope this helps!

Carly

[Former Member]

As Carly suggested the easiest way to do this is to have another object in the desired date format( like YYYY from the date in universe as a new object) and then put your prompt on top of that new object..