Transposing a dataset in SSRS

Problem: We wish to tranpose a dataset in SSRS so that the columns headings become row headings, we dont want to do this in T-SQL

Solution: Move the detail group from the default row group to a column group

The linked document below includes detailed images on how to achieve this, this post contains the same information minus images.

Transpose Dataset using SSRS

The above report is our starting point, we want our end point to be a report where the first row is a row of business entity ids the second is a row of first names etc
1 Add New Table

2 Delete Row Group (and related rows)

3. Add Column Group

4. Delete non detail columns

5. Add rows to the table

6. Bind each cell to a value in the dataset

7. Add a header column

Report Builder With Dynamic SQL

Problem: You wish to create a report using Report Builder which utilises a text type dataset with dynamic SQL

Solution:
Declare your sql variable in the query text
Write TSQL which populates the variable using dynamic sql and execute the variable

Manually create a parameter and map this parameter within the dataset properties.

You wont be able to run this query within the query designer and it wont recognise any fields therefore you will need to manually add each field to the dataset.
Example
DECLARE @sql VARCHAR(MAX) = ‘SELECT ‘ + cast(@i as varchar(10)) + ‘ as field1’
EXEC (@sql)

Create a parameter called @i
Map @i to @i

Add a new query field called field1 to the dataset

User unable to Set up subscription in “My Reports” folder

Problem: A user was unable to set up an email subscription to a report in his My Reports folder. The To field was greyed out.

Solution: Amended users security on My Reports folder in addition to My Reports the user also requires Content Manager