Calling a Custom External Assembly from a Report

One of the most powerful features of Reporting Services is the ability to call code from within a report. Before we get to that, you may or may not know you can call the .net Framework from within the expression of an objects property on a report. However, there is one rule you must follow, the code must be written in VB.Net.

  • For example: 
    =iif( me.Value < 0, “Red“, “Black“) placed in the color property of a textbox this expression will change the text colour based on the value returned.
    =Format(Fields!BirthDate.Value, "MMM dd,yyyy") placed in the value property this expression will format a date value, (ie Dec 10, 2006)=System.Web.HttpContext.GetGlobalResourceObject("AppResources", "ID14831").ToString() placed in the value property uses your existing resource file to translate a label

Although this is all great and very useful, if you need to do something a bit more complicated you can write your own code and call that from an expression in a report also. This can be done in two ways.

1. You can write code directly in the report using the report properties Code tab. The code tab contains a multi-line textbox where you can write code that can be called from the expressions of the report. From the menu select Report - Report Properties... on the Report properties dialog select the Code Tab. Here you can write code like in the example below. Once again it must be VB.Net.

To use this code in your report place the following into the Value proprty of the textbox where you want to display the Pay Frequency.  

=code.GetPayFrequencyString(Fields!PayFrequency.Value)

2. You may want to reuse the code you write, across many reports. if that is the case, first write your assembly in the .net language of your choice. Then make sure the assembly is in the correct folder so it can be found by the Report Server and the Report Designer. I have a Blog entry here to help you with that.

Now you need to create a reference to the Assembly from the report. There is a reference tab on the Report properties dialog. Make sure you put the namespace in front of the class name or it won't work.

Now you can call your Assembly from any expression by referencing the class and method in the expression. Like this:

=code.empReport.GetSickLeave(Fields!SickLeaveHours.Value)

 

 

 

SRS Filters

I have found a lot of students and clients are not getting the most out of filters in there reports. So I thought I would explain a bit about how useful they can be. There are two places you can put a filter in a report.

  • Data Region (Table, Matrix, List, Graph, Grouping Level)

Applying a filter to a DataRegion means I can show different views of the same data. In other words I can create one Dataset and let it feed multiple regions in my report. Say for example I need a report for management that shows the breakdown of Salaried employees vs hourly. They want to see two pie charts one will show the number of salaried employees by department the other Hourly. Instead of creating two queries one that selects salaried employees and one that selects hourly. I can just select all the data into one Dataset and filter the separate Graphs.

 

When you set the filter don't forget you are applying an expression so the value must begin with an equal sign. This is a common source of frustration the first time people use filters.

I can reuse this data in different ways throughout the report. In a matrix to show the number of employees by region, in a table sorted by Hire Date. All from one dataset.

  • DataSet

You can apply a filter to a Dataset, which at first seems odd. Doesn't the Dataset already have its parameters. Why would I filter the report again? When you create a Cached Instance or a Snapshot the report is stored with its data and the parameters cannot be changed. However filters can still be applied to the report. Filters will use the current report parameter values to filter the report instead of creating a new cached instance or snapshot. This can be useful to avoid creating multiple versions of the same report in the cache.

Go Ahead Run your Tests in the Build

Do you want to be able to run tests in Team Build without having to use a .vsmdi file.

Well now you can. Buck Hodges  has a post with a download which includes a task for running tests in your build by simply specifying the .dll's.

Buck also says this new task will be included in the next release of  Team Foundation Power Toys.

Perhaps someone is listening!

San Francisco

This week I am in San Francisco (Actually I'm south of SF) but I landed there and drove down Hwy 101 to Morgan Hill just south of San Jose.

I passed some things that make every geek stop to look. Like when I saw Microsoft Campus for the first.

Today I saw the Google Campus and ebay and I drove past Palo Alto and Silicon Valley. This is my first time to San Francisco so it was very exciting for me.

Tomorrow I will be helping some nice people get the most out of Reporting Services. I'm looking forward to it.

Team Foundation Server Users and Microsoft Project Resources

After some investigation this seems to be a pretty well known issue. However I had not come across this issue so I thought I would Blog it so I can easily find the solution again.

Although there are work arounds for this issue, you will be happy to know this will be fixed and is addressed in KB #919232

When the TFS Client is installed, Microsoft Project like Excell becomes connected to the Team Foundation Server via a new tool bar in Project. Using this a Manager can read or publish work items from the TFS Project. The problem, is when the users on TFS are defined in Active Directory the AD friendly name is Lloyd, David. In MS Project multiple resources can be assigned to a task using a separator. Which by default is a comma.

Here in lies the problem. When you take a project and try to publish tasks with multiple users to TFS you will get an error, telling you the user is not defined. When you read tasks from a TFS Project it will work but you will end up with multiple users. (Lloyd and David)

There may be more than one solution to this, however I found a simple solution on the MS Forums which is to simply change the separator Character in your Regional Settings so Project does not use a Comma as the separator.

If you're not sure where this is follow these directions:

From Control Panel open Regional and Language Options
On the Regional Options tab click the Customize Button
On the Numbers tab change the List Separator

There are other work arounds but they are not even worth mentioning.

Summer

I haven't blogged anything this summer. I plan on getting back into it soon. It's been a crazy summer, nice but crazy.

I just took a week off to go to a family reunion in N.B. The reunion was on my Dad's side of the family. Everyone from my Dad's parents down. It was great to see everyone and the location was beautiful. We met at the Bay of Fundy. I had never been there, if you get a chance go, it's wonderful. After the reunion my son Gareth and I spent the week driving back. It was a nice road trip. Starting at the bay of Fundy we drove to Moncton and spent the night at my cousins. Where we had a wonderful time. The next day we put my wife Tracy on a plane home and drove to PEI. Where we stayed at a Bed and Breakfast near Summerside. The bridge to PEI is huge. Had a great scallop and shrimp meal in New London and did some touring around. From PEI we drove to Portland Maine, where we stayed at another lovely B&B. From there we drove to Salem MA. That was the most fun, we did lots there, including a ghost walk and watched a reenactment of a witch trial from 1692. We went into a replica of the witches dungeon. It's a lovely city with nice pubs, good food and lots of stores full of spell books and potion ingredients. After Salem we drove to Wolfe Island in the 1000 Islands. We were going to stay over night there but decided to head home. So we got a Tim's and hit the 401. Nice trip glad to be done driving.

I'll get Blogging again soon.I just have to get these kids back to school first. :)

Fenway Park

Last night it was the attendees party. I have been to many of these, and this was one of the better ones.

We got the run of Fenway park, one of the oldest Ball parks in the games history (According to Bruce). It's quite amazing.

We sat at the top of the Green Giant. Walked around the field. Bruce wanted to throw a ball from the mound but the actual field was roped off. We kept daring him. I even offered hm 100 to do it, but he didn't want to be on the news today I guess.

They had a couple of bands play. I saw one of them Train. Very good band, they put on a good show.

Last Day of the conference. At this point you just want to go home. Barry was one of the Judges for the Iron Architect Competition on the last day. Bruce and I headed to the airport. I took the Subway which was a nice way to get to the airport cheaply. Not that a cab from downtown costs all that much but the boston subway is pretty cool so I thought I would take it.

 

Boston Culture and Interesting People

The Boston Culture was Durgin Park. We went tonight for dinner. You have to do it. It's a 180 year old Boston landmark. Like they say “Your great grandfather likely had dinner there.”

Interesting People: I went to a session today by Jesper Johansson called Is that application really safe? I went on John Lam's recommendation. I'm glad I did. It was an excellent presentation. Security can be interesting. That was the first interesting person I saw today.

Tonight we went to the influencer's party at Ned Divines Bruce and I were walking around seeing who was there. We asked to borrow a chair from a table where two ladies were sitting and got chatting with them. They were both British and their job was to organize and execute Tech Ed Europe. I have mentioned before in my blog how amazed I am how something this huge comes together, and here I was with the chance to chat with the people who do it. They were very interesting to talk to, they mostly wanted our opinion on various matters. Which of course we gave them, however not without asking a bunch of questions about organizing something like Tech ed or PDC.

Interesting day all around.

More on Visual Studio Team Edition for Database Professionals

Was looking at sessions for the afternoon. And came across one that I had missed before on this topic. The book didn't actually say it was about this but I recognized the name Mairread O'Donovan from the VSTS influencer's the night before. She was the one telling me about this new product.

I have been skipping VSTS sessions so far since I have been working with it for so long I thought they might be too light, but this is new and I wanted to see the product in action.

Some of the features I didn't find out about last night.

  • Reverse engineer your SQL Server Database
  • Version Control
  • Schema Build and Deploy
  • Unit Testing
  • Schema Compare
  • Data Compare
  • Data Generator (This is a nice tool for creating sample test Data, FOR DEVELOPERS NOT TESTERS we'll talk later)
  • Schema refactoring
  • TSQL editor
  • Query Execution
  • Plus the normal Team Foundation Server stuff (Work items and Process Integration)

It would be nice if they would also include in this tool the following. If the Team is reading this feel free to use this as high level requirements.

  • Integrate Profiler
  • Debugging from code straight into SQL easily with no extra work
  • Modeling (for Barry)

VS for DBA's

I attended a little party on Monday night for VSTS influencer's.

I met some very nice people, who are very excited about their work. I had a short discussion about the new Visual Studio for DBA's. This sounds like a nice tool. One of the features I liked from a testing/deployment/promotion model point of view is being able to compare Data between versions of the Database.

When you are testing an application you must have control variables. So often this is not the case. Other industries are laughing at us over this. I remember reading an article in American Scientific written by an engineer who was slamming the software industry because we do not have standard process and controls in place like the engineering community. Anyway One of the most important parts of testing is to have a Database that you know the state of when executing tests. If you don't know what data is in your Database how can you be sure the test results are accurate.

It sounds to me like there will be tools to help in the coming version or Visual Studio for DBA's