Create Drop downs in reporting services’ reports

Recently, I was working on a project where I had to create drop downs as follows:

Just to help beginners, i thought it will be good to share how to do this.

Here are the steps which we follow to get the drop down on the report page so that we can choose between different options.

1. First of all , we will need to decide how many parameters we want to have in the report. Think it like a where clause e.g. lets say you want to select the employees from India region and sex should be female. We will select these values from the drop down box and based on that we will get the output.

All the next steps will be based on the above example.

2. Once we are clear about what kind of parameters we want, we will need to create the datasets. I am hoping that you are aware of all the basic concepts of RS e.g. datasets,parameters etc.

Lets create a table with some values:

create table employee(name varchar(20),sex char(1),location varchar(20),age numeric(10))

insert into employee values(‘Harsh’,’M’,’India’,27)

insert into employee values(‘Gaurav’,’M’,’India’,27)

insert into employee values(‘Divya’,’M’,’India’,27)

insert into employee values(‘Neha’,’F’,’India’,23)

insert into employee values(‘Pranab’,’M’,’India’,27)

insert into employee values(‘Sanket’,’M’,’China’,27)

Now, if we check on what will be the query with where clause:

==> select * from employee where location = ‘India’ and sex=’F’ and age=23

3. Now we are clear of the condition and we will move forward to create a report with parameters and multiple datasets as follows:

We will have one dataset which we will define while creating a report:

image

image

This will be the main dataset which we will customize and add the parameters to get the drop down thing working.Now, we have got the dataset against which we have to add the where clause to customize the output.

The next step would be to add more datasets which we could use for the where clause. For now, hold on to your thoughts as it will be more clear as we move further in the blog.

Lets create another dataset which will select the different locations from the table; right click on datasets folder and add datasets:

image

And same way another dataset for sex column.

image

Now , as we have created the datasets and it’s time for the real thing.

We will create the parameter which we will be used in the report by right clicking on parameters and add parameters:

image

This is the first parameter named @sex and next step will be to assign the value to the parameter and will appear as drop down on the report(dataset2 is query which will obtain the value of sex column):

image

and same way we will create a variable named location.Once we are done with that, we will change the query in the dataset1 as follows:

image

If we see, we have added the parameters in the where clause to get the output we are looking for. Now, If we preview the report – it will look like: –

image

HTH!

Unable to deploy reports on report server

I am learning reporting services and this is my first blog to start with. This is a very basic issue which any beginner can face. So, I thought it’s worth to create a blog on this.

While deploying reports, It failed with the error:

Error    1    The permissions granted to user domainusername are insufficient for performing this operation.        0    0

It was an interesting one because when I checked in the reporting services configuration manager, the service was started with the same domain and username.

Further to some research, I checked the access rights to the reporting services database and tried to give it RSExecRole rights:

image

but when I tried to click ok, it failed with the error:

TITLE: Microsoft SQL Server Management Studio

——————————

Add member failed for DatabaseRole ‘RSExecRole’.  (Microsoft.SqlServer.Smo)

For help, click: _http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617.0+((KJ_RTM_GDR).110422-1901+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.

FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

——————————

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch.

(Microsoft.SqlServer.ConnectionInfo)

——————————

Cannot use the special principal ‘dbo’. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1617&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

——————————

BUTTONS:

OK

——————————

That was not strange to me because the user domainusername was the owner of the database:

image

To get rid of the error, You could simply try EXEC sp_changedbowner ‘domainusername’

Also, it was sysadmin on the server which simply clarifies that there was no issue of access rights on the database.

Delved into it from the reporting services configuration standpoint. Checked the permission at the root level :

image

And we had all the access. Now moving further:

and didn’t find my account:

image

Added my account in that and gave the permissions. Tried to deploy the report and this time it was successful.

HTH!