Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with date filters in SQL 2005 Report Builder

Hi,

I'm having a hard time getting date filters to work properly in Report Builder 2005. One of my model entites contains a datetime field called Date Opened, which corresponds to a datetime field in my database table. This is how the data looks like: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM.

1. I created an ad-hoc report and added a filter on Date Opened field, setting condition to PROMPT where Date Opened EQUALS some value. Next to EQUALS I get a dropdown list with the following values: 6/27/2007 11:31:52 AM, 6/27/2007 11:33:33 AM, 7/3/2007 9:24:07 AM. It looks like this list was generated by pulling all Date Opened values (including a time stamp) from the database. The problem comes up when I run this report and Date Opened filter limits me to choosing exact date time, when I want to see all rows for the specific date (the entire day). Is there any way to make EQUALS list not include the time stamps and show only dates, or give me a calendar control instead so I can choose the date (no time) to filter on? I noticed that if database had no existing values for Date Opened, the Equals list lets me choose a date with a calendar. I need to accomplish the same even if there are some values in the database.

2. The same setup as above. This time I choose to see all rows where Date Opened is AFTER specific date and set it to PROMPT the user. If I actually choose a default value on Filter Date dialog, run the report having picked some new value in a filter, it behaves as expected giving me all rows AFTER the date I specified. If I leave default value unspecified, run the report having picked some new value in a filter, it returns all rows on the date I specified and AFTER. In other words, it behaves like ON or AFTER. Is this a bug?

3. Same as scenario #2 above just using On or BEFORE. If I leave the default value unspecified on Filter Date dialog, run the report having picked some new value in a filter, it returns all rows before the date I specified, behaving like BEFORE rather than On or BEFORE. Is this a bug?

Any help is greately appreciated. I know we'll be getting customer calls about these issues.

Zhenia

Let's start with #1 and proceed to the other stuff after you're using values you're happy with for the filter (and it's true, date match and date conversions as well as date representations, especially across locales, is always a PITA!!)

Can you add a field to your query that adds a CONVERT() to your actual date value, and use this for your list? This way (a) you can be sure of the representation vis-a-vis your locale and (b) take the timestamps off. Note: you may have to re-cast back to a date time in your filter expression, depending on exactly how you are doing this. I am not all that familiar with the ad-hoc Report Builder stuff.

>L<

|||

Hi Lisa,

Thanks for your reply. I don't have direct access to the query built by Report Builder, but your suggestion gave me an idea. I created a new date field as follows New Date Created = DATEONLY(Date Created). DATEONLY is a function provided by Report Builder. This gave me Date Created without a time stamp. I tried filtering using this new field and all of my issues disappeared. I now get a Calendar control as a user prompt and AFTER and ON OR BEFORE conditions work as they should.

I can only guess that Report Builder is not good at filtering on full date time fields. I now need to add this new date only field to all the datetime fileds in my models. What a pain! But at least it works!

Thanks again!

Zhenia

Problem with datasource on Report Builder

I've got a strange problem with ReportBuilder. I've created a DataSource name Config and a Model name Customers. We been using this model for month and everything was working fine. Now when we try to create a new report we cannot run it. When Report Builder is running i select the "Customer" model. Then i put some fields on the report. When i try to run it i've got an error saying that it cannot create the command for datasource1. What i don't understand is why the error occured in Datasource1 and not in my datasource config ?

When i open the Report Manger and click on the model "Customer" the datasource use by this model is Config. So the problem is not because they're is a mistake in the configuration. I've tried to rebuild my model and datasource then deployed them but i still have the same problem.

Any idea of what the problem is ? I can't figure outThis is the exact error i get:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create command for datasource1. (rsErrorCreatingCommand)
An

attempt has been made to use a data extension 'SQL' that is not

registered for this report server. (rsSemanticQueryExtensionNotFound)|||

Can you please try restarting your IISservices (an iisreset on the Webserver hosting reporting services) to see if you get a different behavior?

Thank you.

Ramu_MSFT

This posting is provided "AS IS" with no warranties, and confers no rights.
.

|||Thanks ! I've tried to restart IIS and reboot our server but we still have the same problem.