How to Read a Query From Rdl

The SSRS report architect is a powerful tool for information visualization. In this article, we volition have a first stride into familiarizing ourselves with the SSRS report builder and we volition besides demonstrate a very basic SSRS report example. Additionally, we volition review the self-service business intelligence approach just considering this arroyo tin can be implemented through the SSRS study builder very easily.

Self-Service BI

IT departments get written report development request from business departments thousands of times a mean solar day. Unfortunately, It departments take limited developer source and capacity, and it is for this reason that they can't respond to every BI development asking. How can we address this issue?

1. We can hire more developers
2. We tin hire freelancers
3. Or nosotros can implement the Self-Service BI arroyo

#C is the right and effective solution for this consequence because not-technical persons can learn to develop their own reports and dashboards through prepared datasets. Key stakeholders can gain the ability to develop and create their own reports so that the Information technology department dependency and waiting time will decrease. To attain this, though, report and BI tools must be so piece of cake and understandable by non-technical users.

The purpose of the Self-Service BI approach is to provide study or dashboard development adequacy to not-technical users thus, will give more than freedom and responsibility. The main question is how we can implement this arroyo with SQL Server Reporting Services? Microsoft has obviously taken this into consideration because the SSRS report builder is a very unproblematic report blueprint tool and they can be easily learned and used by non-technical staff. At the aforementioned time, BI developers can provide key users with shared datasets, to abstract users from learning how to think data from desired data warehouse or relational database. Additionally, SQL Server Reporting Services offers a pregnant feature which is the My Report Folder. This characteristic offers a private and personal storage to users so that users tin can deploy and use their SSRS report in this folder. As a outcome, Self-Service BI allows non-technical staff to generate written report by themselves so that it can decrease the costs which related to business concern intelligence implementation.

Preparing to sample data

Before we start our SSRS study builder sit-in, we demand a sample dataset. For this, we will download the sample sales csv file and load information technology to Azure SQL database. It is very basic performance then we can complete the post-obit steps easily. Additionally, y'all tin can apply the following steps on premise installation of whatsoever SQL Server version.

• Connect to Azure SQL or any premise installation through SQL Server Management Studio
• Right click in your test database and so navigate to Tasks > Import Flat File…

  

• Chose the csv file and specify a new table name to which the data will exist imported.

  

• Click Next in the Preview Data stride

  

• Click Next in the Modify Columns pace

  

• Click Stop in the Summary stride

  

• The performance will be completed successfully

  

Create our beginning report with SSRS Study Architect

The SSRS Report Builder is a report cosmos tool which allows users to create, manage and publish reports to SQL Server Reporting Services. We can also create shared datasets with the help of the report builder. The Report builder has a standalone installation then nosotros can easily setup and configure information technology. We tin find out the installation link in the web portal of SQL Server Reporting Services. When we click this link it redirects to download folio.

Nosotros will skip the SSRS report architect installation steps because information technology is pretty self-explainable. When we launch the report builder, "Getting Started" screen is displayed. In this screen, we have four options;

1. New Report: In this tab, we can chop-chop create reports through the various wizard options or we can choose the bare report option. In the blank report option, we demand to set some options manually
2. New Dataset: This option enables creating shared-datasets
3. Open up: With aid of this option, we can open reports which are stored in the folders or in the SQL Server Reporting Services
4. Recent: In this tab, we tin can find out the reports created previously

We will select the Blank Report option and so report architect pattern page will appear.

The Written report builder main screen is not very complicated and it allows us to create reports easily. Let's demonstrate creating a study and learn much more about the SSRS study builder. In this demonstration, we will achieve the following steps;

• Create a datasource connection
• Create a dataset
• Design study with study builder
• Deploy created written report builder report to My Binder path

Create a datasource

In the right side of the written report builder we can encounter the Report Data panel which helps to manage Data Sources and Datasets and also in this panel we tin manage report parameters and built-in fields. Now, we will create a connectedness between Azure SQL and report builder. Right click in the Information Sources folder and and so select Add together Information Source.

SQL Server Reporting Services allows us to use 2 types of connection methods;

• Use a shared connexion or study model: In this method we can utilise a shared information source. This type of data sources helps united states to avoid repeated data source because this type data source tin can be used number of times according to report user authorizations
• Employ a connexion embedded in my reports: This method creates individual connection and also, we accept to create connexion in the report. At present, let's return to our demonstration subsequently this step, the Data Source Properties screen appears then we tin can add target information source which we want to connect. In this screen, we can find out diverse data sources, nonetheless we volition select Microsoft Azure SQL Database and Apply a connection embedded in my reports. We build click Build to fix connexion properties and credentials

Fill up the Server name and other required connexion settings and then check the connection with Test Connexion.

Create a dataset

In this step we volition create a dataset, we utilise datasets in lodge to retrieve information from target data source. Correct click on Datasets folder and select Add Dataset… and and so nosotros volition select a datasource which we created in the Create a datasource connection pace and write a very basic query which returns all data of the SalesTbl

SELECT * FROM dbo . SalesTbl

The Information properties screen includes various settings and configuration tab co-ordinate to dataset. In the Query tab we tin select data source and too we can manage the query types and text. If nosotros click Query Designer, nosotros can execute queries. The Exclamation marker (!) button executes the queries and nosotros can also import prepared queries t-sql through import option.

In field tab we tin add together and delete columns of the dataset and we can also modify the columns names.

The Filter tab helps us to filter dataset with specified filter expression, this filter does not touch the dataset query. Information technology simply influences dataset rows after the query execution so query populates all data to dataset and the filter expression refines the data.

In the Parameter tabs, we tin find out and define parameters which are related to dataset and so nosotros can employ parametrized queries in the report builder dataset.

Designing a report

The Matrix helps us to aggregate data summaries such as excel pivot table. Through the matrix, we tin group and summarize data in several formats. At the end of this section, our study pattern should look like to following illustration.

At first, we will select the Matrix Magician in gild to create a summarized report.

Select the dataset in which we want to display the data in summarized format.

In the next screen, we volition arrange the row and column we want to group and besides we take to select column we want to aggregate in sum.

We will bank check the Aggrandize/collapse groups in the layout screen in guild to enable drilldown toggles so that written report readers can use report interactively.

Click Finish

We will Run the report

The output of the report volition exist as follows

We completed the report design but this report still needs some style improvements.

Select the numerical cells and then alter the format of cells to Number.

We will remove the State subtotals row.

Nosotros volition apply borders to cell so that the report layout will be shown smoother.

Nosotros will alter the font color of the total row to Cherry.

We will Run the report again.

Finally, we completed our design with SSRS written report builder and now nosotros will deploy this report to My Reports Binder. Still, we need to enable My Reports folder.

Connect to SQL Server Reporting Service through SQL Server Management Studio and then right click to report server. Navigate to advanced tab and set truthful to EnableMyReports parameter.

My Reports folder will be shown in the SSRS web portal.

Now, we will publish the report to SSRS. Click the File menu and then select Save Equally and so connect to report server.

We can observe out the report under the My Folder after the deployment procedure.

Conclusion

In this article, nosotros mentioned about how to design SSRS reports with the SSRS report architect. The report builder comes to the forefront with easy usage then it takes more than advantage in self-service BI implementations.

• Writer
• Contempo Posts

Esat Erkec is a SQL Server professional person who began his career 8+ years ago every bit a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec

mabryhery1941.blogspot.com

Source: https://www.sqlshack.com/ssrs-report-builder-introduction-and-tutorial/

Share this post