Solved

SQL Report Builder

Posted on 2014-01-17
3
1,097 Views
Last Modified: 2014-02-14
Query does not give the same results in Report Builder as SSMS when connected to the same server instance.  Simple query
SELECT distinct FK_ProbationFile
FROM         SupervisionTypeHistory
WHERE     (SupervisionType = 'm') returns 1102 rows in SSMS

Returns 731 rows in Report Builder using above query for dataset no filters or parameters
0
Comment
Question by:countyprob
3 Comments
 
LVL 13

Expert Comment

by:magarity
ID: 39790079
I notice you have a lower case 'm' - SSIS is not case sensitive but report builder is. Try: in report builder, select options > data set properties and change 'case sensitive' to 'false'.
0
 
LVL 1

Author Comment

by:countyprob
ID: 39804782
Still looking for an answer It seems to happen with both simple and complex queries.  I didn't build the database so may be missing something simple that is beyond my beginner knowledge
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 500 total points
ID: 39813980
Hi i wonder if the Report Builder is using a snapshot and or the Never Refresh option is on?
Perhaps a long shot...

See....
http://technet.microsoft.com/en-us/library/dd220464(v=sql.120).aspx


Advanced
 

Set the report definition attribute UsedInQuery, a value that indicates whether this parameter directly or indirectly affects the data in a report.
Automatically determine when to refresh
Choose this option when you want the report processor to determine a setting for this value. The value is True if the report processor detects a dataset query with a direct or indirect reference to this parameter, or if the report has subreports.
Always refresh
Choose this option when the report parameter is used directly or indirectly in a dataset query or query parameter expression. This option sets UsedInQuery to True.
Never refresh
Choose this option when the report parameter is not used directly or indirectly in a dataset query or query parameter expression. This option sets UsedInQuery to False.




Caution


Use Never Refresh with caution. On the report server, UsedInQuery is used to help control cache options for report data and for rendered reports, and parameter options for snapshot reports. If you set Never Refresh incorrectly, you could cause incorrect report data or reports to be cached, or cause a snapshot report to have inconsistent data. For more information, see Report Definition Language (SSRS).
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now