MSBI (SSIS/SSRS/SSAS) Online Training

Monday, January 17, 2011

SSRS Complex Expressions

Complex expressions can contain multiple built-in references, operators, and function calls, and appear on the design surface as <>. To see or change the expression text, you must open the Expression dialog box or type directly in the Properties pane. The following table lists typical ways you can use a complex expression to display or organize data or change report appearance, including the property to set, the dialog box you typically use to set it, and the value for the property. You can type an expression directly into a dialog box, on the design surface, or in the Properties pane.

Functionality Property, Context, and Dialog Box Property Value

1. Calculate aggregate values for a dataset. Value property for a placeholder inside of a text box. Use Placeholder Properties Dialog Box, General. =First(Fields!Sales.Value,"DataSet1")
2. Concatenate text and expressions in the same text box. Value for a placeholder inside of a text box that is placed in a page header or page footer. Use Placeholder Properties Dialog Box, General. ="This report began processing at " & Globals!ExecutionTime
3. Calculate an aggregate value for a dataset in a different scope. Value for a placeholder inside of a text box that is placed in a tablix group. Use Placeholder Properties Dialog Box, General. =Max(Fields!Total.Value,"DataSet2)
4. Format data in a text box depending on value. Color for a placeholder inside of a text box in the details row for a tablix. Use Text Box Properties Dialog Box, Font. =IIF(Fields!TotalDue.Value < 10000,"Red","Black")
5. Calculate a value once to refer to throughout the report. Value for a report variable. Use Report Properties Dialog Box, Variables. =Variables!MyCalculation.Value
6. Include specific values for more than one field from a dataset. Filter equation for a group in a tablix. Use Tablix Properties Dialog Box, Filters. For data type, select Boolean.
7. =IIF(InStr(Fields!Subcat.Value,"Shorts")=0 AND (Fields!Size.Value="M" OR Fields!Size.Value="S"),TRUE, FALSE)
8. =
9. TRUE
10. Hide a text box on the design surface, that can be toggled by the user using a Boolean parameter named Show. Hiddenproperty on a text box. Use Text Box Properties Dialog Box, Visibility. =Not Parameters! Show .Value
11. Specify dynamic page header or footer content. Value for a placeholder inside of a text box that is placed in the page header or footer. ="Page " & Globals!PageNumber & " of " & Globals!TotalPages
12. Specify a data source dynamically by using a parameter. Connection string on the Data source. Use Data Source Properties Dialog Box, General. ="Data Source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks2008R2"
13. Identify all the values for a multivalue parameter chosen by the user. Value for a placeholder inside of a text box. Use Tablix Properties Dialog Box, Filters. =Join(Parameters!MyMultivalueParameter.Value,", ")
14. Specify page breaks for every 20 rows in a tablix with no other groups. Group expression for a group in a tablix. Use Group Properties Dialog Box, Page Breaks. Select the option Between each instance of a group. =Ceiling(RowNumber(Nothing)/20)
15. Specify conditional visibility based on a parameter. Hidden property for a tablix. Use Tablix Properties Dialog Box, Visibility. =Not Parameters!< boolean parameter >.Value
16. Specify a date formatted for a specific culture. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. =Fields!OrderDate.Value.ToString(System.Globalization.CultureInfo.CreateSpecificCulture("de-DE"))
17. Concatenate a string and a number formatted as a percentage to two decimal places. Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General. ="Growth Percent: " & Format(Fields!Growth.Value,"p2")

1 comment:

MUKESH BHAGAT said...

Hi,

Good evening. I have some question in SSRS.


Question :--


I am using SSRS.

I have below requirement from the client side.

1.) In report viewer , We want filter the result set of report viewer from user end like excel sheet filter.


Is it Possible ?

2) In Report view , I am showing only five column from dataset into table but While export reports to excel then I want to export all coumns of dataset.

Dataset has 25 columns.

3.)Generate a drop down list from report view result set column and after that apply filter on that.. using SSRS



I want to make a drop down list from the report view table result set .

I want to column name as value of drop down list .

After that I want to filter result set on selected value filed + operator

like,=,>, + text box value.

expression like --

ddl value + operator = 100;

salary(ddl value) >= 2000(text box value) .


Is it Possible ?

4.) show report header above the report parameters in report view using SSRS?




Please suggest.