Add, Edit, update, Delete gridview

Here I as explaining how we can add new recored and edit of existing record and delete a record in Gridview.
Step1 : You can add this gridview in your aspx form.
Here some events of Gridview are fired:-
i)  OnRowCancelingEdit
ii)  OnRowDeleting
iii) OnRowEditing
iv) OnRowUpdating
Add this gridview in .aspx page
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataKeyNames="cat_id" ForeColor="#333333" GridLines="None" OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowDeleting="GridView1_RowDeleting" OnRowEditing="GridView1_RowEditing" OnRowUpdating="GridView1_RowUpdating">
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField HeaderText="Edit-Update" ShowEditButton="True" />
<asp:BoundField DataField="cat_id" HeaderText="Category ID" ReadOnly="True" />
<asp:BoundField DataField="cat_name" HeaderText="Category Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True" />
</Columns>
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
for binding gridveiw you have to write following code:
private void BindData()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
 Step 2:
Write this procedure to add a new row.
as you click on button it will appear on top of gridview and you can add new record in database thru gridview.
protected void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlDataAdapter da = new SqlDataAdapter("SELECT cat_id, cat_name FROM quest_categories", con);
DataTable dt = new DataTable();
da.Fill(dt);

// Here we'll add a blank row to the returned DataTable
DataRow dr = dt.NewRow();
dt.Rows.InsertAt(dr, 0);

//Creating the first row of GridView to be Editable
GridView1.EditIndex = 0;
GridView1.DataSource = dt;
GridView1.DataBind();

//Changing the Text for Inserting a New Record
((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text = "Insert";
}
Step 3:
Suppose you change your mind and canel record to addthen you have to write following code:
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
BindData();
}

Step 4:
if you are editing a row you want to some change in existing record then you have to write following code:
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) {
GridView1.EditIndex = -1;
BindData();
}
Step: 5
You wanna to add new record or you wanna add a new record then u can follow :
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) {if (((LinkButton)GridView1.Rows[0].Cells[0].Controls[0]).Text == "Insert") {SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO quest_categories(cat_name) VALUES(@cat_name)";cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[0].Cells[2].Controls[0]).Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}

else
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UPDATE quest_categories SET cat_name=@cat_name WHERE cat_id=@cat_id";
cmd.Parameters.Add("@cat_name", SqlDbType.VarChar).Value = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text;
cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

}

GridView1.EditIndex = -1;
BindData();
}

Step 6:
If you wnat to delete particulare record from database in gridview then u can follow this:
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["exam_moduleConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "DELETE FROM quest_categories WHERE cat_id=@cat_id";cmd.Parameters.Add("@cat_id", SqlDbType.Int).Value = Convert.ToInt32(GridView1.Rows[e.RowIndex].Cells[1].Text);
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
BindData();
}

Introducing “Razor” – a new view engine for ASP.NET

Introducing “Razor” – a new view engine for ASP.NET

 

One of the things my team has been working on has been a new view engine option for ASP.NET.
ASP.NET MVC has always supported the concept of “view engines” – which are the pluggable modules that implement different template syntax options.  The “default” view engine for ASP.NET MVC today uses the same .aspx/.ascx/.master file templates as ASP.NET Web Forms.  Other popular ASP.NET MVC view engines used today include Spark and NHaml.
The new view-engine option we’ve been working on is optimized around HTML generation using a code-focused templating approach. The codename for this new view engine is “Razor”, and we’ll be shipping the first public beta of it shortly.

Design Goals

We had several design goals in mind as we prototyped and evaluated “Razor”:

• Compact, Expressive, and Fluid: Razor minimizes the number of characters and keystrokes required in a file, and enables a fast, fluid coding workflow. Unlike most template syntaxes, you do not need to interrupt your coding to explicitly denote server blocks within your HTML. The parser is smart enough to infer this from your code. This enables a really compact and expressive syntax which is clean, fast and fun to type.
  

• Easy to Learn: Razor is easy to learn and enables you to quickly be productive with a minimum of concepts. You use all your existing language and HTML skills.
  

• Is not a new language: We consciously chose not to create a new imperative language with Razor. Instead we wanted to enable developers to use their existing C#/VB (or other) language skills with Razor, and deliver a template markup syntax that enables an awesome HTML construction workflow with your language of choice.
  

• Works with any Text Editor: Razor doesn’t require a specific tool and enables you to be productive in any plain old text editor (notepad works great).
  

• Has great Intellisense: While Razor has been designed to not require a specific tool or code editor, it will have awesome statement completion support within Visual Studio. We’ll be updating Visual Studio 2010 and Visual Web Developer 2010 to have full editor intellisense for it.
  

• Unit Testable: The new view engine implementation will support the ability to unit test views (without requiring a controller or web-server, and can be hosted in any unit test project – no special app-domain required).
  

We’ve spent the last few months building applications with it and doing lots of usability studies of it with a variety of volunteers (including several groups of non-.NET web developers). The feedback so far from people using it has been really great.

Choice and Flexibility

One of the best things about ASP.NET is that most things in it are pluggable. If you find something doesn’t work the way you want it to, you can swap it out for something else.
The next release of ASP.NET MVC will include a new “Add->View” dialog that makes it easy for you to choose the syntax you want to use when you create a new view template file.  It will allow you to easily select any of of the available view engines you have installed on your machine – giving you the choice to use whichever view approach feels most natural to you:

Razor will be one of the view engine options we ship built-into ASP.NET MVC.  All view helper methods and programming model features will be available with both Razor and the .ASPX view engine. 
You’ll also be able to mix and match view templates written using multiple view-engines within a single application or site.  For example, you could write some views using .aspx files, some with .cshtml or .vbhtml files (the file-extensions for Razor files – C# and VB respectively), and some with Spark or NHaml.  You can also have a view template using one view-engine use a partial view template written in another.  You’ll have full choice and flexibility.

Hello World Sample with Razor

Razor enables you to start with static HTML (or any textual content) and then make it dynamic by adding server code to it.  One of the core design goals behind Razor is to make this coding process fluid, and to enable you to quickly integrate server code into your HTML markup with a minimum of keystrokes.
To see a quick example of this let’s create a simple “hello world” sample that outputs a message like so:

Building it with .ASPX Code Nuggets
If we were to build the above “hello world” sample using ASP.NET’s existing .ASPX markup syntax, we might write it using <%= %> blocks to indicate “code nuggets” within our HTML markup like so:

One observation to make about this “hello world” sample is that each code nugget block requires 5 characters (<%= %>) to denote the start and stop of the code sequence.  Some of these characters (in particular the % key – which is center top on most keyboards) aren’t the easiest to touch-type.
Building it with Razor Syntax
You denote the start of a code block with Razor using a @ character.  Unlike <% %> code nuggets, Razor does not require you to explicitly close the code-block:

The Razor parser has semantic knowledge of C#/VB code used within code-blocks – which is why we didn’t need to explicitly close the code blocks above.  Razor was able to identify the above statements as self-contained code blocks, and implicitly closed them for us.
Even in this trivial “hello world” example we’ve managed to save ourselves 12 keystrokes over what we had to type before.  The @ character is also easier to reach on the keyboard than the % character which makes it faster and more fluid to type. 

Loops and Nested HTML Sample

Let’s look at another simple scenario where we want to list some products (and the price of each product beside it):

Building it with .ASPX Code Nuggets
If we were to implement this using ASP.NET’s existing .ASPX markup syntax, we might write the below code to dynamically generate a <ul> list with <li> items for each product inside it:

Building it with Razor Syntax
Below is how to generate the equivalent output using Razor:

Notice above how we started a “foreach” loop using the @ symbol, and then contained a line of HTML content with code blocks within it.  Because the Razor parser understands the C# semantics in our code block, it was able to determine that the <li> content should be contained within the foreach and treated like content that should be looped.  It also recognized that the trailing } terminated the foreach statement.
Razor was also smart enough to identify the @p.Name and @p.Price statements within the <li> element as server code – and execute them each time through the loop. Notice how Razor was smart enough to automatically close the @p.Name and @p.Price code blocks by inferring how the HTML and code is being used together.
The ability to code like this without having to add lots of open/close markers throughout your templates ends up making the whole coding process really fluid and fast.

If-Blocks and Multi-line Statements

Below are a few examples of other common scenarios:
If Statements
Like our foreach example above, you can embed content within if statements (or any other C# or VB language construct), without having to be explicit about the code block’s begin/end.  For example:

Multi-line Statements
You can denote multiple lines of code by wrapping it within a @{ code } block like so:

Notice above how variables can span multiple server code blocks – the “message” variable defined within the multi-line @{ } block, for example, is also being used within the @message code block.  This is conceptually the same as the <% %> and <%= %> syntax within .aspx markup files.
Multi-Token Statements
The @( ) syntax enables a code block to have multiple tokens.  For example, we could re-write the above code to concatenate a string and the number together within a @( code ) block:

Integrating Content and Code

The Razor parser has a lot of language smarts built-into it – enabling you to rely on it to do the heavily lifting, as opposed to you having to explicitly do it yourself. 
Does it break with email addresses and other usages of @ in HTML?
Razor’s language parser is clever enough in most cases to infer whether a @ character within a template is being used for code or static content.  For example, below I’m using a @ character as part of an email address:

When parsing a file, Razor examines the content on the right-hand side of any @ character and attempts to determine whether it is C# code (if it is a CSHTML file) or VB code (if it is a VBHTML file) or whether it is just static content.  The above code will output the following HTML (where the email address is output as static content and the @DateTime.Now is evaluated as code:

In cases where the content is valid as code as well (and you want to treat it as content), you can explicitly escape out @ characters by typing @@.
Identifying Nested Content
When nesting HTML content within an if/else, foreach or other block statement, you should look to wrap the inner content within an HTML or XML element to better identify that it is the beginning of a content block.
For example, below I’ve wrapped a multi-line content block (which includes a code-nugget) with a <span> element:

This will render the below content to the client – note that it includes the <span> tag:

You can optionally wrap nested content with a <text> block for cases where you have content that you want to render to the client without a wrapping tag:

The above code will render the below content to the client – note that it does not include any wrapping tag:

HTML Encoding
By default content emitted using a @ block is automatically HTML encoded to better protect against XSS attack scenarios.

Layout/MasterPage Scenarios – The Basics

It is important to have a consistent look and feel across all of the pages within your web-site/application.  ASP.NET 2.0 introduced the concept of “master pages” which helps enable this when using .aspx based pages or templates.  Razor also supports this concept using “layout pages” – which allow you to define a common site template, and then inherit its look and feel across all the views/pages on your site.
Simple Layout Example
Below is a simple example of a layout page – which we’ll save in a file called “SiteLayout.cshtml”.  It can contain any static HTML content we want to include in it, as well as dynamic server code.  We’ll then add a call to the “RenderBody()” helper method at the location in the template where we want to “fill in” specific body content for a requested URL:

We can then create a view template called “Home.cshtml” that contains only the content/code necessary to construct the specific body of a requested page, and which relies on the layout template for its outer content:

Notice above how we are explicitly setting the “LayoutPage” property in code within our Home.cshtml file.  This indicates that we want to use the SiteLayout.cshtml template as the layout for this view.  We could alternatively indicate the layout file we want to use within a ASP.NET MVC Controller invoking Home.cshtml as a view template, or by configuring it as the default layout to use for our site (in which case we can specify it in one file in our project and have all view templates pick it up automatically).
When we render Home.cshtml as a view-template, it will combine the content from the layout and sub-page and send the following content to the client:

Compact, Clean, Expressive Code

One of the things to notice in the code above is that the syntax for defining layouts and using them from views/pages is clean and minimal.  The code screen-shots above of the SiteLayout.cshtml and Home.cshtml files contain literally all of the content in the two .cshtml files – there is no extra configuration or additional tags, no <%@ Page%> prefix, nor any other markup or properties that need to be set.
We are trying to keep the code you write compact, easy and fluid.  We also want to enable anyone with a text editor to be able to open, edit and easily tweak/customize them.  No code generation or intellisense required.

Layout/MasterPage Scenarios – Adding Section Overrides

Layout pages optionally support the ability to define different “sections” within them that view templates based on the layout can then override and “fill-in” with custom content.  This enables you to easily override/fill-in discontinuous content regions within a layout page, and provides you with a lot of layout flexibility for your site.
For example, we could return to our SiteLayout.cshtml file and define two sections within our layout that the view templates within our site can optionally choose to fill-in.  We’ll name these sections “menu” and “footer” – and indicate that they are optional (and not required) within our site by passing an optional=true parameter to the RenderSection() helper call (we are doing this using the new C# optional parameter syntax that I’ve previously blogged about).

Because these two sections are marked as “optional”, I’m not required to define them within my Home.cshtml file.  My site will continue to work fine if they aren’t there. 
Let’s go back into Home.cshtml, though, and define a custom Menu and Footer section for them.  The below screenshot contains all of the content in Home.cshtml – there is nothing else required in the file.  Note: I moved setting the LayoutPage to be a site wide setting – which is why it is no longer there.

Our custom “menu” and “footer” section overrides are being defined within named @section { } blocks within the file.  We chose not to require you to wrap the “main/body” content within a section and instead to just keep it inline (which both saves keystrokes and enables you to easily add sections to your layout pages without having to go back through all your existing pages changing their syntax). 
When we render Home.cshtml as a view-template again, it will now combine the content from the layout and sub-page, integrating the two new custom section overrides in it, and send down the following content to the client:

Encapsulation and Re-Use with HTML Helpers

We’ve covered how to maintain a consistent site-wide look and feel using layout pages.  Let’s now look at how we can also create re-usable “HTML helpers” that enable us to cleanly encapsulate HTML generation functionality into libraries that we can re-use across our site – or even across multiple different sites.
Code Based HTML Helpers
ASP.NET MVC today has the concept of “HTML Helpers” – which are methods that can be invoked within code-blocks, and which encapsulate generating HTML.  These are implemented using pure code today (typically as extension methods).  All of the existing HTML extension methods built with ASP.NET MVC (both ones we’ve built and ones built by others) will work using the “Razor” view engine (no code changes required):

Declarative HTML Helpers
Generating HTML output using a code-only class approach works – but is not ideal.
One of the features we are looking to enable with Razor is an easy way to create re-usable HTML helpers using a more declarative approach.  Our plan is to enable you to define reusable helpers using a @helper { } declarative syntax like below. 

You’ll be able to place .cshtml files that contain these helpers into a Views\Helpers directory and then re-use them from any view or page in your site (no extra steps required):

Note above how our ProductListing() helper is able to define arguments and parameters.  This enables you to pass any parameters you want to them (and take full advantage of existing languages features like optional parameters, nullable types, generics, etc).  You’ll also get debugging support for them within Visual Studio.
Note: The @helper syntax won’t be in the first beta of Razor – but is something we hope will be enabled with the next drop.  Code-based helpers will work with the first beta.
Passing Inline Templates as Parameters
One other useful (and extremely powerful) feature we are enabling with Razor is the ability to pass “inline template” parameters to helper methods.  These “inline templates” can contain both HTML and code, and can be invoked on-demand by helper methods.
Below is an example of this feature in action using a “Grid” HTML Helper that renders a DataGrid to the client:

The Grid.Render() method call above is C#.  We are using the new C# named parameter syntax to pass strongly-typed arguments to the Grid.Render method - which means we get full statement completion/intellisense and compile-time checking for the above syntax.
The “format” parameter we are passing when defining columns is an “inline template” – which contains both custom html and code, and which we can use to customize the format of the data.  What is powerful about this is that the Grid helper can invoke our inline template as a delegate method, and invoke it as needed and as many times as it wants. In the scenario above it will call it each time it renders a row in the grid – and pass in the “item” that our template can use to display the appropriate response.
This capability will enable much richer HTML helper methods to be developed.  You’ll be able to implement them using both a code approach (like the way you build extension methods today) as well as using the declarative @helper {} approach.

Visual Studio Support

As I mentioned earlier, one of our goals with Razor is to minimize typing, and enable it to be easily edited with nothing more than a basic text editor (notepad works great).  We’ve kept the syntax clean, compact and simple to help enable that.
We have also designed Razor so that you get a rich code editing experience within Visual Studio.  We will provide full HTML, JavaScript and C#/VB code intellisense within Razor based files:

Notice above how we are providing intellisense for a Product object on the “@p.” code embedded within the <li> element inside a foreach loop.  Also notice how our \Views folder within the Solution Explorer contains both .aspx and .cshtml view templates.  You can use multiple view engines within a single application – making it easy to choose whichever syntax feels best to you.

Summary

We think “Razor” provides a great new view-engine option that is streamlined for code-focused templating.  It a coding workflow that is fast, expressive and fun.  It’s syntax is compact and reduces typing – while at the same time improving the overall readability of your markup and code.  It will be shipping as a built-in view engine with the next release of ASP.NET MVC.  You can also drop standalone .cshtml/.vbhtml files into your application and run them as single-pages – which also enables you to take advantage of it within ASP.NET Web Forms applications as well.
The feedback from developers who have been trying it out the last few months has been extremely positive.  We are going to be shipping the first public beta of it shortly, and are looking forward to your feedback on it.
Hope this helps,

Exporting a report to multiple Excel tabs

Exporting a report to multiple Excel tabs

Sometimes, SQL Server Reporting Services users like to see what could be considered multiple reports in a single SSRS report. In most cases we can easily accomplish this using more than one data region in the report. If not, we can always use a subreport.
Let's say we have a report called the Top 10 Report. In it, we wish to see the top ten products sold by order quantity. We'd also like to see the top 10 resellers of our products, again by order quantity. This could be accomplished using multiple data regions or using two subreports.
We'll use two table data regions for this demonstration. This is shown in the following figure.

Previewing the report produces the following results.

This generally works well. Our users can view the single report and examine the details from its two data regions. If we choose, we can use conditional formatting techniques to dynamically show or hide each data region. But I digress; back to our example.
When our Top 10 Report is exported into Microsoft Excel format, both data regions are exported to the same worksheet as shown in the figure below.

This causes some users angst. They wish each data region to be exported to a separate worksheet or tab.
We can easily do this. Examine the properties for the first table data region. You'll notice that there is a boolean property named PageBreakAtEnd. By default, this value is set to False. Changing its value to True will cause a page break to be inserted after the table.
 
A page break at the end of a data region will cause a new tab to be created when the report is exported to Microsoft. The first data region, the Top 10 Products Sold by Order Quantity, appears on the first tab.

The Top 10 Resellers by Purchase Quantity report appears on the second tab of the Excel workbook.
 
So using this technique we can effectively combine multiple reports in the one Reporting Services report, while giving our users the ability to export them to different tabs in Microsoft Excel.

Naming Excel sheets when exporting reports

New features of SSRS 2008 R2 - Part 1 Naming Excel sheets when exporting reports

posted 19/6/2014  by Narendra Kushwaha -  Views: [50375] 

This is the first of what I hope to be a long series of posts that demonstrate some new features of SQL Server Reporting Services in the 2008 R2 upcoming release. At some point I may define and go into more detail but for now, I simply want to expose you, the reader, to some new features in the next version of SQL Server Reporting Services (SSRS).
At the company that I work for, we have created a series of reports that have been enhanced over the years based on customer feedback. These reports were built on SQL Server 2005 while it was in beta. The application is still running in SQL 2005 and it uses all of the BI componants of SQL Server -
Enough of the background....
The ProblemOne of our enhancements was taking several reports that were very similar in nature and combining them into one report. The key requirement was that these reports need to export into seperate tabs when exported into Excel. This meant that the user could run one report that contained several pages of seperate reports where as before the user would have had to run the reports seperately. We were able to tell SSRS to create a new page for each grouping of the dataset and so we combined all of the seperate reports into one dataset and seperated the data by using these groups. This worked well with one problem. When you export the data to Excel it names the sheets "Sheet 1", "Sheet 2", etc... There was no way to control this nameing without really digging under the covers of the rendering engine and creating custom code. Newsgroups were full of posts enquiring on how to name sheets in Excel with no good answers.
There was hope... SQL Server 2008 was released... but still no solution. Denied!
The Solution
Back in November I got my first view of SSRS 2008 R2 Nov CTP release during a presentation at SQL PASS. Bob Meyers and Sean Boon did a great job of showing off many new features of SSRS - many of these will hopefully be bloged in the next parts of this series soon. At the end they did a quick demo and showed that in the R2 release we will have the ability to name the sheets for exports to Excel. This 90 second demo earned the applause of the entire room.
Here is how it is done:
I have created a boring and simple report using the Adventure Works database simply for the purpose of showing this feature.
This report simply pulls Sales Person data and groups it by Territory so that each territory has it's own page.
Here is an example of the report exported to Excel before setting the sheet names.

In order to name the Sheets appropriately all you have to do is select the Group that has the page breaks from the Row Groups shown below

Then in the properties window navigate into the Groups section and find the PageName attribute.  By clicking on this attribute you can select the TerritoryName field from the dataset used to fill the report.  This can be any field or expression that you want to use, but for my example I wanted to use the name of the territory as the name of the sheets.

Here is a screen of the final result - SIMPLE!!

Convert Columns to Rows in Sql Server using Pivot

create table studentgrade
(
course char(1),
grade int
)

insert into studentgrade values('A',15),
                               ('A',17),
                               ('B',12),
                               ('C',10),
                               ('C',18),
                               ('C',1),
                               ('C',9)                   
                                   
select * from studentgrade
 
SELECT 
  Course,
  [1] AS 'Grade 1',
  [2] AS 'Grade 2',
  [3] AS 'Grade 3',
  [4] AS 'Grade 4'

 
FROM
(
  SELECT
    Course,
    Grade,
    ROW_NUMBER() OVER(PARTITION BY Course
                      ORDER BY COURSE) rownum
  FROM studentgrade
) t
PIVOT
(
   MAX(Grade)
   FOR rownum IN([1], [2], [3], [4])
) p;


Output:

How to use recursive CTE calls in T-SQL

 with empcte as
 (
  select eid,name,mgrid,2 as level from employee where eid=102
  union all
  select usr.eid,usr.name,usr.mgrid,level-1 from empcte as mgr inner join employee usr on mgr.mgrid=usr.eid 
  )
  select * from empcte as u;


Given below is the employee table with its data and below that is the resultset of the above query.

[SSRS] 101 SQL Server Reporting Services Interview Questions with Answers

Written by Super User. Posted in SSRS

Joomla
Joomla

Wordpress
Wordpress

In this post, I am going to walk you thru SQL Server Reporting Services Interview Questions from
· GENERAL | BASICS | Introduction
· DBA (Production Support) Prospective
· DBA + Developer Common Questions
· Developer (Report Designer) Prospective
· Solution Architect prospective
· IT Manager prospective

GENERAL Questions – SSRS Interview Questions
What is SQL Server Reporting Services?
Reporting Services helps business to Transform raw data (table data) into graphical like chart, histogram etc. to add meaning to raw data.
For example, the table below having a data but the moment that data was presented in chart, that added more business meaning to data.

SQL Server Reporting Services can query and display data from any of the multiple databases and represent in a way which is more understandable to users.
What are the tools available in market as an Alternative to SQL Server Reporting Services / (Business Intelligence tools) ?
Market is full of business intelligence tools here are few listed of them,

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

Open Source solutions
· Jasper Reports
· JFreeReport
· BIRT (Business Intelligence Reporting Tools)
· OpenReport
· DataVision
· Pentaho
What is reporting lifecycle ?
Reporting Services has three mainly three phases
1. Development of Reports (Developer) – First of all a report needs to be design which is primarily done by report developer
2. Management of Reports (DBA) – Once the Report is being developed, DBA need to ensure
1. Security – Only authorized user should access the report
2. Execution – How the report will be executed to optimize data sources performance
3. Scheduling of reports – so that report are executed on scheduled timings
3. Report Delivery (DBA+Developer) – Once the report is being developed and executed now the report should be reached to final recipients (business users) who are going to understand / analyze report data. if any changes, we again go back to development stage.
What is Reporting Services Architecture ?
Reporting Services is a .NET framework-based platform that includes a comprehensive tools that we can use to integrate reporting solution into any centrally-managed environment. CLICK HERE(http://goo.gl/LwBRj) for more details
What can we do with SQL Server Reporting Services ?
Reporting Services can help you in building and deploying fully interactive scorecards, dashboards, and enterprise reports.
SQL Server Reporting Services Feature
· Design ad-hoc dashboards and reports quickly
· Data is interactive and available everywhere even on your IPAD / IPHONE.
· This is bundle product with SQL Server database services, so there is additional cost for this service.
· .Net integrated for rapid development for report even in few clicks by using wizards.
By using Reporting Services, we can build up a solution which is kind of Google maps, where we can list our all business locations.
Check out video post to learn, “How to Integrate Map with SQL Server Reporting Services”
What are the New Features of SQL Server 2008 R2 Reporting Services ?
Lot of new features were added in SQL Server 2008 R2, like
· New Report Types – Table, Matrix, List, Chart, Sub report
· New Tools added to report designer Toolbox
· Report Data Panel -  built in page numbers
· Report Builder 3.0
For details for new feature of SQL Server 2008 R2, please check my previous post, “SQL Server Reporting Services R2 – New Features”
What are the new features are introduced in SQL Server 2012 reporting services?
SQL Server 2012 has introduced a couple of new features like
· Power View - interactive data exploration
· SQL Server 2012 is fully integrated with SharePoint
· Introduction to Data Alerts, data alerts are a data-driven alerting solution that informs you about changes in report data that are of interest to you, and at a relevant time
· SQL Server Data tool
· new rendering extensions supports MS Office 2010
· Project Crescent is being introduced
DBA (Production Support) Prospective
What are the tasks which are mainly performed by a production DBA on a SQL Server Reporting Services ?
Operational support DBA are mainly responsible for
· Install and Configure reporting services
· Backing up Reporting Services (including databases, config files and encryption keys)
· Deploying new reports
· create and manage shared data sources
· Optimizing reports – Enable caching of reports
· Authorize users for reports
· Create and deploy security roles.
· Check Reporting Services Error Logs for errors
· Maintain response time of reporting services
Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?
Yes, SQL Server Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services.
What are the limitations in Reporting Services on SQL Server Express Edition ?
Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition but following restrictions will be applicable to this edition.
· SQL Server Management Studio Express cannot be used to administer report server
· Report Models will not be available
· Other features like Caching, History and Delivery of Report is not available.
· Report Builder is not available
· Local SQL Server is a only option, which can be used as Report Data Source.
· We cannot store the report server database on a remote server, it has to be local only.
· Reports can be rendered only in Excel, PDF, Image formats only
· No SQL Server agent is available, so no scheduling is possible.
· Reporting Services will not be able to use more than 1 GB of RAM
· No Subscriptions (Standard and Data Driven) can be made
· Can not be integrated with Share Point
· Only named instances is supported
· Scale-out Report Servers will not be available
· Can not implement Role based security.
How to Install SQL Server Express with Reporting Services ?
For Step by Step instructions, Please check my previous post, “How to Install SQL Server Express with Reporting Services”
What are the parameters which should be considered for Reporting Services Disk storage requirement ?
There are various factors to be considers as a primary I would consider
1. Number of reports to be hosted server
2. Report size and frequency of reports
3. Number of snapshots to be saved
Can I configure SQL Server Reporting Services on a Windows cluster for High availability ?
Please check my previous post, “Installing & Configuring SQL Server Reporting Services on Cluster Environment for High Availability” to understand how reporting services works on cluster.
What is ReportServer and ReportServerTempDB ?
Reporting Services uses two SQL Server databases for storage by default, the databases are namedReportServer and ReportServerTempdb.
ReportServer is a main database, which store all internal configuration and report meta data where asReportServerTempdb is used to store temporary data, session information, and cached reports.
How to backup SQL Server Reporting Services ?
There are mainly three things, which should be backed up as part of reporting services backup
1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
What is encryption key ?
Encryption keys are used by the report server so that items such as connection strings are maintained securely. these keys are required in case you want to perform restoration of report server databases.
How to backup encryption key ?
Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
Can we install a 32-bit version of SQL Server Reporting Services on a computer that is running a 64-bit version of Windows?
YES, we can install SQL Server 32 bit on Windows 64 bit version. SQL Server Reporting services are part of SQL Server.
Where the SQL Server Reporting Configuration is saved, is that in registry ?
NO, all SQL Server configuration is saved in configuration files (.xml files)
What are the key configuration files for SQL Server Reporting Services ?
Mostly all Configuration files located at Install Directory\Microsoft SQL Server
\<SQL Server Instance>\Reporting Services\ReportServer and  ReportManager
· RSReportServer.config stores configuration settings for feature areas of the Report Server service: ReportManager, the Report Server Web service, and background processing.
· RSSrvPolicy.config stores code access security policies for the server extensions.
· RSMgrPolicy.config stores code access security policies for Report Manager
· ReportingServicesService.exe.config stores configuration settings that specify the trace levels and logging options for the Report Server service.
Other Files
· RSReportDesigner.config contains settings for Report Designer and this file is located in the..\Program Files\Visual Studio 9.0\Common7\IDE\PrivateAssemblies
· RSPreviewPolicy.config stores server extensions used during report preview and this file is located in ..\Program Files \Microsoft SQL Server\100\Tools \ReportDesigner
What are the mostly used PERFMON Counters for monitoring SQL Server Reporting Services?
This is bit tricky and everyone has his own answer, but I personally choose the following at least
· ReportServer: Service Performance Object \ Active Connections
· ReportServer: Service Performance Object \ Report Executed per second
· ReportServer: Service Performance Object \ Total Cache Hits
· ReportServer: Service Performance Object \ Total Requests
· Web Service \ Deliveries per second
· Web Service \ Total processing failures
What all should be backed as part of reporting Services ?
1. Report server databases (ReportServer and ReporterverTempdb)
2. Encryption keys and
3. Configuration Files (xml files)
1. RSReportServer.config,
2. RSSrvPolicy.config,
3. RSMgrPolicy.config,
4. ReportingServicesService.exe.config,
5. RSReportDesigner.config and
6. RSPreviewPolicy.config
How to backup up Encryption Keys?
Use SQL Server Reporting Services Configuration tool to backup symmetric keys
Questions for Developer as well as Developers (depends on profile)
What are the tools which are being offered by Microsoft SQL Server for Developers?
· Report Designer, used to develop reports in Business Intelligence Development Studio (BIDS) and then publish them to a Report Server
· Model Designer, used create a report model that Report Builder users uses to help them build on-demand reports.
· Report Builder – used to build reports without having to understand the underlying data source structures in the database
Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.
What are the tools which are being offered by Microsoft SQL Server for DBA’s?
· Report Manager – a web-based report access and management tool to browse existing reports, upload new reports, and other properties like execution properties, security, and subscriptions.
· Reporting Services Configuration –used to configure a report server like report server databases, SMTP server and services properties.
· SQL Server Management Studio (SSMS) -  an alternative to Report Manager, which is primarily being used by DBA’s for managing reports.
Note – These are built in tools with Microsoft SQL Server, there is no additional cost for using these tools.
How to deploy reports ?
Reports can be deployed by using BIDS (Business Intelligence development Studio ) or by Report Manager which is web based application. Please see previous post, “How to deploy report to SQL Server Reporting Services”
Can I deploy a report at multiple servers at one go ?
NO, native SQL Server Reporting services doesn’t supports this functionality but we can deploy reports directly to multiple servers at one go but this functionality is not built in, we need to buy some third party tools.
What is Report Builder ?
Report builder is used to create small reports and it a define interface. You can’t change the report interface in report builder it pre designed. You can just drag columns in the report.
Report builder creates reports on database objects available with report model project. Please check my previous Video post, ”How to Install Report Builder 3.0”
What is report rendering ?
Exporting a report data with design o different type of file types is knows as Report rending. SQL Server Reporting Services supports multiple rendering extensions like Word, Excel, CSV, PDF, HTML etc.
Can I disable | restrict SQL Server Reporting Services Export Formats (Rendering Formats) ?
YES, we can restrict Reporting services export formats.
SQL Server Reporting Services (SSRS) supports export to different formats like XML, CSV, HTML, PDF, etc., when you view the report.  This export option is available at report viewer Export drop-down list.
We can control this via report services server configuration file which is available at “C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\Report Server\ rsreportserver.config“.
For More details, please check my previous post “How to Disable/restrict SSRS Export format ?”
How to migrate SQL Server 2008 Reporting Services to another computer?
If you are trying to migrate only SQL Server reporting services to new server, then we have an option to install reporting services on new server and while configuring report server, we can point report server services to old report database database.
Or incase, you are trying to migrate report server databases and reporting service together, the install reporting services on new instance and restore configuration files  and restore report server databases and reconfigure the report server services.
How to check how frequent report is being called ?
Check Report history either from SSMS or Report manager. In report manager, go to report and select history tab. check here for more details
What is Report Caching ?
Report caching is a performance enhancing technique in Reporting Services that saves copies of reports for faster viewing. Cached instances have an expiration time set to force a refresh of the data set used
for the report.
In case you have filters in your report, when filters will be applied in Cached Report instance ?
Filters are applied when a report is rendered, Filters will not create a new cached instance on the Report Server.
What is report snapshot ?
Snapshot means a instance of a report for future reference, that means a copy of report (data is freezed) will be saved on a report server for future reference.
What is Data Driven Subscription ?
Subscriptions are used to deliver rendered reports to business users at specified schedule. For example, a sales report should go to sales manager daily at 7:00 AM for last day report. This is kind of pushing reports on a schedule. By creating subscription, we can send reports to users in e-mail or Deliver to a file share
or can put report in Microsoft Office SharePoint server
A data-driven subscription can deliver a report in many rendered formats to many destinations. For example, USER1 wants report in XML format and USER 2 wants in a pdf format and user 3 wants in word but that too on a shared drive.
To meet these objective we can use Data-driven subscriptions as data driven  subscription require a database table to store all these report values so that these values can be used during processing.
How to upgrade report from SQL Server 2005 to SQL Server 2008?
SQL Server Setup is used to upgrade SQL Server Reporting Services. Run the SQL Server 2008 setup on existing SQL Server 2005 and opt for upgrade.
Developer (Report Designer) Prospective
Any Idea, how to to build / design Report using Visual Studio?
Building Reports using Visual Studio is quite easy, Please check my previous Video POST, “How to create a report using Visual Studio 2008?” which describes
1. how to build a report using Visual Studio 2008
2. deploy report to SQL Server Reporting Services
3. How to create an shared data sources
4. How to design a report
5. How to add interactive sorting Change
6. Run the report in SSRS
What is Parameterized Reports in SSRS ?
You often need more advanced reporting such as drop down lists and the ability to use parameters when working in Sql Server Reporting Services. Please check VIDEO Tutorial, “How to make Parameterized Reports in SSRS”  which describes,
· How to use variables that have not been declared in SSRS vs TSQL
· How to create report with auto-Parameterization
· How to View Report Parameters
· How to create a drop-down list
What are different types of reports are available ?
There is as such not any type of of reports, but we can categories them as
Tabular Report – where we represent data in rows and columns format. (Now this is non as Tablix which is mixture of table and a matrix)
CHART REPORTS – where we represent the data in graphical format mainly in charts?
further more these reports can fetch data either from relational database (TSQL REPORTS category) or from CUBES (MDX Reports)
What sort of query we can write in SSRS, is that just SQL / TSQL or is there anything else too?
· MDX – for OLAP / CUBE data sources
· DMX – for data mining
Can you edit a report that an end user created by using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.
What is Sub report ?
SUB report is very helpful when the detail of a report’s data region is so complex that a separate report is a better option then using other tools.
Can sub report data source be different from that of the parent report ?
YES, sub report data source can be different from that of the parent report.
What are the various ways to deploy reports ?
Reports can be deployed to report server by using any of the following method
· BIDS
· Computer Management console
· .nET START command
· SSMS
· Report Manager
but normally BIDS  and Report managers are mostly used.
Can we use Analysis Services Database as a data source with Model Designer ?
NO, Model Designer doesn’t access data from Analysis Services cubes.
What is report subscription?
Subscriptions are standing requests to deliver report data to requested recipients. Once the report is being subscribed and subscriber will get updates from report server on scheduled interval.
What is the RDL file?
RDL stands for Report Definition Language, when we design a report using any tool like BIDS and when we save the report, it’s saved as .rdl file, RDL file is an kind of xml code, which stores the design of the report.
This RDL file is being used  for deploying report to report server.
Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.
How to deploy Reports on SQL Server Reporting Services ?
Please check my previous VIDEO post to learn, Step by Step Learning, How to deploy report to SQL Server Reporting Services 2008
How can I build my first report which is ready to deploy in SQL Server Reporting Services ?
Please check my previous blog post, Introduction to SQL Server Reporting Services, for a brief video tutorial for SQL Server Report designer.
Solution Architect prospective
IT Manager prospective
Can we run Reporting Services with SQL Server Express Edition, which is a free version of SQL Server ?
Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services. we can use SQL Server Express Edition for deploying reporting Services. For more details to know SQL Server Express Limitations, please check my previous post, “What are the limitations in Reporting Services on SQL Server Express Edition”
Is there any free tools to build reports ?
YES, there are log of open source code solution are available, which can used to design reports, here are few listed.
· Jasper Reports
· JFreeReport
· BIRT (Business Intelligence Reporting Tools)
· OpenReport
· DataVision
· Pentaho
What are the product alternatives to SQL Server Reporting Services ?
Market of full of tools and here are few of them

Actuate	Hyperion (BRIO)	SIEBEL-CRM
BusinessObjects	Oracle Express OLAP	Qlikview
Cognos	Informatica Power Analyzer	Proclarity
IntelliView	Dundas Chart for .NET	MS-Excel
SAS	MicroStrategies	Pentaho

How many instances of Reporting Services can be managed by a single DBA ?
This is pretty tricky questions, and there is not any single formula to decide this. This all depends on what sort of monitoring tools you have and what sort of automation you have achieved.
But as per my understanding and calculations a single 3-7 year experience DBA without any third party tool can easily deploy 25-35 report in a day, which is one time activity for most of the organizations and DBA can easily monitor / provide production support to 25-40 instances of SQL Server, which included daily
· Monitor Event Viewers
· Check SQL Server Errors
· Check Reporting Services Logs
· Monitor Perfmon counters
· Backup all databases
· check schedule sql jobs etc.
but I believe, without any tool (just using manual / Script) we can assume 1 DBA for every 25 servers is ideal number to focus more on preventive actions rather then corrective actions.
Is my data is safe, What all we are backing up for SQL Server Reporting Services ?
There are mainly three things, which should be backed up as part of reporting services backup
1. Report Server Databases, which can be backed up by SQL server backup and restore method. Check my previous Video post, “HOW to BACKUP SQL”
2. SQL Server Reporting Services Configuration, SQL Server Reporting Services Configuration is saved in config files, which can be copied as part of backup. look for other to know config files and there location.
3. Encryption Keys backup , use SQL Server Reporting Services Configuration tool to backup symmetric keys.
What is the licensing cost for SQL Server Report Server?
SQL Server Reporting Services is bundled with SQL Server. So in case you have already purchased SQL Server Standard edition for your business then there is no additional cost for using this SQL Server Reporting Services.
Do I need to buy separate licenses for users who view reports ?
NO, there is no separate licenses for report delivery or viewing report on demand, i’’s all bundelled in SQL Server with once license cost.
Is there any free edition where I can use SQL Server Reporting Services ?
Yes, SQL Server 2005 Express Edition with Advanced Services support Reporting Services but there are several limitation, check my post,”What are the limitations in Reporting Services on SQL Server Express Edition“ for these limitations.

Source : http://www.sqlserver-training.com