none
Filter Expression in Column Group

    Question

  • Hello All,

    I have a simple report which displays financials across multiple years.The report has a column group for year.When we run the report the latest 5 years data should be displayed.To achieve this I created a Filter in the group to display 5 years information.

    Here is the filter.

    Expression: [Year]

    Operator: >=

    Value: Parameters!year.value - 4

    This will display latest 5 years of information in the report.For example if value of year = 2012, it displays 2012,2011,2010,2009 and 2008 information in the report.

    The tricky part is sometimes there is no information for some years.If there are no financials for 2010 in the database it only displays 2012,2011,2009 and 2008.In this case I want 2012,2011,2009,2008 and 2007 information to appear on the report.

    Can anybody help me with this?I am not sure how to write an expression to achieve this.

    Thankyou in advance!

    Wednesday, January 08, 2014 5:10 PM

Answers

  • Ok. for this requirement easiest thing would be to bring an additional column in your resultset of query behind called YearRank. The logic would be as below

    SELECT DENSE_RANK() OVER (ORDER BY YearColumn DESC) AS YearRank,
    ... rest of your query

    Then use the filter as below

    Expression: [YearRank]

    Operator: <=

    Value: 5


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by kannari Thursday, January 09, 2014 6:32 PM
    Wednesday, January 08, 2014 5:41 PM

All replies

  • Ok. for this requirement easiest thing would be to bring an additional column in your resultset of query behind called YearRank. The logic would be as below

    SELECT DENSE_RANK() OVER (ORDER BY YearColumn DESC) AS YearRank,
    ... rest of your query

    Then use the filter as below

    Expression: [YearRank]

    Operator: <=

    Value: 5


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Marked as answer by kannari Thursday, January 09, 2014 6:32 PM
    Wednesday, January 08, 2014 5:41 PM
  • Thankyou Visakh for your reply.

    I tried to include DENSE_RANK() into my query and somehow I get an error saying "The OVER SQL construct or statement is not supported".Looks like SSRS doesnot allow you to use OVER statement in a dataset.Is this correct or am I missing something?

    Please shed some light on this!!

    Wednesday, January 08, 2014 6:05 PM
  • Nope...the reason I think is you're trying to type query in SSRS query editor. Try to wrap it in a procedure and call procedure from your report dataset query as

    EEC ProcedureName

    SO long as your database version is 2005 and above you should be able to use this.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, January 08, 2014 6:09 PM
  • It worked in a stored procedure.

    Thankyou!!

    Thursday, January 09, 2014 6:32 PM