There are probably a few ways, but this is what I did.

First, I added two parameters. “SortByDefault” and “SortOrder”

The “SortByDefault” will be a drop down of your columns you want to sort by for your dataset (or group, or table/tablix)

The “SortOrder” is simply Asc (1 to N, A to Z) and Desc (N to 1, Z to A)

Now, here is how mine look:

SortByDefault (I have two columns I want to allow sorting by, PointsLeft and StackRank):

SortOrder:

Now comes the fun stuff: Making it work.

Make sure you remove any “ORDER BY” in your dataset (you don’t have to but this makes it easier).

I also have every column in the report set up for interactive sorting based on the column header/column it shows, but not sure that is necessary here, I just wanted to put that out there just in case

You want to get to your sorting options. So in my case, I have a tablix, so get to your tablix property window and the sorting option:

Now you can see, my “Sort By” and “Then By” are expressions. It is kind of weird here. Also you can’t set expressions for “Asc” or “Desc” so what I had to do was trick it somewhat.

the first is to handle the asc option:

=IIF(Parameters!SortOrder.Value=”Asc”,Fields(Parameters!SortByDefault.Value).Value,0)

the second is to handle the desc option

=IIF(Parameters!SortOrder.Value=”Desc”,Fields(Parameters!SortByDefault.Value).Value,0)

You can see, some magic. If the order by is XYZ then use the field, otherwise 0. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS :)

Well now you should be able to test your report and try to sort orders. What I did next is make my params hidden. The defaults are what I wanted for the existing report (Order By PointsLeft DESC), and what I did next is create a linked report and set the hidden parameters int he report options in Report Manager to (Order By Stack Rack, ASC)

Now I have one report, with hidden sorting params and I can create linked reports with different sort options without having to create a new report. I could add all columns to the choices, or even let users choose as parameters (but they already have interactive sorting in this case).

Happy Report Buildin’!

You install DQS with SQL, and you have to configure it. The server configuration is a cmd line process that runs to create some databases on your server (DQS_MAIN, DQS_PROJECTS, DQS_STAGING_DATA).

I ran into one issue with the running of the configuration, not sure if this happens everywhere, I am running Windows 8, but nonetheless, I ran into. After running the tool and getting error after error, and trying as admin, etc. I dug deeper into the error message and found that I there were some security/permission issues I had to resolve. It ended up being that I had to change permissions on

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Config\machine.config

to allow write access. Once I did that, the configuration tool worked and I could get into DQS.

DQS gives you a “Data Quality Services Client” to work with. When you open it, connect to the database where you configured the three databases I talk about above. Once you, you have 3 panes.

You have Knowledge Bases, Data Quality Projects, and Administration.

Knowledge Bases: datasets of known data that you can use in your Data Quality Projects. You get a default Knowledge Base – state names and some other data similar to that.

Data Quality Projects: Here is where the magic happens. You can choose some source data (Excel xls – xlsx wouldn’t work or SQL table) and then apply your knowledge base on it. Then you can reimport your data at the end back into SQL or export it, and update your Knowledge Base with learned values.

Administration: Not a ton of options, but you can set some thresholds, and also setup your Azure data market settings.

Azure Data Market https://datamarket.azure.com/browse/Data?Category=dqs – Lots of data you can use to combine with your Knowledge Bases. Much more here and I won’t go into detail – it could be its own post in itself.

As a test, I took an excel file, added a few records with columns first, last, city, state (I actually imported into a staging SQL table to work with it) – But in the state field I put different variations of state, WI, Wis, Wisconsin, MN, Minn, Minn., etc.

I then ran the file through creating a new data quality project and ran it against the default Knowledge Base, and it corrected the values it could. Got a weird error clicking next on the project, it seems the button is touchy. Hopefully they come out with a fix soon.

Once you build up and get your Knowledge Base stable, you can use from SSIS packages or in Master Data Services. I see many useful applications for DQS. Either around your corporate data or pulling in data from Azure data market to cleanse existing data you might have (think: looking up gender from first/last name).

This post is a brief look at DQS and how it works, but there is so much more. I hope to get more in depth in the near future.

It has been a while since I have posted, for good reason, but of course it usually boils down to “just time”. This is my 1000th post on this blog. Well, not really, I had some that I probably removed over the years. Back since 2004, I have moved this blog from blogger.com, to wordpress, to my own wordpress (on windows!) to different providers and hosters and what not. It has been a road. And more to come. I have a few drafts queued up, but was waiting to have something good for post 1000.

So what else? Other things are keeping me busy. Since last October, Homebrewing has become a big hobby of mine.

We have done 8 batches so far, and it keeps getting better and more fun. We are now past kits and we now are picking our ingredients, which is cool. The beer is good too :)

MADPASS (www.madpass.org) – I am on the board with MADPASS, we hit our 1 year mark in Feb, which was cool.

We keep doing cool new things, the speakers are good, and the “community” is growing. SQL Saturday 118 is coming up on April 21st as well in Madison (at MATC) – and I am helping out with that as well.

Besides that? Always the same old tech things I do every day. Finally consolidated my Google life to my Google Apps (more on that in a future post). No, no iPad 3/Next for me yet. Maybe though. Thinking about my next tech gadget. Chromebook? New TV? Not sure yet. Nothing really pressing there.

What else could there be? Well… a couple of things :)

I have been in the Madison area since November 2007. Been at Trek since Sept 2008. I have lived in Madison (apts), Sun Prairie (condo) and now in an apartment downtown Sun Prairie. It is a cool place. Nice view. Right in the thick of everything that goes on downtown (parades, festivals, etc). But.. the apt life wears on you. We have been “looking” for a house for a while now. Not ready to just go pick one out that is on the market, but wait for the right one. Right area, right price, right look, etc. Well, we found one. In less than 2 weeks, I will be moving to a house in Sun Prairie. It is about 6 blocks away from downtown, so within walking distance of everything (cough cough – Eddies), but yeah, nice little place. In a semi-secluded street. 3 bed, 1.5 Bath, Nice fenced in yard. Should be a nice house.

But wait, there’s more!

Now, I would say that I have everything I need to be happy, and that is true. But sometimes things happen and you get things in your life that will even make you *more* happy. Well, it is a good thing the new house is a 3 bedroom, because come fall there will be another baby Novoselac running around. Yes! We are expecting. Ella will be a big sister and of course our lives will change for the better once again. I am excited but will be even more excited around October!

So until then, and even after, we will keep watching the movie that we call life and having fun.

Ok. 1000 down. couple more thousand to go. Keep rockin’ everyone.

Last week the Windows 8 Consumer Preview was released. My computer at work was getting “crufty” so I decided to install Windows 8 on it fresh and see what I could see.

Well, first off, if you run Windows 8, you can associate your login account with a Windows Live Id Microsoft account. This is pretty cool for a couple of reasons. Most services on the OS then automatically know who you are, and second, your settings and Windows Store apps will sync between devices.

But what happens on domain? I was as curious as I could be as the info out there is/was sparse as of now from what I could see. So I set up the machine fresh on Windows 8. Logged in as my Microsoft account and got into the OS. Once on the corp network, got it on the domain, as normal, and then I could log in with my domain account. Great.

But then what? Do I lose all the cool Microsoft account stuff? Well, it depends. What you can do is go to the new “Settings” in Metro land on the machine and find the account stuff, you can then associate your Microsoft account with your Domain account. This is pretty cool. Then your settings on your domain machines (if you have multiple) will sync, but you still get the domain stuff of the corporate network. I have two Microsoft accounts anyways (One for personal, one for work – MSDN, etc, etc).

There is one article out there that tells admins how to restrict the Windows Store on domain, or at least they will be able to, so I am guessing most things will be configurable or restrictable by domain admins and group policy, but at least there is some cohesiveness between Microsoft account and Domain account now. Good stuff. More to come on my Windows 8 Experience, as I am and will be putting it through its paces.

After getting Ruby, Rails, MySQL, curl/curb, GraphViz, IBM WordCloud and whatever else configured, I finally got it working. (FYI – MySQL 5.0 – you need to run the config wizard as administrator on Windows 7 or it just hangs at the end).

Pretty cool data and analysis from a higher level. Of course after getting everything working, I wanted to hit the data with PowerPivot. This sounds like an easy feat, but yet seemed to be a complicated task.

I first got the ODBC connector 5.1 for MySQL (Since PowerPivot doesn’t natively connect to MySQL,and 5.1 since that is the only one I could find reliably and get to work.), and set up an ODBC source. Tests fine.

In PowerPivot, I would run through the wizard and it would get architecture mismatches, and catastrophic failures, trying to test the connection. Ignoring that and moving forward, running a query would just hang on import forever. I tried different DSN’s, User/System DSNs, etc, to no avail.

What I ended up doing was firing up my local Microsoft SQL instance, and creating a linked server through a system DSN to the MySQL instance, then I could query the data fine from SQL. I opened up PowerPivot, connected to SQL local and then ran the query to MySQL and it work. What a workaround, what a hack, but at least I can hit the data in PowerPivot locally, which was my goal here.

Of course I could take what Yamanalysis is doing and dump to SQL, or do something similar in C# and dump to SQL, that might be a project for another day.

Apps Missing

• Cisco VPN (yes, it’s a service, but it’s not really there on WP7)
• Disqus – has api http://disqus.com/api/docs/
• Flipboard
• Google Plus
• Hipporemote
• Intonow
• LogMeIn
• Mint
• Oink
• Pandora
• Path
• PayPal
• Posterous – has api http://posterous.com/api
• PS express
• Skype
• Sportacular

Apps There But Not Official or Website only

• Instagram (instacam)
• Untappd (website)
• Pinterest (Pinspiration)
• YouTube (it is native but MSFT made and mobile website wrapper)
• Tumblr (not free 3rd party)
• LinkedIn (3rd party app)
• Google Voice (GoVoice, GV for Windows Phone, etc)
• Digg (Digg7, Digg Reader, etc)
• Dropbox (some readers, etc)
• Google Authenticator (some 3rd party apps)
• Google Latitude (some crappy 3rd party apps)
• Google Reader/reeder (some crappy 3rd party apps)
• RDP Client – some 3rd party clients
• VNC Client – 3rd party clients
• TFS Clients – couple 3rd party clients
• Google Translate – some 3rd party apps, Microsoft Translator, etc

As you can see, pretty big list. Many of the 3rd party apps just don’t cut it. Even some of the apps that are there, they just don’t compare to iOS or Android. Microsoft needs to overcome this App Gap to gain and/or stay relevant, in my eyes.

1. Yammer – working hard on growing Yammer community, external networks, just getting engagement and showing the benefits. It’s fun.

2. Kinect – did some Kinect hacking. On my own then with the group, got some cool stuff to show. It is crazy how easy it is to get something up and running with Kinect and the SDK. I see this stuff taking off in the coming months/years.

3. Azure – dorking around with Azure, looking at what it can and can’t do, what it could do well, how it would fit in with everything.

4. SQL 2012/Power View – been playing around with SQL 2012 since “Denali”, but now its got an official launch date (March 7th) and things are getting real. Power View demos online, trying to figure out how SQL 2012 is going to fit into our infrastructure and just learning as much as I can about it.

5. Ruby – been getting into Ruby and Ruby on Rails on my Mac, git, sqlite3, heroku, etc. Trying to learn more things that just the .NET ecosystem.

6. Ubuntu – same here, set up a VM, been trying to use it consistently, trying to get the other viewpoints from Windows and Mac and where things are at. Keep up with the joneses so to speak.

7. Android – I picked up a Samsung Galaxy (Verizon 4G) a few weeks ago and have been using it. I still love my iPhone, but getting more into Android. Ice Cream Sandwich (ICS) is a pretty good OS, there are still quirks, but its better. Verizon sucks around where I live btw.

8. Google+/Picasa Web Albums – been getting this into my photo workflow, for sharing and backup. Liking it so far.

9. SharePoint 2010/FAST – been researching and reading FAST server like crazy trying to see how it will fit in with a potential project. I think it could be amazing. more to come.

10. SMS – been playing around with different frameworks, and seeing how they compare, trying things out. Using Voice and SMS is all the rage these days. (Hall and Oates thing anyone?)

Bonus: Nothing with computers, but I have been really getting into brewing beer/homebrewing. I think we have made 5 batches now, and the ones I have tasted so far are really good. It is a fun hobby and breaks up the constant technology I am involved in. More to come here too.

And much much more. Time is limited, time to post is limited. Getting out there and doing cool things is fun, and sharing them is fun too. Gotta find the right balance. I hope everyone is having a cool 2012 so far.

Last week, I stumbled across a “legacy” system. Small system, few tables, few stored procs, few web pages that let users manage it. The main table had about 40k rows in it, nothing huge. This system gets used sparingly.

Reports of the main page of the system taking 5+ minutes to load, or just timing out. Really? Dug through the code and lo and behold. A cursor.

Looping through 500+ items, and running 8 or so queries in each iteration. The queries were very similar:

SELECT some stuff INTO Cursor

WHILE LOOPING THROUGH CURSOR

SELECT STUFF 1

SELECT STUFF 2

..

..

END

I ended up re-writing the query to just grab all the data and did some case statement in the SELECT. They query went from 5+ minutes to less than 1 second.

Yes, CURSORs are bad. Avoid them. Rewrite your SQL. It amazes me that with all the good info out there on the web around SQL that people still write CURSORs in their queries. I asked the DBA to look at all procs on all systems and find all the CURSORs so we can root them out once and for all.

End of Emergency Broadcast

I, like many have been guilty of using same passwords for different services, throwaway services, etc. My most important services I used different passwords, but no more. I have been using 1Password for a couple years to store passwords, but now I am taking it to the next level. I went through the majority of the services and changed password and created a randomly generated password in 1Password. Yes it is a pain in the butt to have to look up password or whatever, but I think at this point, the best password is one you don’t even know.

1Password syncs across iOS, Android, all browser extensions, Mac and Windows, so it suits me well. Even after a few days I am getting used to the new workflow with any passwords I might have to look up or auto login to. All I know is the security and authorization on the net has to improve, it shouldn’t be this tough.

How is it social? Well, people can report slowdowns, accidents, police, etc and it will show up on your map as you drive. You can see other wazer’s on the map as you drive. They also gamify it by giving you points for miles and little rewards when you hit milestones, pretty cool. Works for me. The product becomes better as more people use it, and I assume as your friends or groups you are in use it more.

Check it out at waze.com Looks like they have apps for more than iOS as well.