Add Interactive Sort to a Table or Matrix (Reporting Services)

Sorting Detail Rows for a Table with No Groups

Add an interactive sort button to a column header to enable a user to click the column header and sort the details rows in a table by the value displayed in that column.

To add an interactive sort button to a column header to sort the table by value

1. On the report design surface, in a table with no groups, right-click the text box in the column header to which you want to add an interactive sort button, and then click Text Box Properties.
2. Click Interactive Sort.
3. Select Enable interactive sort on this text box.
4. In Sort, click Detail rows.
5. In Sort by, specify a sort expression. From the drop-down list, select the field that corresponds to the column for which you are defining a sort action (for example, for a column heading named "Title", choose [Title]). Specifying a sort expression is required.
6. Click OK.
7. Repeat steps 1-6 for every column to which you want to add an interactive sort button.
   To verify the sort action, you can preview the report, and click the interactive sort buttons.

Sorting a Top Level Parent Row Group for a Table or Matrix

Add an interactive sort button to a column header to enable a user to click the column header and sort the parent group rows in a table or matrix by the value displayed in that column. The order of child groups remains unchanged.

To add an interactive sort button to a column header to sort groups

1. In a table or matrix on the report design surface, right-click the text box in the column header for the group to which you want to add an interactive sort button, and then click Text Box Properties.
2. Click Interactive Sort.
3. Select Enable interactive sort on this text box.
4. In Sort, click Groups.
5. From the drop-down list, select the name of the group that you are sorting. For groups based on simple group expressions, the Sort by value is populated with group expression.
   

   Note
   For complex group expressions, manually set the Sort by expression to the same value as the group expression.

   For more information about simple and complex expressions, see Understanding Simple and Complex Expressions (Reporting Services).
6. Click OK.
   To verify the sort action, you can preview the report, and click the interactive sort buttons.

Sorting Child Groups or Detail Rows for a Group

Add an interactive sort button to a group header row to enable the user to sort the values of a child group from a parent group or to sort the detail rows for the innermost child group.

To add an interactive sort button to a text box in a group row header to sort child groups or detail rows

1. On the report design surface, right-click the text box in the group header row to which you want to add an interactive sort button, and then click Text Box Properties.
2. Click Interactive Sort.
3. Select Enable interactive sort on this text box.
4. In Sort, click one of the following options:
   
   • Details   Click Details to sort the detail rows. From the drop-down list, select the field to sort by. For this option, you must specify the value to sort by.
   • Groups   Click Groups to sort the child group values. For this option, the Sort by expression is automatically filled in from the group expression.
5. Click OK.
   To verify the sort action, you can preview the report, and click the interactive sort buttons.

Sorting Rows Based on a Complex Group Expression

Add an interactive sort button to a column header to enable a user to click the column header and sort the combined parent and child groups. To achieve this affect, you must change the group expression to be a composite of both groups. For example, suppose a matrix displays inventory totals for a store for items grouped by both color and size. To sort the rows based on the combination of color and size, instead of having a separate group for color and a separate group for size, you can define a group based on the combination of color and size. For more information about defining group expressions, see Examples of Group Expressions (Reporting Services).
In the following procedure, terms specify Tablix data region areas. For more information, see Understanding Tablix Data Region Areas.
Typically, when you sort rows based on multiple groups, you want to see totals for the sorted rows, regardless of column groups. In this procedure, no column groups are used. You start by adding a matrix and removing the default column group. Alternatively, you could start by adding a table and removing the details group.

To add an interactive sort button to a column header to sort multiple groups

1. On the report design surface, add a matrix.
2. Drag a numeric field to the data cell to link the dataset to the matrix.
   Next, you will create a group with a group expression that specifies multiple fields, and a group header to use to display the group values.
3. Verify that the matrix is selected on the design surface. The Grouping pane displays a default row and column group.
4. In the Row Groups pane, right-click the default row group, and then click Edit Group. The Group Properties dialog box opens.
5. In Name, replace the default name with a name that specifies the multiple groups that you want to group by.
6. In Group expressions, in Group on, click the Expression (fx) button to open the Expression dialog box.
7. Type the expression that specifies all fields that you want to group by. For example, the following group expression combines a field named Color and a field named Size: =Fields!Color.Value & Fields!Size.Value.
8. Click OK.
   You have now defined the group. Next, drag the fields to display to the Tablix body area of the matrix. Add the fields that you chose to group by in step 7 to the Tablix body area, each in its own column.
   For this scenario, the first column in the Tablix row groups area is not needed. To delete the column, right-click the column header, and then click Delete Columns. A dialog box asks whether to delete the associated groups. Click No. The row group area is deleted and only the Tablix body area remains.
   Next, you will remove the default column group.
9. In the Column Groups pane, right-click the default column group, and then click Delete Group. A dialog box asks whether to delete the group and related rows and columns or the group only. Click Delete group only. The column group is deleted, and the column group area is deleted. Only the Tablix body area remains.
   Next, you will add an interactive sort button to the text box that spans the matrix.
10. Click in the text box in the first row and then click Text Box Properties.
11. Click Interactive Sort.
12. Select Enable interactive sort on this text box.
13. In Sort, click Groups.
14. From the drop-down list, select the name of the group you created in step 5. The group expression is automatically copied to the Sort by text box.
15. Click OK.
    You have added the sort button to the text box.
16. (Optional) You can suppress duplicate values in the columns that display group values. On the report design surface, click the text box that displays the value for which you want to hide repeating values. In the Properties pane, scroll to HideDuplicates, and from the drop-down list, select the name of the dataset that is linked to this matrix.
    To verify the sort action, you can preview the report, and click the interactive sort button. The matrix sorts by the combined values of the group expression, although each individual value displays in its own column.
    
    
    ref url : http://msdn.microsoft.com/en-us/library/ms157462.aspx
    
    http://msdn.microsoft.com/en-us/library/ms157462.aspx

Repeat Header / Keep Header Visible in Tables while scrolling with SSRS

- set KeepWithGroup to After
- set RepeatOnNewPage to true for repeating headers
- set FixedData to true for keeping headers visible










Ref : http://blogs.msdn.com/b/robertbruckner/archive/2008/10/13/repeat-header-and-visible-fixed-header-table.aspx

How to write SSRS report into server folder as Excel File

Public Sub RenderReportToExcel(ByVal _ReportPath As String)

    ReportViewerCostReport.Visible = True
    ReportViewerCostReport.Reset()
    ReportViewerCostReport.ServerReport.ReportPath = "/myReportFolder/reports/" + _ReportPath
    ReportViewerCostReport.ServerReport.ReportServerUrl = New System.Uri("http://localhost/ReportServer")
    ReportViewerCostReport.ServerReport.ReportServerCredentials = New MyReportServerCredentials2()

    Dim myparamProjectID As ReportParameter
    Dim myparamsProjectID As New List(Of ReportParameter)
    myparamProjectID = New ReportParameter("ProjectID", DropDownListPrj.SelectedValue)
    myparamsProjectID.Add(myparamProjectID)
    ReportViewerCostReport.ServerReport.SetParameters(myparamsProjectID)

    Dim warnings As Warning
    Dim streamIds As String
    Dim mimeType As String = String.Empty
    Dim encoding As String = String.Empty
    Dim extension As String = "xls"

    Dim bytes As Byte() = ReportViewerCostReport.ServerReport.Render("Excel", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)

    Dim oFileStream As System.IO.FileStream
    Dim strFilePath As String = String.Empty
    strFilePath = Server.MapPath("~/YourFolderOnRoot/FileName.xls")

    oFileStream = New System.IO.FileStream(strFilePath, System.IO.FileMode.Create)
    oFileStream.Write(bytes, 0, bytes.Length)
    oFileStream.Close()

  End Sub

Reference url:http://forums.asp.net/t/1746293.aspx?How+to+write+SSRS+report+into+server+folder+as+Excel+File

Exporting to Word/PDF using Microsoft Report (RDLC) without using Report Viewer

protected void Page_Load(object sender, EventArgs e)
{
    LocalReport report = new LocalReport();
    report.ReportPath = "Report1.rdlc";
    ReportDataSource rds = new ReportDataSource();
    rds.Name = "DataSet1";//This refers to the dataset name in the RDLC file
    rds.Value = EmployeeRepository.GetAllEmployees();
    report.DataSources.Add(rds);
    Byte[] mybytes = report.Render("WORD");
    //Byte[] mybytes = report.Render("PDF"); for exporting to PDF
    using (FileStream fs = File.Create(@"D:\SalSlip.doc"))
    {
        fs.Write(mybytes, 0, mybytes.Length);
    }
}  

Reference url:http://www.codeproject.com/Articles/492739/Exporting-to-Word-PDF-using-Microsoft-Report-RDLC

How to render client report definition files (.rdlc) directly to the Response stream without preview

/// <summary>

/// References:

/// </summary>

private void RenderReport() {

    LocalReport localReport = new LocalReport();

    localReport.ReportPath = Server.MapPath("~/Report.rdlc");
  

    //A method that returns a collection for our report

    //Note: A report can have multiple data sources

    List<Employee> employeeCollection = GetData();

    //Give the collection a name (EmployeeCollection) so that we can reference it in our report designer

    ReportDataSource reportDataSource = new ReportDataSource("EmployeeCollection", employeeCollection);

    localReport.DataSources.Add(reportDataSource);

    string reportType = "PDF";

    string mimeType;

    string encoding;

    string fileNameExtension;

    //The DeviceInfo settings should be changed based on the reportType

    //http://msdn2.microsoft.com/en-us/library/ms155397.aspx

    string deviceInfo =

    "<DeviceInfo>" +

    "  <OutputFormat>PDF</OutputFormat>" +

    "  <PageWidth>8.5in</PageWidth>" +

    "  <PageHeight>11in</PageHeight>" +

    "  <MarginTop>0.5in</MarginTop>" +

    "  <MarginLeft>1in</MarginLeft>" +

    "  <MarginRight>1in</MarginRight>" +

    "  <MarginBottom>0.5in</MarginBottom>" +

    "</DeviceInfo>";

    Warning[] warnings;

    string[] streams;

    byte[] renderedBytes;

    //Render the report

    renderedBytes = localReport.Render(

        reportType,

        deviceInfo,

        out mimeType,

        out encoding,

        out fileNameExtension,

        out streams,

        out warnings);

    //Clear the response stream and write the bytes to the outputstream

    //Set content-disposition to "attachment" so that user is prompted to take an action

    //on the file (open or save)

    Response.Clear();

    Response.ContentType = mimeType;

    Response.AddHeader("content-disposition", "attachment; filename=foo." + fileNameExtension);

    Response.BinaryWrite(renderedBytes);

    Response.End();

}

reference url: http://weblogs.asp.net/rajbk/archive/2006/03/02/How-to-render-client-report-definition-files-_28002E00_rdlc_2900_-directly-to-the-Response-stream-without-preview.aspx

how many saturdays and sundays between two dates with sql server

declare @StartDate datetime,@EndDate datetime
set @StartDate='20140101'
set @EndDate='20140131'

                           
                            select count(*) as Daycount
                                                                    from master..spt_values as Number
                                                                    where Number.type = 'P' and
                                                                          dateadd(day, Number.number, @StartDate) <= @EndDate and
                                                                          datepart(dw, dateadd(day, Number.number, @StartDate)) in ( 1,7)



SELECT  dATE , COUNT(dATE)   FROM   (
                SELECT TOP (datediff(DAY,@StartDate,@EndDate) + 1 ) [Date] = DATENAME(dw , dateadd(DAY,ROW_NUMBER() OVER(ORDER BY c1.name),
                            DATEADD(DD,-1,@StartDate)))        
                            FROM   [master].[dbo].[spt_values] c1 ) X
                            GROUP BY Date


                                                                         
          -- mondays count with replication                                      
     DECLARE
   @StartDate date = '2014-01-01',
   @EndDate date = '2014-01-31';

WITH A AS (SELECT DayCount = DateDiff(day, @StartDate, @EndDate) + 1),
B AS (
   SELECT
      DayCount,
      WeekCount = DayCount + 6 / 7,
      Dow = DateDiff(day, '18991230', @StartDate) % 7 FROM A
)

SELECT
   MondayCount =
      Len(Replace(Substring(
         Replicate('0100000', WeekCount),
         Dow, DayCount
      ), '0', ''))
FROM B;

Serverside alert control in C#

     Serverside alert control 
   string javaScript = "<script language=JavaScript>\n" + "alert(' Data has been added successfully '); location = 'Home.aspx';\n" + "</script>";
        ClientScript.RegisterStartupScript(typeof(Page), "btnSave_Click", javaScript);




With UpdatePanel Serverside alert control

    ScriptManager.RegisterStartupScript(this.Page, this.Page.GetType(), Guid.NewGuid().ToString(), "alert(' Data has been added successfully'); window.location ='Home.aspx';", true);

Populate DropDownList with Selected Value in EditItemTemplate of GridView in ASP.Net

Nice article

http://www.aspsnippets.com/Articles/Populate-DropDownList-with-Selected-Value-in-EditItemTemplate-of-GridView-in-ASPNet.aspx