02/01/2019
by Marlon Ribunal
4 Comments

SQL Server 2017 Reporting Services: The Basics Part 2

Note: I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

This is the Part 2 of our SQL Server 2017 Reporting Services (SSRS) Basics Series. You can find Part 1 here. We are going to create our first report – a basic Table Report. Before we do that, you should have the following tools installed on your local machine or laptop:

These are the basic tools we need to create our first report in SSRS. You actually have two (2) basic options for designing reports: Visual Studio Report Designer (see above) and the standalone Report Builder which you can download here. We are using Visual Studio in this series. You can explore other tools for authoring reports here.

Basic Components of a Report

First thing first: There are three (3) basic components of a report. These are Data Source, Dataset, and Report Definition Language (RDL) file.

  • Data Source – In SSRS context, the data source is where your data is coming from. In our case, it is the local instance of SQL Server 2017. The data source includes connection string, data provider, and the credentials for accessing the source.
  • Dataset – We define a dataset in SSRS with a query command. The result set returned by our query will be displayed on our table report.
  • Report Definition Language (RDL) File – RDL is a dialect of Extensible Markup Language (XML). The report itself is in the form of this XML-like language. Don’t worry if you do not know how to write RDL. The report designer will translate our report components into RDL behind the scene so we don’t have to do that ourselves.
Shared Components

You have two (2) options on how to utilize the Data Source and Dataset: Shared or Embedded. The difference between Shared data source and dataset and their Embedded counterparts is reusability. As the adjective indicates, Shared can be used by different reports. You define them once and use them multiple times.

A report with an embedded data source and dataset will work right off the bat when uploaded in the report server without explicitly telling the report service what data source and dataset to use. If you have a separate, shared data source and dataset, you have to explicitly tell the report service which data source and dataset are used for the specific report. In this series, we are going to use a shared data source and dataset.

Basic Table Report

Let’s create our first report – a basic table report. Fire up your Visual Studio. Create a new project called SSRSTutorial_001 or any name you prefer. Use the Report Server Project template provided by Visual Studio. Click OK.

That should open Visual Studio designer. We’re going to create the components in this order: Data Source, Dataset, and Report Definition Language (RDL) file.

Data Source

In the Solution Explorer window (Press CTRL+ALT+L if the window is not showing, or Menu > View > Solution Explorer), right-click the Shared Data Sources folder and select Add New Data Source. That will display the Shared Data Source Properties dialog box.

In the Name field, let’s input DS_SSRSTutorial_001. Select Microsoft SQL Server for the source Type (should be the default).

Click the Edit button in the Connection String. That should bring up the Connection Properties dialog box. The Microsoft SQL Server (SqlClient) should be good for the data source and we’ll leave it like that. Leave the Authentication as Windows Authentication.

For the Server Name, input “localhost“ or “./” (without the quotes) to point to our local instance of SQL Server. Select the WideWorldImporters database. Click the Test Connection button to make sure you’re connecting to the source. Click OK. There is no need to set the Credentials because we are using Integrated Security with Windows Authentication. Click OK. That will create our Shared Data Source (.rds file).

You may notice that the Connection Properties dialog created the Connection String for us.

Dataset

Creating the dataset is similar to how we created the data source. Right-Click the Shared Datasets folder in the Solution Explorer. and select Add New Dataset. Let’s name it ds_SSRSTutorial_001.

For the Data Source, select the data source we just created above, DS_SSRSTutorial_001 . It should be selected by default because it is the only data source created in our project at this point.

Select Text for the Query Type. Let’s borrow the dataset I used in my Creating Bar Graph In SQL Server 2017 Using R post. Copy and paste the following TSQL Code into the Query box, then click OK.

SELECT sg.StockGroupName
	 , SUM( il.LineProfit ) AS LineProfit
FROM Sales.InvoiceLines il
JOIN Warehouse.StockItems si
  ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
  ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
  ON sis.StockGroupID = sg.StockGroupID
GROUP BY sg.StockGroupName ;
Report Definition Language File

Now we’re ready to create our first basic table report. Right-Click the Reports folder in the Solution Explorer. Your instinct is probably select the first option in the context menu, Add New Report. But the option runs the Report Wizard. We’re going to skip the wizard and create a report from scratch. Select Add > New Item. That will display the Add New Item dialog box. Select the Report template and name the report rdl_SSRSTutorial_001. Click Add.

You should now see the Report Designer with an empty canvas in your Visual Studio.

At this point, you should now have the three basic components in your Solution: Data Source, Dataset, and Report Definition.

Design the Report

Let’s design our report. Drag the Table component from the Toolbox window into the empty report canvas. If the Toolbox window is not showing, press CTRL+ALT+X. Or you can open it from the Menu > View > Toolbox.

Dragging the Table component into the canvas will display the Dataset Properties dialog box. Let’s name our report dataset as rpt_table_ds_SSRSTutorial_001. Tick Use a Shared Dataset radio button; and select the dataset we created above, ds_SSRSTutorial_001. Click OK.

Click on the table to show the bar around the table designer. Right-Click the bar on top of the header on the third column, then select Delete Columns. We don’t need a third column for now.

Place your cursor in the data cell in the first column. That will display the little Table icon in the top corner of the cell. Click the icon and select StockGroupName.

Do the same for the other cell and select LineProfit.

The report designer should now display something like this:

Click the Preview tab to display the preview of our report:

And there you go. You just created your first basic table report. In the next post, we will deploy this report to our report server. Stand by.

01/25/2019
by Marlon Ribunal
3 Comments

SQL Server 2017 Reporting Services: The Basics Part 1

I’ve been meaning to start a series on the basics of SQL Server Reporting Services (SSRS). The last time I did this was on a book on SQL Server 2012 Reporting Services published by Packt. SSRS has since evolved into a better reporting platform with the addition of KPI’s, mobile reports, etc.

In this SSRS series, I’d like to go back to the basics. If you’ve never developed and deployed SSRS Reports to a server before, this is for you. Just like my book, this series goes through the step-by-step of the basics of SSRS. My goal is for anybody with Zero experience to be able to create and deploy reports in no time.

I am aware that there are other resources out there that are probably more extensive than what I can offer here. But, hey, the more resources, the better it would be for the learners.

In this post, let’s do the most basic step – installation.

Installing SQL Server 2017 Reporting Services

First thing first: My assumption is that you are installing SSRS in your local machine. And, also, that you already have an instance of SQL Server 2017 running on it. If you don’t have it, please install it and come back here. I’ll be waiting for you.

Download SSRS 2017 from here.

One of the good things that Microsoft started doing was simplify their software installation. SSRS installation is a breeze with the lightweight installer. If you haven’t installed any enterprise software before, you have nothing to worry about. The wizard should be as good as it gets.

When you fire up the executable, you are presented with two edition options: Free or Not Free. Under free edition, select Developer. In the Microsoft parlance, a Developer edition is equivalent to the Enterprise edition except that you cannot use it for production.

SQL Server 2017 Reporting Services Developer Edition: As Good As The Real Thing!

Follow the steps and you should be fine just by clicking Next until you reach the final step.

You may or may not restart your machine at this point. But just to make sure, reboot. After rebooting, fire up Report Server Configuration Manager. You should find it in your Start Menu.

I’d like to start setting up the database first before anything else. On the panel on the left, select Database.

Click Change Database. The next step presents two options – create a new or select an existing report server database. My assumption is that this is the first time you’re installing SSRS; so, select a new database.

The Server, Database, and Credentials defaults should be fine. Click Apply at the bottom when done. You should now see two new databases in your Object Explorer – ReportServer and ReportServerTempDB (assuming that you named your database as ReportServer)

Select Web Service URL from the panel. Review the default. You may or may not want to change the default. I’d keep it as is. Click Apply at the bottom. Open your internet browser and test the service. The URL is provided under Report Server Web Service URLs. You should get something like the screenshot below.

Yep! Your SSRS Web Service Is Up And Running!

Now, select Web Portal URL. This is where you upload your reports, data sources, datasets, etc. Review the defaults. Then click Apply at the bottom. Take note of the portal URL. Then test.

And that should be it. Your Report Server can be installed that quick. Of course your mileage varies depending on your server setup.

Up Next: Your First Report in Part 2! Tune in for more!

01/18/2019
by Marlon Ribunal
Comments Off on Miscellaneous Notes On System Architecture and Design

Miscellaneous Notes On System Architecture and Design

When I first joined the programming team at my previous job in an aerospace company, the first observation I made was that their Data Integration System was antiquated – architecture-wise. The system architecture was adopted from legacy mainframes. Most of the business processes have been migrated to modern servers at the time.

But as I dug deeper and deeper into the system, I slowly realized that the architecture stood the test of time and to the changing demands of the modern business world. Old ways of doing things are not necessarily a bad thing when they preserve the fundamental principles of a sound and stable architecture.

Here are some of the things I’ve learned from that experience:

Nothing Beats A Sound Architecture

Everything is anchored in the architecture. You cannot optimize a system that is based on a bad design. You might be skilled in performance tuning but you can only do so much when the problem is the architecture itself. What’s a good design? That’s open to debate. Simply put, a good design is one that allows for stability, flexibility, and scalability.

Leave the Data Validation in ETL

Cleansing of data must be done at the time of the ETL process. The database engine works well with Storing and Retrieving of data. Complicated validation and cleansing routines don’t do the database engine any good. SSIS, just like most ETL Tools, is an in-memory pipeline. Row-By-Row transformation and validations are efficiently handled in memory.

Platform is Just a Tool

One time I asked our Senior System Architect what Server to use – SQL 2008 or 2012 – he said: “I don’t care as long as it runs.” But I gathered what he really meant – that the architecture, when designed correctly, works regardless of the platform, and that a sound architecture does not have to depend on the target platform.

Cursor Doesn’t Scale

At this point, we should just agree that Cursor is a bad thing when there are other viable options that can do the job. Row-by-Row processing doesn’t scale. Period.

Beautiful SQL Doesn’t Translate To Performance

You need a gazillion of JOINs to return a dataset. Good luck with that one. You can write beautiful codes worthy of the Nobel Prize in Literature but the barometer of any codes is their performance. Readability is also a good attribute of good code. Your choice of query optimization techniques is limited by, guess what, the architecture. When you’re spending 70% – 80% of your maintenance effort on the query tuning, then maybe it’s time to re-think the design.

Errors Should Be Actionable

Error Alerts should prompt for action. The process should not just end in error logs or unactionable notifications. Push the errors to the proper stakeholders. The stakeholders should have the ability to correct the data on the fly – probably in a UI context. The corrected data should then be pushed back to the pipeline on the same batch as “late requirements” or exceptions. Don’t wait until the next scheduled batch to reprocess them. A late requirement process should be put in place as part of the system to handle such a case.

Good Design Enables

Good design is an enabler. User enablement is not a feature of the UI. It is an integral part of the system architecture. Good UI functions go as far as the flexibility of the design go – and not further. User Experience is therefore rooted in the architecture.

Institutionalize Peer Review

Let other people scrutinize your work. This is a good practice. There’s a reason peer review happens way before the deployment. Take advantage of the resources and bright minds around you. Let your teammates check your work. Peer Review may reveal potential problems that you might have overlooked during your unit testing or test cases.

You Cannot Over-Document

One cannot just over-document. Documenting is a good practice to develop. Document when necessary. And it’s almost always necessary to document in all circumstances. Sure you don’t have time to put together some quick documentation, but that’s not a good excuse to not do it. Do you want to secure your legacy in the annals of greatest employees? Yes, you got it. Document.

Building a robust system architecture involves a wide array of principles and checkboxes to tick as the project progresses from requirements to delivery. You’ll have a better chance of succeeding when you follow consistent and sensible fundamentals. Old, solid principles don’t die; they don’t fade either.