Using a filter on a calculated field in Report Builder

December 16, 2011

6 comments

A short and sweet tip on using Report Builder 1.0

So, my users are still using Report Builder 1.0. I recently discovered that using a filter on a automatically calculated field there doesn’t work. I tried running a report where I ran a filter on a year generated filed of a certain date. In essence, I filtered on Year creation date = 2011. The report took ages to run so I took a look at it on the profiler to see the query that was run. Apparently, it wasn’t even running the filter correctly.
Changing the filter to creation date on or after 01/01/2011 ran fast and more importantly – the query was correct ๐Ÿ™‚

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

6 comments

  1. Eliran AzulayDecember 18, 2011 ื‘ 0:29

    hey Ella,
    how are you?

    what do you thing about PowerView?
    are you going to migrate Report Model -> Tabular Mode and let users use this method to create reports?

    Eliran.

    Reply
  2. Ella MaschiachDecember 18, 2011 ื‘ 15:33

    Hi Eliran,

    That sounds like a great idea!
    I think Power View looks great and really does have a feel of Report Builder 1.0 on steroids.
    Power View has a great look and feel, I can use itโ€™s model like in RB, it shows the user only the entities connected to the entity youโ€™re in, and itโ€™s easy to create models with it.
    The only minus I know of is that in Power View you get all the data in one page and need to scroll down in it.
    In Report Builder 1.0 you can scroll between the pages (which I think is more comfortable).
    But (and thatโ€™s a big โ€œbutโ€) I donโ€™t know when the municipality will move to SQL Server 2012, so I canโ€™t say when weโ€™ll make the switch.
    โ€ฆand a belated Happy Birthday ๐Ÿ™‚

    All the best,
    Ella

    Reply
  3. Eliran AzulayDecember 18, 2011 ื‘ 23:52

    thank you very much ๐Ÿ™‚

    i’m working on creating a new server with 2012 in my organization so i can test this out.
    will let you know how it goes.

    regards,
    Eliran.

    Reply
  4. Ella MaschiachDecember 19, 2011 ื‘ 15:47

    I look forward to hearing from you how it went!

    All the best,
    Ella

    Reply
  5. rtDecember 21, 2011 ื‘ 5:46

    Hi,

    I did a simple OLAP cube in SSAS 2008 and attached to reporting services. Created a data source and data model based on that cube. When I launch the report builder 3.0 and create a dataset with a filter, the query times out. Without filter ,it gives back the report in few seconds. Can you shed some light on why this is happening? And the memory used to almost 100% in the machine. I’m totally new to reporting services. Is attaching cubes to report server and let the power users use the report builder 3.0 a feasible design? Your help will be greatly appreciated.

    Reply
  6. Ella MaschiachDecember 26, 2011 ื‘ 11:17

    I’m terribly sorry, but I can’t say what’s the reason. Please try your hand at the SSRS forum:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/threads

    All the best,
    Ella

    Reply