Report Viewer and DateAdd RRS feed

  • Question

  • Having an issue trying to filter data rows in a table. For my collection of data, if I use a filter like Fields!ThisDate >= =DateAdd(DateInterval.Month, -1.0, Now), then I get the records I want. But if I try and filter records using Fields!ThisDate >= =DateAdd(DateInterval.Day, -7.0, Now), then nothing shows up. I definitely have records from the past week, so why can't I access them, but can access records from the past month? Doesn't make sense to me. Thanks in advance.
    Wednesday, November 7, 2007 8:53 PM

All replies


    Can you give a couple of example values, please?  I'll give this a shot.


    One thing I would try right off, if I experience what you do with your values, is an explicit cast using CDATE on Fields!ThisDate.Value.  I notice that you are using Fields!ThisDate (without the .Value) and I'm a little suspicious about how that object might be being implicitly cast.



    Thursday, November 8, 2007 5:09 PM
  • Thanks for the reply. I wasn't thinking when I wrote this, I do include .Value in the filter. I've already tried CDate and it didn't do anything new for me.

    Basically, I have a collection of records like this:
    Date                                       Temperature
    11/7/2007 11:04:34 AM                   45
    11/3/2007 10:24:56 AM                   48
    10/27/2007 1:54:24 PM                   32

    I want to present the data in terms of average and standard deviation over different time periods (1 week, 1 month, etc). So I created a series of tables where I created groups based on the filters (in the Grouping and Sorting properties) I mentioned before (e.g. Fields!ThisDate.Value >= DateAdd(DateInterval.Day, -7.0, Now)). The problem is that I can get this to work when I filter rows based on DateInterval.Month or DateInterval.Year, but for some reason it refuses to do anything for DateInterval.Day. All of the tables are copies of each other, so it doesn't make sense that one set of filters works on one table, but does not work on another.

    An easy way to test this is to create a data table with the above data. Create a new Report for this data, and have a table show the CountRows() function, using the filter I've described above. It should return 3 for the data above using a filter of 1 month, and nothing at all for a filter of one week.
    Thursday, November 8, 2007 5:32 PM

    >>An easy way to test this is to create a data table with the above data


    Nah, there's an easier way .  Just use any data source you want and use a literal query like the following (I just lifted it out of your message):


    Code Block

    SELECT  CAST('11/7/2007 11:04:34 AM'  AS DATETIME) AS TheDate,  45 AS Temperature
    SELECT CAST('11/3/2007 10:24:56 AM'  AS  DATETIME)    ,            48
    SELECT CAST('10/27/2007  1:54:24 PM'   As  DATETIME) ,             32




    I can't repro your issue, though.  I get 3 records for the first filter and 2 records for the second filter, which is what I would expect.  For the record, my second filter was:


    Code Block

    =Fields!TheDate.Value >= =DateAdd(DateInterval.Day, -7.0, Now)




    So what are we doing differently?



    Thursday, November 8, 2007 6:47 PM
  • Really odd. I don't know much about Report Viewer, but I added my filter in the "Grouping and Sorting" tab of the table. Did you create a table and use the filter in the table?
    Thursday, November 8, 2007 6:53 PM
  • >>

    Really odd. I don't know much about Report Viewer, but I added my filter in the "Grouping and Sorting" tab of the table. Did you create a table and use the filter in the table?





    I am not sure what this has to do with Report Viewer exactly? We're in the Report Designer.  I created a table, and I used the Filters tab of the Table Properties dialog.


    When you say "Grouping and Sorting", are you in a detail group for some reason, and why?



    Thursday, November 8, 2007 7:03 PM
  • Sorry, it has nothing to do with the Report Viewer. I meant the Report Designer.

    I'm actually working with a collection of data, and I was sorting them into separate groups and displaying them in tables. Each table is for a different period of time (1 week, 1 month, etc). Something didn't work before when I tried to apply the filter to each table, so I applied the filter to each of the groups in the table. This is where I received the weirdness that filtered data over a month, but not over a week. I just tried this again, where I filtered the table instead of the individual groups, and this worked. So, to sum up, my problem has been solved. Thanks for the help, though I'm still confused where the problem came from.
    Thursday, November 8, 2007 7:17 PM
  • I think you are confused about how the filter is applied to the groups.  Also I still have a small suspicion that you may have been putting your filter on the detail band rather than the group levels, based on your description of the dialog -- not sure what difference that would make, just sayin'.


    As to how this was working (I mean "behaving", whether badly or well <s>) when you applied to the groups, I can't say, it would depend on what your group expressions were and possibly also the ordering of your data, I suppose.



    Thursday, November 8, 2007 7:29 PM
  • I'm sure I am confused about a lot of things. All I know is that it worked on multiple tables when I filtered by month or by year, and when I copied the table and just changed DateInterval.Month to DateInterval.Day in the new table, then the filter did not work at all, and no data was returned. So I know I did something right to begin with, because I had multiple tables that returned the right data. Just have absolutely no idea why that one table would not work.
    Thursday, November 8, 2007 7:39 PM
  • While not arguing the "confusion" point -- since we are all confused about a lot of things, and since I still don't know the specifics of your grouping expressions etc -- let me offer a couple of observations:


    1) what you did before was not necessarily "right", it might have worked by accident and the finer-grained filter showed up what the issue with doing it that way might have been.


    But, on the other hand:


    2) What you did might have been perfectly fine.  The copy and paste operation, on the other hand, might have a bug in it.  I have experienced a couple of things that (when I looked into the XML) can't be explained any other way.  If I recreate in a fresh report and don't do the copy and paste, the very same thing looks different in the XML and works differently.  It's not an easily-reproducible thing.






    Thursday, November 8, 2007 8:49 PM
  • Try this

    =DateAdd("m", -1.0, Now)
    =DateAdd("d", -7.0, Now)

    Monday, December 3, 2007 7:34 PM