Alex Duggleby’s Palace of Words

Some facts:

• SQL Server Reporting Services were great in the past. SSRS 2008 are better.
• MSDN is great. MSDN has lots of details. Sometimes it’s too much if you are under pressure.
• Google usually gets me the information I want faster. Especially when I need to get something done quickly.

All in all this post is for those of you that need to learn how to use SQL Server Reporting Services fast or need to solve the above mentioned problem fast, without knowing too much about the details. I read a lot of and like these introductory posts and so now I’m writing another one, here goes.

I assume you have SQL Server Reporting Services already setup (by your grand one-and-only unmissable sys-admins or by yourself, but in the latter case you probably are not the target audience for this) and you have access to the all required tools. Talking about tools, there are three you should know of:

- RSConfigTool - (Start menu > SQL Server 2008 > Configuration > Reporting Services Configuration): What do I need it for: Troubleshooting, Finding Urls, Configuration Report Server. Having problems trying to connect. See Additional Tip #1.

- Report Manager - (http://<yourserver>/reports): What do I need it for: Managing your reports (you won’t actually see anything on a clean install yet)

- Business Intelligence Studio - (Start menu > SQL Server 2008 > SQL Server Business Intelligence Development Studio): What do I need it for: Creating and editing your reports

and of course you will need SQL Server Management Studio for managing the data the report is based upon.

(side note: This tutorial is based on the 2008 version, can’t say how many differences there are between this and the older versions.)

1. Create your views

There are ways to aggregate your data in the report itself, but it’s easier and cleaner to use SQL views to provide the data for the report and use Reporting Services only for the report. So go to SSMS and create your view.

2. Create a new BIDS (Business Intelligence Development Studio) solution based on the “Report Server Project” Template. Add a new report by clicking on the Report node in the Solution Explorer. Next we need to connect to a data source. Give it a name, click edit and connect to your database.

3. Click next and the Query Designer appears. Here you enter the T-SQL statement for your data. Ideally it’s just a “SELECT * FROM yourView” (or select each column distinctly). You can test it using ‘Run Query’. You can also choose to use data from a stored procedure if you like, just select the appropriate option.

4. Design your report. You can add elements to the report surface, by right clicking on the center surface and select them from the insert menu. The most important are Header, Footer and Table. In the Header and Footer you can add Textboxes with text or by right-clicking on them and choosing expressoin you can choose some variable input. For example page number, report name etc. In the table you can drag columns from the “Report Data” (on the left showing the data source you just added). When you are finished, you might have something like this. Click preview to get a glimpse at what your boss might get.

5. Now let’s deploy it to the report server. First we must set the report server url. Right-click on the solution in the Solution Explorer and click on Properties. You will see the following dialog. Change the TargetReportFolder value to something you wish and provide the TargetServerURL as http://yourserver/reportserver (not just /report!). Press save. Now right-click on the solution again and select Deploy.

6. When it finishes open the url http://yourserver/report (not /reportserver!). You will see something like this. Go ahead and click on Test.Reports (or whatever you specified as TargetReportFolder) and then click on your test report. You will be greeted with the web version of your report.

7. Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.

8. Now you can go to Subscriptions. Click on email. Don’t have email as an option? See Additional Tip #2. Enter the recipient information as you wish and select a schedule for the sending. If you press save and encounter an error that ‘SQL Server Agent is not running’ either figure it out yourself what the problem could be or read Additional Tip #3 :). Once you have finished, lean back and let SSRS make your boss happy and in turn make you happy.

So we have seen the absolute basics of report creation. A quick and dirty introduction. This should cover your first encounters with SSRS. Hope it helps someone!

Stop reading here unless you are Troubleshooting…

Additional Tip #1:

I try connecting with SSMS (Management Studio) and it won’t connect. Maybe you renamed the server? Open RSConfigTool, go to Database > Change Database > Choose an existing report server database. Just choose the existing one, and let it run through it’s config. That should repair everything and you should be able to connect using SSMS again.

Additional Tip #2:

You don’t have email in the options for delivery in the subscriptions dialog? Probably it’s not configured. Thankfully that is very easy. You start the RSConfigTool. Connect and go to the Email tab. There you just specify a sender address and the SMTP server you would like to use. Click apply and voila. The server will automatically restart and the now the email option appears.

Additional Tip #3:

Saving the subscription results in an error message saying SQL Server Agent is not running. This is a DB or sysadmin issue, but just fire up SQL Server Configuration Manager (in the Start Menu under SQL Server 2008 > Configuration) then click on SQL Server Services, right-click on the SQL Server Agent > Properties. Go to the service tab, change Start Mode to Automatic and press OK. On the next reboot it will start automatically, but you still need to start it once for now. Right-click on the ‘SQL Server Agent’ and press Start.

I’m quite busy at the moment with the Academic Community Launch, so I’ll leave the talking to Christian, who has posted some information about why I’ve seen most of Austria’s big cities within the last week.

Info and Pictures of the ACL 2008

We are currently running traces against a web applications that we are developing and one of my colleagues was telling me that the JetBrains dotTrace applications didn’t allow running profiles against the ASP.net web development server (it way grayed out). The reason was that only Visual Studio 2008 was installed, which by default puts the WebDev.WebServer.exe (which is the ASP.net web development server) into the c:\<program files>\<shared files>\Microsoft Shares\DevServer directory instead of the old Visual Studio 2005 path which was c:\windows\Microsoft .NET\Framework\v2.x.y.z\.

There is a way to profile against the VS08 web server as described here, but you can also just copy the WebDev.WebServer.exe from the one directory to the Windows\Framework directory. This probably isn’t the best way to do it (because patches will only go to the original install directory), but with it dotTrace can start a profiling session with the ASP.net web server.

A bit out of the ordinary (I am in between writing up some more Sync Framework articles) but a fellow student partner Mario from Linz has put together a series of screen casts to get you started on Visual Basic .NET. He covers the basics but also some of the new VB.net features. If you are interested and understand german, take a look at: http://www.vb-magazin.de/casts/video_index.aspx

On the last day Mario and Christian were scheduled to do an Interview with Amanda Silver, the Senior Program Manager Lead for Visual Basic and Jay Schmelzer, the Principal Group Program Manager for Visual Basic.

As it turned out Christian was already scheduled for another interview - he was a very busy guy at Tech-Ed - and so I jumped in to film the interview and as it happened I also had to ask some questions. The video is not published yet - and you won’t see me anyway, because I posed questions from behind the camera - but there is a summary of the interview on Channel 8 and a german version on vb-magazin.de.

What is a Microsoft Student Partner? What do they do? Why do they do it? What do they get out of it? And how do I become one?

Some of you may know I’m an MSP for the universities in Vienna. I’ll be blogging about some increased MSP activity that’s coming up for Austria soon, but this post is going in a different direction. I want to use this post about the TechEd EMEA summit for MSPs to also talk a bit about what we are. I’ll start by answering the questions above in reverse order.

How…

can I participate? The Microsoft Student Partner program has been implemented in 65 countries and a further 35 are starting soon. That means there is a good chance that you can participate in your local program. Usually the person to ask about it is your Academic Relations Manager at Microsoft. Try finding someone from your local academic team - anyone really - and just ask for the right person. They’ll be more than happy to help usually and will help you make your first step in the right direction. and once you’ve done that you have your foot in the door.

The program itself is implemented differently in different countries, some countries interview you like an employee and other’s will just take a look at your community work and award you an MSP for doing that.

Who…

is a good MSP? Well as usual “it depends”. But there are things that MSPs have in common all over the world. It’s their passion for:

1. Technology

You don’t have to be the world’s best coder, you don’t have to speak in 0s and 1s, you don’t even have to study computer science. You need to have that special something that makes you want to look behind the technology. It makes you the guy or girl everyone asks for tech information or you simply have the will to continuously keep up-to-date on everything that’s new in the industry.

2. Communities

On of the goals of the program is to reach out into the community and spread the word. Share your passion with others. Think about it like passing on the Olympic torch, it’s not the fire that matters, it’s the feeling you pass on that makes it something special. If you love sharing and telling people about interesting things, then use your passion and brush up your skills by engaging in community activities such as user groups, student workshops…

3. To think that one step further than the rest

You are committing to activities and tasks and seeing no paycheck in return. But you wouldn’t even think of asking for one, because your return on your investment is so much more in the long term and you know that.

What…

do we do? And again “it depends”. Usually some kind of community work is involved. A lot of MSPs run user groups, do presentations and workshops for their peers, participate or mentor for the Microsoft Imagine Cup… The limit is basically your imagination. You can’t do anything wrong. It’s a great chance to test out your skills, form your personality and basically do a lot of self marketing.

Depending on where you are, your integration into your local Microsoft ecosystem may be different. You could have a two level hierarchy where MSPs are self-organized through Senior MSPs, but usually the hierarchy comes together at the ADE - the academic developer evangelist or academic relations manager (again depending on your location). He is your primary link to Microsoft, he’ll help you where he can. But with time you’ll build up your own network of contacts within the mother ship.

You need to be studying to be a MSP. So once you have graduated, you are finished with the MSP program. After that a lot of MSPs start at Microsoft and most of them continue in the Microsoft community and use the network they’ve built. Currently an alumni program is being developed that will help the MSP process to fade-over into a long-running link between former MSPs and Microsoft.

Why…

…do we do it? The question doesn’t arise when MSPs meet. But it is certainly often asked when you tell outsiders about the program and possibly even try to recruit them. “What’s in it for me?” If you’ve already told them about the program, the possibilities and experiences and they still ask that question. Then - in my opinion - they aren’t the right people for the program anyway.

Take the Tech-Ed as an example. The program makes it possible for me to visit these otherwise (for students) quite unaffordable events and of course you could interpret that as a monetary ROI for my efforts (and each year I think I need to do more next year just to balance my carma out *g*). But it goes much further than that.

You are part of one of the most successful high-tech companies worldwide. The network of contacts in your local subsidiary grows rapidly and can be used for all kinds of opportunities in the future. Do you want to work for Microsoft in your country? Do you think it could help that you know half of the developer and platform team there already? Sure, it would.

The program has been going for some years now and has gained a tremendous reputation within the product groups at Microsoft. They are eager to work with MSPs in all kinds of areas - they know you are the next generation of users and who better to ask than you. Early product development feedback and they actually listen to you!

Maybe you’d like to work for one of the groups in Redmond. How about an internship at Microsoft? Well you’re surely better off applying as an MSP than anyone else. You already have your foot in the company, your recommendation letters from your local subsidiary and a great reputation (otherwise you wouldn’t be an MSP). If you were recruiting - where would you look for new people first? Strangers, or the people who are committed to your technology.

Ok, suppose you’re not heading towards a career at Microsoft, suppose you stop using Microsoft technology the day you leave the program. The by far largest benefit from my point of view is the network of MSPs from all around the world that you can build upon. The industry is all about who you know. Whether you decide to work somewhere else or to work on your own, you can turn friends you make as a student partner into business partners later on. Even while still at university/college you can use your contacts for foreign semesters, international collaboration or just simply for visiting someone in a foreign city (MSPs are everywhere…).

The EMEA summit 2007

Each year a number of MSPs are invited to Tech-Ed to get up-to-date on what’s new in the Microsoft world. As part of the Tech-Ed there is the MSP summit where we get to hear about what’s new in the program, get really close to those involved in the worldwide program and to Tech-Ed speakers and it’s followed by a networking event. This year we went bowling.

During the first part of the summit Rob Miles - yes, THE Rob Miles. Gosh I’d like to be like him :) that joke never wears off - came by to talk a bit about how he became an MVP. A lot of MSPs become MVPs (and some even already are). Next up was Karen Young who is the Group Regional Manager for EMEA and APAC for the MVP program. (Just my point from above, when do you get to meet people like this other than as an MSP?). She was followed by Holly Peterson who designs recruitment programs for the development centers in Europe and Israel for Microsoft. Now you can’t get any closer to an internship or job than that, can you? By the way, it’s a common mistake that the only development jobs are in Redmond. There are a number of development centers in Europe as well, such as in Ireland.

A number of top notch Tech-Ed speakers then joined us for a Q&A panel and they all bravely answered our questions.

But then it was time to head over to the Bowling center. Some MSPs like to do things around Tech-Ed as kind of a thank-you. If you haven’t noticed yet, I like to blog about it. My colleague from Austria Christian and Will Perry an MSP from England (and former intern in Redmond) are recording interviews for Channel 8. They were running around chasing the top speakers here at Tech-Ed to interview them on all kinds of topics. Of course they couldn’t resist the opportunity at the bowling event to grab whoever was standing around and ask questions.

One question you may have asked yourself about the MSP program is: What is the gender ratio? Well, sadly, it’s similar to the industry we operate in. But nevertheless there are a number of great female student partners (we are very proud to have on in the Austria team - a quick shout-out to Corina who will be attending the IT Pro version of Tech-Ed next week and is missing out on lot’s of MSP fun). You might call it a homage to the Channel 9 series “Women in IT” but Christian and Will managed to get three of our wonderful ladies for an interview. From left to right: Diana Tataran (Rumania), Susana Vilaca (Portugal), Christian Liensberger and Kateryna Drobotko from Ukraine.

(A little bit of cross-marketing: check-out these beautiful young women over at Channel 8 - if their interview is not online yet, check out the other content and come back later. - I must put some of the things I learned in those marketing classes to good use.)

They also interviewed Holly but the taping turned more into an interesting equal dialog between Holly and Eric with him talking about his internship and Holly talking about the process of becoming an intern.

Karen joined Christian, me and some others for a last bowling game. You can guess who won by looking at the pictures.

Summary

The MSP program is a great opportunity for students to become connected to Microsoft. Both partners thrive from that relationship and the experiences you make as an MSP are amazing. If you’re interested in becoming one, contact your local Microsoft Academic team, if you’re interested in knowing more about it, post a comment or ask your local MSP if you have one.

Go MSPs go! We rock! Some more photos here.

Good night.

Technorati Tags: MSP , Microsoft Student Partners , Microsoft Academic , Tech-Ed Europe , MSP Summit , Bowling , Channel 8