Designing a Reporting Services Dataset

Practice Labs Module
Time
21 minutes
Difficulty
Intermediate

The "Designing a Reporting Services Dataset" module provides you with the instructions and devices to develop hands on skills in the following topics: Configuring an OLE DB source to use parameterized queries and map a query parameter to a variable, Creating an SSRS Report on an SSAS OLAP Cube by creating appropriate MDX query with query...

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Overview

Introduction

The Designing a Reporting Services Dataset module provides you with the instructions and devices to develop hands on skills in the following topics:

  • Configuring an OLE DB source to use parameterized queries and map a query parameter to a variable
  • Creating an SSRS Report on an SSAS OLAP Cube by creating appropriate MDX query with query parameters
  • Creating a DAX-Based dataset by including query parameters within an SSRS report to retrieve tabular data
  • Creating SSRS reports using stored procedures with parameters

Exercise 1 - Configuring an OLE DB Source to Use Parameterized Queries and Map a Query Parameter to a Variable

OLE DB source allows to access data from several sources by making use of either a table or a view or a SQL command. The SQL command used to access data can be a parameterized query. OLE DB source connects to the data source by using an OLE DB Connection Manager. An Integration Services project offers the data source object which can be used to create an OLE DB Connection Manager. The parameters in the query can be mapped to variables. At run time, these variables give the values of the parameter.

In this exercise, you will first create an SSIS package based on AdventureWorks2012 database. You will add a Data Flow Task, an OLE DB source and map a query parameter to a variable. You will then connect the OLE DB source to a Flat File destination and execute the package.

Exercise 2 - Creating an SSRS Report on an SSAS OLAP Cube

Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cell sets that contain the cube's data. This exercise provides a hands-on experience on creating an SSRS report using MDX query with query parameters.

Exercise 3 - Creating a DAX-Based Dataset to retrieve tabular data

In this exercise, you will use DAX to query data in an SSAS tabular database from within SSRS. This demonstration assumes that PLABSQL01 is installed with an additional instance of Analysis Services in tabular mode.

Exercise 4 - Creating SSRS reports using Stored Procedures with Parameters

SQL Reporting Service provides an option for using stored procedure instead of using a query in the report. In this exercise, you will create an SSRS report using a stored procedure with parameter.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.