.NET Playground

blog moved

2009-10-13T23:57:51-05:00:00

http://adenhertog.blogspot.com/

Reporting Services (SSRS) - Get data value into page header/footer

2008-11-06T11:46:15-06:00:00

In reporting services (ssrs), there are times where you need to get a value from a data source and put it into your page header/footer. The catch here is that ssrs does not allow you to access data sources within your header/footer. This is one way around it.

First the easy but inflexible way.

Create a textbox in the body of the report & set the value to what you want appearing in your header/footer
Set the name of your textbox to txtVarStore
In the properties of this textbox, set Visibility -> Hidden to True (ie: hide the textbox)
Create a textbox in your header/footer & set the value to the expression: "=ReportItems!txtVarStore.Value"

This will set the value of the textbox in your header/footer; but will only do it for the page that the hidden textbox is on.

The second way is a bit more involved, but persists across pages:

Go to Properties of your report
Go to the Code tab
Create a new public shared variable for your value, ie:
1. Public Shared Dim MyVar as String
Create a new function to set the value of this variable, ie:
1. Public Function SetMyVar ( ByVar var as String)
2. MyVar = var
3. End Function
Create a new textbox in the body of your report & set the value to the expression: "=Code.SetMyVar(First(Fields!colVar.Value, "DataSourceName"))"
Create a new textbox in your header/footer, set the value to the expression: "=Code.MyVar"

What this does is set up a new static variable (shared variable for you VB ppl), which persists once across the entire report. In SSRS, the body content gets generated first, which is why we can set the static variable using the SetMyVar function.

When it comes time for the header/footer to render, it simply reads out the value of the static variable.

Bit of a sleazy hack, but such are the quirks of SSRS

Google Chrome - Pros & Cons

2008-09-03T12:26:26-05:00:00

Opera, Safari, iExplorer, Firefox, and now Chrome. Like we didn't have enough mainstream browsers as it was. Google's attempt to grab a slice of yet another market has now been realised, but is their new Chrome offereing enough to get people to switch?

I've finally had time to put Chrome through its paces on some of the sites I've created & maintained, which are all ajax-based, css standardised, and javascript heavy. As a devout Opera 10 user, it's next to impossible to get me to switch browsers, but will Chrome have enough shine to move me?

To summarise:

Pros

Compiled Javascript - all the sites performed much faster
Site sandboxing - runaway scripts can be killed without having to crash the browser
Plugins - I'm against plug-ins in general (bar small amounts of flash and firebug), but allowing them to run in their own process space sounds like a good thing
UI look & feel - plus big points for minimalistic approach
Open source
Built-in DOM inspector

Cons

"Most Visited" sites home page - Browse histories don't do it for me. I need a programmable & static set of icons (Opera!)
No mouse gestures - again, maybe it's because Opera's spoiled me, but without mouse gestures, old-school 'point and click' is plain slow
"Beta" - fair enough it's just been released, but is there anything that ever makes it past beta from google (gmail anyone)? Perhaps they should just register the "beta" thing as a trademark and be done with it
No Synrchonisation - This was something that got released with the new build of Opera a few months ago. It lets you log into your browser, and have it sync your bookmarks et al regardless of where you are or which computer you're on.

Overall

Very nice attempt, if I was coming from IE or Safari, or possibly even firefox chances are I'd convert (though if I was a firefox fanboy, I'd stick with FF out of principal). Chrome beats all contenders when it comes to observable performance, plus the internal wiring's more advanced. However, when it comes to useability, it just doesn't size up to Opera.

Since Chrome's open-source, I'm sure it's just a matter of time until the community creates enough plug-ins to mimic the UI smarts built in to Opera; but until then, I'm willing to sacrifice a couple of seconds of site performance & load time, to the incredible amount of time saved with navigation.

Nice going Google, but I'll wait for the RC!

Free Silverlight and WPF goodness for all!

2008-08-15T07:58:56-05:00:00

WpfStyles.com (http://www.wpfstyles.com) launched the other day (hooray!). The site lets WPF and Silverlight developers and designers upload their components and share them with the community.

It's all free, so not only are you able to share your components, but you can also download those that other likeminded developers have put up there.

If you have any such components that the community will find useful, throw them up there. I urge you to get behind this resource and show your support.

Importing CSV flat files in SSIS - dealing with Double Quotes

2008-08-14T12:57:37-05:00:00

I've gone through a number of phases in SSIS. The first was fascination, the next was anger, then came love, and now I'm somewhere in the middle. One major flaw I've noticed is how SSIS handles double quotes (") in imported flat file data, or should I say how it doesn't handle it at all?

Simple scenario, I have a *csv file that I'm importing to a db table. The flat file is all text delimited using double quotes. A number of the fields contain literal double quotes, which in any CSV text delmited implementation you would normally escape by using another double quote delimeter. To illustrate:

This:

he cried out "SSIS!" in frustration

Becomes This:

"he cried out ""SSIS!"" in frustration"

Simple, standard csv behaviour, parses fine in excel.

Try importing to SSIS and your import will crash out, and if you try to preview that row you'll get: "The flat file parser does not support embedding text qualifiers in data"

Solution? The only way I've found to get around this is to write your own escape functionality. I piggy-backed off html encoding and replaced literal quotes with " , and then used derived columns with REPLACE(ImportedField,""","\"") to change them back prior to putting them in their destination table.

I hate having to write work arounds like this, especially for functionality which should be part of the underlying core system. If anyone has a better way, please drop your comments in below.

Sql Server - How to split time into n minute groups

2008-04-29T13:30:11-05:00:00

Another little data manipulation I've had to do recently is to split a heap of sql server datetime values into 15 minute segments (eg: 10:00, 10:15, 10:30 etc). Although there's no inherit function that I know about in sql server that achieves this, there's a nifty little trick using the modulus operator (%) that can do this.

select
   case 
      when datepart(hour, DateTimeval) < 12 then 'AM' 
      else 'PM'
   end as Meridiem,
   Datepart(hour, DateTimeval) as Hour,
   case 
      when (datepart(minute, DateTimeval) % 15 ) < (15 / 2) then datepart(minute, DateTimeval) - (datepart(minute, DateTimeval) % 15)
      else (datepart(minute, DateTimeval) + (15 - (datepart(minute, DateTimeval) % 15))) % 60
   end as QuarterHour
from
   MyTable

Where DateTimeval is the column that holds the... date time value.

Firstly the < (15/2) part determines the range of the midpoints (ie: the midpoint 15 ranges from 7.5 to 22.5). Next it gets the minutes part from the value (eg: 10, 49, 15, etc), then does % 15. This will give the remainders (10, 04, 00). If the remainder is less that the range value (ie: < (15/2)), then by simply subtracting the remainder from the original value will give us the midpoint (eg: N/A, 49 - 4 = 45, 15 - 0 = 15).

If, however, the remainder is > (15 / 2), then we need to add the difference of (15 - remainder) to get the upper midpoint value (eg: 10 + (15 - 10) = 5, 45, 15).

And voila, the date time values are segmented into 15 minute chunks.

Of course you can substitute 15 with however many minutes you want to segment by (eg: 5, 10, 30 etc).

Performance has been Ok. I ran this over 200,000 records and it took about 3 seconds on a shared dev server. Not bad.

Generating List of Dates

2008-04-28T10:15:05-05:00:00

At times, especially when working with cubes with date/time dimensions, it's necessary for me to dynamically generate a list of date values for use in those dimensions. That single date dimension often services a number of cubes, and the range of date values can be determined from the min/max date values from serveral tables.

I've written the following SQL that will retrieve the min/max date values, and create a single record for every day in that min/max range. It's useful to turn this sql into a view which will refresh itself automatically every time the cube calling it is processed.

select
   date,
   datename(dw, date)
   + ', ' + datename(mm, date)
   + ' ' + datename(dd, date)
   + ' ' + datename(yy, date) [datename],
   'Fiscal ' + datename(yy, date) fiscalyearname,
   datename(mm, date) 
   + ', ' + datename(yy, date) fiscalmonthname,
   datepart(day, date) as dayNumber,
   datepart(month, date) as monthNumber,
   datepart(year, date) as yearNumber
from
   (
      select
        dateadd(
            day, 
            n1.val * 10000 +
            n2.val * 1000 +
            n3.val * 100 +
            n4.val * 10 +
            n5.val,
            cast(floor(cast(mindate as decimal(12,5))) as datetime)) date
      from
        (
        select 0 as val
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4 
            union all select 5 
            union all select 6 
            union all select 7 
            union all select 8 
            union all select 9) n1,
            (
        select 0 as val
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4 
            union all select 5 
            union all select 6 
            union all select 7 
            union all select 8 
            union all select 9) n2,
            (
        select 0 as val
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4 
            union all select 5 
            union all select 6 
            union all select 7 
            union all select 8 
            union all select 9) n3,
            (
        select 0 as val
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4 
            union all select 5 
            union all select 6 
            union all select 7 
            union all select 8 
            union all select 9) n4,
            (
        select 0 as val
            union all select 1 
            union all select 2 
            union all select 3 
            union all select 4 
            union all select 5 
            union all select 6 
            union all select 7 
            union all select 8 
            union all select 9) n5,
            (
        select 
            min(minDate) as minDate, 
            max(maxDate) as maxDate 
        from 
            (select 
               min(date) minDate, 
               max(date) maxdate
            from 
               <Data table 1>
 
            union all
 
            select 
               min(date), 
               max(date) 
            from 
               <Data table 2>) dateVals
 
        where 
            year(mindate) >= 1980
            and year(maxdate) <= dateadd(year, 1, getdate())
            ) c
      where
        dateadd(
        day, 
        n1.val * 10000 +
        n2.val * 1000 +
        n3.val * 100 +
        n4.val * 10 +
        n5.val,
        cast(floor(cast(mindate as decimal(12,5))) as datetime)) <= maxDate
      ) dateVals

Automatically Create Install Scripts with Sql Server

2008-04-18T12:11:53-05:00:00

Problem: How can I QUICKLY create an install script automatically in sql server every time I change my database? Creating an install script in sql server is pretty easy thanks to the "Script Database As..." command. However, this is generally a long winded process that doesn't give you too much control.

In this post, I propose a solution to create build scripts with the following requirements

Don't need to write any code
Easily maintainable
Can script schema AND data
Can specify which objects to script
Can run automatically before an MSI setup/deployment project is built
It has to be FREE

The first thing you need to do is download Microsoft's Database Publishing Wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en)

Next, in your development sql server, add two new accounts named: ScriptSchema and ScriptData. Make sure they are created as a sql login. At this point, don't give them any other access than public access to the database you want to script.

Open the database you want to script. For each table/view/stored proc etc that you want to include in the install, grant view definition access to the ScriptSchema user as follows:

grant view definition on <schema>.<object name> to scriptschema

Next, for each table that you want to script the data for, grant select permission on that table to the ScriptData user, ie:

grant select on <schema>.<table name> to scriptdata

You can double up, so for instance if you want to script the creation and population of data of a certain table called [dbo].[customers] you'd run the two commands:

grant view definition on [dbo].[customers] to scriptschema

grant select on [dbo].[customers] to scriptdata

Once you've set up the permissions, you can check out what will be scripted by connecting to the database as the scriptschema and scriptdata users. As you've now defined what will be scripted, it's time to do the scripting itself.

Jump out into a cmd.exe, and run the two commands;

sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptschema; pwd=password;" schema.sql -schemaonly -f

sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptdata; pwd=password;" data.sql -dataonly -f

The first command connects to the database using the scriptschema account, and generates the definition of all the objects it can see, ie: those which you gave grant view definition access to.

Likewise the second command connects to teh database using the scriptdata account and generates the scripts to insert the data, based on all the tables it can select from.

You now have two scripts: schema.sql, data.sql. You can add these to your msi/setup projects in visual studio (outside the scope of this post). Furthermore you can then add the two commands mentioned to the pre-build events of your setup's resource project to build the install scripts prior to the MSI etc being built.

I really like this solution to automatically generate sql install scripts. I feel it's elegant, there's no code to be maintained, and no manual updating of sql scripts every time you change the database.

Get date without time in Sql Server

2008-03-31T11:45:33-05:00:00

There are times when you need to write a query joining on dates, that is the day of a date, but not worry about the time portion. Without using the between x and y, as sometimes this isn't possible from places like SSAS Data source views, what's the most efficient way to remove the time part of a datetime?

There are a number of ways to drop off the time part, as shown here:

select CONVERT(datetime, CONVERT(varchar(10), c.date, 111), 111) from dbo.phonecalls c
go

select cast(CAST(YEAR(c.date) AS VARCHAR(4)) + '/' +
           CAST(MONTH(c.date) AS VARCHAR(2)) + '/' +
           CAST(DAY(c.date) AS VARCHAR(2)) as datetime) from dbo.phonecalls c

go
select CAST(FLOOR(CAST(c.date AS DECIMAL(12, 5))) AS DATETIME) from dbo.phonecalls c

After clearing the query cache for the server (dbcc freeproccache) I executed the above in sequence, and got the following execution times:

150871

186319

94119

So it appears that casting the date to a 5 point decimal, dropping the decimal portion, and then casting back to a date time is by far the most efficient. I'm putting this down the fact that it's a completely numerical operation, and doesn't resort to casting to different value types (ie: varchar).

Sanitise your database input

2008-01-31T08:13:42-06:00:00

Saw this little strip today from http://xkcd.com/

SQL injection's a nasty by-product of poor development. Anywhere a user can enter data, there's the potential for an exploit. In my opinion, performing input validation on the web/ui layer is repetitive and insecure. I've always taken the approach to move all that to the Data layer.

Here resides a few exclusive set of methods that are the only ones that can interface with the database. Performing input checking here ensures that everything that goes to the database isn't going to cause any grief. Furthermore you can do it once and not have to worry about validating input from every new field you put on your display layer.

Furthermore it's unecessary and bad practice to be generating sql in any of your layers except for the few times when it's necessary to gen dynamic sql in stored procs. Effectively if you design towards these sorts of goals, you can pretty much guarantee little Bobby tables won't be causing you any issues.