Solved

SQL Report Builder

Posted on 2014-01-17
3
1,101 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

861 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