MS SSRS: Jump to the Report Manager´s folder from a report

Case 1: I want to have link text “Go to Documents” in my report and clicking it takes me to ”Documents” folder in the Report Manager (ReportServer1).

> I add a Text Box to the report and type "Go to Documents" to it. Then I open the text box properties > Action. I choose "Go to URL" and click fx-button. See pic. below. 

I type the target folder URL to the expression box:

=”http://ReportServer1/Reports/Pages/Folder.aspx?ItemPath=%2fDocuments&ViewMode=List”                                

And it works.

Case 2: I want to have link text in my report and clicking it takes me to ”Documents” folder in the Report Manager (Report Server1). I can’t have Report Server name in the link because same report and it’s link has to work in several different report servers.

> There's a Built-in fields collection the Globals in the Reporting Services and one of the variables is ReportServerUrl. It returns the URL of the report server on which the report is being run. I use that:

=Globals!ReportServerUrl + "/Reports/Pages/Folder.aspx?ItemPath=%2fDocuments&ViewMode=List"                                      

As a result, it jumps to Report Server database directory:

And when I look at the address:

http://ReportServer1/ReportServer/Reports/Pages/Folder.aspx?ItemPath=%2fDocuments&ViewMode=List

I see ReportServer in the path because ReportServerUrl variable returns “http://servername/ReportServer”.

> So I have to revise this expression to have right path. Let’s take off that part using REPLACE function:

=Replace(Globals!ReportServerUrl + "/Reports/Pages/Folder.aspx?ItemPath=%2fDocuments&ViewMode=List", "ReportServer", "")

And now the link text works as I wanted.

MS SSRS: Multivalue report parameter and "Select All" default value

For example report user is prompted to select one or more employees from a drop-down list to filter the report data. The "Select All" check box is automatically the first choice in the list and easy to choose but when report user wants to make the subscription of that report "Select All" is not included and she needs to go through clicking the whole list. 
This is my solution to handle multivalue report parameter filter with ´Select All' default value:

1. Make dataset query that retrieves all the employees.
2. Add report parameter, choose Allow multiple values option  and get Available values from "employee"-dataset.
3. Add filter to the dataset that retrieves the report: "employee IN [@employee]" (data type Text).
- - Now you have a multivalue parameter filter ready for the report. --
4. Set default values for the parameter using same employee dataset.
- - Now all employees are checked in the report prompt by default and subscription is also easier to do. - -

CASE statement giving error in SSRS query designer

This is someting "back to basics" stuff.. :)

In SSRS query designer I needed write CASE statement like this:

        (CASE WHEN (tf_table.applicationtype = 1)  THEN ('first') 
         ELSE (tf_table.applicationtype) ) AS application

.....But I got error message:

MS SSRS - Sorting chart axis labels

In the beginning I had difficulties to remember where to find sorting options for the chart axis labels. Clicking around chart area and checking properties got to my nerves but then I decided to write it down for the future and then I begin to remember. I thought this would be useful post to some others too. In this example I add sorting to x-axis "finished_month".

[ 1 ]   Click over x-axis labels so that x-axis category field is seen
[ 2 ]   Right-mouse click open  Category Group Properties and add sorting.
 

x-axis labels - sorting

Notice...

[ 3 ]   It doesn't show anything at Chart Member Properties: SortExpression box. You have to click it open to see the sorting options for that Chart Member.

MS SSRS - Creating and copying custom palette

There are some preset palettes in Report Designer to use in charts. I did custom palettes because the variety of colours is not enough in preset palettes. I needed more than 10-13 different colours to the charts. I designed the custom palette using a colour pair system. In that system dark and light shade of same colour make one pair. That way bars in the chart or slices in the pie would be easier to recognize.

Errors while loading the designer - fixed in code view

During these months when I have build reports with Reporting Services, I haven't got many error messages but here is one that I met today...

I got following error message when I tried to open report file in Reporting Services designer:

One or more errors encountered while loading the designer.--- The errors are listed below. --- Deserialization failed: The 'DataType' attribute is not declared. Line 4921, position 20.

I could open report file in code view by right-clicking report and choseing "view code". I scrolled to that part of the code which was metioned in error message; line 4921, position 20. Error was in prompt parametervalue command/rule: DataType= "Integer".
Line was: <Value DataType= "Integer">0</Value> 
I removed that text ' DataType="Integer" --> <Value>0</Value>

After this correction report could be open in design view.

MS SSRS - Gradient colour to action button

I tried to find how to apply gradient colour to textbox item and after searching options from help and internet I found an answer from the social.msdn.microsoft.com forum site. There was told that gradient colour is possible only in chart and gauge report items.

Luckily there was suggestion to create chart and set it's backgroud, hide all the chart element so that only the gradient fill is seen. I tried that then.

I set BorderSkin (Skin Type) as Raised then it looked like more as buttons in the software what for this report was done. I also named this chart as BackButton. I put my button text to chart title, because I didn't know how to get my textitem over this chart.

I needed to fix appearance of my caption and also set docking properties so that it looked fine (1). DockOffset was set to 10 and Position as TopCenter.

1. Docking settings

Then I set Action for my button text to go back to main the report page.


There is still one thing I hadn't solved, how to hide that light gray line around the button.

Back button with gradient colours.