Solved

SQL Report Builder

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

758 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

20 Experts available now in Live!

Get 1:1 Help Now