Skip to Content
author's profile photo Former Member
Former Member

How can I get my Query to return results for a period of time?

I want to make a smart report that will bring back only results within a 6 month period between current and 180 days from current. I do not see an easy way to do this so that I don't have to address the prompt everytime I run the report. I would prefer to do this at the Query Filter level rather than in the universe or in the SQL. Does anyone know how to make this work?

Example:

Bob has a start date of 1/24/2013

Jan has a start date of 7/1/2014

I want the query filter setup so that it will return Bob's result in the report because it is within 180 days of today, but it won't return Jan's results until I run the report at the end of January. I know that I can do this at the universe level or write it directly into the SQL, but that would require IT intervention. It is needed because track changes only highlights those items that have changed on the report based on the query run vs. the Input Controls setup at the report level.

Thank you

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 11, 2013 at 05:39 AM

    Hi Mary,

    First Of all If you wamt to do something in the query filter then that calcution has to be done at the Universe Level.

    However Sinec you dont have acess to Universe.

    There ar etwo option either ypu can use two queries and use Input Of First query to the second query Or there is Function at webi level relative date you have to manipute that like

    Relativedate(DateInPicture;currentdate ;-180)

    It will Give you a date which is always 180 days back ,

    You have to play with this Function to get the Desired results

    Hope it helps

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 12, 2013 at 02:02 AM

    The ideal way to do this is to o it at universe level... I would highly recommend to ask the universe designer to create a filter or just a plain dimension to have a Sysdate-180 which you can utilize at report level as a filter criteria..

    If you try to do this at report level the performance of the report will be adversely impacted..

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.