Executive reports are usually done in a cross-tab manner. The problem with developers is how to do this out of the SQL code directly without any help from Report wizards.

Usually, when you do a select statement, it will just produce all the records row after row. just like this:

SELECT Job, LengthStay, Salary FROM Employees

Results:

1. Clerk , 10 , 12000

2. Clerk, 15, 14000

3. Mgr , 20, 15000

4. Sales, 15, 11000

To make it a cross-tab report , just simply use a CASE statement:

SELECT  Job,

SUM(CASE WHEN Lengthstay = 10 THEN Salary ELSE 0 END ) as 10Years,

SUM(CASE WHEN Lengthstay =  15 THEN Salary ELSE 0 END ) as 15Years,

SUM(CASE WHEN Lengthstay = 20 THEN Salary ELSE 0 END ) as 20Years

FROM Employees

GROUP BY Job

This post is more for myself, so I can go back here and check out how to write codes to query against an Entity Framework.

.NET Framework 4 , and future versions, will be focusing more on Entity SQL more than LINQ. Here’s a sample of how you can select 1 or more records given a condition.

1. Built the Entity instance

SignUpEntities _db = new SignUpEntities();

2. Create an Object Query 

ObjectQuery<orderhist> query = new ObjectQuery<orderhist>(“SELECT * FROM orderhist WHERE orderid = @orderid “, _db);

*note, this uses System.Data.Objects class

3. add paramters to the query

query.Parameters.Add(new ObjectParameter(“orderid”, _orderid));

4. Create a list and run, to allow code to iterate over the records

List<orderhist> _orderhist = query.ToList();

Sample full code

Read More

I saw this site when I was reading Smashing Magazine (www.smashingmagazine.com) and instantly, I got hooked. Refreshing and inspirational. Check out the daily winners.

www.thecssawards.com