Michael Könings Blog

How To: Create Age Pyramid Charts in Reporting Services 2008

admin — Mon, 29 Jun 2009 08:40:00 GMT

I had to create charts for a HR department that show the distribution of age (or years of service) and gender in organization units. The customer wanted that typical Christmas tree look, which shows a symmetric bar chart in two directions, like shown in the example below.

There are two challenges here:

make the tree look symmetric, the left and right bars should be in line.
make the labels and axis show positive values, even on the left side of the graph.

How can you achieve that in Reporting Services ?

We use the Dundas chart control with the Bar Chart layout. A stacked bar chart did not work too well for me, it did not put the labels on the tip of the bars.

To have the values point in to directions multiply one set of data with -1. you can do that in your query or by editing the value expression.

The value expression for the data column might look like this:

=IIF(Fields!Gender.Value = "Male" , Sum(Fields!Head_Count.Value),Sum(Fields!Head_Count.Value)*-1)

Since we have gender on one axis, it will show the values in two different colours.

The default settings of the bar chart will draw the different bars with a little offset (side by side), so it would not look symmetrically. I found this parameter that sorts this one out:

the Custom Attribute DrawSidebySide of the Chart Series should be set to False. Now the two bars for each gender in one time period are in line nicely.

What's left to do?

We need to take care of the negative label values for the gender pointing to the left. You guessed how I did it:

change the series label Format property and the Axis LabelsFormat to your wish:

0;0 did the job for me.

That's it! Maybe you have a better way to do it ? Let us know! Put a comment!

Too brief ? ask questions!

Technorati-Tags: Reporting Services,HowTo,Charts,Age pyramid,RS2008

How To: get a RSS feed from BlogSpot filtered by a Tag

admin — Thu, 11 Jun 2009 12:30:00 GMT

by default Blogspot offers Atom feeds, which can not be digested by some systems (like our Community Server)

but there is help! try this syntax :

http://yourfantasticblog.blogspot.com/feeds/posts/default/-/TheTag?alt=rss

i put this here so I don't have to remember it next time I need it

Cloud Computing In Even Simpler Words

admin — Wed, 15 Apr 2009 07:55:00 GMT

I like this one too, it emphasizes the scalability of cloud computing a bit more.

A bit like "Sendung mit der Maus" style, but an excellent presentation! (check the feedback on youtube)

I hope you enjoy it, it's only about 5 minutes long.

Cloud Computing explained in simple words

admin — Tue, 14 Apr 2009 09:35:00 GMT

Ok, maybe a bit too simple for a geek like you...

Anyway, just watch it in your coffee break, and you'll have a basic understanding of what it's all about.

Oslo is here!

admin — Sun, 02 Nov 2008 13:53:00 GMT

at the PDC Douglas Purdy (together with Vijaye Raji) finally presented Oslo (quite entertaining, it's long but it's worth it)

Oslo comprises 3 things:

A language: "M" to model domain specific models and languages (grammars)

A tool: "Quadrant" to visualize models and to give it simple views (yes views , like in MVC).

A repository: (basically a SQL Server database) to store models and data

find more information here, the SDK is ready for download.

what's so special about Oslo?

Microsoft skipped the classic UML/MDA thing and started something completely new. Well, not that new, actually, people (outside the MS world) do use dynamic languages (especially Ruby) to create DSLs for quite some time now. But it's good to see that MS picks up good ideas [1].

It's all about better readability, less (better: no) plumbing code, low noise (less braces and brackets, semicolons, declarations, typing, initializations, castings etc.) . All this is intended to gain productivity: we can express our intent directly in simple code, whoever needs to read it will understand it better.

Microsoft takes these concepts and puts it on steroids: all dynamic, visual tools, Intellisense , all the goodness. The complexity of WCF, WF, EF and any other complex domain can now be expressed in domain specific code, we (the developers) do not have to deep dive into XML (XAML...)and APIs in all its flavors anymore (you can still do that if you need to, its all still there).

Check out the upcoming shows on this topic, they will explain the modeling language "M" in more detail.

That's the beginning of a new era for Microsoft. The post- "write to much code and deal with clumsy XML files" era. Very cool. ;-)

[1]

Check out RSpec (a language to describe expected behaviour for Behaviour Driven Development), HAML (a better MVC-View language for creating HTML) and SASS (same thing for CSS) to see what I mean (I forgot YAML, which is a human-friendly data format). BTW, All these great tools live in in Ruby on Rails. For me, DHHs (the creator of RoR) greatest achievement is that he opened our eyes: development should be as clear and simple like Danish Design.

Don opens the Box on Oslo

admin — Thu, 16 Oct 2008 19:33:21 GMT

Don Box and Doug Purdy where on Dot.Net Rocks. I was quite excited to listen to them this morning on my morning commute to Basle. But I'm still confused: what is Oslo ? They still where talking in riddles, not telling any details. There are some traces in the blogs, like Doug put it in 3 bullet points:

To that end, we have boiled down Oslo to three very simple things:

A tool that helps people define and interact with models in a rich and visual manner
A language that helps people create and use textual domain-specific languages and data models
A relational repository that makes models available to both tools and platform components

That is it. That is all Oslo is. Oslo is just the modeling platform.

On the DNR show Doug said things like: "the next big thing" ...

well, let's see what they will show on the PDC starting October 27th. There will be 6 sessions about Oslo.

How To Do a Scrum Project With Busy Consultants

admin — Fri, 15 Aug 2008 12:20:00 GMT

Inspired by the great interview on DotNetRocks with Stephen Forte, who leads Scrum style agile projects with teams distributed around the globe, I decided: it must be possible to do agile project management with tools that are reachable from everywhere through the Internet. The members of my current project team do not always work together at the same place, they all have tightly scheduled calendars, hopping from one customer project to the next. But almost everywhere they have their mobile phones and a computer in reach.

One rule in SCRUM is the daily stand-up meeting, where every team member tells what he or she was doing the last day , what is next and what impediments keep her or him from continuing the work.

Normally you will do that in in front of a board with index cards, where all your tasks of the current sprint are written on. The idea is to have a simple visual representation of your work items. The index cards and a simple spreadsheet (the "scrum sheet") that records the state of the tasks is all you need to do your meeting. But how can you do that when not everybody is in the same room ?

For backlog and sprint management we use the hosted VersionOne Team Edition

This is a free online tool (a V1 representative will call you back) to manage agile projects, it allows up to 5 team members. The clean Ajax-style UI makes it really easy to use.

it sports a very cool standup dashboard which emulates the index card/scrum sheet view in the browser.

this view is interactive, so every connected team member can move tasks around, change task information and so on. Changes will be reflected (after clicking an update button) on all browsers. This can be a great tool to do a "distributed" scrum meeting. I will tell you more about our experiences with it, when we used it for a while...

There are other tools out there, (you probably heard about basecamp from the RoR Company 37Signals). I decided to use V1 because it is really focusing to support scrum. Mingle from Fowler's ThoughtWorks was another candidate, but they don't offer a hosted version!

For team time management we use a simple google calendar. Its the easiest way to share information about the presence of the team members. you can access it from everywhere, even with smartphones and PDAs. you can integrate the google calendar with exchange and other systems to your desire.

stand-up meetings: a conference call is perfect for this. there a lots of providers to make it as simple as possible. all you need is a appointment time, a telephone and a pin number. Services like powwownow make that very easy. Or use Skype ! The biggest challenge is to get every person involved sitting in front of a computer at a certain time...

Code Management: my company has a subversion service running that can be accessed from the Internet. If you are not in this comfortable situation: try project services like assembla, they offer complete "team spaces" for free, including subversion and other helpful tools for collaboration.

Build management: our current project is about BI, so we don't need a classic build or even continuous integration environment to build our code base yet. But the next project will come soon... There is something coming up but I'm not supposed to talk about it yet . :-)

I will let you know how things worked out...

Entity Framework V2 day one

admin — Tue, 24 Jun 2008 10:31:00 GMT

i think this and this is good news: MS takes a second try to make the ADO.NET Entity Framework a really good product that helps developers to create a data access layer with a clean object model. This time, the community will be involved to help make the framework ready for the challenges of "real world" projects. The section in this initial blog post about Transparency in Design says a lot about the new spirit that lives in the MS development teams.

Some key points that will be added or improved, according to the blog post:

Persistence Ignorance (POCO)

N-Tier Support (DataSet like experience)

TDD Scenarios

Lazy load

So: get involved, look out for downloadable and testable bits and help make EF2 a great product :-)

altdotnet.org : the movement for alternative programming with .NET found a home

admin — Thu, 29 May 2008 10:48:00 GMT

There is so much new stuff that we ( the .net programmers) should know about. the growing Open Source .NET community (and Microsoft's P&P too) pushes out new tools and technologies almost every day. Who keeps pace with O/R Mappers, MVC frameworks, IoC Containers , agile Development methods and practices, etc?

Altdotnet.org is a movement that started in blogs and forums that deal with all the mentioned items. Now it found a home in altdotnet.org.

just read the welcome text, I can't explain any better what it is all about:

you need an openid account to log in (another thing worth learning about :-) ) .

Don't expect too much right from this website, it is just a starting point.

There is an excellent WIKI ( follow the 'shape' link) that tries to put together a one-stop shop for all the different topics with loads of links and references (books).

Its a community based site, everybody can help to make it grow.

The community is not just virtual, there are real-world gatherings ( meetings and conferences) too.

So, if you want to learn more about these new technologies and want to be part of this movement, give it a try!

Silverlight 2.0 : I will wait...

admin — Sun, 23 Mar 2008 14:49:00 GMT

check out the latest Silverlight 2.0 Demos from Infragistics and Telerik:

How long would it take you to create controls and animated behavior like this, if you would try to do it by yourself ?

If you are not a skilled graphics designer (or have one on your team) , well, I would propose we concentrate on the code. That's probably challenging enough :-)

Cool Tool: Mindmeister online shared mindmaps

admin — Fri, 21 Mar 2008 20:42:00 GMT

Mindmaps are great if you are trying to grasp a complex matter. Just start to write down terms and short sentences that come to your mind an connect them visually into a hierarchical, tree like structure. Normally you do that on a cocktail napkin in the lunch break with your colleagues. Maybe you are a geek and you use software based tools to draw nice maps. But how to share them with your team members and colleagues if they don't have the same tool ? How do you capture ideas in a team, like you do with a sketch on a simple piece of paper, if you are miles apart ?

Try Mindmeister. The fine folks at mindmeister.com created a stunning tool that is stuffed with features.

You can create mindmaps (online and offline).
Share mindmaps (with a closed group or public).
Export them (GIF, PNG, PDF, common mm tool formats).
Collaborate (real time brainstorming, as they call it).
Embed your mindmaps (URL, IFrame) .
Subscribe to change notifications (Email and even Twitter).
Use the API to program your mindmaps (REST based).

This is how the online editor looks like, a fine piece of AJAX in perfection:

this is the link to my public .NET buzz mindmap (you can do better, I know.) .

this is the code to embed a (view only) mindmap in a html page:

 <iframe width="600" height="400" frameborder="0"  src="http://www.mindmeister.com/maps/public_map_shell/5294051?width=600&height=400&zoom=1" 
scrolling="no" style="overflow:hidden"></iframe>

check how it looks like here (move it around like a google map):

this is an exported GIF (click on it) :

So: If you like mindmaps and work in a distributed environment: give it a try!

How-To: clean up the Visual Studio Help mess

admin — Sun, 03 Feb 2008 17:12:00 GMT

Is your's messy too? After installing an uninstalling tons of tools, updates, libraries frameworks etc., mine was a big mess. I found an easy way, that helps you to get rid of unwanted stuff in the help system and also get back the good stuff you were looking for but couldn't find it anymore.

There is no option to manage the help files from the menu. Even in the Help on Help section, I couldn't find a hint !

Actually its very easy, but quite hard to find:

Open the Help Application (just hit F1 in VS). In the search box, enter "Visual Studio Combined Help Collection Manager"

A page with that name should be found. It has some checkboxes, depending on the stuff you have installed. Check the entries you need, uncheck the ones you don't need and hit the "Update" button.

Next time you start the Help System it will take some time to merge the help libraries. After that, everything should be fine!

nice and clean...

How-To: add geographical information to IIS log files with LINQ

admin — Wed, 23 Jan 2008 17:52:00 GMT

LINQ in action: a real life problem, solved with a few lines of easy-to-read code. In my last post, I promised to provide a solution to enhance the information we can derive from IIS logs with the help of Log Parser. All you need is the free Geo-IP database (updated frequently) that can be found here plus the free C# API that is provided by these generous folks.

create a (less messy :-) ) little command line program that has a LINQ statement like this:

using System;
using System.Collections.Generic;
using System.Linq;


namespace CountryLookupProj
{
    class SumColumns
    {
        static void Main(string[] args)
        {
            string[] lines = System.IO.File.ReadAllLines(args[0]);

            int exam = int.Parse(args[1]);


            AddSingleColumn(lines, exam - 1);
            Console.WriteLine();
            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }

        static void AddSingleColumn(IEnumerable<string> strs, int examNum)
        {
            CountryLookup countryLookup = 
                new CountryLookup(@"C:\Stuff\GeoLiteCity\Geoip.dat");
            var columnQuery =
                from line in strs
                let x = line.Split(';')
                select countryLookup.lookupCountryName(x[examNum]) 
                       + ";" + String.Join(";", x);

            System.IO.File.WriteAllLines(@"c:\temp\output.log",
                                         columnQuery.ToArray());

        }

    }
}

run it like this:

c:>\myprogram.exe "mylogfile.CSV" 1

the 1 is the (one-based) position of the ip-address column in your CSV-file.

thanks for the inspiration found here (MSDN)

How-To: SQL query IIS Logs with Log Parser

admin — Thu, 10 Jan 2008 21:02:00 GMT

Face it: the built-in reports in Community Server are not good enough if you want detailed information about your traffic. Google Analytics is great, but you can't export the data in order to use it in your own queries. A better way is to analyze the IIS log files directly and join the information with the CS database tables. But how can you find useful data in these huge files?

First of all: what do you really want to know?

I wanted to answer the following question: How many request do we get per day and blog post ?

I'm only interested in "real people" requests. So we need to eliminate all requests from bots and feed readers.
I only want to count page requests, so we need to eliminate all requests to images and other files.
I only want to count blog page requests, no navigation on tags, or other pages.
I only want to count successful requests.
I want to use the data to join it with CS Database data, in order to query additional meta-information (Author, Blog Name, Post creation date etc.) so some transformation of the standard IISW3C fields has to take place.

Since we are database specialists here at Trivadis, it's quite natural that SQL is a perfect language to query data. But how do you query an IIS log file which is in a special format (no CSV)?

No, don't even try do do it with Excel or Access. Even SQL Server can't import dozens of files in a proprietary format easily. You could try to play with SSIS, but it would kill you, believe me :-). The files can have breaks where the header is repeated somewhere in the middle of a file, and none of the tools I mentioned can deal with that. Also, the size is just too much for office tools.

Use Log Parser! This tool is just great, simple to use and extremely powerful. It's only purpose is to query log files (not only IIS). And it does it very well: with a SQL-like query language you can run queries against complete folders of files, and the performance is just amazing. It takes some seconds to query gigabytes of date. But it is only a command line tool. The cream topping is the UI that the fine people at SerialCoder wrote: Visual Log Parser GUI . It's quite simple, but has all the features you need if you are a GUI spoiled developer like me :-). Just think of it as a SQL Server Management Studio, but for files.

A query to provide data for the use case I mentioned above could look like this:

select  
ViewDate, PostName, 
KlickCount,
Blogname

using 
      date as ViewDate, coalesce( replace_str(extract_prefix(extract_token(cs-uri-stem, 7,'/'),0,'.'),'-',' '),'default') as PostName
     , to_uppercase( extract_token(cs-uri-stem, 2 ,'/'))  as BlogName
     , count(*) as KlickCount
from  'E:\Blog\*.log'
where cs-uri-stem like '/blogs/%' 
and cs-uri-stem not like '%.png' 
and  cs-uri-stem not like '%.jpg' 
and  cs-uri-stem not like '%.gif'
and  cs-uri-stem not like '%rss%' 
and  cs-uri-stem not like '%/tags/%' 
and  cs-uri-stem not like '%MainFeed.aspx'  
and cs(User-Agent)  not like '%bot%' 
and cs(User-Agent)  not like '%slurp%'
and cs(User-Agent)  not like '%crawl%'
and [date] between to_timestamp('2007-12-01' ,'yyyy-MM-dd') and to_timestamp('2007-12-17','yyyy-MM-dd')
and extract_token(cs-uri-stem, 3 ,'/') in ('archive', 'default.aspx')
and sc-status = 200
group by date, PostName, BlogName
order by KlickCount desc

The output should look somewhat like this (one example row) :

12.09.2007 00:00:00;mvc castle subsonic bye bye webforms;111;MICHAELKOENINGS

For Community Server admins:

The PostName column matches the column PostName on the cs_Posts Table. The Blogname column matches the ApplicationKey column on the sections table. I used MS-Access to bring the data together. This is the perfect starting point to provide excel pivot tables about the performance of your service that makes every manager happy :-)

Some notes:

The hits on the default.aspx page cannot be matched with a particular post. I will try to find a solution to match it by date. The last post that was saved before the page was displayed should be count.

More cool stuff to come: there is a free regions DB available on the internet. With that you can resolve the country, region and city of your visitors IP address. Since it cannot be queried with SQL (it is a proprietary binary format that can only be read via a API), we will use LINQ to help us with that. That's right: a real world LINQ example! So stay tuned!

Backup à la Web 2.0 mit Amazon S3

admin — Sat, 08 Dec 2007 21:34:00 GMT

Du hast sicher schon vom Amazon S3 gehört. Amazon bietet "unendlichen" Speicherplatz auf ihren Systemen an, den man pro Gb (Transfer und Speicherplatz) bezahlt. Dabei profitiert man von der riesigen hochverfügbaren Infrastruktur von Amazons Rechenzentren. Die Daten werden an verschiedenen Orten der Welt komplett verschlüsselt (PKI) redundant gespeichert. Dieser Service wendet sich primär an Software-Hersteller und Firmen, um neue Ideen für die Datenspeicherung und Bereitstellung zu entwickeln. Es ist aber auch für Privatleute sehr einfach möglich, diesen Service in Anspruch zu nehmen. Seit kurzem ist dieser Service auch in Europa verfügbar.

Es handelt sich dabei um einen Webservice, der über entsprechende APIs angesprochen wird. Die Abrechnung ist genauso einfach wie Weihnachts-Shopping: Die Amazon Kundendaten vom letzen Buchkauf kann man direkt übernehmen...(US $)

Das WebService API ist für Enduser natürlich nicht ohne weiteres direkt verwendbar. Es gibt also einen Markt für kleine Software Hersteller, die das Problem lösen: der Amazon Speicherplatz ist direkt aus dem eigenen PC ansprechbar wie einen lokale Festplatte.

Es gibt für Windows, Linux und Mac z.B. das Produkt Jungle Disk , das dies sehr elegant ermöglicht. Als Zusatznutzen bietet dieses Programm eine automatisierte Sicherung, die regelmäßig lokale Verzeichnisse auf das Amazon System sichert. Die Software kann man sogar per USB Stick überall mit hinnehmen, ideal für Leute, die an vielen verschiedenen Computern arbeiten (müssen). Ich liebe sowas...

Warum braucht man diese zusätzliche Software ?

Das Problem ist schnell erklärt: Bandbreite.

Jungle Disk "cached" z.B. bestimmte Dateien, da der Windows Explorer sonst alle .EXE Dateien runterladen würde, um die Icons auszulesen... Das wäre (bei haushaltsüblichen Internet-Verbindungen ) viel zu langsam und würde den Arbeitsfluss am PC stören.

Updates auf das entfernte System werden asynchron im Hintergrund gemacht. So kann man "schnell" ein paar Dateien oder ganze Ordner auf das Netzwerklaufwerk "ziehen", ohne ewig dem Dateikopier-Dialog zuschauen zu müssen.

Leser meines letzten Beitrags haben sich sicher gefragt: "und wie sicherst Du deinen Windows Home Server?" Nun, das ist jetzt wohl beantwortet... Jungle Drive bietet ein Add-In für WHS um Shared Drives online zu sichern.

C# User aufgepasst: Amazon hat ein schönes Beispiel für die Verwendung des APIs hochgeladen.

Hey, sind wir nicht eine Daten-Company? Ich freue mich auf coole Projekte, wo wir dem Kunden Zugriff zu einem hochverfügbaren, sicheren Datenspeicher ermöglichen können, und das von überall, mit nahezu unbegrenzter gleichzeitiger Nutzung. Think about it (Andere tun es auch)...