Solved

SQL Report Builder

Posted on 2014-01-17
3
1,130 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

617 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