How to create SSRS report in newspaper format

This post is about how to create the SSRS reports in the newspaper format i.e. if you want to move your data from first column, then second column, then third and then you wanted to move on to third page then how you can achieve this in SSRS.
For that there is very simple solution in SSRS.

Select the RDL click on the outside the rdl on report area(yellow screen) and press F4. Now property window will open there change the column value from 1 to three.

Then you report RDL will convert into three columns like below

Thanks,
RS

Strikethrough selected text JavaScript

Here is code which shows how to strike though the selected text in textbox using JavaScript.

<script type="text/javascript" language="javascript">

    function sendtext(input) {

        var striketext = '';

        var textvar = $('#txtText').val().substr(input.selectionStart, (input.selectionEnd - input.selectionStart));

        $.each(textvar.split(''), function () {

            striketext += '\u0336' + this;

        });

        var result = $('#txtText').val().substr(0, input.selectionStart) + striketext + $('#txtText').val().substr(input.selectionEnd, $('#txtText').val().length);

        $('#txtText1').val(result);

    }

</script>

<h2>Index</h2>

<input id="txtText" type="text">

<input id="txtText1" type="text">

<input type="button" value="click" onclick="sendtext(document.getElementById('txtText'))"/>

Simple tricks in SQL-Server

Here I am going to explain some cool tricks in SQL-Server.

Trick 1: Code Wrap

Some times will see our code is going to beyond the window size and will get the scroll bar at the bottom and every time we need to scroll to see the full code. We can remove this scroll bar just setting the word-wrap property in SQL- Server.

For this go into the tools -> Options

In the pop up window select text -> General. In this click on the word wrap check box and hit the ok button.

Trick 2: Create Table from another table with out data

If we want to create a table from another table but with out data. Then we can use the below query.

 SELECT * INTO ChatMessage FROM Chat WHERE id = 0

Trick 3: Short cut to select all rows from the table

For selecting all the rows from the table every time we are writing a query as below

SELECT * FROM ChatMessage

In SQL-Server we can assign the shortcut key for this as below.

• Go to Tools -> Options
• In the pop up window select Keyboard -> Query Shortcut
• Now select any blank shortcut key and write "SELECT * FROM ". Make sure you will have a blank space after from.

Now go to the query window and write only the table name. Select the table and press the shortcut key which you have assigned.

Thanks,

RS

Code formatting in SQL-Server

I have noticed that code formatting is a difficult task for many of us. I found one small plugin notepad++ in order to do the code formatting. Below are the steps.

1. Download notepad++ Download Link
2. Go Plugins menu and select Plugin manager -> Show plugin manager.
3. In the window search Poor Man's T-Sql Formatter and install.

After performing the above steps close and open your notepad++.

Now copy your code and paste it in notepad++ and run the plugin by selecting from Plugins -> Poor Man's T-Sql Formatter.

Find the foreign key and Generate the drop statement

This post will help to find to the all the constraint and generate the drop statement for all foreign key constraint.

Below is the code.

DECLARE @temp TABLE (

       RowId INT PRIMARY KEY IDENTITY(1, 1)

       ,FKConstraint NVARCHAR(200)

       ,FKConstraintTblSch NVARCHAR(200)

       ,FKConstraintTbNm NVARCHAR(200)

       ,FKConstraintClNm NVARCHAR(200)

       ,PKConstraint NVARCHAR(200)

       ,PKConstraintTblSch NVARCHAR(200)

       ,PKConstraintTblNm NVARCHAR(200)

       ,PKConstraintClmNm NVARCHAR(200)

       )

INSERT INTO @temp (

       FKConstraint

       ,FKConstraintTblSch

       ,FKConstraintTbNm

       ,FKConstraintClNm

       )

SELECT U.CONSTRAINT_NAME

       ,U.TABLE_SCHEMA

       ,U.TABLE_NAME

       ,U.COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME

WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @temp

SET PKConstraint = UNIQUE_CONSTRAINT_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.FKConstraint = R.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintTblSch = TABLE_SCHEMA

       ,PKConstraintTblNm = TABLE_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PKConstraint = C.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintClmNm = COLUMN_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PKConstraint = U.CONSTRAINT_NAME

--SELECT * FROM @temp

--DROP CONSTRAINT:

SELECT 'ALTER TABLE [' + FKConstraintTblSch + '].[' + FKConstraintTbNm + ']

DROP CONSTRAINT ' + FKConstraint + ' GO'

FROM @temp

Thx,
RS