SSRS: Current Month Date Parameter Defaults

Now and again, I have to pretend that I am a report monkey. SQL Server Reporting Services makes that easy, since most of things are relatively intuitive. Today, I got to figure out (and by 'figure out,' I mean 'Google') the easiest way to create report parameters that default to the first and last days of the current month. The following is what I came up... Surprised that there is not a function to readily do this. So, without further ado, for my future reference, I present the following expressions.

The first of the current month:

=FormatDateTime(

dateserial(year(Today), month(Today), 1),

DateFormat.ShortDate -- Format as mm/dd/yyyy

)

The last day of the current month:

=FormatDateTime(

DateAdd(

"d",

-1,

DateAdd(

"m",

1,

dateserial(year(Today), month(Today), 1)

)

),

DateFormat.ShortDate

)

And free of formatting and comments, for your copy-and-paste convenience:

=FormatDateTime(dateserial(year(Today),month(Today),1), DateFormat.ShortDate)

=FormatDateTime(DateAdd("d", -1, DateAdd("m", 1, dateserial(year(Today),month(Today),1))), DateFormat.ShortDate)

Wolfram Fail

I have been looking forward to Wolfram Alpha going live. I am no longer so impressed. After trying to find how many state employees California has, to no avail, I asked a simpler question. It looks like, contrary to the hype, Google is safe for a while...

Perl: Convert PDF Files to Text

Using the CAM::PDF package, it is easy to extract the text from PDF files. The following script takes all PDF files from a directory and extracts the text of the entire file and writes it to a text file.

This script is also available at dba4Life.

use IO::Handle;

use strict;
use warnings;

use CAM::PDF;
use CAM::PDF::PageText;

my $PDFDIR = "./SomeSubDirectory";
my $pdf;
my %ddl;

opendir DDL, $PDFDIR || die "Error in opening PDF directory $PDFDIR\n";

while((my $filename = readdir(DDL)))
{
# Skip non-PDF files
next if ($filename !~ /\.pdf$/);

$filename = $PDFDIR . '/' . $filename;

if(!-f $filename) { print "\nCould not load $filename";}

# Name output file same as the PDF
my $output = $filename;
$output =~ s/\.pdf/\.txt/;

print "Creating $output...\n";
open(TXTFILE, '>' . $output);

# Load the PDF
$pdf = CAM::PDF->new($filename);

# Total number of pages within the PDF
my $pages = $pdf->numPages;

# Get the text for each page
for(my $x = 1; $x <= $pages; $x++)    {    print TXTFILE text_from_page($x);     }     close(TXTFILE); }  closedir DDL;     sub text_from_page  {   my $pg_num = shift;   return CAM::PDF::PageText->render($pdf->getPageContentTree($pg_num));
}

Simple SQL: Optional Parameters, Static Statements

Dynamic SQL, as wonderful a thing as it is, is horrible for security and maintenance. Your best bet is to avoid it. People often whine about making parameters optional. If you set the parameters that you are not using to null, this is a simple thing to do:

declare
@state char(02),
@zip char(05)

set @state = 'CA'
set @zip = null

select
Address
from
CustomerAddress
where
State = coalesce(@state, State)
and Zip = coalesce(@zip, Zip)

Simple and elegant.

Tags: sql

EC2-SQL: Competition in the Cloud

Not to be outdone by Oracle, Microsoft SQL Server will be available on Amazon's EC2 come late autumn. Read about it here... And you may want to read up on Microsoft's Windows Cloud operating system for developers at Slashdot. Is Microsoft adapting? Come on, now... It happens... Sometimes... Seldom.

And so goes another step for cloud computing...

Tags: amazon, mssql, MicroSoft

Sulphur Clouds! Oracle on Amazon's EC2

Oracle puts its 11g database in Amazon's cloud. With a title like that, what can I add? This is a well written article about Oracle on EC2. They are also supporting backups being shipped to Amazon. Anything else and I will be reiterating the article.

Tags: amazon, oracle

Oklahoma Finds Next Level of Stupidity

Wow. That is all that I have to say after reading this article:

Oklahoma Leaks Tens of Thousands of Social Security Numbers, Other Sensitive Data.

Technically, State information is public... But this goes beyond overshare. You just know that the application account has escalated privileges... I have to go back to my opening statement; wow. What a kick in the head...

Tags: security, sql injection

Bad Design: A Reminder to Document

Brian Walker writes about SQL Server database design disasters: What not to do. Unfortunately, these design failings are all too common. We all take different things from articles; my tangent is about a lack of documentation.

As a nerd, I would much rather do the fun part--creating, troubleshooting or anything else that requires thought. As a consultant, time and time again I have seen poor designs that are compounded by a lack of documentation. Not determining standards and and conventions, you facilitate others mutating a design with their own habits. Even if you have a poor system, the maintenance of it can be helped by documentation. Poor and consistent is a far cry better than poor and inconsistent. That makes it generally referred to as 'awful.'

It really does not take much time to document your approach. Simple documentation to outline the what and the why--even just a paragraph or two--might save countless hours down the line. Bad design is often perpetuated by lack of formalizing the business process. The least that a good database designer (or architect, if you prefer (I do--who doesn't want to be an architect?)) can do is document their approach; a naming convention document and a data dictionary. It is also important to do this before, during and after the design/implementation. Saying you will do it later typically means a rushed deadline is justification for not documenting (yeah, I have used that excuse too). Perhaps writing it out will key you into a couple weaknesses of your design... And wouldn't that make that horrible documentation process worthwhile?

Tags: design, documentation

Random: Data Presentation of Discretionary Spending

The New York Times has an interesting article about discretionary incoming spending, broken into a few categories. Numbers are always interesting, but what really got me was the presentation of the data--very cool way of showing the world.

More on Naming Standards

Ronald Bradford has a very nice article about naming conventions--and not just because we generally line up on our opinions.


Tags: design,
naming convention