January 4, 2016 at 10:42 am
I have been tasked with finding out why a report isn't giving back the intended results. I have a copy of the *.rdl file, so I open the report in Report Builder 3.0 and look at the dataset used for the report.
I right mouse click on the dataset and select "Properties", which opens up the DataSet Properties screen. When I go into the "query" section, the query is a giant XML file (small sample posted below). Unfortunately, this doesn't really help me understand what is happening to find out why the report is supposedly not working. I really want to find the SQL Statement that is executed so I can copy it into SSMS and play with the query to find out why some data is being overlooked (I have a feeling the user doesn't understand what the report returns and it works as designed, but without knowing the query, I am not sure how to prove / disprove my feeling). Is there anyway to convert this XML into an actual SQL Statement? Or is there some option I might be missing in Report Designer to show the actual SQL Statement?
<SemanticQuery xmlns="http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:qd="http://schemas.microsoft.com/sqlserver/2004/11/semanticquerydesign" xmlns:rb="http://schemas.microsoft.com/sqlserver/2004/11/reportbuilder">
<Hierarchies>
<Hierarchy>
<BaseEntity>
<!--Reporting Client Contact-->
<EntityID>G8859e987-c659-4061-963f-8c4f0f316aee</EntityID>
</BaseEntity>
January 5, 2016 at 2:39 am
Hi Kocheese,
This looks like your report is using a Report Model (https://technet.microsoft.com/en-us/library/dd220475%28v=sql.105%29.aspx) as a datasource.
There wont be a SQL query in the report as it queries the semantic layer rather then the database, hence all the XML. If you can get access to the model project you can examine the data source view which will show you the SQL and joins underlying the model, however there is still the model layer in between which can contain calculated fields and aggregates.
Before starting that I'd say the best course of action is to confirm whether the report appears to be working by comparing to your own ad hoc queries on the database. If you think there is an issue then you'll need to delve into the model.
January 6, 2016 at 12:36 pm
Thank you for the help and explaining the underside of what was happening. Greatly appreciate the help...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply