Test Page

How Do I Freeze The Top Row?

Step 1 Deselect any existing related options. Right click on your table and select "Tablix Properties":

You want these options deselected:

Step 2 In the grouping panel click the far left drop down arrow to get advanced mode:

That will reveal "(Static)" grouping options:

Click into the table and then on the top left grey square to select the whole table:

Then click Row Groups – Static. The top cell of your first column will be selected:

Update to these properties: FixedData = TRUE KeepWithGroup = AFTER RepeatOnNewPage = TRUE

Step 3 The row headers have no background Fill colour set by default, if you Preview at this stage records will appear behind the headers as they scroll. Right click on each column header choose Textbox Properties and change this:

…to this:

How To Force Tables Onto Separate, Named, Excel Worksheets When Exporting

Right click on the table and choose "Tablix Properties". Add a page break after the table:

To name the destination worksheet, make sure the tablix is selected and look for the Properties Window and the Page Name property:

The second table should be as close as possible (0pt gap) to the previous with a different PageName for the destination worksheet:

How To Perform A Lookup In SSRS

TBC

How To Pass Multiple Parameter Values To OpenQuery SQL

Create the dataset for the parameter list, e.g. a list of Feed Names:

Create the parameter itself, ensuring multi-select, available and default values:

Use the parameter values as filter values on your main dataset:

Keep the linked server param visible until you deploy to PRD 

This can be useful to check where you’re pulling data from. Set Visible = False for the LinkedServer parameter when you deploy to PRD:

How To Increase The Number Of Rows Displayed Per Report Page

In Design mode increase the Height property of your report. It defaults to approx 30cm, doubling that to 60cm is usually a good idea:

How To Restore The Report Data Pane

If you lose this panel:

…the quickest way to restore it is CTRL+ALT+D (capital) https://stackoverflow.com/questions/13599228/cant-see-report-data-pane-when-creating-reports

How To Display What Values Were Passed To An Executed SSRS Report

SSRS reports fired as part of an SSIS solution are executed by the “ExecuteSSRS” SSIS package. If you ever need to see what parameters have been passed to such a report (either during testing or post Go Live) head here, find the relevant execution and click on “Overview”:

The result should be this with the parameters visible if you scroll down:

How To Retrieve The Parameterised URL Of An SSRS Report

TBC

How Do I Create A Report Variable To Hold A Value (Such As Textbox Colour)?

Why would I want to do this? Any static (or calculated) value which you want to involve in multiple expressions throughout your report will quickly become a management nightmare if a user requests changes. Far better to centrally manage such values. You could stick the value in a hidden textbox on the report and reference its value but you can use Report Variables to achieve the same result more elegantly. A tutorial is here: https://www.youtube.com/watch?v=k_WQ4a7J7VY Right click in the white space beneath your report in Design mode and choose Report Properties:

Create your variable (rv = “report variable”):

Now you can use the variable in a textbox expression:

The result:

Now the colour can be managed centrally via the variable rather than embedded in every textbox Fill expression.

How To Improve SSRS Performance By Caching Of Report Data

TBC

How To Force The Number Of Pages To Appear On A Multi-Page Report

Multi-page SSRS reports do not return the total number of pages on first render, as below:

To force this, add an invisible text box into the *footer* of the report with this expression ="Page "&Globals!PageNumber &" of "&Globals!TotalPages

How To Make Hyperlinks Work When Reports Are Exported To Excel 

A hyperlink to the explanatory Wiki page of a report is a useful feature to add to a report. When deployed the URL will open in the browser. However, if the report is exported to Excel the link has been found not to work. You can solve this by adding an expression to the text box action of your URL:

=iif(Globals!RenderFormat.IsInteractive,"javascript:void(window.open('https://closebrothersam.atlassian.net/wiki/spaces/TES/pages/240058417/How+to+use+SSRS+Trade+Search+and+Trade+Info+and+Audit+Reports','_blank'))","https://closebrothersam.atlassian.net/wiki/spaces/TES/pages/240058417/How+to+use+SSRS+Trade+Search+and+Trade+Info+and+Audit+Reports")

How Do I Create A Bespoke Table Structure?

There are times when you may want to create a much more bespoke layout for your reports rather than traditional tabular structures. For example:

Don’t use textboxes for individual field elements. Use the Table element instead:

Make sure you attach at least one Dataset to it:

…and then add your headings and expressions, inserting blank rows/columns to separate data as necessary. Note that despite attaching the Table element to only one datasource you can leverage other datasources in your expressions:

The end result can look something like this: