• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1182
  • Last Modified:

SQL Report Builder

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
countyprob
Asked:
countyprob
1 Solution
 
magarityCommented:
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
 
countyprobSystem AnalystAuthor Commented:
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
 
Tony303Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now