What's the deal with transactions in a stored procedure called by an SSRS report?

On the data source of an SSRS report, there is a property for "Use single transaction when processing the queries". There are a couple different behaviors tied to marking this checkbox:

1) As you noticed, SSRS will use an explicit transaction to process the data sets using this data source, if this checkbox is marked. When it is not marked, there is no explicit transaction.

2) When this checkbox is not marked, data sets using the data source will execute in parallel, using separate connections to the server. If the checkbox is marked, then the data sets use a single connection and are processed serially.

Additionally, there may be different motivations for using this checkbox. The two major ones are:

1) Read consistency: If you are using Snapshot Isolation, you may want all data sets to be consistent to a single point in time. In this case, using a single explicit transaction and using the Snapshot isolation level can ensure all statements within the transaction are consistent to a single point in time.

2) Performance: Depending on a number of factors (which are really a separate topic), you may see dramatic performance difference between running the queries for different data sets in parallel vs running them serially.