SSRS Interview Questions II

1. What does rdl stand for?
    RDL stand for Report Definition Language. RDL is the extension for reports file.

2. What is Report Manager?
    Report Manager is a web based tool that allows to access and run reports.

3. What is Report Builder?
    Report Builder is a self-service tool for end users

4. What permission do you need to give to users to enable them to use Report Builder?
    "Report Builder" role and "system user". Report builder should also be enable in report server properties

5. What do you need to restore report server database on another machine?
    SSRS Encryption key

6. How to pass multi-valued parameter to stored procedure in dataset?
    Join function in SSRS and split function in T-SQL

7. How to create "dependent" parameter "Make, Model, Year"
    They need to be in correct order, and previous parameter should filter next parameter dataset. For instance Model dataset should be filtered using Make parameter

8. How to create "alternate row colour"?
    IIF(ROWNUMBER(NOTHING)%2=0,"White","White Smoke")

9. How to create percentile function?
    Custom code is required for this
    Code : 
       Public Function Percent(ByVal CurrVal As Long, ByVal MaxVal As Long) As Decimal
                 If CurrVal = 0  or MaxVal = 0 Then
                       Return 0
                 Else
                       Return (CurrVal / MaxVal * 100)
                 End If
        End Function

10. How to find a value in another dataset based on current dataset field (SSRS 2008 R2)?
      using lookup function
      Example : =JOIN(LookupSet(Fields!DepartmentID.Value, Fields!ID.Value, Fields!Name.Value, "Departments"),","

11. How to identify current user in SSRS Report?
      User!UserID

12. What is the shared data source in ssrs?
      A shared data source is a set of data source connection properties that can be referenced by multiple reports, models, and data-driven subscriptions that run on a Reporting Services report server. Shared data sources provide an easy way to manage data source properties that often change over time. If a user account or password changes, or if you move the database to a different server, you can update the connection information in one place.

Shared data sources are optional for reports and data-driven subscriptions, but required for report models. If you plan to use report models for ad hoc reporting, you must create and maintain a shared data source item to provide connection information to the model.

13. What is the report rendering?

     Report rendering is to call the report from server to application. Report rendering can be possible through different ways.

SQL-Server : Unlock tables locked by SQL

If your tables are locked in SQL SERVER using some SSIS or SSRS or some other tools.
For unlock we need to kill process id from sql server .

if your table is locked then SPID=-2 stored in syslockinfo table .

you can't delete directly this process id (SPID=-2)
use this query and kill whatever you got from this query result.

select req_transactionUOW
from master..syslockinfo
where req_spid = -2

KILL '010C2764-C765-7168-854X-5475A5D789S3'

Thx,
RS

SSRS : Limitations

Below are the some limitations with SSRS


1) Page number can't access in RDL Body.
2) Cannot merge table columns.
3) SSRS preview mode does not allow you to modify any formatting on the fly like Crystal Reports does.
4) You can use Limited Html tags and you cannot use JavaScript in SSRS unlike Cognos.
5) As SSRS doesn’t allow JavaScript code, we can’t rename the Label of parameters dynamically based on the selection of some other parameters.
6) SSRS does not currently support CSS.
7) Customization of chart colors according to the company’s brand color. If you click a color (country) say ‘blue’ in a first chart (say For ex. Sales across Countries) then the child chart (state) must display the data in different shades of blue.
8) SSRS Subreport not allowed in Page Header/Footer.
9) SSRS Subreport value can not access anywhere in RDL.
10) SSRS doesn’t provide Backward Compatibility.

SSRS : Spliting Values

If we want to split the value in SSRS then we can use the in built split function as follows

For example : 

if we want to split "AB,BC,CD" in to "AB" "BC" and "CD".

Split("AB,BC,CD",",").GetValue(0)
Split("AB,BC,CD",",").GetValue(1)
Split("AB,BC,CD",",").GetValue(2)

Thx,
RS

Swipe a column value in SQL-Server

Some time we need to update one column with another in the same table or we can say swipe a value. For this we can use simple update statement as follows

UPDATE temp t
SET t.testColimn1 = t.testColimn2, 
t.testColimn2 = t.testColimn1

Thx,
RS

SSRS : client machine date and time

Hi All,

In SSRS some times we need to show the client machine date and time in the Report File we can do this using below code

=System.TimeZone.CurrentTimeZone.ToLocalTime(Globals!ExecutionTime)

Thx,
TX

SQL SERVER – 2008 – Download and Install Samples Database AdventureWorks 2008 - Using Script

Hi All,

This post is for Adventure Works Sql-Server sample database. Many time we need a sample database to perform our query and operation. AdventureWorks is the Microsoft's open source database. There are different-2 method to download and install the AdventureWorks but here i'll mention the method which i feel easy.

Below is the link to download the script and data files.

Click here : AdventureWorks 2008 OLTP Script
                                        OR
                  AdventureWorks 2008R2 OLTP Script

Click on the above link and download the full files. Extract it some where in your PC. Open the folder there you will find "instawdb.sql" file open it. 

un comment "SqlSamplesDatabasePath" and "SqlSamplesSourceDataPath" variable and change the path. 

Now in menu select Query>SQLCMD Mode.

Execute the script.

Now this AdventureWorks sample database is intalled in your computer.

Thx,
RS