Bottoms up! Finally simple bottomN report is working

I had difficulties to create simple Top N or Bottom N report in SSRS but finally it's working :) ! Here's the example how use bottom N filter in a tablix report. In this case I wanted to have list of the oldest jobs from the database table and the prompt where user can choose how many records are shown in the list.

[ 1 ] I made Report Parameter p_bottomN that is Visible to user.
[ 2 ] In its properties Available Values tab I specified values 5, 10 and 20 for user to choices. Label and value were both same. In Default Values tab I added value 5.
[ 3 ] Then in report's Dataset Properties and Filters tab I added filter:

• Expression [JobDate]
• Operator Bottom N
• Value [@p_bottomN]

Report looked fine when previewing it an I even didn't need to pre-order my data in dataset query. But after I deployed the report to the report manager, publishing site - bottomN prompt wasn't visible and default record amount wasn't 5. This was because at first I had this parameter hidden and default value was 10. Problem was solved in report manager. I looked to report's properties and parameter tab where I needed to put this prompt visible and change the default value.

Report Manager - Parameter tab.

Report Page Setup - Paper size

I use next setup for the reports that are displayed in HTML and PDF formats and need to be ready for printing.

Report Properties - Page Setup

Report Properties > Page Setup I have inches for units because I couldn't set size right with centimeters. When orientation is portrait then Width: 8,27in and Height: 11,69in. (For landscape - the other way round.)

Page margins are 0,5 inches so that it's easy to control report body width in relation to paper size. I have the ruler displayed in report designer and I set report body width to some what 7,2in (paper width 8,27in - margins 2*0,5in). Now report content won't exceed paper width and printing blank papers is avoided.

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.