What are the limitations in SSRS on SQL Server express edition?

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

• Management Studio cannot be used to administer report server
• 
  
• Report Models will not be available
• 
  
• Report Builder is not available
• 
  
• Caching, History and Delivery of Report is not available.
• 
  
• SQL Server agent is not available
• 
  
• No scheduling is possible
• 
  
• Remote server database is not available for Report Data Source (Local SQL Server is a only option,)
• 
  
• We cannot store the report server database on a remote server (it has to be local only)
• 
  
• Reports can be rendered only in Excel, PDF, Image formats only
• 
  
• Reporting Services will not be able to use more than 1 GB of RAM
• 
  
• No Subscriptions (Standard and Data Driven) can be made
• 
  
• Can not be integrated with Share Point
• 
  
• Can not implement Role based security
• 
  
• Only named instances is supported
• 
  

Scale-out Report Servers will not be available

What is difference between IIF and Switch Function

The Iif function returns one of two values depending on whether the expression is true or not. The following expression uses the Iif function to return a Boolean value of True if the value of LineTotal exceeds 100. Otherwise it returns False: 

=IIF(Fields!LineTotal.Value > 100, True, False)

Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on the value of PctComplete. The following expression can be placed in the fill color of a text box to change the background color depending on the value in the text box. 

=IIF(Fields!PctComplete.Value >= 10, "Green", IIF(Fields!PctComplete.Value >= 1, "Blue", "Red"))

Values greater than or equal to 10 display with a green background, between 1 and 9 display with a blue background, and less than 1 display with a red background.

A different way to get the same functionality uses the Switch function. The Switch function is useful when you have three or more conditions to test. The Switch function returns the value associated with the first expression in a series that evaluates to true:
 

=Switch(Fields!PctComplete.Value >= 10, "Green", Fields!PctComplete.Value >= 1, "Blue", Fields!PctComplete.Value = 1, "Yellow", Fields!PctComplete.Value <= 0, "Red",)

Values greater than or equal to 10 display with a green background, between 1 and 9 display with a blue background, equal to 1 display with a yellow background, and 0 or less display with a red background.

SSRS difference between IIF and Switch Function

he SWITCH function allows you to have multiple conditions when evaluating an expression. It evaluates each condition within the function. It will ignore rest of condition once it finds first True condition.

A common example of this is when you want to format your background color of a cell or row to one of three or more colors depending on the value of a field. Using nested IIFs that statement might look something like this:

=IIF(Fields!something.Value=1, ”Blue”, IIF(Fields!something.Value=2, ”White”, IIF(Fields.something.Value=3, ”Red”, ”Green”)))

With the SWITCH function your syntax will look like this:

=SWITCH ( Fields!something.Value=1, “Blue”, Fields!something.Value=2, “White”, Fields!something.Value=3, “Red”, True, “Blue” )

In the example above, you can see that we are using True to simulate the ELSE condition in the standard CASE statement.

Keywords: SSRS Interview Questions , SSRS Interview Questions and answers, SQL Server Reporting Services Interview Questions

How to handle security on report server?

Reporting Services provides an authentication subsystem and role-based authorization model. Authentication and authorization models vary depending on whether the report server runs in native mode or SharePoint mode. If the report server is part of a SharePoint deployment, SharePoint permissions determine who has access to the report server.
Default authentication is based on Windows Authentication and integrated security. You can change the authentication settings to allow the report server to respond to different authentication requests, or even replace the default security features with a custom authentication extension that you provide.
Authorization is based on roles that you assign to a principle. Each role consists of a set of related tasks, which are in turn composed of related operations. For example, the Manage reports task grants access to the following report server operations: view reports, add report, update report, delete report, schedule report, and update report properties.

Keywords: SSRS Interview Questions , SSRS Interview Questions and answers, SQL Server Reporting Services Interview Questions  

How will you design the report where rows as well as the columns are not fixed?

For creating this report you can use Matrix report as for matrix report both rows and column can grow at run time.

Use a matrix to display grouped data and summary information. You can group data by multiple fields or expressions in row and column groups. Matrices provide functionality similar to crosstabs and pivot tables. At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. Values in matrix cells display aggregate values scoped to the intersection of the row and column groups to which the cell belongs. You can format the rows and columns to highlight the data you want to emphasize. You can also include drilldown toggles that initially hide detail data; the user can then click the toggles to display more or less detail as needed.

Keywords: SSRS Interview Questions , SSRS Interview Questions and answers, SQL Server Reporting Services Interview Questions