SQL Server 2008R2: Edition comparison & Pricing

In addition to my previous post about the SQL Server 2008 editions, here’s another comparison sheet for the 2008 R2 Editions:
Features Supported by the Editions of SQL Server 2008 R2

Many of the differences were kept intact, except for the addition of the Datacenter Edition.
If you want to use the new PowerPivot features, you’ll be forced to buy the Enterprise Edition though.

SQL Server 2008 R2 Pricing
For more information about pricing and licensing, check the SQL Server 2008 R2 Licensing Quick Reference Guide.

Don’t forget: The 2008R2 Express with Advanced Services Edition (free) comes with Reporting Services:

SQL Server 2008: Standard vs Enterprise detailed feature comparison

Before choosing the right edition, you may want to know that there are many versions:

– Enterprise Edition
– Standard Edition
– Workgroup Edition
– Web Edition
– Compact Edition
– Express Edition
– SQL Server 2008 Express Edition.
– SQL Server 2008 Express with Tools.
– SQL Server 2008 Express with Advanced Tools

Standard vs Enterprise: Microsoft published a simple comparison sheet here and a detailed comparison here (PDF).
A full comparison sheet for Compact vs Express is available over here.

Basicly it comes down to a few mind breaking features.
The Enterprise Edition offers extra/extensive features like:
– To work with large amount of data (terabyte-style or billion-row fact-tables)
– More BI features available (such as parallel processing, cube partitioning, and text mining)
– Less downtown (by using online indexing, fast recovery, etc.)
– Replicate data from Oracle (if you want to)

But….the Standard Edition is significantly less expensive than the Enterprise Edition.

SSRS InScope() function: The key to format your matrix!

The InScope() function checks to see if the current item is in the specified scope.
For a basic matrix report with the use of ‘Totals’ on both axes:

The Green cells fall out-of-scope of the ColumnGroup, and In-Scope for the RowGroup
The Grey cells fall out-of-scope of the RowGroup, and In-Scope for the ColumnGroup
The Light blue cells fall In-Scope of both the ColumnGroup and the RowGroup.
The Dark blue cell falls out-of-scope of both the ColumnGroup and the RowGroup.

Expression example code:

=IIf(InScope(“ColumnGroup”),
IIf(InScope(“RowGroup”),”LightBlue”, ”Grey”),
IIf(InScope(“RowGroup”),”DarkGreen”, “DarkBlue”)
)

Thanks to Luke Hayler for his great example.

New Features in Reporting Services 2008 and 2008 R2

Stumbled upon a good blog article regarding the new features in SSRS 2008 & 2008 R2.
It is written by Bill Pearson, check it out.

Multi value parameter within SSRS dataset (Command type: text)

Today I wanted to use my Multi-value parameter function within a SSRS dataset (Command type: text).
Guess what…it didn’t work!

After literally an hour, I found the answer….it’s not possible at all.
Instead SSRS basically ‘chops’ the multi value string into something like: ‘aa’,’ab’,’…’
My function requires a single string instead of many small ones, for example: ‘aa,ab,…’

Now you’re thinking, what about the Join-function? As in:
=JOIN(@MultiValue,”,”)
Which returns a single string like aa,ab (no quotes!)

Or if you wish:
=”‘”+JOIN(@MultiValue,”,”)+”‘”
Which returns a single string like ‘aa,ab’ (with quotes using dynamic string-manipulation)

Too bad….the JOIN-function isn’t available within the Dataset (nor is EXEC() btw)

The answer is actually quite simple, don’t use statements like
‘X IN (SELECT * FROM dbo.fn_MultiValue(@MultiValue,’,’))’
Instead use the dataset Filter (or List if you use cascading parameters in your report):

Suspending package validation

Here’s a great tip from my colleague Karlo Bartels concerning the slow validating process of large Visual Studio solutions.

Check out his blog:

Karlo Bartels: Suspending package validation

MDX CASE Example (Calculated Member – DateTime Format)

Rewrote my previous post, so it can be used within your MDX statement.

WITH
MEMBER [Measures].[MonthName] AS
CASE MONTH([Time].[Month].MEMBERVALUE)
WHEN 1 THEN “January”
WHEN 2 THEN “February”
WHEN 3 THEN “March”
WHEN 4 THEN “April”
WHEN 5 THEN “May”
WHEN 6 THEN “June”
WHEN 7 THEN “July”
WHEN 8 THEN “August”
WHEN 9 THEN “September”
WHEN 10 THEN “October”
WHEN 11 THEN “November”
WHEN 12 THEN “December”
END

[Time].[Month] value= [Time].[Month].&[2010-01-01T00:00:00]

For “January 2010”, just use:
WHEN 1 THEN (“January ” + STR(YEAR([Time].[Fiscal Month].MEMBERVALUE)))

SSRS month labeling (MDX dataset)

SSRS Switch Example (within expression) using MDX dataset.

In my case, the parameter dataset (MDX) doesn’t have a nice label for @Month:
@Month.Value = [Time].[Fiscal Month].&[2010-01-01T00:00:00]
@Month.Label = 2010-01-01 00:00:00

So I’m using a switch to project the name of the month on my report.

=Switch(
MONTH(CDATE(Parameters!Month.Label))=1,”January”,
MONTH(CDATE(Parameters!Month.Label))=2,”February”,
MONTH(CDATE(Parameters!Month.Label))=3,”March”,
MONTH(CDATE(Parameters!Month.Label))=4,”April”,
MONTH(CDATE(Parameters!Month.Label))=5,”May”,
MONTH(CDATE(Parameters!Month.Label))=6,”June”,
MONTH(CDATE(Parameters!Month.Label))=7,”July”,
MONTH(CDATE(Parameters!Month.Label))=8,”August”,
MONTH(CDATE(Parameters!Month.Label))=9,”September”,
MONTH(CDATE(Parameters!Month.Label))=10,”October”,
MONTH(CDATE(Parameters!Month.Label))=11,”November”,
MONTH(CDATE(Parameters!Month.Label))=12,”December”
)

For “January 2010”, just use:
MONTH(CDATE(Parameters!Month.Label))=1,
(“January “+CSTR(YEAR(CDATE(Parameters!Month.Label)))),

Link sharing

SQL Server 2008 R2 Books Online (by Microsoft)

Reporting Services Expression Examples (by Microsoft)