Tips

•        CDate(“mm/day/year”).

CDate("may/02/2011")—result is 5/2/2011

Table Grouping: Problem 2

Situation:
      If you want to keep particular group alone in single page.

d    Solution:

    Step 1:  Select the group cell. Do  write click. Select Row Group--> Grop Properties

Step 2: Check the box like below,

Note:

That particular group alone will display in every single page.

Table Grouping: Problem 1

Situation:

 If you using two tables in same page. If you wish to display the 2nd table data content in new page. Do the following steps:

           Solution:
Select table1's properties. Then click the sorting. Select page breaks.

Expantion for .rdl and .rdlc file

Note :

Rdl stands for- Report Definition Language

Rdlc stands for-Report Definition Language Client-Side

Difference between .rdl and .rdlc file

Difference between rdl and rdlc:

·         The <Query> element of RDL contains query or command information and is used by the report server to connect to the data sources of the report.

·         The <Query> element is optional in an RDLC file. This element is ignored by the ReportViewer control because the ReportViewer control does not perform any data processing in local processing mode, but uses data that the host application supplies.

·         Report Viewer’s dataset is different from rdl’s dataset.

• Dataset and data source terminology has different meanings in Visual Studio 2005 and SQL Server 2005. In Visual Studio, a dataset is a collection of data tables and is logically equivalent to a query or command that retrieves data. In Reporting Services, a dataset includes the following parts:
• Data source information, including the data source type and connection string. The data source type specifies a data processing extension that is deployed on the report server.
• Credential information that specifies how to obtain credentials for the data source connection.
• Query or command information used to retrieve data.

Visually, an .rdlc file that is configured to run in a ReportViewer control has the dataset object, binding source object, and table object displayed at the bottom of the designer view. The following diagram provides an illustration of how these objects appear in a Windows form (the same data binding objects appear differently in a Web page):

If you select an .rdlc file for use in a ReportViewer control and the data objects do not appear in the project, you are most likely looking at a renamed .rdl file that requires additional conversion steps to make it a fully functional .rdlc file.

The dataset name might be misleading; by default, Reporting Services uses the name of the data source for the first dataset that is created (for example, a dataset might be called "Northwind" even if it only retrieves data from one of the tables).

Convert rdl(2008) to rdlc(2008) file Problem

Cant use  ssrs SQL R2 in visual studio 2008,(if u try to convert rdl to rdlc, result is error with unknown html code)

Reason is,

Visual Studio 2008 was released much earlier than SQL Server 2008, so ReportViewer 2008 is based on the 2005 version of RDL.
A SQL Server Reporting Services 2008 server report is based on the 2008 version of RDL, so, it can't be degrade to 2005 RDLC.

In this case, we need to redesign the local report with Visual Studio.

In order to convert a RDL 2008 to RDLC 2008, we can follow the steps in the original article you have post.
However, this RDLC can't be used in Report Viewer 2008 as Report Viewer 2008 need RDLC 2005. To use RDLC 2008, please use Report Viewer 2008 R2(10.0.0.0). Report Viewer 2008 R2 is coming with Visual Studio 2010.

How to retrieve the table's particular cell value or Textbox

Note:

        Step 1:  Select anyone of table cell or Textbox in your SSRS report.

        Step 2:  Note that cell name or textbox name in properties.

        Step 3:  Then you can retrieve and use that value in anywhere, Like ReportItems!Textbox1.Value

                       Ex: ReportItems!TextboxName.Value

Fields in SSRS

Fields:

Note:

       Ex: (=Fields!bgt_rq_nbr_id.Value)

1. If you used this in table or list etc. The total values will display from dataset. The table will fetch the value one by one from dataset.

About DataSet in SSRS

DataSet:

Note:

        your datasource can be stored procedure or table etc. Anyway your result will be a dataset only.

you can see the list of dataset column names(First value of each column. 


Ex:=First(Fields!ColumnName.Value, "DataSetName")) in Expression window.

To Find a last value of dataset, 

Ex: Last(Fields!ColumnName.Value, "DataSetName")

Note:

        If you used(=First(Fields!ColumnName.Value, "DataSetName"))  this in table,the same value(first value) will repeated. For  Example, if dataset contains 100 recordes, it will repeat for 100 times. Because it pointing the first value position of dataset.

Build-In-Fields

1.       Rigth click on TextBox or Table Cell etc. Select Expression.

2.       Select Build-In-Fields.

ExecutionTime:

The date and time that the report began to run.( =Globals!ExecutionTime)

Language:

The language ID of the client running the report.(=User-language)

Ex: (=User!language) – is              en-US.

OverallPageNumber:

The current overall page number. Can be used only in a page header or footer.

Note:

It returns the current page number.

OverallTotalPages:

The total number of pages in the report. Can be used only in a page header and footer.

Note:

 It returns the total number of pages generated.

PageName:

The name of the current page in the report. Can be used only in page header or footer.

PageNumber:

The current page number, which can be reset through the use of page breaks. Can be used only in page header or footer.

Note:

Its similar to OverallPageNumber.The difference is PageNumber can be reset through page break.

RenderFormat.Name:

The name of the renderer as registered in the RSReportServer configuration file.

Ex: Globales!RenderFormat.Name ----   IMAGE

ReportFolder:

The full path to the folder containing the report. This does not include the report server URL.

Ex: Globals!ReportFolder

ReportName:

The name of the report as it is stored in the report server database.

Ex: Globals!ReportName

ReportServerUrl

The URL of the report server on which the report is being run.

Ex: =Globals!ReportServerUrl

TotalPages:

The total number of pages in the current continous page sequence. The number can be reset by using page breaks. Can be used only in a page header and footer.

Ex:=Globals!TotalPages ----  Current Total Pages. It can be reset by the page breaks or group break etc.

UserId:

The ID of the user running the report.

Ex: =User!UserID  ----   Will returns the current user Name and group name.

Create Variables and Constants

1.Select Expression.

2.Select Report in menu bar. Report--> Report Properties. Then Select Variables.

3.Here Read Only property is checked. Then you cant reassign value to this variable. Also you can enter your own expression by clicking          fx    button(Uncheck the readonly property). 

4. Now you check the variables list in Expression window.

5. Now you can use this variable in your project. :) 

Simple steps for developing SSRS Report

Steps to build Simple SSRS Report:

1) Create New Project with choosing Business Intelligence Projects\ReportServerProject.

2) By opening this Simple Report Project, click on Solution Explorer View. You are able to see following screen.

3) Now Right click on Shared Data Sources folder and choose Add New Data Source tab to add the Data Source for report.Write the appropriate connection string like following:

You can choose the database connection string from Edit Button displayed on above screen. Pressing Edit Button will redirect you on the following screen.

Choose the appropriate data base name and server name and if Test Connection is Succeed
then Press OK button. This will reflect to Shared Data Source connection string. It will be
automatically changed as per you selected in Connection Tab.


5)Now turns to add report to our Report Server Project. Let’s create a sample report, which can display the Employee Details like First Name, Last Name, Address, City, Region, Country, etc.For all this we need to add a report first, so for this right click on “Report” Folder in The Simple Report Project and choose “Add” Then “Add New Item” you can see the following screen:

Choose “Report” and give appropriate report name and click on ADD button.

6)After adding a new report into project, you will be able to see tree tabs.

a. Data – You have to provide the dataset for the report into this option. You can specify the SQL script into that.

b. Layout – This tab handle the layout of the report. You can specify the report layout in design mode. This works same as Report designers like Crystal report etc…

c. Preview – You can see the Preview of the report in this tab.

In Data tab User will need to create dataset for the report. The Dataset dropdown will have option by using this User will be able to create the dataset.

For this in DATA Region of the Report, click on Item of the Data Set Drop Down List Box. By clicking this you are redirected in following screen.

Here you have two options:

1. Write SQL Queries directly by selecting Command Type=”TEXT”
2. You can give Stored Procedure Name also by selecting the Command Type=” StoredProcedure”.

Press ok after applying proper queries or stored procedure depends as per requirements.

Following diagram shows the Data tab of the Report.

7)Now turns to design the Actual Report as per our needs.

We want to display the Employee Details in our report. For this we are required to put
Labels, Text Boxes that shows the actual details of the Employees from the Employee table
as we applied into the Data Region.

Following image is showing the Layout view of the report in this we are using Table 
object,which have the facilities of Header and Footer of the Rows. So we are displaying the
Column Title in Header and the actual record in Rows section of the Table.

8)The stored procedure will returns the dataset. The dataset’s column names will be displayed in Report Data Explorer. You can use datasource as dataset or stored procedure.  If you used dataset,the datasets column name would be listed. Here the stored procedure passed with parameters.