Friday, August 31, 2018

SSRS - Export SSRS report into multiple excel sheets name

SSRS is the most amazing data visualisation tool which provides many amazing features. One of the best feature of this tools is to export data into multiple sheets in excel and provides Name the worksheets according to group value. We can assign dynamically sheet name / tab name. When SSRS report is exported into excel format then the report data will be divided into multiple sheets and each sheet must have Tablix Header.

To create multi tabbed report, we have to follow the following steps -
  1. Add Row Group to the report and choose Parent Group
  2. In Group Properties window, choose Page Break and checked Between each instance of a group
  3. In Row Group section, click on Group details and go to property window
  4. In property window, open expression window for PageName and assign the group name from the report dataset
  5. To fix tablix header in every new sheet/tab, Click on Advance mode form Row and Column Group and set true property of KeepTogether and RepeatOnNewPage
  6. Preview of the report ---- Done
For actual implementation, please watch the below video -

Tuesday, August 28, 2018

SQL Server - Import raw data file into SQL Server database

In SQL Server Management Studio, SQL Server Import and Export Wizard option is helpful to into your raw data into SQL Server database. Import Flat File provides us to create simple packages that import and export data between many popular data formats including databases, spreadsheets, and text files. The import and export wizard also create the destination database and the tables into which the data is inserted.

Import Text/CSV file into SQL Server follow below mention steps:
  1. In SQL Server Management Studio, right click on the database and choose Import Flat File from tasks options
  2. Specify Data Source as Flat File Source and Text/CSV file Location
  3. Specify Export location Details
  4. Mapping Source and Destination tables (Leave this option as it is)
  5. Summary page to display our current configuration
To watch a demo here-

SSRS - How to creating Dynamic Tabs in SSRS Report

In this video of SSRS Tutorial, we will learn that how to create a SSRS Report with dynamic Tabs and pull the data according to selected tab.

As you know that there is no straight forward way to include tabs in SSRS report builder. We have to achieve this by using different tricks. Here is what we will learn while creating this report by using some normal steps and some logical tricks.

Few steps to follow- 
  1. Create Empty report in SSRS in SSRS Project
  2. Add Report Header so can display the Report Title on each of the Page
  3. Add dataset for dynamic tabs
  4. Add Matrix and assign dataset as dataset name property
  5. Drop tabs value from dataset into matrix column header
  6. Add parameter for dynamic tab to filter the data 
  7. Set colour formatting for tabs based on the condition as per in the below video 
  8. Add action to matrix column and provide the parameter from dataset column
  9. Hide the report parameter
  10. Add another dataset to pull the data from the database and filter the data by passing the value from the parameter
  11. Add table to generate the tablix report and set the dataset name
  12. Drop columns from the report dataset
  13. Do needful formatting to the report
  14. Preview the report