SQL Reporting Matrix Tips

Create a Percent Column in a Matrix

1) Create 2 text boxes in a column
Re: http://www.simple-talk.com/sql/reporting-services/advanced-matrix-reporting-techniques/









2) The first column will contain the standard sum expression : = Sum(Fields!DetaiField.Value)
DetailField is a placeholder for the field that you count on.

3) The second column will contain the following:
= Sum(Fields!DetaiField.Value)/
Sum(Fields!DetaiField.Value,"matrix_RowGroup")

Substitute the name of your row group for "matrix_RowGroup"
What we do here is sum up the count in the default scope divided by the total of items in a row.

4) The last step is to enclose the last expression into FormatPercent function:

FormatPercent(Sum(Fields!DetaiField.Value)/
Sum(Fields!DetaiField.Value,"matrix_RowGroup"))


Show a Variance Instead of a Total in the Subtotal Column

If you have a 2-column matrix, and you want to show a variance instead of the subtotal, you could use the following expression:

=Iif (inScope("matrix_ColumnGroupName"), Sum(Fields!DataField.Value),
Sum (IIF(Fields!ColumnName.Value = "Closed",
Fields!DataField.Value, Fields!DataField.Value * -1)))

First, you define an expression for the column data fields: Sum(Fields!DataField.Value).
The second part of the IIF function defines an expression for the Subtotal column. The value of the second data column becomes negative which allows us to obtain the variance instead of a sum. The expression assumes that the first column is named "Closed".

An example:

Date\State			Closed			Pending					Variance
8/17/2009			317			22					295
8/18/2009			5215			816					4399
9/14/2009			5526			503					5023
9/16/2009			49			3					46

Create a Variance Percentage Column

If instead of the variance you want to show a variance percentage, you need to use the following expression in the subtotal column created earlier:

FormatPercent (Sum (IIF(Fields!State.Value = "Closed", Fields!DataField.Value, Fields!DataField.Value * -1))/ Sum(Fields!DataField.Value)))

What you do here is obtain a variance value first, then a simple sum of all data fields in the row, and then divide the first value by the second.

Change Control Size Automatically with Form Size Change

Here is how you can change a form control size automatically when the form size is changed by user.
Declare a class variable that will hold the value of the current form size, and set its value in the contructor.

Size curSize;

public Form1()

{

InitializeComponent();

curSize = this.Size;

}

To capture form size change, use the ClientSizeChanged event handler with the following code:

private void Form1_ClientSizeChanged(object sender, EventArgs e)

{

    int heightDif = Size.Height - curSize.Height;

    int widthDif = Size.Width - curSize.Width;

    yourControlName.Height += heightDif;

    yourControlName.Width += widthDif;

    //store the current size until the next time this event is raised

    curSize = this.Size;

}

C# Interview Questions

Some links to C# interview questions as based on Google search. The usefulness of the questions improves as you go down the list.

Mark Wagner's blog

Tech Interviews

Questions for a senior C# developer -

http://www.hanselman.com/blog/WhatGreatNETDevelopersOughtToKnowMoreNETInterviewQuestions.aspx

The following one is more interesting -

http://dev.fyicenter.com/Interview-Questions/C-sharp/

http://www.coolinterview.com/interview/8990/

http://venkatcsharpinterview.blogspot.com/2009/01/c-interview-questions.html

http://www.dotnetfunda.com/interview/ShowCatQuestion.aspx?category=34

Capture the Enter key on WinForm Text-box

If you want to to capture the Enter key when you finish entering data in a .NET Framework WinForm text-box, and imitate clicking on a button, you should attach a KeyDown event to the text-box first.

In the KeyDown event handler code, use the following:

private void Text1_KeyDown(object sender, KeyEventArgs e)

{

    if (e.KeyCode == Keys.Enter)

       this.Close();

} 

If you have a button on the form with DialogResult property set to OK, closing the form will be equal to clicking that button.

However, in this case, a simpler solution is to set the form's AcceptButton property to the name of your OK button. This will have the same effect.

Remove List Elements That Exist in Another List

Problem:

We have two lists:


List bannedList = new List { "one", "three", "seven" };
List myList = new List { "one", "two", "three", "four", "five", "six" };

We need to keep only those entries in myList that are not contained in bannedList.

First of all, the conventional C# 1.0 approach to this problem:

List listDistinct = new List();
foreach (string elem in myList )
{
if (!bannedList.Contains(elem))
{
listDistinct.Add(elem);
}
}

Now, let's use lambda expressions to arrive at a much shorter syntax.

List listDistinct= myList .FindAll (elem => !bannedList.Contains(elem));
listDistinct.ForEach(elem=>Console.WriteLine(elem));
The result is:
two
four
five
six

By the way, if the first statement returns a List, you can chain template methods as follows:

myList.FindAll (elem => !bannedList.Contains(elem))
.ForEach(elem=>Console.WriteLine(elem));

But the easiest approach that does not involve creating a third list is to use the RemoveAll template method:

myList.RemoveAll(elem => bannedList.Contains(elem));

Easy Syntax to Print List Elements

Create a list.
List <string> myList = new List <string> { "a", "b", "c", "d"};

a list-printing delegate :
Action <string> print = elem => { Console.WriteLine(elem);};

a statement:
myList.ForEach(print);

Get Rid of a Blank Row When Exporting to Excel

When you export a GridView to Excel using C# code similar to this:

Response.ClearContent();
Response.ContentType = "application/excel";
Response.AddHeader("content-disposition", "attachment; filename=data.xls");

Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());
this.EnableViewState = false;

System.IO.StringWriter strWriter = new System.IO.StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(strWriter);

gv.RenderControl(htmlTextWriter);

htmlTextWriter.Close();
strWriter.Close();

Response.Write(strWriter.ToString());
HttpContext.Current.ApplicationInstance.CompleteRequest();

You end up with the first row being blank in Excel.

To fix this problem, you need to override the Render (HtmlTextWriter writer) method:

protected override void Render(HtmlTextWriter writer)
{
}