Excel

Dinesh Asanka
Exporting SSRS Reports to Multiple Sheets in Excel with dynamic sheets.

Exporting SSRS reports to multiple worksheets in Excel

September 11, 2020 by

Introduction

SQL Server Reporting Services (SSRS) has multiple options of exporting data into a variety of formats and we will be discussing the options of exporting SSRS Reports to multiple sheets of excel. In SSRS, there are multiple formats available to export reports depending on the user’s needs. Microsoft Word, Microsoft Excel, Microsoft PowerPoint, Tiff file, MHTML (Web Archive), CSV (comma delimited) and XML file with report data are the popular formats that can be exported from SSRS as shown in the below screenshot.

Read more »
Emil Drkusic

Learn SQL: INSERT INTO TABLE

December 12, 2019 by

In the previous article, we’ve created two tables, and now we’re ready to use the SQL INSERT INTO TABLE command and populate these tables with data. In order to do so, we’ll prepare statements in Excel and then paste these statements into SQL Server and execute them. We’ll also check the contents of both tables before and after these commands using the SELECT statement. So, let’s start.

Read more »
Daniel Calbimonte

How to create Excel reports based on SSAS information

December 18, 2017 by

Introduction

In SSAS, when I offer Power BI, Reporting Services, PowerPivot or SharePoint to connect to SSAS, the business analysts look scared. On the other hand, if I talk about MS Excel, everybody seems so happy and comfortable with it.

Excel is still the most popular spreadsheet in the world even when there are a lot of free spreadsheets like OpenOffice and LibreOffice to download, in the BI world, Excel is still the most popular.

Read more »
Marko Zivkovic

How to import data from an Excel file to a SQL Server database

November 10, 2017 by

There are many ways to import data from an Excel file to a SQL Server database using:

In this article, steps for importing data from an Excel file to a SQL Server database will be explained using the SQL Server Import and Export Wizard including some of problems that can occur during the processes.

Read more »
Sifiso Ndlovu

How to resolve Excel Timeline Filter Errors caused by SQL Server OLAP Data Sources

January 31, 2017 by
Over the course of my career, I have developed and deployed to production several business intelligence solutions. These solutions have been consumed using numerous data visualisation tools. One of those data visualisation tools, is Microsoft Excel – which remains a popular data visualisations tool for many of my end users. Like many software products, newer version releases of Excel usually introduce new features. One such feature is the Timeline filter control which was first introduced in Microsoft Office 2013. The Timeline filter control enables end users to visually filter Excel PivotTables using fields defined as dates. The Timeline filter has several benefits compared to the traditional Excel dropdown filter. Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011. Read more »
Daniel Tikvicki

How to use SQL Server Analysis Services (SSAS) query results in Excel with Power Pivot

December 2, 2016 by

Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is native add-in in Excel 2016).

In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.

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 Ndlovu

New Excel slicer feature for report users in Office 2016

May 9, 2016 by

Whilst researching for the article Report filtering: Excel slicers vs SQL Server Reporting Services (SSRS) parameters, I discovered a new Excel Slicer feature in Microsoft Office 2016 that allows users to select/deselect multiple items without having to hold down the control (Ctrl) keyboard key. For some people, this may not be a sufficient reason to upgrade to Office 2016 but for developers of business intelligence (BI) solutions, this new feature further enhances the experience of consumers of BI solutions.

Read more »
Daniel Calbimonte

Using MS Excel to access to a relational SQL Database in Azure

August 14, 2015 by

Introduction

There are great Microsoft tools to access to SQL Server in Azure. However, as a Database Developers and Administrators we need to help the end user to access to the information using a familiar and intuitive interface.

Most of the end users have a good knowledge in MS Excel. That is why it is recommended to connect our SQL Azure database to Excel. That way, the end user will be able to easily create reports, charts and generate useful information.

In this chapter, we will show how to connect to SQL Azure using Microsoft Excel.

Read more »
Steve Simon

Excel in loading multiple workbooks into SQL Server

December 8, 2014 by

Introduction

A year or so ago, I was working on a project that revolved around daily data loads (from various asset management groups within an enterprise) into the main SQL Server data repository. Each group completed and published its own daily figures within their own Excel Work Books. These Excel workbooks were then placed in a common directory and then loaded into the Corporate SQL Server database. Let us have a look at how this may be achieved. In short, we are going to create one package that will process all the spreadsheets within the given directory. Read more »
Steve Simon

How to design a map-based report using Business Intelligence Semantic Model (BISM) and Excel

November 27, 2014 by

Introduction

One of a database designers’ worst nightmares is having to design a database for business analysts and data stewards whom insist upon creating their own reports, using Excel as a GUI. The reason that I mention this is that user created reports often open up “Pandora’s box”; with many of these folks creating their own ‘miss-information’ due to a lack of understanding of the underlying data. A few weeks back I had the ’fortune’ of working on such a project, which prompted an ‘ah-ha’ moment. I decided to design the backend SQL Server database using the Business Intelligence Semantic Model (BISM) and to employ the super set of tools provided by Microsoft Power BI, with Excel as a GUI. The end results were wildly accepted by the user community and once you see how easy this is to apply, you will be ‘chomping on the bit’ to employ the same techniques on your own user driven projects.

Read more »