Dynamic cell formatting in SSRS

This post is about, how to change cell formatting dynamically in reports. Even though there is no any particular property, this can be done using “Conditional Formatting”.

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 length of Description not exceeds 50. Otherwise it returns False. If function rerun true then font size will be 10pt, else 8pt.

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.

In the above example I have shown only formatting the font size, the same formatting expression can be applied for color changing, font style and many more.

Cheers!

ORDER BY - T-SQL / SQL in SQL Server

This post is about ORDER BY function and its different functionality.

1. TOP n or Top (n)

2. TOP (n) PERCENT

3. WITH TIES

4.OFFSET-FETCH

TOP n or Top (n): This will return top n records.

TOP (n) PERCENT: This will return n% of the records, for example the below example shows that total record 830, so 1 % of 830 ~ 9 records.

This example shows that how to use TOP function dynamically: TOP (@n)

WITH TIES: It will retrieve the TOP (n) records + get the same order by field records as well:

In this 2nd query, even we try to retrieve top 3 records; third records order date is same as 4th records so it returns that as well. But, in third query Order By (order date and order id), 3rd and 4th records are not same order id, so only 3 records returns.

Filtering Data with OFFSET-FETCH:

Skipping capability, in this below query 2 record skip and take next 4 records

Cheers!

Correlated Subqueries

Correlated subqueries are subqueries where the inner query has refer a reference to a column from in the outer query.

As an example, suppose that you need to return products with the minimum unit price per category.

As another example of a correlated subquery, the following query returns customers who placed orders on particular day using EXISTS.

We can use NOT EXISTS also:

Cheers!

ORDER BY (OFFSET-FETCH and WITH TIES) - SQL 2012

This post is about ORDER BY and the relevant functions: In here I don’t write the details in the text because the screen shots will make clear all.

TOP 3 or Top (3)

TOP (1) PERCENT

TOP (@n)

WITH TIES: It will retrieve the TOP (n) records + get the same order by field records as well:

In this 2nd query, even we try to retrieve top 3 records; third records order date is same as 4th records so it takes that as well.

But, in third query Order By (order date and order id), 3rd and 4th records are not same order id, so only 3 records retrieved.

Filtering Data with OFFSET-FETCH:

Skipping capability, In this below query 2 record skip and take next 4 records.

Practicable example for OFFSET-FETCH:

you’re implementing a paging concept where you return to the user one page of rows at a time. The user passes as input parameters to your procedure or a function the page number they are after (@pagenum parameter) and page size (@pagesize parameter). This means that you need to skip as many rows as @pagenum minus one times @pagesize, and fetch the next @pagesize rows. This can be implemented using the following code (using local variables for simplicity).

Cheers!

LIKE Function

The table provides some additional functionality of LIKE function

Wildcard	Meaning	Example
% (percent sign)	Any string including an empty one	‘D%’ : string starting with D
_ (underscore)	A single character	‘_D%’ : string where second character is D
[<character list>]	A single character from a list	‘[AC]%’ :  string where first character is A or C
<character range>]	A single character from a range	‘[0-9]%’ : string where first character is a digit
<^<character list or range>]	A single character that is not in the list of range	‘[^0-9]%’ : string where first character is not a digit

If you want to look for a character that is considered a wildcard, use the escape ‘!’ character before the wildcard character.

Ex:

‘!_%’ à starting from _

‘!*%’à starting *

How to use app.config (Windows Form) and web.config (Web based) file for C# applications

Once you create a web based application project, by default Web.Config file will be appear under solution Explorer.

If you create Windows based or Console application, there won’t be any config file under solution explorer, so we need to create manually.

Always try to use the Visual Studio IntelliSense to add variables, because there can be many possibilities to make mistakes such as spelling mistakes and wrong key words.

First add the key word:

Then add user values into the relevant places.

In the application, first need to add the System.Configuration object reference

For windows form and console application:

For web based application:

Cheers!

SQL and T-SQL string and date time functions (FORMAT, STUFF, DATALENGTH, PATINDEX, SWITCHOFFSET)

String Functions

FORMAT: you can format an input value based on a format string, and optionally specify the culture as a third input where relevant. You can use any format string supported by the .NET Framework.

STUFF: The STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters indicated by the third argument. Then it inserts in that position the string specified as the fourth argument.

LEFT / RIGHT : The LEFT and RIGHT functions extract a requested number of characters from the left and right ends of the input string, respectively. 

DATALENGTH : The DATALENGTH function returns the length of the input in terms of number of bytes.

String Concatenation: Handling null value.

PATINDEX : T-SQL also supports a function called PATINDEX that, like CHARINDEX, you can use to locate the first position of a string within another string. But whereas with CHARINDEX you’re looking for a constant string, with PATINDEX you’re looking for a pattern.

Other String functions:

Date and Time Functions

GETDATE and CURRENT_TIMESTAMP both same GETDATE is T-SQL function and CURRENT_TIMESTAMP is standard SQL.

SYSDATETIME and SYSDATETIMEOFFSET are similar, only returning the values as the more precise DATETIME2 and DATETIMEOFFSET types (including offset), respectively.

There are no built-in functions to return the current date or the current time: CAST(SYSDATETIME() AS DATE).

GETUTCDATE function returns the current date and time in UTC terms as a DATETIME type, and SYSUTCDATE does the same, only returning the result as the more precise DATETIME2 type.

With the SWITCHOFFSET function, you can return an input DATETIMEOFFSET value in a requested offset term. Consider the expression SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00'). Regardless of the offset of the instance you are connected to, you request to present the current date and time value in terms of offset '-08:00'. If the system’s offset is, say, '-05:00', the function will compensate for this by subtracting three hours from the input value.

TODATETIMEOFFSET : You use it to construct a DATETIMEOFFSET value from two inputs: the first is a date and time value that is not offset aware, and the second is the offset. You can use this function when migrating from data that is not offset-aware, where you keep the local date and time value in one attribute, and the offset in another, to offset-aware data.

Other data time functions:

Cheers!

Choosing a Data Type for Keys (Identity, Sequence object, Nonsequential GUI, Sequential GUI)

The typical options people use to generate key columns are:

• The identity column property A property that automatically generates keys in an attribute of a numeric type with a scale of 0; namely, any integer type (TINYINT, SMALLINT, INT, BIGINT) or NUMERIC/DECIMAL with a scale of 0.

• The sequence object An independent object in the database from which you can obtain new sequence values. Like identity, it supports any numeric type with a scale of 0. Unlike identity, it’s not tied to a particular column; instead, as mentioned, it is an independent object in the database. You can also request a new value from a sequence object before using it.

• Nonsequential GUI Ds You can generate nonsequential global unique identifiers to  be stored in an attribute of a UNIQUEIDENTIFIER type. You can use the T-SQL function NEWID to generate a new GUID, possibly invoking it with a default expression attached to the column. You can also generate one from anywhere—for example, the client  by using an application programming interface (API) that generates a new GUID. The GUIDs are guaranteed to be unique across space and time.

• Sequential GUI Ds You can generate sequential GUIDs within the machine by using the T-SQL function NEWSEQUENTIALID.

• Custom solutions If you do not want to use the built-in tools that SQL Server provides to generate keys, you need to develop your own custom solution. The data type for the key then depends on your solution.