Creating your first SSRS report.2008

Creating your first SSRS report.

Hello guy.Now i m going to describe how to make SSRS Reporting step by step...
Now that you are all set with installing SSRS 2008 and some SQL server adventure works database, Let us proceed with creating our first SSRS report, deploying the same and viewing in the web browser. For report development , we will use Microsoft Business Intelligence Development studio. Quite often people ask me, how to install Business intelligence Development studio. Installing business intelligence Development studio, is as simple as adding components to an existing SQL installation. Just run the SQL setup again, and choose add features to an existing installation, and choose select the required features you need.

Lets follow a step by step process to create your first Business Intelligence Report.

Step 1: Open Business Intelligence Development Studio (BIDS) (Start --> Microsoft SQL server 2008 --> SQL server Business Intelligence studio

Step 2: Click File --> New project --> Report Server Project Wizard. Give any name and location.

 

Step 3: Once you click OK, you will be presented with the following screen.

Step 4 : Click Next. Now configure your connection string. I have chosen the type as 'Microsoft SQL server'

Step 5 : In this step, you need to enter the query for the data, that you need the SSRS report to be populated with. You can also use the query builder.

Step 6 : Click on query builder, and click on the right most top icon, to add tables

Step 7: Select the necessary table, and verify your query, then click the (!) icon to see the result of the query after execution.

Step 8: If everything looks good, click on Next

Step 9 :  Click Next

Step 10: Add the fields, as shown in the below screenshot.

Step 11 :  Click next

Step 12 : Choose a style, and click next.

Step 13 : Choose a deployment location. It will be in the form (http://machinename/Reportserver)

Step 14 : Click Next.

Step 15 : You can preview the report in Business Intelligence Development Studio.

Step 16 : Now, Right click the project , and click deploy.

Step 17 :  After successfully deploying the project, open up IE, and hit http://machinename/reports

Step 18 :  Click on your report to open it.

Congratulations! You have made your first SSRS report.

How to fetch the nth highest salary from a table without using TOP and sub-query?

WITH Salaries AS
(
    SELECT Basic_Salary, ROW_NUMBER() OVER(ORDER BY Basic_Salary DESC) AS 'RowNum'
    FROM dbo.EMPLOYEE
)
SELECT
  Basic_Salary
FROM
  Salaries
WHERE
   RowNum <= 5

New Code "CONVERT NUMERIC TO WORD"

using System;

/// <summary>

/// Summary description for NumberToWordsConvertor

/// </summary>

public class NumberToWordsConvertor

{

public NumberToWordsConvertor()

{

// TODO: Add constructor logic here

}

public string NumberToText(int number)

{

if (number == 0) return "Zero";

if (number == -2147483648) return "Minus Two Hundred and Fourteen Crore Seventy Four Lakh Eighty Three Thousand Six Hundred and Forty Eight";

int[] num = new int[4];

int first = 0;

int u, h, t;

System.Text.StringBuilder sb = new System.Text.StringBuilder();

if (number < 0) { sb.Append("Minus "); number = -number; }

string[] words0 = { "", "One ", "Two ", "Three ", "Four ", "Five ", "Six ", "Seven ", "Eight ", "Nine " };

string[] words1 = { "Ten ", "Eleven ", "Twelve ", "Thirteen ", "Fourteen ", "Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen " };

string[] words2 = { "Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ", "Seventy ", "Eighty ", "Ninety " };

string[] words3 = { "Thousand ", "Lakh ", "Crore " };

num[0] = number % 1000; // units

num[1] = number / 1000;

num[2] = number / 100000;

num[1] = num[1] - 100 * num[2]; // thousands

num[3] = number / 10000000; // crores

num[2] = num[2] - 100 * num[3]; // lakhs

for (int i = 3; i > 0; i--)

{ if (num[i] != 0)

{ first = i; break; } }

for(int i = first ; i >= 0 ; i--)

{ if (num[i] == 0) continue;



u = num[i] % 10; // ones

t = num[i] / 10;

h = num[i] / 100; // hundreds

t = t - 10 * h; // tens

t = num[i] / 10;

h = num[i] / 100; // hundreds

t = t - 10 * h; // tens

if (h > 0) sb.Append(words0[h] + "Hundred ");

if (u > 0 || t > 0)

{

if (h > 0 || i == 0) sb.Append("and ");

if (t == 0) sb.Append(words0[u]);

else if (t == 1) sb.Append(words1[u]);

else sb.Append(words2[t-2] + words0[u]);

}

           if (i != 0) sb.Append(words3[i - 1]);

}
return sb.ToString().TrimEnd();

}

}

Conver Numeric value to Words

using System;

/// <summary>

/// Summary description for NumberToWordsConvertor

/// </summary>

public class NumberToWordsConvertor

{

public NumberToWordsConvertor()

{

// TODO: Add constructor logic here

}

// Single-digit and small number names

private string[] _smallNumbers = new string[] { "Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen" };

// Tens number names from twenty upwards

private string[] _tens = new string[] { "", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety" };

// Scale number names for use during recombination

private string[] _scaleNumbers = new string[] { "", "Thousand", "Million", "Billion" };

// Converts an integer value into English words

public string NumberToWords(int number)

{

// Zero rule

if (number == 0)

return _smallNumbers[0];

// Array to hold four three-digit groups

int[] digitGroups = new int[4];

// Ensure a positive number to extract from

int positive = Math.Abs(number);

// Extract the three-digit groups

for (int i = 0; i < 4; i++)

{

digitGroups[i] = positive % 1000;

positive /= 1000;

}

// Convert each three-digit group to words

string[] groupText = new string[4];

for (int i = 0; i < 4; i++)

groupText[i] = ThreeDigitGroupToWords(digitGroups[i]);

// Recombine the three-digit groups

string combined = groupText[0];

bool appendAnd;

// Determine whether an 'and' is needed

appendAnd = (digitGroups[0] > 0) && (digitGroups[0] < 100);

// Process the remaining groups in turn, smallest to largest

for (int i = 1; i < 4; i++)

{

// Only add non-zero items

if (digitGroups[i] != 0)

{

// Build the string to add as a prefix

string prefix = groupText[i] + " " + _scaleNumbers[i];

if (combined.Length != 0)

prefix += appendAnd ? "" : ", ";

// Opportunity to add 'and' is ended

appendAnd = false;

// Add the three-digit group to the combined string

combined = prefix + combined;

}

}

// Negative rule

if (number < 0)

combined = "Negative " + combined;

return combined;

}

// Converts a three-digit group into English words

private string ThreeDigitGroupToWords(int threeDigits)

{

// Initialise the return text

string groupText = "";

// Determine the hundreds and the remainder

int hundreds = threeDigits / 100;

int tensUnits = threeDigits % 100;

// Hundreds rules

if (hundreds != 0)

{

groupText += _smallNumbers[hundreds] + " Hundred";

if (tensUnits != 0)

groupText += "";

}

// Determine the tens and units

int tens = tensUnits / 10;

int units = tensUnits % 10;

// Tens rules

if (tens >= 2)

{

groupText += _tens[tens];

if (units != 0)

groupText += "" + _smallNumbers[units];

}

else if (tensUnits != 0)

groupText += _smallNumbers[tensUnits];

return groupText;

}

}