Nominated for MSSQLTips.com Rookie of the Year Award – Please Vote!

As Most of you are aware that I have been writing at MSSQLTIPS.COM. It’s a wonderful community with highly experienced authors worldwide and it is a pleasure for me to be part of this great community.

This year MSSQLTIPS has come up again with two awards – MSSQLTips.com Author of the Year and MSSQLTips.com Rookie of the Year.

I have been nominated for the MSSQLTips.com Rookie of the Year award. I urge each one of you to follow the voting link and vote for  me (Ghanesh Prasad).

Voting Link: https://www.mssqltips.com/mssqltips-author-voting.asp

Voting Directions: Select the Author of the Year in the left column and the Rookie of the Year in the right column (Ghanesh Prasad) then press the "Submit" button at the bottom of the page to cast your vote. NOTE - One vote per day per IP address.

Interactive Sorting for a SQL Server Reporting Services Report

You created a report for your client and the report default sorting is in descending order which was applied at the dataset level. But what if the client wants to see the data in ascending order? Or what if the client wants to be able to switch back and forth as needed? In this tip we look at how to make the sorting option interactive, so after the report has been rendered the user can changed the sort order.

Check out complete article here.

Dynamically Refresh SQL Server Reporting Services Report Date Parameters

You created a report in SQL Server Reporting Services that has four parameters Year, Month and two Calendar Date Picker parameters StartDate and EndDate. The default values for StartDate and EndDate are determined by theYear and Month parameters. So when a new Month or Year is selected the value for StartDate should be the first day of the Month and the EndDate should be the last day of the Month. When you set default values for StartDateand EndDate the parameter values are correct the first time, but if you change the Month or Year the values forStartDate and EndDate don't change. In this tip we show how to solve this problem.

Check out complete article here.https://www.mssqltips.com/sqlservertip/3560/dynamically-refresh-sql-server-reporting-services-report-date-parameters/

SSIS Toolbox is not visible in SQL Server Data Tools

The SSIS Toolbox is very important in SQL Server Integration Services to allow you to add components to the SSIS package. If you close the SSIS Toolbox sometimes making it display again doesn't work. In this tip we look at what is happening and how to resolve this issue.

check out complete article here.

SQL Server Encrypt Column data using Symmetric Key

USE TEACHMESQLSERVER
GO

-- STEP 1 CREATING A DUMMY TABLE

CREATE TABLE EMP(EMPID VARCHAR(12))
GO

-- STEP 2 INSERTING DUMMY ROW

INSERT INTO EMP VALUES('111-111-111')
GO

-- STEP 3 CREATING MASTER KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='ABCD1234'
GO

-- STEP 4 CREATING CERTIFICATE

CREATE CERTIFICATE CERTIFICATE1 WITH SUBJECT ='PROTECT DATA'
GO

-- STEP 5 CREATING SYMMETRIC KEY

CREATE SYMMETRIC KEY SYMMETRICKEY WITH ALGORITHM =AES_128
ENCRYPTION BY CERTIFICATE CERTIFICATE1
GO

-- STEP 6 CREATING A NEW COLUMN WITH DATA TYPE VARBINARY

ALTER TABLE EMP ADD ENCRYPTEDEMPID VARBINARY(MAX)
GO

-- STEP 7 ENCRYPT THE COLUMN DATA USING UPDATE COMMAND

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
GO
UPDATE EMP SET ENCRYPTEDEMPID = ENCRYPTBYKEY(kEY_GUID('SYMMETRICKEY'),EMPID) FROM EMP
GO
SELECT * FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY


-- STEP 8 DECRYPT AND READ THE COLUMN DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
SELECT CAST(DECRYPTBYKEY(ENCRYPTEDEMPID) AS VARCHAR(MAX)) AS DECRYPTED FROM EMP
CLOSE SYMMETRIC KEY SYMMETRICKEY

-- STEP 9 INSERT THE ENCRYPTED  DATA

OPEN SYMMETRIC KEY SYMMETRICKEY
DECRYPTION BY CERTIFICATE CERTIFICATE1
INSERT INTO EMP(ENCRYPTEDEMPID) VALUES(ENCRYPTBYKEY(KEY_GUID('SYMMETRICKEY'),CAST('121-345-765' AS varchar)))
CLOSE SYMMETRIC KEY SYMMETRICKEY
SELECT * FROM EMP
GO

Get detailed explanation here.

SSRS Report Subscription Important Points to remember.

There are two option to create report subscription in Report Manager.

1- New Subscription
2- Data Driven Subscription


New Subscription 

-Report Subscription will not work with Window Authentication.
-Report Subscription will work with Store Database Credentials.
-SQL Server Agent must be running to create Report Subscription.
-Once Report Subscription is created, a SQL Server Agent will be created to run the report on scheduled time.
-Path to share report will be \\MachineName\Users\UserName\Documents
-Once subscription is created, you can find it in MY Subscription list in Report Manager.
-You can create Subscription Schedule for Once or Monthly, Weekly, Day, Hourly basis.
-If subscription is deleted from Report Manager, SQL Server Agent job to run the report will also be deleted.
-If SMTP connection connection is not created, then you will not get option to send report through email.
-If report has parameter and parameter default value is not set then you can't create subscription. You have to define it at the subscription creation time.


Data Driven Subscription

-Report can be shared via email and windows file sahre.
-Null Delivery Provider, will be used for report caching. In this mode, report will not be rendered but will be stored in the report server temporary database.
-Steps to create Data Driven Subscription
Step 1 - Specify how recipients are notified:, Specify a data source that contains recipient information:
Step 2 - Create connection to source which holds data driven subscription information.
Step 3 - Write SQL query to return FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 4 - Specify delivery extension settings for Report Server FileShare, assign values for FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 5 - Specify Report Parameters default value
Step 6 - Specify when subscription will be processed

OLTP VS OLAP Systems

OLTP stands for Online Transaction Processing, OLTP System deals with operational data. Operational Data is business transaction or activities which happen every day in the business i.e. In a banking system, you withdraw amount from your bank account, then withdrawal amount, available balance, account number, transaction number etc is operational data. In OLTP system, data is frequently inserted, updated and queried. OLTP system is most optimized to perform DML operation.

OLAP stands for Online Analytical Processing, OLTP System deals with historical data or archival data. OLAP systems are highly optimized for read operation. OLAP systems collects data over a period of time and store it in a very large database called Data Warehouse. This historical data is used for analysis purpose and to understand the trend of important KPI i.e. if we collect last 5 years of flight reservation data, then data can give us much meaningful information such as trends in reservation, peak reservation time, peak booking hours etc. 

Handle Excel exceeds maximum 65,536 rows in SSRS 2008R2

You have created report in SSRS2008R2 and your report contains more than 65,536 records. When you export the report into Excel you get Export Error message “Exceeding 65,536 rows limit”. How can we resolve this issue?

SQL Server Reporting Services allows you to export data into multiple formats (i.e. Excel, PDF, XML, etc.) and you can find all the supported formats here. SSRS 2008R2 supports the .xls file extension for Excel, but these Excel files support a maximum of 65,536 records per sheet. When you try to export a SSRS report which has more than 65,536 records it will fail to export the report into Excel.

In this tip I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report. To demonstrate the solution, I will use theAdventureworks2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.

Read complete article here.

Freeze Excel Column Header for SQL Server Reporting Services Report

You created a SQL Server Reporting Services report and the requirement is to output to Excel format. As you may know, SQL Server Reporting Services (SSRS) provides functionality to export a SSRS report into Excel, but for long reports the column headers disappear in Excel when you scroll through the data. In this tip we look at how to freeze the column headers in Excel when the report is created using SSRS.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report.

In this tip I will describe how to freeze the Tablix column headings in SSRS, so that when viewing the data in Excel the column names can be seen even after scrolling down. SQL Server Reporting Services doesn't provide any out of box functionality to freeze the column headings when exporting to Excel, so we will show a workaround to do this.

Click here to read complete article.

Execute a SQL Server Reporting Services report from Integration Services Package

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

This tip assumes that you have previous real world work experience building a simple SSRS Report and SSIS package. I will use AdventureworksDW2008R2 sample database and SQL Server 2012 to demonstrate the problem and solution.

I have divided this tip in two parts.

Part 1: I will create a sample SSRS report and deploy it to the Report Server.
Part 2: I will create a SSIS Package which will execute the SSRS report created in Part1.

Read complete article here.

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?

This tip assumes that you have previous experience building a simple SSRS Report.

In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.

I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Read complete article here.

Creating a Tabbed Report in SSRS

Have you ever struggled with creating tabs for a SQL Server Reporting Services report? If yes, then you will most likely know that SQL Server Reporting Services does not provide any built in feature to accomplish this task. Finding a suitable solution can be very tricky. So how can we create a tabbed report in SSRS?

In this tip I will give a demo on how to create a tabbed report in SSRS? To demonstrate the solution, I will use the AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

SQL Server Reporting Services Text Box Orientation

If a SQL Server Reporting Services report has many columns with text descriptions in a horizontal direction then those headings will need significant space in the report. There are situations where report data will not fit in a single page and the users will have to scroll horizontally to see the data. Unfortunately, this is very irritating and users may ask you to modify the report in such a way that report data can fit in a single page. How can we modify the report and what approach you should follow in this case?

Proper orientation of a Textbox saves a lot of space in a report layout; if a report has 10 columns with text descriptions in a horizontal direction then it will take more space in the report as compared to the same report having those columns in a vertical direction.

Read complete article here.

Export SQL Server Reporting Services Report Data into Multiple Excel with Worksheets

Problem

Sometimes SQL Server Reporting Services users want to export SSRS reports into Excel with multiple sheets, but when the SSRS report data region (Tablix/Matrix) doesn't have any grouping then all the data will be exported into a single sheet. How can we export a SSRS report into Excel with multiple sheets?

Solution

In this tip we will first review the problem and then demonstrate the solution. To demonstrate, I will use theAdventureworksDW2008R2 sample database and SQL Server 2012 Reporting Service.

This tip assumes that you have previous real world work experience building a simple SSRS Report.

Read complete article here.

Restore SQL Server System Database master.mdf without Backup

Problem:

One of the biggest mistakes that most of the DBAs do is backing up their user database but not the system databases. May be, they cannot correctly predict the importance of system database or the smooth functionality of SQL Server never made them confront the need to backup this database. For any excusable reason, if backup for system database is not maintained, here is a tip shared that will help to get back the master database back even if no backup is available.

Solution:

First of all, why master database is so important that it must be backed up. This database stores all system–level information of the server. For example: The login info, the servers linked together, and other server level objects are stored in this database. This database can get into an inconsistent mode due to varied reasons that include hardware or software related issues which may restrict the server to get up and render its services.

In the later sections, I will be covering solution to restoring a damaged master database via following mediums:

#: Backup of master database is available and it has to be restored following the RTO challenge. The restoration of this DB is considered risky because it comprises of information about other databases and is a crucial component for starting up the database.

To get started, it is important that SQL Server single-user mode is activated. For that, open ‘SQL Server Configuration Manager’ and click on ‘Startup Parameters’ tab. In the text box for startup parameter, enter “-m” flag and click on ‘Add’ button. Click Apply and restart SQL Server.

To get the single user mode activated, the services has to be stopped and restarted again.

Now, for restoring the database through SQL Server Management Studio, the process is simple. Select the system database that has to be restored, right click on it, choose ‘Tasks’>> ‘Restore’>> ‘Files and Filegroups…’.

You will be presented with two different pages: “General” and “Options”. Select them and make relevant modifications in settings for restoration.

#: Rebuild Master Database and Restore it from Backup.If a workable master database is not available, it has to be first created. If a master database is already available, then the scenario and solution shared above will work. There are two methods that can be adopted for rebuilding the database.

1)    Use the Setup for Re-Creating the Master Database

In the process of rebuilding the master database, it is important to know that the resultant will have three databases created:master, model and msdb. For SQL Server 2012, there is no need for setup DVD/ISO. Run the following command from the bootstrap directory (C:\Program Files\Microsoft SQL Server\<SQL Version>\Setup Bootstrap\<Release>).

Syntax Details:

The values for these parameters should be replaced accordingly. Before running the command, make sure that a healthy copy of model and msdb database is saved as safe location.

Now when the master database is in a workable state, it can be recovered from the available backup. For this, it is important that all related services should be stopped and the empty msdb and model DB is replaced with the copy that is saved at safe location.

2)    Use the Template Master Database

In SQL Server 2012, the system database recovery criteria is a little different. There is template database that gets created at the time of installation. When the setup.exe is run for rebuilding the database, the template database is replaced over the database and transaction logs.

However, in case the msdb and model database are in working state, then it is a simpler job to just copy the template files to master database in spite of going for the long procedure of rebuilding the database and then restoring the msdb and model DB from backup. The template files are located at following location:

If master database backup is available, then restoration instruction can be checked out in section 1. But if there is no backup available, then section 3 will give an idea to deal with the problem.

#: The master database is unusable and there is no backup available. In this situation, what can be done is the master database can be reconstructed. For this, first of all connect to Server using SQL Server Management Studio and the admin account that was used while installation process or while rebuilding (in the above procedure). Here, you will observe that on expansion that databases tree is blank. This is because of the reason that master database stores the location info of all DBs which is now lost.

But it has to be understood that the database is still available, they are to be relocated. If the backup of the databases is there, then the job is easy, but if it is not, then the location of the database has to be recorded and attached manually. For this, the following T-SQL code can be used:

Note: It is necessary to change the database ownership as they must be owned by the account that has re-attached them.

Nevertheless, now a days, the idea of restoring system backups have taken a great turn in the way that the process has been simplified to a great extent. MDF file recovery programs for getting back the data from system databases have made restoration without backup very easy. The plus point is, for recovery of single database, others are not supposed to be involved like in the manual procedures. Most of the DBAs depend upon manual fixing in normal scenarios, but yes, these tools have made an impressive entry to deal with major issues in less RTO.

System Objects Restoration

On rebuilding the master database or on restoring it from the template, it is possible that some of the information like linked databases, login details etc. are lost. To get some of the objects back, you can take help of SSMS to create scripts. Although you won’t get the objects with details in exact match, but there is a fair possibility of getting very close to it.

 

Display a fixed number of rows per page for an SSRS report

You have a requirement where you need to display a fixed number of rows on each page of an SQL Server Reporting Services (SSRS) report, but there is no out of the box option to limit the number of records displayed per page. How can you limit the numbers of records per page for an SSRS report?

This tip assumes that you have previous experience building a simple SSRS Report.

In this article we will demonstrate how to display a fixed number of rows on each page of an SSRS report using a Tablix data region. I will use the AdventureWorksDW2008R2 sample database and SQL Server 2012 Reporting Services to demonstrate the solution.

I have already created a data source connection to the AdventureWorksDW2008R2 database, now let's create a dataset for the demo report. The below dataset returns Productkey and EnglishProductName.

Click here to read complete article.

Include Report Parameter Selection Values in Report Output for SQL Server Reporting Services

In your SQL Sever Reporting Services report, if you don’t display the parameter values which are used to filter the report data then it will be very difficult to the end users to find the parameter values against which the report was ran. This is especially the case when a report is exported to another format i.e. Excel or PDF. So it is always a good idea to display the parameter values in the report. This tip will demonstrate how to display a single value and multi valued report parameter selection values in SQL Server Reporting Services.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) Report with parameters. I have prepared this article in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.

Set Select All as Default for Multi-Value Report Parameters in SQL Server Reporting Services

Most SQL Server Reporting Services (SSRS) reports contain multi-value report parameters and sometimes there be a requirement to set "Select All" as the default. There is not a simple way to set "Select All" as the default for an SSRS report, but in this tip I will demonstrate how to set "Select All" as the default parameter value.

Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

This tip assumes that you have previous real world work experience building a simple SQL Server Reporting Services (SSRS) report with parameters. I have prepared this tip in such a way that a SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services.

Click here to read complete article.

Allow NULL value in Multi Value Report Parameter in SQL Server Reporting Services

Your SQL Server Reporting Services (SSRS) report has a multi value parameter, but it doesn't show NULL in the parameter drop down along with the other parameter values. In SSRS a multi-value parameter cannot include a NULLvalue, so users can't filter the data for NULL values. Your requirements state a need to be able to filter the data forNULL values, so in this tip I will demonstrate how to allow NULL values in a multi value SSRS report parameter.

Click here to read complete article.

Alternate Row Background Color in SQL Server Reporting Services Tablix and Matrix

You want to make your SQL Server Reporting Services (SSRS) Tablix's and Matrix's more attractive by configuring an alternative row background color. Unfortunately, you cannot find any out of box properties in SSRS to do this. How do you configure alternative row background colors in a Tablix or Matrix?

The alternative row background color improves the readability of data in your SQL Server Reporting Services (SSRS) Reports. In this tip I will show you how to configure alternative row background colours in a Tablix and Matrix each with one simple example.

Click here to read complete article.

How to change Rendering Extensions in SQL Server Reporting Services ?

SQL Server Reporting Services provides many different rendering extensions. Our users don't want to see the list of all rendering extensions, they only want to see Excel and PDF rendering extensions. How can you enable or disable Rendering Extensions in SQL Server Reporting Services Report Manager?

Check complete article here

Creating a multi-option parameter report for SQL Server Reporting Services

I have a reporting requirement where users want to enter report parameters by either selecting values from a dropdown list or entering To and From range values.  How can I do this in a Reporting Services report?

This tip assumes that you have experience building a simple SSRS report and T-SQL skills. In my previous tips I have explained optional query parameters and cascaded report parameters in SSRS, it is recommended to read these tips as well.

In SSRS we can't enable or disable report parameters based on other parameter values. If we have a report parameter, then we have to provide a value. SSRS doesn't provide any functionality to choose query parameters from report parameters, but we have a workaround.

Check out complete article here.

SQL Server Reporting Services Interview questions

In this article I have tried to cover all important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.I will write the answers for each question in future.

1.       What is SSRS?

2.       What is the architecture of Reporting Services?

3.       How can you find which report is running slow?

4.       What is Data Driven Subscription?

5.       When to use Null Data Driven Subscription?

6.       What are the components of SQL Server Reporting Services?

7.       What is encryption key and what is its extension?

8.       What are the types of parameter in SSRS?

9.       What is the file extension of SSRS report and what is it stands for?

10.   Which language Report files made of?

11.   Can you create subscription using windows authentication?

12.   What is the report rendering?

13.   What are the different types of report rendering methods?

14.   What do you need to restore report server database on another machine?

15.   When storing the credentials of a data source in the server, are those credentials safe?

16.   What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value? 

17.   You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong? 

18.   What caching options do you have for reports on report server?

19.   What is report caching?

20.   What is report snapshot?

21.   If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that? 

22.   What is report server?

23.   What is report Manager?

24.   What are the three SSRS command-line utilities and their primary functions?

25.   What permission do you need to give to users to enable them to use Report Builder?

26.   What is data source in SSRS?

27.   What is Dataset in SSRS?

28.   What are the different types of data sources in SSRS?

29.   What are the different types of Datasets?

30.   When to use shared Data Source and Shared Dataset?

31.   Why should you not overwrite a shared data source in production?

32.   What is the main benefit of using embedded code in a report?

33.   What programming language would you use to create embedded functions in SSRS? 

34.   How do you reference an embedded function in a report expression?

35.   What are different types of roles provided by SSRS?

36.   Can a user or group belong to more than one item-level or system-level role? 

37.   What is a Report Model?

38.   In which SQL Server version report builder introduced?

39.   What is the latest version of report builder?

40.   What are the issues when exporting SSRS reports into Microsoft Excel?

41.   What new data source types were added in SSRS 2014?

42.   What are the new features are introduced in SQL Server 2012 reporting services?   

43.   How do you display the Image Properties dialog box? 

44.   What are data regions?

45.   Can you use the Report Wizard to create a report with Charts?

46.   What is the difference between Logical Page and Physical Page in SSRS?

47.   Which rendering formats are affected by the PageSize properties?

48.   Can you use a stored procedure to provide data to an SSRS report?

49.   What is the main difference between a Matrix report item and a Table report item? 

50.   When report caching is not used then which is better to use parameters to filter information in the query or to use filters in the dataset?

51.   Can you edit a report that an end user created by using Report Builder in BIDS?

52.   What mechanisms do you identify to reduce the overhead of Reporting Services data sources?

53.   How can you edit the .rdl code associated with a linked report?

54.   What are sub reports and how to create them in SSRS?

55.   How to create alternate row color?

56.   How to display fixed number of row per page in SSRS?

57.   You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?

58.   Which functions are used to pass single parameter value and multi parameter value to MDX query?

59.   How to call text-box value of report table in header or footer?

60.   How will I enable the column header should repeat on each page?

61.   How to use Calendar Date Picker for MDX Based reports?

62.   How to create optional query parameter?

63.   Explain Reporting Life Cycle?

64.   How to fine tune SSRS report?

65.   Name and Describe few console utilities for SSRS?

66.   Which version of Reporting Services doesn’t support report model?

67.   Can we join two datasets in SSRS?

68.   When do we get parameter forward dependencies error?

You can find SQL Server Interview Questions here.

How to use a multi valued comma delimited input parameter for an SSRS report

You have a requirement where users want to filter report data in SQL Server Reporting Services by entering a list of comma delimited values instead of choosing from a drop down list.  In this tip I will show how this can be completed with Reporting Services.

In this tip we will show how to pass multiple values from a user input report parameter to a query parameter in SQL Server Reporting Services. I will use the AdventureWorksDW2008R2 sample database for SQL Server and SQL Server 2012 Reporting Services to demonstrate the solution.

Click here to read article. 

Execute a SQL Server Reporting Services report from Integration Services Package

You have a requirement where a user does not want to use the SQL Server Reporting Services (SSRS) report subscription service, but wants to execute the SSRS report from a SQL Server Integration Services Package. In this case, whenever the user executes the package, a particular SSRS report will be executed and exported into Excel.  The exported Excel file will be saved in a shared folder. In this tip I will demonstrate how to solve this problem.

Click here to read full article.

Cascaded Parameters in SQL Server Reporting Services

You have a reporting requirement where a user has two or more parameters for a single report, but the values for one parameter depend on the values chosen in a previous parameter.  

Let's understand with a simple example; suppose you have two parameters: ProductCategory and Product. When you choose the ProductCategory then the Product parameter lists only those Products which belong to the selected ProductCategory. 

How can we create cascaded parameters in SQL Server Reporting Services?

Read complete article here!

SQL Server Reporting Services Reports With Optional Query Parameters

You have a requirement where users want to run a SQL Server Reporting Services (SSRS) report without passing any parameter values. In this case the SSRS report should show all the records, but the users also want an option to filter the data, if required. In SSRS terms the users want an Optional Query Parameter; which filters the dataset if a value is provided. If a value is not provided, then the query will return all records. So how do you create an Optional Query Parameter in SQL Server Reporting Services?

Click here to read full article.

Calendar Date Picker for MDX based Reports in SSRS

Generally any SSRS report contains one date parameter. User never prefers list of values for date parameter, they always want to choose the date value from the Calendar Date Picker. It is very easy to use Calendar Date Picker when we create report against any relational database, but when we create SSRS report against SQL Server Analysis Services then we can’t directly feed Calendar Date Picker value into the MDX query. 

This article will answer for these questions: 

How to feed calendar date picker value into MDX query?
How to use Calendar Date Picker parameter for MDX based reports? 
What are the advantages of Calendar date picker over list of values parameter?

Read Article here