Removing decimal places in VS.NET 2008's Crystal Reports

There are times where the business requires numbers with decimal places to have those decimal places removed if the decimal value is zero. For example, it's better to display 707 than 707.00, but if it's 707.55, it still stays at 707.55. Here's how you can tell Crystal Reports to NOT use decimal places in this case.

1. Open up the Crystal Report.
2. Right-click on the decimal value field, then click on Format Object.
3. Select the Number tab.
4. Choose Custom Style, then click on the Customize button.
5. Click on the X*2 button next to the Decimals drop-down box.
6. Change it from Crystal Syntax to Basic Syntax.
7. Enter something akin to the following code:If ({TABLE.DECIMALFIELD} - Round({TABLE.DECIMALFIELD}, 0)) = 0.0 Then formula = 0 Else formula = 1 End IfWhere 0 and 1 are the number of decimal places.
8. Click on the Save and Close button.
9. Click on OK twice, then save the report.

SQL Server 2005's EXCEPT & INTERSECT Union-like Operators

SELECT name FROM master..sysobjects where name = 'all_columns' UNION SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns routes SELECT name FROM master..sysobjects where name = 'all_columns' EXCEPT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- all_columns SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'routes'; Results:name ----------- SELECT name FROM master..sysobjects where name = 'all_columns' INTERSECT SELECT name FROM master..sysobjects where name = 'all_columns'; Results:name ----------- all_columns

SQL Server 2005's Ranking Functions

You can create columns that provide unique ranking/sorting capabilities. For example, ROW_NUMBER() (produces unique row numbers):

SELECT ROW_NUMBER() OVER (ORDER BY name) AS ROWNUM, name FROM sysobjects; The preceding query produces the following results:ROWNUM name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 CoOp 2 Country 3 DF__dtpropert__versi__77BFCB91 4 DF_Carriage_CarriageTypeName 5 DF_CoOp_CoOpName 6 DF_Country_CountryName 7 DF_ExceptionLog_Occurred 8 DF_History_ActionDateTime 9 DF_History_ActionTakenByUserID 10 DF_History_Comments 11 DF_History_ETA 12 DF_History_LastNoticeStatusID ..... Or, there's RANK() (produces non-unique sorting numbers with possible gaps/skips in the sequence)...SELECT RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That query produces this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 3 @11data 3 @11data 5 @12data 5 @12data 7 @13data 7 @13data 9 @14data 9 @14data 11 @15data 11 @15data 13 @16data 13 @16data ..... Or, there's DENSE_RANK() (produces non-unique sorting numbers with NO gaps/skips in the sequence)...SELECT DENSE_RANK() OVER (ORDER BY name asc) AS RANK, name FROM master..syscolumns WHERE LEN(name) > 0; That will produce this:RANK name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 @10data 1 @10data 2 @11data 2 @11data 3 @12data 3 @12data 4 @13data 4 @13data 5 @14data 5 @14data 6 @15data 6 @15data 7 @16data 7 @16data ...... Then there's the interesting NTILE(n) function. This will produce partitioned/divided result sets with equal sequenced numbers, like this:WITH SystemObjects AS ( SELECT TOP 10 name FROM master..sysobjects ) SELECT NTILE(5) OVER (ORDER BY name) AS 'NTILE(5)', name from SystemObjects; That query produces the following output, with the numbers 1 through 5 having a set of 2 for each divided/partitioned section:NTILE(5) name -------------------- -------------------------------------------------------------------------------------------------------------------------------- 1 all_columns 1 all_objects 2 all_parameters 2 all_sql_modules 3 all_views 3 allocation_units 4 assemblies 4 assembly_files 5 assembly_modules 5 assembly_references

Creating Pivoted Joins in SQL Server 2005

You can take a vertical result set and turn it into a horizontal one, like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT DISTINCT tmp.Make, [380i], [M5], [M3] FROM @TBLTEMP tmp INNER JOIN (SELECT Make, Model FROM @TBLTEMP) PivotTable PIVOT ( COUNT(Model) FOR Model IN ([380i],[M5],[M3]) ) PivotColumns ON tmp.Make = PivotColumns.Make;

The preceding query takes a count of how many licenses belong to which BMW models, and displays them horizontally. The output should be something like this:

Make 380i M5 M3 -------------------------------- ----------- ----------- ----------- BMW 2 1 2

If were were to utilize a simple select statement, it would look something like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT Make, Model, COUNT(*) AS [Count] FROM @TBLTEMP GROUP BY Make, Model; The results being this: Make Model Count -------------------------------- ------------ ----------- BMW 380i 2 BMW M3 2 BMW M5 1

Random Data Sampling Queries in SQL Server 2005

The following query will return a random sampling of data:

SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (10 PERCENT); SELECT * FROM TBLAPPLICATIONLOG TABLESAMPLE SYSTEM (200 ROWS); Not only will the records returned be random, but so will the number of rows.

New Join Types in SQL Server 2005

CROSS APPLY (equivalent to an inner join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so CROSS APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get no null ColumnName fields because this is equivalent to an inner join, so only objects with matching columns or parameters, in the case of SPROCs and functions, will be returned.

OUTER APPLY (equivalent to a left join):

SELECT so.name as ObjectName, sc.ColumnName FROM sysobjects so OUTER APPLY ( SELECT name AS ColumnName FROM syscolumns WHERE so.id = id ) sc ORDER BY so.name, sc.ColumnName You will get any type of object, even non-tables, non-SPROCs, etc, will have their ColumnName fields as null, as this is equivalent to a left join.

Note that any object with no parameter or column will not have an any records in the syscolumns table (eg, SPROCs with no parameters).

Using Common Table Expressions in SQL Server 2005

Pretty cool stuff:

-- Common Table Expressions example: WITH tblDBs AS ( SELECT COUNT(*) AS [Number of Databases] FROM sysdatabases ), tblTables AS ( SELECT COUNT(*) AS [Number of Tables] FROM sysobjects WHERE xtype = 'U' ), tblColumns AS ( SELECT COUNT(*) AS [Number of Columns] FROM syscolumns ) SELECT * FROM tblDBs db, tblTables, tblColumns;

Results from my DB:

Number of Databases Number of Tables Number of Columns ------------------- ---------------- ----------------- 23 6 11258 (1 row(s) affected)

ASP.NET MVC - Validation of viewstate MAC failed.

Yup, View State is not supported in MVC, so beware of this. If you try to use a control that relies on it, you'll get the following error:

Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster.

Note that you can still use ViewState with ASPX/Web Forms as Views in MVC, but that violates the MVC pattern.

ASP.NET MVC - Preventing an Action Method from Being Called Publicly

By default, any action method defined in a controller class can be called via a browser request. To prevent this, simply mark the method with the [NonAction] attribute:

[NonAction] public ActionResult Index(int AreaCode) { return View(); } The result of calling a non-action method will be an HTTP 404 error: "The resource cannot be found."

ASP.NET MVC - Creating Routing Constraints

To restrict the format of the parameter passed to an action method, you can create a custom route with a regular expression to determine in what format you want the parameter.

1. Open up the Global.asax file.
2. Add the following routing code, right above the default route: routes.MapRoute( "Test1", "Test1/{AreaCode}", new { controller = "Test1", action = "Index" }, new { AreaCode = @"[1-9][0-9][0-9]" } );
3. Save the file and compile (Ctrl-Shift-B) and load the application (Ctrl-F5).

The controller action method (in the Test1Controller.cs class file) for the preceding route should look something like this (provided a View was created for that action result:

public ActionResult Index(int AreaCode) { return View(); }

Test1 refers to the controller, Index refers to the action, or method, and {AreaCode} refers to the parameter passed to that action/method. When it runs, MVC calls the following method: Test1Controller.Index(AreaCode). The "[1-9][0-9][0-9]" regular expression ensures that the parameter passed contain 3 digits, no more, no less. If the constraint is violated, a "The resource cannot be found." error code is returned.

The following URL is valid for the preceding example: http://localhost:????/Test1/111

The following URLs are NOT valid for the preceding example:
http://localhost:????/Test1/11x
http://localhost:????/Test1/11
http://localhost:????/Test1/1123