Darren Herbold

VS2008 ASP.NET Reports Web Site Project Type

Darren Herbold — Tue, 07 Oct 2008 02:13:00 GMT

Those of you who have had the opportunity to start working with Visual Studio 2008
will certainly have noticed all the new project templates now included with this new version.
If you are a web developer in particular, when choosing to create a new "Web Site"
project, you now have the option to create a new and very popular project template called
the WCF service. This option may have distracted you from another very interesting
template called the ASP.NET Reports Web Site.

What is this new project template you ask? Well, it turns out that the main purpose of
this template is to provide the developer with the means to create and expose Reporting
Services reports over the web. This template by default will supply the project with an
RLDC report (Reporting Services Report for the web) and a ReportViewer control bound
to the default aspx page.

On the start of creating the new project, the developer will be prompted with the Report
Wizard to start the design of the report you wish to expose in the web form. This article
will walk you through the steps of the wizard and explain them along the way.

Choosing the Reports Web Site project template:

This is the first page of the wizard. Of course you can choose to Cancel and go it
alone. More advanced developers will mostly likely choose to Cancel this wizard,
but it sure gets you off to a great start!

The next step is to Choose or Create a new Data Source:

The next step will ask you if you would like to store the newly created connection
to the config file.

This step will prompt for database objects to expose to your report. You can
choose from tables, views, stored procedures, and functions (UDFs).

In this example, I've chosen fields from the Product table.

This next step simply asks which data connection you wish to use to connect
to your data source.

After selecting the data connection, the next set of steps will help you define the
aesthetics of the report. In this example I've chose a Tabular layout.

Determine where you would like your fields to be positioned.

Choosing the table layout.

Choosing the color style of the table.

After completing the wizard, you will notice the following items in the
solution explorer pane. The wizard creates an object data source that
references a strongly-typed dataset. Also notice the report with an RDLC
extension. You can always design the report manually without the wizard
if you so choose.

Pressing F5 or clicking on the green play button will start the debugger
of the project and run the report in the web browser. Keep in mind that
the report is being displayed inside the Report Viewer control that is
bound to the web form.

SSIS variables in Data Flow Script Component in Milwaukee

Darren Herbold — Sat, 13 Sep 2008 12:03:04 GMT

While at a client in Milwaukee, I came upon an interesting problem on how to access a
user-defined variable in a Script Component in the Data Flow. There are actually two ways
to solve this problem. One technique is to place a Derived Column before the Script component
and create a new derived column using the variable as the expression. This will create a new
column in the data pipeline with the value of the variable as its value.

The other technique is to use the "Me" namespace in VB.NET directly in the Script Component
script code. The syntax will be as follows: Me.Variables.<variableName>. So if the variable name
was called myVar, the syntax would read: Me.Variables.myVar
One last thing you need to do is double click the Script Component to open the editor and under
the Script menu item, set the variable you wish to use in your script here:

I happened to be there in the Winter time so it was fiercely cold and I mean cold beyond
comprehension. The first day I got there it was -27. That's right, NEGATIVE 27. I knew
I was in trouble when I overheard to guys in the elevator having a conversion that went
something to the effect of:

Guy1: Wow, it's cold today.

Guy2: Yeah, but tomorrow its going to warm up and snow.

That's what he said, no kidding. Warm Up, AND SNOW!! Apparently it was TOO COLD
to snow! How crazy is that?!?!

Anyway, here are some pictures I took during my tenure there:

This is the view from the client's office. That's Lake Michigan, FROZEN.

My hotel in down-town Milwaukee

Riverwalk Downtown is beautiful at night. During the day, not so much.

SSIS in Chicago

Darren Herbold — Fri, 12 Sep 2008 23:00:55 GMT

While at a client in Chicago, I was tasked with loading data from a series
of flat files. Easy enough. The interesting requirement though, was to obtain
a hash value from the file before loading to determine if in fact the file had been
previously loaded.

The client had an executable that when run, provided a hash value as a return
value. The solution I came up with was to use a for-each file enumerator to loop
through the files, assign a variable to the full-qualified value of the file and then
pass that variable to the executable using an Execute Process Task.

When the Execute Process Task retrieved the value of the hash for that file, I
would then assign it to a variable. Earlier in the process I created a log table
that stored nothing but hash values. So all I needed to do at this point was to
check for the existence of the hash value in the log table to determine if the
file had been previously loaded. If the hash value existed in the table, then the
file has been loaded, and if not, load the file. That's it!

While we're on the topic of Chicago, I must admit that it is my favorite city to
visit. Why? Because it just plain rocks! All the great places to eat and drink,
the tall buildings with amazing architecture, and the people are the friendliest
I've encountered all of the 50 states.

Some of my favorite things to do when I'm working in Chicago is eat, drink,
and explore the city. Once I get off work, it's show-time baby!

Below are some pictures of my time there:

The Chicago River The Sears Tower

Union Station SSIS in Chicago!

Food court inside the Sears Tower
Look at all that deep-dish! Financial district

The hustle and bustle of the city.
I Love It!

Theater District

There's that Fat Tire again!

Custom Reporting Services Templates

Darren Herbold — Thu, 11 Sep 2008 22:43:00 GMT

Every client that I've been to has always requested that their reports be "consistent"
across the environment. What better way to accomplish this than to create a template
that can be used for all new report creation!

Basically a report template is nothing more than a report (RDL file) that you choose
as a starting point for building new reports from. The only catch is that you have to
place this report in a specific location on the file system so that the BIDS IDE will
see it. That location is:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Now when you want to use this template, right click on the Reports folder in your
project and select New Item. You will then see the name of the report template
you created and added to the above directory. In the graphic below the template is
called AdventureWorksTemplate.

Below is the template I created that you can download and use and modify at your convenience.

Download the RDL file here: CustomReportTemplate.zip

If you have any questions, please feel free to reply to this blog!

Great experience in Redmond (Microsoft)

Darren Herbold — Wed, 10 Sep 2008 22:09:00 GMT

Well I just got back from Redmond where I had the opportunity to be included
in a focus group to set standards for upcoming Microsoft certification exams.
I collaborated with a group of highly-skilled individuals (most all MVPs) and had
a lot of fun during the process! Some of the folks I met were: Matthew Roche
(MVP who has been recently added to Microsoft ranks, Congrats Matthew!),
Peter (an MVP from Australia), John (MVP) and Rafael (MVP) from Mariner in
Charlotte, and Thomas from Paris, France. I don't remember some of the others
names, so if I left anyone out, I apologize!

Below are some pics I took while on the trip:

View of the city from the highway(obviously) SafeCo Field

Heading to Redmond!

Microsoft's building (one of many)

Me drinking Fat Tire at Red Robin
(Red Robin, yuck! I'm sorry, its
just plain awful) Market Street

Awesome seafood dish at Market Street!

Me and Thomas Some random I met while out drinking
(he kinda looks like Bill Gates!) (kidding, that's the wife Ashley)

Of course no West Coast trip would be complete without Fat Tire!
Skinny Dip beer is also made by New Belgium and it's awesome!
It may be time to move to Seattle!

Some weird space-looking thing Me and the random again.
(I'm in so much trouble!).

Redmond Bound!

Darren Herbold — Thu, 04 Sep 2008 10:07:35 GMT

I'll be at the Microsoft campus on Friday contributing to a focus group on BI certification
testing questions/scoring. I'm excited to contribute to this effort and mingle with some of
the Microsoft folks. I always love to travel to the west coast because it means I get to drink
my favorite beer, Fat Tire!!

PragmaticWorks Summit

Darren Herbold — Thu, 04 Sep 2008 09:56:56 GMT

Last week we kicked-off our week-long summit in Jacksonville, Florida. This is where we have
the opportunity to get together as a team and brainstorm on how we can grow the company.
This usually includes creating a roadmap for our new products, develop new sales strategies,
and conduct round-table brainstorming sessions on our offerings as a company. We accomplished
everything on our agenda and I'm very excited about the growth prospects of our company as
a whole. We are also proud to welcome our newest member to our sales staff, Robert Peters.
He's a highly qualified sales director from Quest Software, who decided to make a move to
a small and innovative company (that's us). Anyway, we are grateful to have him on board and
look forward to working with him. I'd also like to mention our newest and very talented developer
Mike, who just came on board a couple of weeks ago. Welcome Rob and Mike!
Below are some pics that I took on my BlackJack II mobile during my week down there.

These pics were taken at our office in Jacksonville.

Me and Brian above. Robert Peters, Brian, and Nayan

Brian, Tim, Nayan, and Rob Me, Tim, Nayan, and Rob

Tim and Nayan at Brian's house Tim and Nayan

Me and Rob getting a little crazy (things got a little fuzzy after that)...

Reporting Services 2005 Webinar on August 28th

Darren Herbold — Thu, 28 Aug 2008 01:52:07 GMT

On Thursday the 28th I will be giving a free SSRS 2005 training webinar, so
for those of you who can attend, please do! It's broken out into 2 sessions,

9:00AM - Register here: https://www2.gotomeeting.com/register/148698457

1PM - Register here: https://www2.gotomeeting.com/register/344677967

Here's the agenda:

In this session , join SSRS author Darren Herbold for a crash course in SQL Server Reporting Services (SSRS). Darren covers creating an end to end report highlighting all the steps necessary in designing, developing, and deploying a Reporting Services report in your environment. He covers using stored procs as data sources, design controls, parameters, grouping, drill-down, and deployment as well as other useful tips and techniques along the way.

To Type 2 or Not Type 2?

Darren Herbold — Thu, 14 Aug 2008 11:38:03 GMT

If you have ever worked on a Data Warehousing project, specifically with being tasked in developing
and loading (ETL) a dimensional model, you will have no doubt encountered the decision to implement
a dimensional type architecture. Basically, you have 2 choices (3 really, but I won't go there) when
deciding on a dimension type in your warehouse. With Type 1, you basically get no history.
For example, consider a Customer dimension called DimCustomer. This dimension may contain address
information. With Type 1, you would never know if a particular customer had changed addresses.
Is this important? Maybe it is and maybe it is not. It's up to your business analysts to decide.
Of course you will always add new customers to the table as they arrive, but no updates, because
that's Type 1.

Much to your dismay, the business analysts have decided that they want to track the changing
address attribute of your DimCustomer table. This is where Type 2 comes in. It keeps track of
history. Unfortunately, implementing this type of dimension is a bit more work. Since you are now
tracking history for this dimension, you will need to add 2 date attributes that will define a range
of time when the row of data has changed. A good example of the names for these attributes
would be EffectiveDate and ExpiredDate. You will also need another attribute that will "flag" the
row as the most current. This will of course, make your ETL more complicated from a Dimension
and Fact table load.

So, when deciding between these two dimensions types, the answer lies in the business question.
Is it, for example, important to your decision makers to track a customer's address change between
business transactions? If so, use a Type 2, if not, then Type 1.

SQL Server Reporting Services 2008 Presentation

Darren Herbold — Wed, 13 Aug 2008 00:29:00 GMT

Last week I had the opportunity to speak on SSRS 2008 at the Midlands PASS chapter in South Carolina.
Given the fact that it was a horribly stormy night, (I got soaked walking, no, running to the building) I appreciate
all the folks who came out for the session. Fortunately, I was working with the RC0 version of the product
which is significantly more stable than the CTP6 version I was previously using. I can't tell you how excited
I am about this latest version of reporting services.

To recap, the presentation covered the following topics:

- Architectural changes
- Designer enhancements
- Tablix
- Charts and gauges

I'd like to share the materials I used in this presentation which includes the project files as well!

Project files: SSRS08.zip
Powerpoint: SSRS08.pptx

SQL Server 2008 Integration Services Book

Darren Herbold — Tue, 12 Aug 2008 23:51:20 GMT

Recently, I received from the publisher, an updated image of the cover of the forthcoming
book titled Professional Microsoft SQL Server 2008 Integration Services. I'm very excited
to have been involved in this project and hope that it gives those the knowledge they need
to start working with this exciting new technology.

I have to admit, seeing my face on the cover of a Wrox Press book (now Wiley Press) is
one of the proudest moments in my life and marks a milestone in my career. (I'm the one
the far right.) While co-authoring this text, I relied on my deep experience with this product
from a consulting point of view to bring "real world" knowledge to the table. Enjoy!

Here's the link on Amazon:
http://www.amazon.com/Professional-Microsoft-Server-Integration-Services/dp/0470247959/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1218583284&sr=8-1

Speaking at PASS in Columbia SC

Darren Herbold — Sun, 27 Jul 2008 02:18:02 GMT

While doing a big ETL project in Columbia, SC, I volunteered to
speak at the local PASS chapter. Brian Kelley who heads up the
group was kind enough to let me speak. The topic of the evening
will be on Reporting Services 2008. Below is the information regarding
the event. If you are in the area, please attend!

The Midlands PASS chapter will hold our normally scheduled meeting on Thursday, August 7, 2008, to host Darren Herbold of Pragmatic Works Consulting and contributing author of the forthcoming Professional Microsoft SQL Server 2008 Integration Services from Wrox (Darren is on the far right). Darren will be giving a presentation on SQL Server 2008 Reporting Services. The meeting will once again be held at Training Concepts off of Berryhill Road. We will begin our meet and greet time at 6:15 PM as usual and start the presentation between 6:30 and 6:45 PM. I’ll be sending out an agenda next week as a reminder.

Performance Point Basics Part 1

Darren Herbold — Thu, 19 Jun 2008 01:45:02 GMT

As we all know, our friends in Redmond have publicly declared their intention to "own" the BI market.
Their latest offering to obtain that ambitious goal is a product called Performance Point Server.
Admittedly the name of the product doesn't readily reveal its function or purpose, which leads us to
the question: What is Performance Point Server?

Performance Point Server in short is a set of client tools (and a server component) that empowers
end-users to create rich business intelligence structures and reports without the requirement of
possessing data warehousing experience (sort of). The reason I say "sort of" in regards to DW
experience, is that even though Microsoft has done a great job in abstracting complex data ware-
housing concepts in the tool (by design), having some knowledge of data warehousing will most
certainly be helpful, but again not necessary.

While past or current experience in data warehousing is not a requirement to successfully use
the product, a solid background in accounting is. Performance Point's main bent is toward financial
reporting and thus, accounting functions of an organization. It's safe to say that Performance Point
is a tool to aid financial and accounting departments to get the most out of their data from both a
planning and a forecasting perspective plain and simple.

In the next series of articles, I will explain all the pieces and elements of Performance Point, their
purpose, and how they all work together to form a complete solution.

Creating a Charting Report from Analysis Services Cube Data in Reporting Services 2008

Darren Herbold — Sat, 07 Jun 2008 20:22:42 GMT

Reporting Services in 2008 has provided some exciting new enhancements that will really
spice up your reports. In this example, we will create a charting report that uses the new
charting controls (Dundas charts that MS bought. Sweet!).

The cube data was created from Analysis Services using the AdventureWorksDW database.
Its a fairly simple cube that contains Internet Sales as the fact table, and Date and Product
dimensions. The Date dimension contains a hierarchy with year, month, and day levels. The
Product hierarchy contains Product Category and Product Subcategory levels.

Start a new Reporting Services Project and then select New -> Data Source from the Report Data
menu on the left.

On the "Type" drop-down, choose Analysis Services. Once you
have done that, edit the credentials and select the Analysis Services database that you created.

Once you have created the data source properly, you can now create a Data Set
for your Chart to consume. Locate the Data Source you just created from the
Report Data window, right click on it and select Add Data Set.

Adding a data set will prompt you for more information to create the data set.

You may notice that this property page says nothing about Analysis Services,
but don't worry, it's just a click away! Click on the Query Designer button
to build your "query", in actuality it will allow you to choose your dimensions
and measures for the report as shown below. From here you can really let
loose. From the Dimension column drop-down from the filter section, select
the Dim Product dimension.

Select a product hierarchy that contains a Product Category level. In the
Filter Expression, select the "ALL" level. Next, expand the fact Internet
Sales measure group and drag over the Sales Amount measure onto the
query designer below.

Next, expand your Dim Product dimension from the available dimensions on
the left. In this example I have a Product hierarchy called: E2E Product Hierarchy
that contains the category, subcategory, and product levels. Expand your
product hierarchy and drag over the Product Category level onto the query designer.

For the next dimension, I have a Time dimension with a time hierarchy that
contains Year, Quarter, Month, etc.. If you have created a similar one, expand
the dimension and also expand your hierarchy. Drag over the year level onto the
query designer to finish off our query. Your data set should look similar to the
results below. If your results differ, revisit your hierarchies in Analysis Services
and make adjustments where needed. Click OK when finished.

Back in the Dataset Properties window you will see the created MDX in the
query window. Unfortunately, you can't modify from here as it is grayed out!
Click OK to return to the Report Designer.

Displaying the dataset we just created in a chart is the easy part now!
Drag over a chart control from the toolbox onto the report designer.
Once the chart has been placed on the designer, the only thing you have
to do now is drag over the fields from the dataset onto the Chart landing
areas.

Drag the fields from the dataset from the dataset you created onto the chart
areas as shown below. Drag the Sales_Amount field to the Data Fields area,
drag the Product Category field to the Series area, and drag the Calendar
year to the Category area of the chart.

When you are finished dragging over the fields, simple click the Preview tab to view the results!

Speaking at SQL Server User's group meeting in ATL

Darren Herbold — Wed, 04 Jun 2008 13:47:23 GMT

This Monday (June 9th) I will be speaking at the SQL Server Users Group meeting in Atlanta.
The topic will be on SQL Server Reporting Services 2008. I'm excited to introduce the new
and exciting features of the latest version of Reporting Services. I plan on making the
presentation very hands-on with just a few slides. Some of the topics will include
the Tablix and Charting controls. These are by far the best new features and I look forward
to demonstrating them. Come out and see us: www.atlantaMdf.com