Posts

Building SSRS Reports Using Multiple Data Sources

SQL Server Reporting Services (SSRS) allow users to create reports using data from a variety of sources, including relational databases, multidimensional databases, web services, XML, and other document types. Using SSRS, we can present data from multiple data sources in a single report.

Methods to achieve this include:

  • Using sub reports
  • Using Openrowset queries.
  • Using Report Builder functions like Look Up, Look Up set, and Multi look up.

In this post we will look at Look Up and Multi Look Up functions.

Consider a typical scenario in which we have two different database instances:

  • MS SQL Server Instance – This instance holds the records for customer sales.
  • Oracle Server Instance   – This instance holds the records for customer information.

The SQL server would have customer table with records like:

Dbo.CUSTOMER_TRANS

customer_id

trans_date

amount

1233

2012-03-01 00:00:00.000

100

2708

2012-04-05 00:00:00.000

150

3421

2012-07-22 00:00:00.000

120

8765

2012-08-27 00:00:00.000

109

The Oracle server would have Customer tables containing customer information like:

CUSTOMER_INFO

cust_id

Name

Email

1233

John

john@xyz.com

2708

David

david@xyz.com

3421

Michael

Michael@xyz.com

8765

Jack

jack@xyz.com

In order to fill data from these two sources into a single report, we would follow these steps:

  1. Create and select data sources (that are already present on your report server):

Untitled-1 Building SSRS Reports Using Multiple Data Sources

Untitled1 Building SSRS Reports Using Multiple Data Sources

  1. Create a dataset using Sql server data source (db_Customer_SqlServer):

Untitled-4 Building SSRS Reports Using Multiple Data Sources

And similarly create a dataset using Oracle Data source:

Untitled-3 Building SSRS Reports Using Multiple Data Sources

  1. Add a table to the report and select the SQL server dataset:

Untitled-2 Building SSRS Reports Using Multiple Data Sources

In the report add any columns that you want to display:

Untitled Building SSRS Reports Using Multiple Data Sources

You’ll see that names and emails are not populated from the SQL server dataset. To fill out names and emails we have to refer Oracle server dataset.

Right click the name cell and go to values. This will open an expression builder screen.

Write an expression along the lines of this:
=Lookup (Fields!customer_id.Value, Fields!cust_id.Value, Fields!Name.Value, ds_Customer_Oracle)
Basically the syntax is:
=Lookup (source expression, destination expression, result expression, dataset)
We can perform the same actions for the email value, therefore when we run the report we will get the desired outputs.

The Look Up instead returns a single value only; in this case it will return a name corresponding to a customer ID that it pulls from the SQL dataset. Similarly, Lookupset return an array of values for a particular condition.

If you’re interested in learning more comment below or contact us at info@optimusinfo.com

Building SSRS Reports With Multiple Data Sources

SQL Server Reporting Services (SSRS) allow users to create reports using data from a variety of sources, including relational databases, multidimensional databases, web services, XML, and other document types. Using SSRS, we can present data from multiple data sources in a single report.

Methods to achieve this include:

  • Using sub reports
  • Using Openrowset queries.
  • Using Report Builder functions like Look Up, Look Up set, and Multi look up.

In this post we will look at Look Up and Multi Look Up functions.

Consider a typical scenario in which we have two different database instances:

  • MS SQL Server Instance – This instance holds the records for customer sales.
  • Oracle Server Instance   – This instance holds the records for customer information.

The SQL server would have customer table with records like:

Dbo.CUSTOMER_TRANS

customer_id

trans_date

amount

1233

2012-03-01 00:00:00.000

100

2708

2012-04-05 00:00:00.000

150

3421

2012-07-22 00:00:00.000

120

8765

2012-08-27 00:00:00.000

109

The Oracle server would have Customer tables containing customer information like:

CUSTOMER_INFO

cust_id

Name

Email

1233

John

john@xyz.com

2708

David

david@xyz.com

3421

Michael

Michael@xyz.com

8765

Jack

jack@xyz.com

In order to fill data from these two sources into a single report, we would follow these steps:

  1. Create and select data sources (that are already present on your report server):

Untitled-1 Building SSRS Reports With Multiple Data Sources

Untitled1 Building SSRS Reports With Multiple Data Sources

  1. Create a dataset using Sql server data source (db_Customer_SqlServer):

Untitled-4 Building SSRS Reports With Multiple Data Sources

And similarly create a dataset using Oracle Data source:

Untitled-3 Building SSRS Reports With Multiple Data Sources

  1. Add a table to the report and select the SQL server dataset:

Untitled-2 Building SSRS Reports With Multiple Data Sources

In the report add any columns that you want to display:

Untitled Building SSRS Reports With Multiple Data Sources

You’ll see that names and emails are not populated from the SQL server dataset. To fill out names and emails we have to refer Oracle server dataset.

Right click the name cell and go to values. This will open an expression builder screen.

Write an expression along the lines of this:
=Lookup (Fields!customer_id.Value, Fields!cust_id.Value, Fields!Name.Value, ds_Customer_Oracle)
Basically the syntax is:
=Lookup (source expression, destination expression, result expression, dataset)
We can perform the same actions for the email value, therefore when we run the report we will get the desired outputs.

The Look Up instead returns a single value only; in this case it will return a name corresponding to a customer ID that it pulls from the SQL dataset. Similarly, Lookupset return an array of values for a particular condition.

If you’re interested in learning more comment below or contact us at info@optimusinfo.com

Multilevel Cascading with Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:

1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot of what this process will look like:
Multilevel-Cascading-with-Select-All-1 Multilevel Cascading with Select All

 

Data Source

1. Right click on the Data Source folder in the left window.
2. Click on Add Data Source.
3. The following screen will appear:
Multilevel-Cascading-with-Select-All-2 Multilevel Cascading with Select All

 

Datasets

Dataset 1: Country

SELECT DISTINCT Country

FROM Northwind.dbo.Customers

ORDER BY Country ASC

 

Dataset 2: City

SELECT l11.City1+’_’+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11

,l11.City

FROM (

SELECT l1.City+’_’+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1

,l1.City

FROM (

SELECT DISTINCT City

FROM Northwind.dbo.Customers

WHERE Country IN (@Country)

)l1

)l11

ORDER BY l11.City ASC

 

Dataset 3: Company Name

SELECT l11.CompanyName1+’_’+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11

,l11.CompanyName

FROM (

SELECT l1.CompanyName+’_’+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1

,l1.CompanyName

FROM (

SELECT DISTINCT CompanyName

FROM Northwind.dbo.Customers

WHERE City IN(@City)

)l1

)l11

ORDER BY l11.CompanyName ASC

 

Dataset 4: Contact Title

SELECT l11.ContactTitle1+’_’+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11

,l11.ContactTitle

FROM (

SELECT l1.ContactTitle+’_’+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1

,l1.ContactTitle

FROM (

SELECT DISTINCT ContactTitle

FROM Northwind.dbo.Customers

WHERE CompanyName IN(@CompanyName)

)l1

)l11

 

ORDER BY l11.ContactTitle ASC

 

Dataset 5: Contact Name

SELECT l11.ContactName1+’_’+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11

,l11.ContactName

FROM (

SELECT l1.ContactName+’_’+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1

,l1.ContactName

FROM (

SELECT DISTINCT ContactName

FROM Northwind.dbo.Customers

WHERE ContactTitle IN(@ContactTitle)

)l1

)l11

ORDER BY l11.ContactName ASC

 

Parameters

Parameter 1: Country

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-3 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-4 Multilevel Cascading with Select All

 

Parameter 2: City

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-5 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-6 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-7 Multilevel Cascading with Select All

 

Parameter 3: CompanyName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-8 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-9 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-10 Multilevel Cascading with Select All

 

Parameter 4: ContactTitle

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-11 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-12 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-13 Multilevel Cascading with Select All

 

Parameter 5: ContactName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-14 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

 

Now assign parameter values to datasets

Steps:
1. Double click dataset “CompanyName”.
2. The following screen will appear:
Multilevel-Cascading-with-Select-All-16 Multilevel Cascading with Select All

3. Click Okay for both windows.
4. Now double click dataset “ContactTitle”.
5. The following screen will appear:
Multilevel-Cascading-with-Select-All-17 Multilevel Cascading with Select All

6. Click Okay for both windows.
7. Now double click dataset “ContactName”.
8. The following screen appear:
Multilevel-Cascading-with-Select-All-18 Multilevel Cascading with Select All

9. Click Okay for both windows.
10. Now run the report by pressing F5.

If you are interested in learning more, please feel free to ask in the comments below or email us at info@optimusinfo.com

Multilevel Cascading with Select All

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:

1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot of what this process will look like:
Multilevel-Cascading-with-Select-All-1 Multilevel Cascading with Select All

 

Data Source

1. Right click on the Data Source folder in the left window.
2. Click on Add Data Source.
3. The following screen will appear:
Multilevel-Cascading-with-Select-All-2 Multilevel Cascading with Select All

 

Datasets

Dataset 1: Country

SELECT DISTINCT Country

FROM Northwind.dbo.Customers

ORDER BY Country ASC

 

Dataset 2: City

SELECT l11.City1+’_’+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11

,l11.City

FROM (

SELECT l1.City+’_’+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1

,l1.City

FROM (

SELECT DISTINCT City

FROM Northwind.dbo.Customers

WHERE Country IN (@Country)

)l1

)l11

ORDER BY l11.City ASC

 

Dataset 3: Company Name

SELECT l11.CompanyName1+’_’+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11

,l11.CompanyName

FROM (

SELECT l1.CompanyName+’_’+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1

,l1.CompanyName

FROM (

SELECT DISTINCT CompanyName

FROM Northwind.dbo.Customers

WHERE City IN(@City)

)l1

)l11

ORDER BY l11.CompanyName ASC

 

Dataset 4: Contact Title

SELECT l11.ContactTitle1+’_’+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11

,l11.ContactTitle

FROM (

SELECT l1.ContactTitle+’_’+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1

,l1.ContactTitle

FROM (

SELECT DISTINCT ContactTitle

FROM Northwind.dbo.Customers

WHERE CompanyName IN(@CompanyName)

)l1

)l11

 

ORDER BY l11.ContactTitle ASC

 

Dataset 5: Contact Name

SELECT l11.ContactName1+’_’+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11

,l11.ContactName

FROM (

SELECT l1.ContactName+’_’+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1

,l1.ContactName

FROM (

SELECT DISTINCT ContactName

FROM Northwind.dbo.Customers

WHERE ContactTitle IN(@ContactTitle)

)l1

)l11

ORDER BY l11.ContactName ASC

 

Parameters

Parameter 1: Country

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-3 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-4 Multilevel Cascading with Select All

 

Parameter 2: City

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-5 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-6 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-7 Multilevel Cascading with Select All

 

Parameter 3: CompanyName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-8 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-9 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-10 Multilevel Cascading with Select All

 

Parameter 4: ContactTitle

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-11 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-12 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-13 Multilevel Cascading with Select All

 

Parameter 5: ContactName

Steps:
1. Right click on the Parameters folder in the left window.
2. Click on Add Parameter.
3. The following screen appear. Click on General in the left window:
Multilevel-Cascading-with-Select-All-14 Multilevel Cascading with Select All

4. Now click on Available Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

5. Now click on Default Values:
Multilevel-Cascading-with-Select-All-15 Multilevel Cascading with Select All

 

Now assign parameter values to datasets

Steps:
1. Double click dataset “CompanyName”.
2. The following screen will appear:
Multilevel-Cascading-with-Select-All-16 Multilevel Cascading with Select All

3. Click Okay for both windows.
4. Now double click dataset “ContactTitle”.
5. The following screen will appear:
Multilevel-Cascading-with-Select-All-17 Multilevel Cascading with Select All

6. Click Okay for both windows.
7. Now double click dataset “ContactName”.
8. The following screen appear:
Multilevel-Cascading-with-Select-All-18 Multilevel Cascading with Select All

9. Click Okay for both windows.
10. Now run the report by pressing F5.

If you are interested in learning more, please feel free to ask in the comments below or email us at info@optimusinfo.com

SSRS: An Ideal Business Intelligence Reporting Tool

ssrs-150x150 SSRS: An Ideal Business Intelligence Reporting Tool

SSRS Reports are well integrated with the Microsoft stack.

Microsoft’s SQL Server Reporting Services (SSRS) is a flexible platform, incorporating a centrally managed reporting system and on-demand web-based and desktop applications. From report writing to deployment, the SSRS business intelligence reporting platform supports the entire report life-cycle.

Based on our experiences in SSRS report development, SSRS is an ideal tool for business intelligence reporting due to the following key factors:

Five Styles of Business Intelligence

The five styles of BI are:

  1. Reporting: Operational and financial reports that are mainly used by the junior to mid level management to facilitate their day-to-day work
  2. Scorecards and Dashboards: Graphical representation of data that is mainly used by the senior level executives to monitor the organization’s performance
  3. Ad-hoc query and analysis: Interactive reporting mainly used for scenario analysis
  4. OLAP analysis: Simple form of analysis that uses standard features such as page-by, pivot, sort, filter and drill up/down to flip through a series of report views.
  5. Alerting and Report Delivery: Proactive notification/reports that will be delivered directly to the users (via email, web, or XML files) whenever a critical  alert, alarm or system event occurs

SSRS is a single technology where all five styles can be used as plug-and-play modules. It allows companies to post reports as download items to a portal, email them to users, or allow users access via a web-based report server. Users can locate and run the reports as required using subscription, search and navigation features. Through personalized subscriptions, users can select the versioned format they prefer to personalize the reports.

Data and User Scalability

Companies collect and generate an enormous volume and scope of data over the years. The infinite number of systems in an organization, including supply chain management, sales force automation, ERP, websites and also CRM, contribute to this massive data influx.

SSRS and SQL Server Analysis Services (SSAS), when combined, can access very large databases, while comfortably interacting with the users. High volume environments can be supported by the scalability of its modularity and web-based design. As more people use the system, the performance is enhanced by a dynamic multi-level caching architecture, which makes each user’s experience faster and more interactive, while imposing the minimum load on database resources. One could serve thousands of web-based clients though creating a reporting server farm with multiple report servers accessing the same core reports.

Self Serve BI – Ad hoc Reporting

With the increase in user base and the amount of data, user self-service becomes critical. Users can create their own operational or financial reports and explore organizational data using an SSRS ad hoc reporting tool. Users without deep technical understanding can build reports through Report Builder’s user-friendly business query model.

Granular Analytic Capability

SSRS reports incorporate drill down and parameterized functionality, which has become standard in today’s BI market. Company’s performance can be easily measured with the help of SSRS’s advanced functionalities. By combining data planning and trending, SSRS can be used to provide valuable insights to future performances.

Security and administration

To protect reporting resources, SSRS implements a flexible and role-based security model. This model ensures that every employee of the organization is allowed access to data which their user role entitles them to. By using SSRS’s extensible interfaces, other security models can be easily integrated.

Microsoft Office integration

SSRS integrates with Microsoft Office products seamlessly. The end-user can view and manage reports completely from within a SharePoint document library. Additionally, SSRS can provide pixel perfect reporting by exporting reports into Excel and PDF. There is no requirement of any programming or customization for this integration. It also increases the productivity of the users due to it’ s familiar desktop environment.

Branding and Presentation

SSRS enables company branding with picture perfect formatting. Adding the company logo and client information with reports documents is a simple process due to the ease of integration with Microsoft Office and Adobe PDF.

Automated Maintainability

Through SSRS, reports can be updated automatically, reflecting continual changes to fundamental business definitions,  structures and database. All users have access to a single version of truth through the centrally defined and managed business rules provided by SSRS.

Cost Effective

SSRS is already available with an Enterprise or Standard edition of Microsoft SQL Server. If a company already has these running, it does not need spend extra money for purchasing SSRS. This is one of the key benefits of the Microsoft Business Intelligence stack which provide a complete set of tools for end-to-end BI solutions.

SSRS has the lowest licensing cost among the leading business intelligence vendors. This provides a cost effective benefit for companies that without any SQL Server, but are inclined to buy Microsoft’s BI stack. Additionally, the server-based license model of SQL Server is definitely more cost-effective compared to per-user-based licenses.

Open APIs

Traditional and interactive reports can be created, managed, and delivered through a complete, server-based platform offered by Reporting Services. Additionally, with the help of its modular design and extensive application programming interfaces (APIs), enterprises, data providers and software developers can integrate reporting services with legacy systems or third-party applications. Other environments such as Visual Studio .Net development can be easily integrated with SSRS.

To learn more about SSRS report development, connect with us. We are happy to provide free consultations.

 

SSRS Report Builder 3.0 Advantages and Disadvantages

SSRS-Report-development-150x150 SSRS Report Builder 3.0 Advantages and Disadvantages

Using Report Building to create reports for SSRS.

SQL Server Reporting Services (SSRS) Report Builder 3.0 is a report creation tool developed by Microsoft. I have only recently started using Report Builder, but have been pleasantly surprised on how quickly I could pick up some of the more advanced features. Here are some of the SSRS Report Builder 3.0 advantages and disadvantages that I have found so far.

First off, I will give you a brief overview of the different features in the Report Builder workspace. After creating a new report your workspace will look similar to the image above. The first step in creating a report is to add a data source. You can add a data source by right clicking on Data Sources then opening ‘Add Data Source’ in the Report Data section. Selection 1 in the above image shows the Data Source section.

In Report Builder there are shared and embedded data sources.  I strongly recommend to always use shared data sources. Shared data sources can be used by multiple reports; whereas, embedded data sources have the database name and credentials stored in the report. If you need to rename a database or change credentials, using a shared data source you can update it for all reports at once.

After creating your data source you need to create a dataset. sYou do this by right clicking on Data Sets and then selecting ‘Add Data Set’.  Selection 2 in the figure above shows the data set selection. You then complete the wizard and select the data from the data source you want to use in your data set.

After configuring your data source and data set, you can present your data by using one of the data visualization or data regions wizards. Report Builder 3.0 uses the same familiar ribbon interface used by Microsoft Office. The Insert tab of the ribbon interface contains all the different data wizards you can use to group and visualize your data.  Selection 3 in the figure above shows the three basic data regions:

  1. Table Wizard helps you display data in a row and column format. A table only grows vertically downward.
  2. Matrix Wizard helps you display data in a column/row format just like a table, but a matrix can grow both vertically and horizontally.
  3. List Wizard helps you display data in a list.  Unlike the matrix and table wizard, a List supports a free-layout that can include multiple tables or matrices vertically ordered.

After selecting one of the wizards, you are asked to select the data set you wish to use. After which you can drag and drop the different columns of your data set into the wizard. Once you complete the wizard, a table will appear in the workspace with reference to the dataset you have selected. Even though the wizards provide an easy mechanism to present information, they don’t provide me with the necessary customization and control I require. So instead of using a wizard, I prefer to insert a table and populate the fields manually by dragging columns from my dataset into the cells of my table.

Report Builder allows you to directly modify the SQL queries of data sets. This is especially useful when you already have created the necessary query and you just need to plug it into the query designer to retrieve the required tables and columns from your data source.  You can then drag and drop the query results into your table.

Sometimes you have cases in which your report requires the user to input certain parameters before generating the report. You can create those parameters by right clicking on ‘Parameters’ and selecting ‘Add Parameters’. As shown in selection 4 of the above image. Another better method of doing this is to edit the SQL query directly and adding ‘@variable’ to the query where needed. Report Builder automatically detects this and creates a parameter entry for it, you can then use in the report.

Another useful feature of Report Builder is the right hand properties pane. Selection 5 shows the property pane. You can enable this pane by going to the View tab in the ribbons interface and checking the properties check box. The properties pane has saved me a lot of time when authoring reports. This is because it allows me to edit all properties of an object selected directly, instead of right clicking and going through the properties dialogue window.

I will end this blog post by pointing out some of the pros and cons of Report Builder that I have come across while authoring reports:

  1. One major positive feature of Report Builder 3.0 in my opinion is the ability to have shared data sources and data sets. This feature drastically saves time and allows multiple reports to share the same data. For example, create shared data sets for all parameters that will be used in multiple reports.
  2. A second major positive feature of Report Builder 3.0 in my opinion is the ability to edit and modify the SQL of any data set. This allows for superior control and more advanced data collection.
  3. One drawback of Report Builder 3.0 is how Microsoft implements the Query Editor. The query editor makes it difficult to debug your custom SQL query since it automatically rolls back to the last working query when it encounters an error in the current query. Due to the Query Editor doing this you can’t go back and try to fix the current SQL error without retyping it. To avoid this issue, use SQL Management Studio to write your initial queries.