Thursday, November 25, 2010

Age this month calculation in Crystal Reports

I keep having lots of fun developing enterprise reports with the excellent Crystal Reports.

Today I needed to calculate the age of a person for this month. The typical use of this formula would be something like “determine if an employee is turning 65 years old this month).

I was able to come with the following Crystal Formula Syntax:

// Age this month calculation 
WhileReadingRecords;
dateTimeVar _1stOftheMonth := CurrentDate;
dateTimeVar LastOftheMonth := CurrentDate;

// Get the run date and make it the 1st day of the current month
_1stOftheMonth := Date(Year(_1stOftheMonth), Month(_1stOftheMonth), 01);

// Get the next month's 1 st day. Subtract 1 day to get the last day of the current month
LastOftheMonth := DateAdd( "d", -1, DateAdd("m", 1, _1stOftheMonth) );

// Proceed with the age calculation taking into consideration the leap year
if (Month(LastOftheMonth) * 100) + Day(LastOftheMonth) >=
(Month(CDate({Employee_Master.BirthDate})) * 100) + Day(CDate({Employee_Master.BirthDate}))
then
Year (LastOftheMonth) - Year(CDate({Employee_Master.BirthDate}))
else
Year (LastOftheMonth) - Year(CDate({Employee_Master.BirthDate})) - 1;



An interesting nuance about this formula: if one substitutes the CurrentDate (on line 3 and 4)  to an event date of interest (being a certain date or a database field) it is possible to calculate the age on this specific date!

Sunday, November 7, 2010

Free SQL IDEs Review – SQL Everywhere vs. Toad for SQL Server

image

Recently I have been given a short term assignment to develop a number of reports in Crystal Reports.

Upon arriving to the client location I quickly found out that the only tool I will be using to create the reports is basically the Crystal Reports itself.

My initial look at the database quickly revealed that the database structure is far from perfect and that it has some confusing table relationships, another hurdle was in the fact that several columns in various tables were user customizable for values to hold without any hint onto where the value is used and the relationship to other database objects.

I definitely had a challenge figuring out what and how the right SQL queries should be developed. To my dismay the matters were marred even further by my account being read-only so the possibility of creating database diagrams to visualize the data was not feasible.

After some short peering into what links Crystal Reports produces I realized I am lucking a tool that can close the gap.

Just as an aside, every morning I am normally reading a few blogs, one of them I literally read while having my first cup of coffee is an excellent one by Chris Alcock The Morning Brew in which it was just not long ago mentioned about the free tools for SQL Server by Aaron Bertrand. Bingo! I thought, I need to take them for a test drive! Not knowing anything about the tools I decided to download both.

A short wait for the downloads and Toad for SQL Server 5.0 gets installed first. Surprisingly, the free license is only for a few people in an organization (clever!). Well, it impressed me with a clean interface, but cluttered with panes and panels it quickly made me a little slow navigating here and there, my first 5 min and I think these are the most critical ones for the software spent and I see something – Object Explorer:

image

this must be the one I murmured to myself that has the ability to find an object based on its name. Nice, but it turned out to work only as a filter on the top level objects, so for example if a database is highlighted then applying a filter it will only show objects matching the criterion. Not particularly useful. At least for me, I needed to find where a specific column name is used. Hmm, let’s give SQL Everywhere a try! OK, it is installed in a snap! My first ehh, seconds, I find in View the Object Browser, good, search for my objects, whoa! I got a large set of results to go through! Nice, I got my columns, tables, indexes, functions and what not listed, gosh, even the T-SQL for this object as to be added is shown:

image

Cool, and besides, in addition a streamlined version is available through the Objects tab on the Explorer pane:

image

Double-clicking on the column name takes to it in a given table.

Good now we are ready to write some useful SQL queries.

Well, I did not want to give up yet on Toad for SQL Server and went on to writing a complex join. So I am typing in SELE… hmm, I notice the intellisense does not pop up with suggestions, well all right, after all I know how to type, now seems it does not support suggesting on keywords, or it may be my impression. Now when I arrived to the join part to another table the intellisense does show up and tries to guess my join, and to my amusement the choice at the top actually makes sense. Not bad! Now I try to do the same in SQL Everywhere and and start with typing SELE… wow wait what is going on? Ahm three choices appeared: scf, ssf and st100, I hit tab and get nice! Similarly ssf expands to and respectively.

Not a bad productivity improvement for free!

OK, now some ugly T-SQL Code is churned I would be ashamed to copy and paste to a Command in Crystal Reports, let’s beatify it first! OK, I still love Toad, ahm, where SQL Format or something like that is? It is there but disabled, well, turned out it is disabled in SQL Everywhere too, pity! No problem, I can that even without installing a single piece of software, going to http://www.sqlinform.com/online.phtml and whoa I have my long SQL looking good!

I thought I deserve to rest and I started to play around with the settings, surprisingly, both editors offered a lot of customization, Toad even made me laugh by offering a pumpkin skin and a Valentine’s skin:

image 

What a jolly amphibian Smile!

(I guess the creators of it thought a developer would change its appearance based on kind of a holiday approaching).

SQL Everywhere sports a more humble approach to its appearance and to me it looks more tasteful and more importantly, practical. These are simply color schemes.

As far as the productivity goes, both tools offer a good code snippets support, though SQL Everywhere has a more functional set of snippets, one thing, it is only very short.

Now let’s highlight some interesting differences. The first that comes to mind is how the tools handle the connection to the database. While SQL Everywhere offers an on-demand connection (default) thus unloading the server, Toad seems to not to offer any customization of this kind. Others are “As early as possible” and “On First Use”.

Another nifty feature is “Execute Here” (if you click on a database name). It is particularly useful if you want to run the same SQL statement against one database and the another, for this purpose I guess it offers data set comparison features but it was not available in the free editions, I guess it would be as easy to do by dumping the results to a file and compare using say ExamDiff.

For database administrators viewing active processes in SQL Everywhere (by pressing F2)

image

is just so simple it blows the mind compare to how you get this in SSMS, sure this feature is gonna be loved!

In closing, I would highlight some features I found lacking or not to may satisfactory:

First, I am bothered a lot by both IDEs not able to output the results of a query to text. However it is easily compensated by the “export to csv” functionality.

Second, is inability to edit data right in the grid in SQL Everywhere.

Third, while SQL Everywhere offered search within the grid with results, none of this appeared working in Toad (I am curious if this does work in the paid version of the product).

Speaking of the prices, Toad’s Pro version (the cheapest option) is $595 USD vs. $199 for SQL Everywhere, a big leap for not so much more I am tempted to say, also curious if this is the result of the so called “Bran Recognition” paradox marketing?

Well, all in all, both tools are a very good compliment to the SSMS and I hope will wet your appetite for exploring them more and contributing to your bottom line!

As it stands now, my first choice is SQL Everywhere for more flexible, practical and streamlined (e.g. macros support) database development experience.

Monday, May 17, 2010

Useful for your technical blog MSDN Widget with the latest MSDN content

Useful for your technical website or blog: latest MSDN content and news using MSDN widget!
Check out here http://www.msdnwidget.com/ the new MSDN Widget is there. You can customize the content feeds, widget size and skin it with your logo, too.

in reference to: MSDN Widget Generator (view on Google Sidewiki)

Monday, April 26, 2010

The best seen by me so far site dedicated to IIS!

The best site dedicated to IIS I have seen so far!
It is an all-in one portal on valuable resources about IIS including learning, downloads and links to best blogs.

in reference to: The Official Microsoft IIS Site (view on Google Sidewiki)

Sunday, April 18, 2010

What’s new in .NET Framework 4 Client Profile


What’s new in .NET Framework 4.0 - Client Profile


Client profile is a new feature aiming at reducing the size of the installation package for desktop based applications and also reducing the possibility for attacks.

In Visual Studio 2010 this will be the default mode for installations unless you are installing a server-side program as say an ASP.Net Web application.

For a complete list of details and to obtain more information please visit the posting by WPF performance and .NET Framework Client Profile team on MSDN http://goo.gl/cjaS


DuckDuckGo - a new kid on the block of search engines

DuckDuckGo is a new kid on the block of the search engines, but certainly raises the bar in terms of uncluttered search results delivery, no personal or usage data collection and at the same time adds some interesting features as searching with !, shortcuts, direct websites hits e.g. !tw will take you directly to Twitter.com so say if you type !map Aruba in the search bar it will show you the map of the island.
Interestingly, the maps and some other online resources get pulled in from Google.

This mighty Duck even dare to compare itself with Google!



Friday, March 26, 2010

The Web Developers' World is fascinated about JQuery, here is another reason to cheer - jQueryPad!

If you liked SnippetCompiler to quickly test some .net code without starting Visual Studio then you will definetly like jQueryPad - the most elegant and slick way of building your jQuery code.

Thank you Paul!

in reference to: jQueryPad (view on Google Sidewiki)

Wednesday, February 17, 2010

A very useful list of .net libraries

Today The Morning Brew #541 listed a very useful list of .net libraries. I think it is the most concise, yet complete list of them, very handy, thank you Chris (http://blog.cwa.me.uk/feed/)!

in reference to: 60+ .NET libraries every developer should know about. (view on Google Sidewiki)

Thursday, February 4, 2010

The most concise and comprehansive cheat sheets collection on the web!

It seems this is the most concise and comprehansive cheat sheets collection on the web for developers!

PS: Found through DonationCoder.com

in reference to: Cheat Sheet and Quick Reference Card Directory | devcheatsheet.com - Cheat Sheets for Developers. (view on Google Sidewiki)

Thursday, January 14, 2010