10/26/2015

Mostly used SSRS expressions in day-to-day report design - vol.2

Funny Videos: FB Fun4you


Below are listed most useful SSRS epressions which are used at the time of designing complex reports and dashboards in SSRS:

Ø  To add comments with Field Value in SSRS expressions

="Report Executed By: " + User!UserID

Ø   To get total number of rows in the tablix or dataset’s result set

=CountRows("DataSet1")

Ø  Display  Report Execution Time in a textbox:

="Report Execution Time: " & Globals!ExecutionTim
Ø  To Join all selected values of multi valued parameter in a single string:

=InStr(Join(Parameters!CityName.Value, ", ")

Ø  To display Page number of Total pages in the footer or header:

="Page No. " & Globals!PageNumber & " of Total pages: " & Globals!TotalPages

Ø  To display or print report name with user name and execution time of the report in a textbox as: Report (reportname) generated by DOMAIN/User on 10/23/2015 3:25:08 AM:

="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals!ExecutionTime

Ø  If we want to hide or show columns based on the selection of columns name in parameter on the SSRS report , suppose we have 10 columns on the report which are having name as:

Column1, Column2, Column3, …… , Column10

And these columns name are listed in a mulit value parameter so if user selects Column1, column5 and Column8 from the parameter list then only these three columns should be shown on the report so we can use below expression in the column visibility property, under the Show/Hide based on expressions:

=IIF(InStr(Join(Parameters!ColumnName.Value,","),"ColumnName")=0,True,False)

Decision Functions in SSRS expressions:

Ø  IIF() – IIf function is used to check conditions whether the expression is true or false, to perform next task.

=IIF(Fields!SalesAmount.Value > 100, "LimeGreen","Red")

Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on multiple condition based on the the value of SalesAmount field as:

=IIF(Fields!SalesAmount.Value >= 10, "Green"

,IIF(Fields!SalesAmount.Value >=  1, "Blue", "Red"))

Ø  Switch() ­- 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",)

Ø  A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of TotalSalesOrder controls the color.

=Choose(Fields!TotalSalesOrder.Value,"Red","LimeGreen","Yellow")

Ø  Page Breaks: In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.

·         The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.

·         =Ceiling(RowNumber(Nothing)/25)

Ø  To allow the user to set a value for the number of rows per page, create a parameter named RowsPerPage and base the group expression on the parameter, as shown in the following expression:

=Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)

Ø  Conversion Functions: Conversion functions can be used to convert the default data type for a field to the data type needed for calculations or to combine text. 
=CDec(500)
=CStr(Parameters!MySelection.Count)

Ø  To show field’s value in UpperCase:

=UCASE(Fields!ItemName.Value)

Ø  To show field’s value in proper case, Ist letter in each word must be uppercase and rest of characters should be in lowercase:

=StrConv(Fields!ItemName.Value, VbStrConv.ProperCase)

Ø  Replace Null vlaue with a string or number:

=iif(Fields!SalesAmount.Value = nothing, 0.00,Fields!SalesAmount.Value) =iif(Fields!ItemName.Value = nothing, "Unknown" ,Fields!ItemName.Value)

More on SSRS expressions:



For more on SSRS expressions go through these other web links:


If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

FB Fun4You: New Funny Prank Compilation
 
 

Mostly used SSRS expressions in day-to-day report design - vol.2

Funny Videos: FB Fun4you


Below are listed most useful SSRS epressions which are used at the time of designing complex reports and dashboards in SSRS:

Ø  To add comments with Field Value in SSRS expressions

="Report Executed By: " + User!UserID

Ø   To get total number of rows in the tablix or dataset’s result set

=CountRows("DataSet1")

Ø  Display  Report Execution Time in a textbox:

="Report Execution Time: " & Globals!ExecutionTim
Ø  To Join all selected values of multi valued parameter in a single string:

=InStr(Join(Parameters!CityName.Value, ", ")

Ø  To display Page number of Total pages in the footer or header:

="Page No. " & Globals!PageNumber & " of Total pages: " & Globals!TotalPages

Ø  To display or print report name with user name and execution time of the report in a textbox as: Report (reportname) generated by DOMAIN/User on 10/23/2015 3:25:08 AM:

="Report (" & Globals!ReportName & ") generated by " & User!UserID & " on " & Globals!ExecutionTime

Ø  If we want to hide or show columns based on the selection of columns name in parameter on the SSRS report , suppose we have 10 columns on the report which are having name as:

Column1, Column2, Column3, …… , Column10

And these columns name are listed in a mulit value parameter so if user selects Column1, column5 and Column8 from the parameter list then only these three columns should be shown on the report so we can use below expression in the column visibility property, under the Show/Hide based on expressions:

=IIF(InStr(Join(Parameters!ColumnName.Value,","),"ColumnName")=0,True,False)

Decision Functions in SSRS expressions:

Ø  IIF() – IIf function is used to check conditions whether the expression is true or false, to perform next task.

=IIF(Fields!SalesAmount.Value > 100, "LimeGreen","Red")

Use multiple IIF functions (also known as "nested IIFs") to return one of three values depending on multiple condition based on the the value of SalesAmount field as:

=IIF(Fields!SalesAmount.Value >= 10, "Green"

,IIF(Fields!SalesAmount.Value >=  1, "Blue", "Red"))

Ø  Switch() ­- 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",)

Ø  A third way to get the same functionality uses the Choose function. The Choose function uses the first parameter as an index to one of the remaining function parameters. The first parameter must be an integer. If the background color of a text box in a table is set to this expression, the value of TotalSalesOrder controls the color.

=Choose(Fields!TotalSalesOrder.Value,"Red","LimeGreen","Yellow")

Ø  Page Breaks: In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.

·         The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.

·         =Ceiling(RowNumber(Nothing)/25)

Ø  To allow the user to set a value for the number of rows per page, create a parameter named RowsPerPage and base the group expression on the parameter, as shown in the following expression:

=Ceiling(RowNumber(Nothing)/Parameters!RowsPerPage.Value)

Ø  Conversion Functions: Conversion functions can be used to convert the default data type for a field to the data type needed for calculations or to combine text. 
=CDec(500)
=CStr(Parameters!MySelection.Count)

Ø  To show field’s value in UpperCase:

=UCASE(Fields!ItemName.Value)

Ø  To show field’s value in proper case, Ist letter in each word must be uppercase and rest of characters should be in lowercase:

=StrConv(Fields!ItemName.Value, VbStrConv.ProperCase)

Ø  Replace Null vlaue with a string or number:

=iif(Fields!SalesAmount.Value = nothing, 0.00,Fields!SalesAmount.Value) =iif(Fields!ItemName.Value = nothing, "Unknown" ,Fields!ItemName.Value)

More on SSRS expressions:



For more on SSRS expressions go through these other web links:


If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

 

10/23/2015

Date Time Functions of SSRS expressions

Below are Data time functions which are used in SSRS expressions:

Ø  To get current date and time according to system:

Today() - Returns or sets a Date value containing the current date according to your system:

=Today()

="Tomorrow is " & DateAdd("d",1,Today())

="Tomorrow is " & DateAdd(DateInterval.Day,1,Today())

Now() - Returns or sets a Date value containing the current date according to your system.

=Now()

="This time tomorrow is " & DateAdd("d",1,Now())

="This time tomorrow is " & DateAdd(DateInterval.Day,1,Now())

Ø  MonthName - Returns a String value containing the name of the specified month.

=MonthName(10,True)

=MonthName(Month(Fields!BirthDate.Value),False)

="The month of your birthday is "&MonthName(Month(Fields!BirthDate.Value))

Ø  WeekDayName - Returns a String value containing the name of the specified weekday.

=WeekdayName(2,True,0)

=WeekDayName(DatePart("w",Fields!BirthDate.Value),True,0)

=WeekDayName(DatePart(DateInterval.Weekday,Fields!BirthDate.Value),True,FirstDayOfWeek.System)

Ø  Year  - Returns an Integer value from 1 through 9999 representing the year.

=Year(Fields!BirthDate.Value)

To format Date values through expressions:

=Format(Today(),"dd-MM-yyyy") --- 23-10-2015

=Format(today(),"dd/MM/yyyy") --- 23/10/2015

=Format(today(),"MMM-dd-yyyy") --- Oct-23-2015

=Format(today(),"MMM-dd-yy") --- Oct-23-15

=FORMAT(Today(),"M/d/yy") ---10/23/15

=FORMAT(Today(),"MM-dd-yyyy") ---10-23-2015

=FORMAT(Today(),"MMM-dd-yyyy") Oct-23-2015 

=FORMAT(Today(),"MMMM dd, yyyy") ---October 23, 2015 =FORMAT(DateField,"MMM dd, yyyy hh:mm:ss") ---Oct 23, 2015 01:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss") ---Oct 23, 2015 13:43:33 =FORMAT(DateField,"MMM dd, yyyy HH:mm:ss.fff") ---Oct 23, 2015 13:43:33.587

=FORMAT(DateField,"MMM dd, yyyy hh:mm:ss tt") ---Oct 23, 2015 01:43:33 PM

Ø  To add/substract date or time(day, month, year, sec etc.) with given date field we can use DateADD function in SSRS expression, which returns a Date value containing a date and time value to which a specified time interval has been added:

=DateAdd(DateInterval.Month, 6, Today())

=DateAdd("d",3,Fields!BirthDate.Value)

=DateAdd(DateInterval.Day,3,Fields!BirthDate.Value)

Ø  DateDiff - function returns a Long value specifying the number of time intervals between two Date values.

=DateDiff("yyyy",Fields!BirthDate.Value,"1/1/2010")

=DateDiff(DateInterval.Year,Fields!BirthDate.Value,"1/1/2010")

Ø  DatePart - Returns an Integer value containing the specified component of a given Date value.

=DatePart("q",Fields!BirthDate.Value,0,0)

=DatePart(DateInterval.Quarter,Fields!BirthDate.Value,FirstDayOfWeek.System,FirstWeekOfYear.System)

Ø  To get first day of current Week:

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Ø  To get first day of current Month:

=DateAdd("d",-(Day(today)-1), Today)

=DateSerial( year(today()), month(today()), 1)

More SSRS expressions:


If you have any thoughts or suggestion, feel free to post in the below comment section.

Thanks!

<iframe width="459" height="344" src="https://www.youtube.com/embed/zZ9DMnLyeT0" frameborder="0" allowFullScreen=""></iframe>

10/19/2015

Report Services 2008 R2 - Data Bar

SQL Server Reporting Services - Displaying some message within a Tablix if there is no data.

Creating 5 Star Rating using Indicators in SSRS 2008 R2

SSRS Action to Open a URL in a New Window

Mostly used SSRS expressions in day to day reports design

Mostly used SSRS expressions in reports design:

Ø  For showing underline based on the expression in SSRS Textbox:

-          Go to Textbox properties and under the Font option, write below expression in the Effects option for showing under line:

=IIF((Fields!MyOrder.Value = 2 Or Fields!OrderItems.Value = 5) and Fields!TotalSales.Value >0, "Underline","NONE")

Ø  If we want to show link for Drill through based on the values only then we can write an expression in the “Specify a report” field of “Go to report option” action of the textbox:

=iif(Fields!MyOrder.Value = 2 Or Fields!MyOrder.Value = 5 , "ReportName", Nothing)

Ø  To show Report’s execution time:

="Report run at: " & Globals!ExecutionTime

Ø  To change the format of a date time field in SSRS expression:

=Format(Fields!OrderMonth.Value,"MM/dd/yyyy")

Ø  To change the data type into integer , decimal, double and string etc., below are expressions:

=CStr(Fields!MyOrder.Value)

=CInt(Fields!MyOrder.Value)

=CDec(Fields!MyOrder.Value)

Ø  Nested IIF condition in SSRS:

=IIF(Fields!MyOrder.Value = 2,1,

     IIF( Fields!MyOrder.Value = 3 and Fields!graphid.Value="N",0,

                IIF( Fields!MyOrder.Value = 4 and Fields!graphid.Value="Y",1,-1))

)

Ø  Switch condition in SSRS:

=SWITCH(Fields!Tolerance.Value = "Low", "Red",

Fields!Tolerance.Value = "Moderate", "Orange",

Fields!Tolerance.Value = "Medium", "Yellow",

Fields!Tolerance.Value = "High", "Blue",

Fields!Tolerance.Value = "Very High", "Green")

Ø  Expression to set Alternate background color in SSRS:

=iif(RowNumber(Nothing) Mod 2 = 0, "LightGrey","LightBlue")

Ø  Expression to set background color in specific condition as:

-          If Sales value is less then 40 then red else LightGrey

=IIF(Fields!TotalSales.Value>=40,”LightGrey”,”Red”)

-          If Address contains “Delhi” then red else LightGrey

=IIF(InStr(Fields!Address.Value,”Delhi”)=0,”LightGrey”,”Red”)

NOTE: Here InStr(Fields!Address.Value,”Delhi”)=0 is working as like ‘%Delhi%’operator of SQL

Ø  To set the font to bold, set the Font-FontWeight property of the table cell to be:

=IIF(Fields!Mark.Value < 80, "Bold", "Normal")

-          To set multi font color and font size go on this link:


Ø  To create a dynamic database connection based on the report’s parameter value, we need to write below expression in the Data Source editor:
="Data Source=PC-Tran305;Initial Catalog=Tran"&Parameters!DatabaseName.Value

Ø  To show parameter values on report in a single text box, we need to write an expression as:

-          & is used to concatenate

-           Join operator is used to combine multiple values for a multivalve parameter so values will be coming as: A, B, C etc.

-          vbcrlf  is used to Print value in New line in SSRS textbox:

-          If we have labels different from the value then we can show Label only by using “.Label” instead of “.Value” in the expression.

="Site Name: " & Parameters!SiteName.Label

+ vbcrlf & "Main Category(s): " & Join(Parameters!MainCategory.Label, ", ")

+ vbcrlf & "Sub Category(s): " & Join(Parameters!SubCategory.Label, ", ")

+ vbcrlf & "Date From: " & Parameters!DateFrom.Label

+ vbcrlf & "Date To: " & Parameters!DateTo.Label

+ vbcrlf & "Refreshed On: " & DateAdd("h",-1,Globals!ExecutionTime) & " CST"
-          toString() function is used to convert value into string.

& vbcrlf & "Monthly/Weekly: " & Parameters!mwparam.Label.toString()

Ø  Opening a link in SSRS within a new window: In SSRS, we want to provide a hyperlink on a textbox value to open a webpage by providing input value i.e. ID along with link, we can use below expression as:

-          Go to Action option of Textbox properties and select “Go to URL” radio button and in the fuction(fx) window, set hyperlink expression:

="javascript:void(window.open('http://www.google.com','_blank'))"

-          If URL value is coming from dataset field:

="javascript:void(window.open('"+ Fields!URLPath.Value + "','_blank'))"

More SSRS expressions:



If you have any thoughts or suggestion, feel free to post in the below comment section.
Thanks!

10/15/2015

T - SQL Real Time Scenario 4


T - SQL Real Time Scenario 4:

In this post, I am going to share some analytical SQL Queries which are generally used for analysis of data and most of them are asked in the SQL interviews:

We have two tables: Products and Transactions

Products table contains three columns:

ProductID
ProductSubCategory
ProductCategory
P1023
Storage & Organization
Office Supplies
P1024
Appliances
Office Supplies
P1025
Binders and Binder Accessories
Office Supplies
P1026
Telephones and Communication
Technology
P1027
Office Furnishings
Furniture

Transactions Table contains these columns:

 
You can download this sample data from this link: Sample Data for Data Analysis

For Create table and insert scripts also: SQL Create table and Insert scripts

So download these files for sample data and run script on you SQL Server to generate both tables with data.

You can also upload excel data in SQL through Import/Export data mechanism of SQL Server.

So basis on these two tables, we need to write SQL queries to generate result set for below scenarios:

Ø  Design a query to get all customer’s transactions with product details

Ø  Show 5 Highest selling Products with total counts for each year and Month (Build two queries for showing count and total sales amount)
Ø  Show 5 Lowest Selling Products with total counts for each year and Month (Build two queries for showing count and total sales amount)
Ø  Provide last transactions details done by each customer.
Ø  Show top 5 products which are having highest profit margin (after deduct shipping, discount and other charges)
Ø  Show Region wise top 5 products which are having high sales with highest profit margin with counts
Ø  Show 5 Region with products list and count of transactions, which are having lowest products list for sale.