Reporting Services (SSRS) - WiseOwl Tutorials

Refer Source: Reporting Services (SSRS) Part 1 - Getting Started with a New Project

.rdl  - extension for report

sql server BIDs - Sql Server 2008,2008R2 - Visual Studio 2008

sql server Data Tools - Sql Server 2012 - Visual Studio 2010

some basic formatting in this lesson.

Refer Source: 

Reporting Services (SSRS) Part 2 - Data Sources and Datasets

1. Defining  "Data Source"  &  "Data Set".

While creating a report , one need it to point to the Source of the data
i.e. the "Data Source" , then in the report we show column values
that is the "Data Set".

Shared Data Source:

create a shared data set , specify the "Server" &
the "Database" where data resides.

Shared Data Set:

Here you specify the "Shared Data Source" name which gives the connection to "Server" & "Database"

Here one can specify a "Stored Procedure" name, "Query" or use the "Query Builder" where one sees the Table graphically and build the query.

2.

Right hand side in the "Report Data"

Data Set: Add Data Set, specify the shared data source name , then specify the shared data set

, then data set elements will appear i.e the column names , in the report designer, put a control i.e. Table then one

can drag those fields and format & create the desired report

Note: While using Shared Data Source , if anything is changed it changes for all report using it.

3."Embedded Data Source"  & "Data Set"

If one want to have the data source defined in the report, in the Report Item side , right click on

Data Source and add a Data Source , one can point to the above Shared Data Source.

Similarly in the Data Set , specify the above embedded Data Source name.

Refer Source: 

Reporting Services (SSRS) Part 3 - Working
with Tables

Interactive Sorting:

At the runtime if you need to set up sorting by user, in the report design
view, click on the column & right click & choose "Textbox Properties" then choose "Interactive Sorting".

Filter:

If one adds filter to the DataSet on the Report Data , all reports using that
Dataset will be affected.

Rather in design on that report , choose fields highlighted then click on the top square box clicking will show Tablix property add a filter to that specific report.

Page Header to show in each page:

Row Groups                                                                                         Column Groups

First in the Column Groups - click on  "Advanced Settings" , under "Row Groups" it would appear

"static","static" and each of these corresponds to a heading.

Row Groups                                                                           Column Groups->"Advanced Settings"

Static                                                                   

Static

Details

Click on "Static" choose the property "RepeatOnNewPage" to true.

Refer Source: 

Refer Source: Reporting Services (SSRS) Part 4 - Keeping Header Rows Visible

Static  - Repeat On New Page (True)

Row Group

while scrolling to show the header: 

Static  - Repeat On New Page (True) & FixedData- True, back ground color ( white).

Refer Source: 

Reporting Services (SSRS) Part 5 - Controlling the number of rows per page



In the report to show the Row Number , in a column right click choose
"Expression" , =RowNumber(Nothing)

This report will show no. of rows as needed. But to configure to show a
desired one, click on Report menu-> here can change the "height".

Controlling the number of rows in a accurate way:

Row Groups Column Groups
Details -> Add Group -> Parent Group

In the "Group By" dialog box , instead of a field name use an "Expression",
Ceiling(RowNumber(nothhing)/10)

10 - number of rows one want to see in a page.

1/10 - 0.1 ( nearest integer is 1 , that ceiling function gives so till 10 rows
reaches it gives 1, it shows 10 recs. in one page.)


By doing grouping it adds a sort, clicking on Row Groups-> choosing "Group1" - deleting the sorting in it removes the error message.

In Design just Delete the column Group not the Group.

Then to have a "Page Break", choose the "Group1" properties choose
"Page Break".

If you want user to specify how many recs. it want to see

Using Parameter:->

Under "Report Data" , create a "Parameter" , give a name, can set a "Default value"

Next need to associate it to group as group expression has 10, that
need to point to this parameter value.
Ceiling(RowNumber(nothhing)/Parameters!RowNumber)

Refer Source:

Reporting Services (SSRS) Part 6 - Grouping in Tables

So grouping can be done by under Row Groups in details by "Adding Groups"
Row Groups Column Groups
Details -> Add Group
In the design window "Table" control is added and it shows the group by


column name and the other columns that is dragged from dataset.


Now in design window it shows a bracket and = sign for all those 3 rows.


Row Groups                                                                     Column Groups
Director Name
Details -> properties ( Hidden - True , ToggleItem - DirectorName1)



Row Groups                                                                     Column Groups-> Advanced                                                                                                                                                  Option(True)
Director Name
Static-> properties ( Hidden - True , ToggleItem - DirectorName1)
Details -> properties ( Hidden - True , ToggleItem - DirectorName1)
Static-> properties ( Hidden - True , ToggleItem - DirectorName1)


Create Nested

Row Groups                                                                                                   Column Groups
Director Name-> Add a Group ->Parent Group->Country Name
Static
Details 
Static

Refer Source:

Reporting Services (SSRS) Part 7 - Calculated Fields

Data Set -> right click ->Calculated Column ->

Refer Source:

Reporting Services (SSRS) Part 8 - Conditional Formatting in Tables

When you choose more than one text column , to find the "Expression" , choose the "Properties"
window, choose "BackGround" Color, in their below "Expression" is there.
There is Iff,Switch 

Alternate Row coloring:

Using the above same "Expression" ,  under Misc "RowNumber" function

=IIf(RowNumber(Nothing) Mod 2 = 0 , "color1","color2")

Mod func., will find the even number of rows, if the number leaves a remainer of 0)

Refer Source:Reporting Services (SSRS) Part 9 - Indicators

Toolbox , choose "Indicator" & drag to an empty cell, then one need to specify which
number it indicates too. When you click on the Indicator , it shows the "Gauge Data"
Panel, choose "Unspecified", it shows the field names then pick the field.

In the "Indicator" , if you right click choose "Properties"

Refer Source:

Reporting Services (SSRS) Part 10 - Gauges

Refer Source:

Reporting Services (SSRS) Part 17 - Linked Reports

Parameter

Report - Action

Refer Source:

Reporting Services (SSRS) Part 23 - Report Variables and Group Variables

Instead of now() function , the report variable will hold the time and show the value a specific value unlike now() giving
the exact value of time.

Report variable - Report ,properties

Group variable - create a group - properties

Refer Source:

Reporting Services (SSRS) Part 24 - Report Templates

To copy the template file: from : project folder 

To:

Program file or x86...

if sql server 2008r2 reporting services, Microsoft Visual Studio 9.0 

if sql server 2012 then Microsoft Visual Studio 10.0 

\Program file\rightversnofvisualstudio\common7\IDE\PrivateAssemblies\ProjectItems\ReportProject\template.rdl

Now it will so in the folder from where you choose Report Project.

Refer Source:

Reporting Services (SSRS) Part 25 - Adding Code to Reports

Refer Source:

Reporting Services (SSRS) Part 26 - Creating a Custom Assembly

Visual Studio 2010 - project type - Installed Template - Visual Basic - Class Library

.NET Framework 3.

.NET Framework 4.0 ( is not supported)


Get syntax, by first coding in visual stuido then pasting in "
Report properties - code"

Refering the code start with "code."

Make a template having this code and copy to same location as it's mentioned for Template.


Refer Source:

Reporting Services (SSRS) Part 26 - Creating a Custom Assembly

search: Visual Studio Express

Express 2013 for windows Desktop

or

Visual Studio 2010 Express


Create Class Library project:

Template: Visual Basic

Class Library

(Use: NET Frame Work 3.5)

After creating a function which formats a date , the project is compiled & by that a DLL is created.

The same function is created in C# & vb.

DLL location:

Project-properties->compile ( output path: bin\debug\)

copy the file.

Paste location can be for Report Designer or when the report deployed the location of that.

Report designer location : c:\Program Files\Visual Studio 10\Common7\IDE\PrivateAssemblies\

Copy to the Report Server : C:\Program Files\ Microsoft Sql Server 2012\MSRS11.SQL2012\Reporting Services\ReportServer\bin\

For testing:

1) In the designer:

Create a new report , right click in the "References" , specify the dll location & in the report put
text field, check the expression

2) In the Report Server: Deploy the report by clicking on the "Deploy" option in the designer.

i.Navigate Report Manager url, click on the project and then click on the report and the result shows right.


Note:
While building the functions in the visual studio 2012 , instead of copying the dll to the deginer location & report server folder , it can be automated in the visual studio function project , compile,
"PostEvent" ,  there specify the copy /y  "$target" specify the location to copy to, so every time with
successful compile it will copy it.