Get a query from a report

NBi is able to extract queries from SSRS reports and use them in your tests. Other features related to queries execution are fully applicable to query extracted from reports: you can definitively make usage of Query's parameters and Query's template-variables.

To extract the queries from your report you can choose to connect to a ReportingServer database or try to parse to a rdl file on a disk. In both cases you need to specify the xml element report in place of the xml element named query

<system-under-test>
  <execution>
    <report ... />
  </execution>
</system-under-test>

Before trying to achieve this, you must know that a report may contain more than one query. In consequence, it's always needed to specify which query you want to extract from the report. This can be done by using the xml attribute named dataset in the corresponding xml element.

Shared dataset

Since version 1.7, NBi supports the shared dataset. Independently of the way the dataset is created in the report (embedded or shared), NBi will retrieve the corresponding query. It's not needed to adapt the test syntax, it's transparent.

From ReportingServer database

To extract from a ReportingServer database, you must specify the source as the connection-string to connect to this ReportingServer database.
<report source="Data Source=(local)\SQL2012;Initial Catalog=ReportServer;Integrated Security=True;" .../>

Don't be confused with the attribute connectionString defining the database on which you will apply your query. This connection-string is optional and can be replaced by a default value or a reference specified in the settings.
<report connectionString="..." />

Finally, you must define which report you'll want to extract e the means of the attributes path and name. The path is referencing the folder and sub-folders of the report and name it's displayname on the portal. Note that the leading and final "/" on the path are mandatory.
<report source="Data Source=(local)\SQL2012;Initial Catalog=ReportServer;Integrated Security=True;"
		path="/AdventureWorks 2012/"
		name="Store_Contacts"
		dataset="Stores"
/>

From a rdl file on a disk

The difference with the ReportingServer database is expressed on the attribute source which must be unspecified. The path will be expressed from the test-suite file and will have a final "\".
<report
	path="AdventureWorks Sample Reports\"
	name="Store_Contacts"
	dataset="Stores"
/>

From SSRS Web Service

Will be introduced on a next release.

Full sample

The following code extracts the query from a report named StoreContacts, in directory/AdventureWorks 2012/ hosted on a ReportingServer database. The query is available in the dataset named StoreContacts and NBi applies a value of 300 to the parameter named StoreID_ when executing the query on a database with a connectionString referenced in the default settings applying to a system-under-test.
<system-under-test>
	<execution>
		<report source="Data Source=(local)\SQL2012;Initial Catalog=ReportServer;Integrated Security=True;"
			path="/AdventureWorks 2012/"
			name="Store_Contacts"
			dataset="StoreContacts"
		>
			<parameter name="StoreID">300</parameter>
		</report>
	</execution>
</system-under-test>

Last edited Sep 1, 2014 at 7:47 PM by Seddryck, version 3

Comments

No comments yet.