My Tech Views

How to create Map reports in SSRS First create a table which contains State name and Population as columns. Next insert a Map item in the report body. Next choose a source of spatial data by default Map gallery is selected and it is for USA.  Now in Map Gallery choose USA by State Exploded, we can see preview on the right. Next select Basic Map as the map visualization and in Data field select #STUSPS for abbreviated names of the state in the MAP. Now in order to assign the dataset, click on the Polygon drop down and select Layer Data, for assigning the Tooltip to display the population go to Polygon properties. In the Layer Data go to Analytical data and select the dataset and also choose the STATENAME, If more than one column is matching then choose others too. Also assign the tooltip as sum(population) in polygon properties At last choose Polygon Color Rule to change polygon colors.

How to join two datasets in SSRS by using Lookup function First create two datasets each one with a different datasource. Next insert a table in the report body and insert the required columns from the dataset1. Next in order to insert the columns from 2nd dataset, you need to write a look up function where you need to give the matching columns from both the datasets and the required column from dataset and scope as 2 datset Here since we are inserting the columns of dataset1 first, we are writing exp with scope as dataset 2.

How to export the SSRS report to PDF format. Once you create a report and preview it, from the drop down list of options you can see an option as PDF, click on it to export the report in PDF format. When the list of columns is huge, by clicking on Print Layout you can see that some of the columns are moved to next page in such case, click on the Page set up and select the output type as Landscape and click OK. When you export the report in PDF format you can see that the groups are not toggled and you can't see the complete report information. In such case go to group properties and edit the visibility expression as follows.

How to Export SSRS Report to CSV,Tab or Pipe Delimited File After creating the report once you preview it and click on the drop down you can see CSV(comma delimited) export option, when you click on it the CSV format of the report is downloaded. Right click on the report and click on open with note pad, you can the output in the below format. If you wish to change the delimited format go to the report server location and open the config file and do the below changes Under the render tag create one more extension by the name Tab delimited as shown below and save the file. Once you refresh the report you can see the tab delimited option. If you are not able to see then stop the service and start again. If you want to see the Pipe delimited then edit the FieldDelimiter to pipe (|) symbol

How to Export detail and Drill down Reports to Excel Firstly create a detail/drilldown report. We can export the created report in two ways. When you preview a report , when you click on the drop down we have various options to export. When you click on the Excel, the report is exported in xlsx format. The Second option is through the report manager. When you deploy the report same as in the preview tab we can see various options to export and you need to select Excel. When you export a drilldown report and wish to see each group in a different page with sheet name as group you can follow below steps. Go to properties of group in Row Groups and edit the PageName field to Group name and save the report. Also right click and go to group properties and and enable the Page break at the end of the group. When you preview the report you can see the region names as sheet names.

What are the new add ons in Report builder compared to SSDT. When you open the report builder and click on insert in the menu, you can see a chart wizard item which doesn't exists in SSDT. Here there is an option similar to report wizard for the creation of charts. While you create a dataset through the wizard in report builder you can add by clicking on the filters selecting the required column and 'is' operator is for single value, 'is any of' for multi value parameter and enabling the check box below the parameter.

How to build reports in SSRS using Report builder  Firstly open report builder from report manager URL or from the local system.  Next when you open the report builder a dialog box appears where you can directly select a wizard to directly create a table, matrix, chart and Map. If you want to directly create a report from scratch then you can choose blank report. You can connect to the report server using connect option at the bottom of the wizard. Once you get connect to the report server,you can use the files deployed to the report server. You can directly create a data source in the report builder or you can use the data source present in the report server. As shown below you should select shared datasource and click on browse and connect to the server. If you want to make changes to the existing report in the server and redeploy it again you need to first open the report by connecting to the server. In the file menu(2016 server) or ribbon(...

How to deploy Data source and Report by using Report Manager. Firstly copy the Report server URL from reporting services configuration manager, Report server gives the directory structure of the folders present in the reporting services. Copy the URL and paste into it into the properties of the SSRS project, Also edit the names of the TargetDataSource Folder and others same as the folder names created before so that only single folder exists after deploying Now right click on any data source or report and click on deploy. If you want to download the report click on the drill down and select download option.

How to create folders and Data sources by using Report Manager in SSRS Firstly select the report manager URL from reporting services configuration and go to the reporting services home. Next click on New Folder option and create a folder for data sources, also create various folder for Data Sets and Reports. Now click on the Details view in order to move or delete multiple reports at once. For creating a New Data Source click on the New Data source in the Home and provide the connection string, also connect using a service account so that we can provide access to that service account and every one uses that service account for connecting to the database. Finally after adding/deploying a report, select manage from the dropdown and choose the correct datasource .

How to configure reporting services and how to access Report Server in SSRS. Firstly open reporting services configuration manager in the machine and connect to the report server instance. Next choose the service account,here i am selecting the ReportServer and next click on Apply Next go to web service URL and copy the URL. This is the URL which you need to provide in the SSRS project properties while deploying the report items to the server.  Next go to Database and check is there is any Report server database existing or else create a database using the below steps. First click on create a new database and choose the Authentication type, the preferred choice is to create a service account and test the connection. Finally select the report manager URL, you will be directed to the reporting services home where everything is deployed.