Archive for the ‘SSRS’ Category

Transposing a dataset in SSRS

June 13, 2012

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

Advertisements

Report Builder With Dynamic SQL

June 11, 2012

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

June 11, 2012

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