General

Minette Steynberg

The end is nigh! (For SQL Server 2008 and SQL Server 2008 R2)

April 4, 2018 by

Introduction

As with everything else, all good things must come to an end. After hanging around for a good decade SQL Server 2008 and SQL Server 2008 R2 has started the countdown to its End of Life.

D-day for SQL Server 2008 and SQL Server 2008 R2 is set to the 19th of July 2019. Which is approximately a year from now, give or take a couple of months.

If you are still running SQL Server 2008 or SQL Server 2008 R2, now is definitely the time to upgrade. In this article; I am going to tell you why you would want to upgrade to a newer version of SQL Server (2016 or 2017) or Azure SQL Database. I will also discuss some of the tools that you might use to make the upgrade as painless as possible.

Read more »
Daniel Calbimonte

How to upload multiple images to SQL Server

March 6, 2018 by

Introduction

Sometimes we need to store information including photos in our database. For example, the photo of the product, the photo of the team members. But how can we store images in SQL Server?

We could create an application in .NET or Java, but if we do not have experience in those programming languages, we could use SQL Server tools to do it.

In this new article, we will learn the following tips that will help us to work with images including how to:

  • insert one image into SQL Server
  • store multiple files into a table
  • verify that the images were inserted
Read more »
Esat Erkec

R script word-cloud in SQL Server Report Builder

November 28, 2017 by

What is R

R is a very popular data programing language. R is especially used in data analysis, statistics calculations, predictions, data mining and machine learning. R is used by data scientist, economist, genetic scientists, and statisticians. R has very wide usage in real life. Healthcare, finance, marketing, and manufacturing are some of them.

Read more »
Steve Simon

Reporting in SQL Server – Using calculated Expressions within reports

December 19, 2016 by

Introduction

Late in October, I received an unusual request from the head of sales within one of my client sites. Sales sells three articles: bread, perfume, and Jaguar motor cars. Now the reader will note that one of these items is a staple and the other two are for those folks with considerable disposable income. Management within the firm had increased the salesmen’s bonuses for those folks that managed to sell perfume and/or Jaguars along with the standard loaves of bread. The summary report may be seen below showing the final bonus rate for each sales order booked during the month.

Read more »
Steve Simon

Reporting in SQL Server – Customize the visual appearance of your reports

October 3, 2016 by

Introduction

As we are nearing the end of the North American summer, I thought that we would take a lighter look at nifty ways of reporting information. In today’s “fireside chat” we have a look at a unique way of displaying our information using charts and line graphs all in one single chart (see below). We shall take things one step further and work with the color fill of the vertical bars to reflect the values that they represent.

Read more »
Steve Simon

SQL Server and BI – How to document your Tabular model with Excel

July 12, 2016 by

Introduction

A few weeks back I had been working on an interesting proof of concept for a client within the food / grocery industry. The objectives were to be able to provide the client with information on sales patterns, seasonal trends and location profitability. The client was an accountant and was therefore comfortable utilizing spreadsheets. This said, I felt that this was a super opportunity to build our proof of concept utilizing a SQL Server Tabular Solution and by exploiting the capabilities of Excel and Power Reporting for the front end.

Read more »
Sifiso W. Ndlovu

SQL Server pivoting on non-numeric data types

February 19, 2016 by

Introduction

In the article, Multiple Options to Transposing Rows into Columns, I covered various options available in SQL Server to rotating a given row into columns. One of the options included the use of a PIVOT relational operator. The mandatory requirement of the operator is that you must supply the aggregate function with only a numeric data type. Such a mandatory requirement is usually not an issue as most aggregations and subsequent pivoting is performed against fields of numeric data type. However, sometimes the nature of business reporting requests may be such that you are required to cater for pivoting against non-numeric data types. In this article we take a look at how you can deal with such requirements by introducing a workaround to pivoting on non-numeric fields.

Read more »
Sifiso W. Ndlovu

Multiple options to transposing rows into columns

January 4, 2016 by

Introduction

One of the primary functions of a Business Intelligence team is to enable business users with an understanding of data created and stored by business systems. Understanding the data should give business users an insight into how the business is performing. A typical understanding of data within an insurance industry could relate to measuring the number of claims received vs successfully processed claims. Such data could be stored in source system as per the layout in Table 1:

Read more »
Steve Simon

SQL Server and BI – Creating a query for the revenue projection

June 5, 2015 by

A few days ago I received an interesting challenge from one of our clients. The lady was attempting to estimate her potential monthly revenue recognition for the fiscal year beginning January 1, 2015, through December 31, 2015. The lady named Linda sells goods and services (each class yielding differing sales margins).

In the first portion of this two-part discussion, we shall be looking at the revenue projections for goods.

Read more »
Steve Simon

Using the “Row_Number” function and a WHILE loop to create the rolling average report

May 27, 2015 by

Introduction

A few days ago I received an email from a gentleman in the healthcare industry. He had read an article that I had previously published a few years back on a well known SQL Server website. Based on the original article, he requested a few ideas on how to expand the sample code to do a rolling three-month revenue summary, in addition to a rolling three-month revenue average.

In today’s get together, we shall be looking at doing just this!

Read more »
Steve Simon

Reporting in SQL Server – create a matrix based sub-report called by the previously created main report

March 4, 2015 by

Introduction

As you will remember from our last “get together” we created an application that permitted us to report upon financial data based upon an unorthodox financial year. In fact, our fiscal year started in July and ended in June. We created a chart to display the data.

In today’s “get together” we are going to push our application a bit further and build in a subreport which will bring up the underlying data when the end user clicks upon the chart for any particular month. Thus should the user click on February 2015, then all of February’s data (for the selected funds) is shown in a matrix. If the user chooses March, then March’s data is shown.

Read more »
Steve Simon

Reporting in SQL Server – create a chart based on the data extracted for a given date range

February 27, 2015 by

Introduction

I recently heard from a lady from overseas who wanted to find a quick and dirty mechanism of extracting data for a given date range (based upon a fiscal year that started July 1st and ended June 30th). The idea interested me and as always, I had to try it out.

In today’s “get together”, we are going to have a look at how this may be achieved.

Read more »
Steve Simon

Creating dynamically generated CSV files containing SQL Server data

February 12, 2015 by

Introduction

A few months back, I presented a paper at SQL Saturday 327 in Johannesburg, South Africa. Late last month I received an email from one of the attendees. His issue was quite interesting and I decided to share it with you. The gentleman wanted a SSIS script that would permit him to extract data from a SQL Server database table and place it in a CSV file with a dynamically allocated name. Being a strong advocate of using the SSIS toolbox, I experimented with an alternative solution. We are going to construct THIS SOLUTION in today’s get together.

Let’s get started.

Read more »
Steve Simon

Getting started with data mining in SQL Server

January 19, 2015 by

Introduction

In past chats, we have had a look at a myriad of different Business Intelligence techniques that one can utilize to turn data into information. In today’s get together we are going to have a look at a technique dear to my heart and often overlooked. We are going to be looking at data mining with SQL Server, from soup to nuts.

Read more »
Steve Simon

How to utilize DAX queries against a Tabular Database

January 12, 2015 by

In an earlier “get together”, we had a quick look at the DAX language and how to construct useful queries. In today’s conversation we shall be concentrating on utilizing the knowledge that we obtained from the earlier article and seeing how these queries may be utilized for “multiple value” query selection criteria (against a tabular database).
Enough said, let us get started!

Read more »
Steve Simon

SQL Server security mechanism – How to control what data are users able to view within their reports

January 8, 2015 by

Introduction

A few years back, a client asked me to implement a quick and dirty “security mechanism” to control what data the myriad of users were able to view within their reports. There were numerous tables with multiple columns and all departments (within the enterprise) had their data within these tables.

SQLShack Industries has tasked us with creating a similar quick and dirty “security mechanism”. We shall attack this challenge by creating the necessary stored procedures (to extract the required data) and then utilize these stored procedures to render and consume the data within our reports.

Read more »
Steve Simon

Getting started with Data Analysis Expressions (DAX) in SQL Server

December 22, 2014 by

Introduction

In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.

With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.

In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.

Read more »
Page 1 of 212