SQL Server

You are currently browsing the archive for the SQL Server category.

A Record SetAs you may already know, it is possible to use the Execute SQL Task to populate a package variable with a result set.

In case you’re not that familiar with this technique yet, here are a quick two words on setting that up.  You just give it a query, set the ResultSet property to Full result set and configure a package variable in the Result Set property window.  The package variable’s type is System.Object.

But what exactly is this mysterious System.Object and how can we explore it?  Well, that depends.  More precisely, it depends on the Connection Type which you’ve chosen in the Execute SQL Task properties.

Let’s explore two possibilities: ADO.NET and OLE DB.  Our end goal is straightforward: retrieve the number of records in the result set.

The query which I’m using in the Execute SQL task is this one:

select ProductAlternateKey
from dbo.DimProduct
where Color = 'blue'

On my AdventureWorksDW2012 database it should return 28 records: 28

Exploring the ADO.NET result set

The first step is finding out what type exactly this result set object is.  Hook up a Script Task to your Execute SQL task and put a breakpoint on it.  Now run your package and examine the Locals window:

Debugging the Control Flow to find the object type

Well look at that, it’s a System.Data.DataSet!  Using this knowledge it’s fairly simple to produce code that fetches the record count:

DataSet ds = (DataSet)Dts.Variables["MyResultset"].Value;
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());

Note: don’t forget to add the package variable to the ReadOnlyVariables before opening the code editor.

The System.Data namespace is included by default in the using statements, no worries there.  So we can just cast the variable into a Dataset.  The DataSet object contains a DataTableCollection called Tables.  As there’s only one result set this is located at index zero.  We travel down the object tree to finally find the Count property of the Rows DataRowCollection.

And here’s the result:

The message box shows 28 items

That’s all there’s to it, easy huh?  Let’s move on to our second option, OLE DB.

Exploring the OLE DB result set

Once again we start at the beginning: with the debugging of the Control Flow to find out what object type our mysterious System.Object is:

The OLE DB result set gives us a System.__ComObject, hmm...

Hmm, System.__ComObject, that’s … special.  Ow right, the OLE DB provider uses a COM wrapper.  How can we “unwrap” our object and introduce it to the .NET world?  Let’s see if we can find out what’s hidden behind that wrapper, by using the following code:

MessageBox.Show(Microsoft.VisualBasic.Information.TypeName(Dts.Variables["MyResultset"].Value));

TypeName is a VB.NET function and retrieves the data type of the parameter passed into it.

To get this to run in a C# SSIS task you first need to add the Microsoft.VisualBasic reference:

Adding a reference to the VB.NET assembly

Executing the package results in this:

Result type: Recordset

So, our result is Recordset, hmm, well, I think we more or less knew this already.  What kind of Recordset?  Well, an ADO Recordset.  We know this because the following code works:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["MyResultset"].Value);
MessageBox.Show(dt.Rows.Count.ToString());

Basically, we use the Fill method of the OleDbDataAdapter to fill a System.Data.DataTable with the data from the ADO Recordset.  The version of the method in our example (there are several overrides) accepts two parameters:

public int Fill(

DataTable dataTable,

Object ADODBRecordSet

)

With the DataTable filled we’ve got once again access to a Rows DataRowsCollection, exactly the same as in our ADO.NET example in fact.  Executing the package now results in exactly the same message box as shown earlier: 28 records!

Beware of pitfalls

If you mix the two methods up you’ll get funky errors such as:

System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

and also

System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.

So be careful, use the right object types for your particular System.Object.

Conclusion

In this article I’ve demonstrated a couple of methods which can be used to retrieve information from the mysterious System.Object result set in an SSIS package.

Have fun!

Valentino.

Additional References

Execute SQL Task

Result Sets in the Execute SQL Task

OleDbDataAdapter Class

Share

Tags: , ,

About half a year ago I wrote an article in which I explained how you can get newsletter-style reports implemented which work in every renderer (except just the image ones), I called that article Creating Multiple-Column Reports.  And last week that article received an interesting comment, a comment which requires a bit more than just a couple of lines in reply.  So I decided to write an article instead!

I’ll be using the same technique as in my previous article but the output (and part of the technique) will be different.  So I will not go into all detail on the parts that overlap.  If something in this article isn’t clear, please have a read through my previous article first.

The resulting RDL can be downloaded from my Skydrive.

The Scenario

Just like in my previous article the data should get rendered over multiple columns, in the example I’ll implement two columns but the technique can be used easily for more columns as well.  However, the difference is this: instead of spreading the data horizontally, from left to right, it should spread vertically, from top to bottom!

The Query

Let’s start with a really simple query:

select DP.ProductKey, DP.EnglishProductName
    , COUNT(*) over () RecordCount
from dbo.DimProduct DP

When executed against the AdventureWorksDW2012 database it gives something like this:

Output of my simple query

What you see is a list of products.  The last column, RecordCount, has been added by using the OVER clause and represents, guess what…, a record count.  It will become clear later on why this is needed.  In case you’re not familiar with the trick with the OVER clause, have a look at this article: Aggregating Data With The OVER Clause.

The Tablix

Start by adding an empty Table to the report and remove one of the columns.  Also remove the header line and the default (Details) Row Group:

Empty table without row group

Connect the table to the dataset by setting the DataSetName property.

Nest another Table inside the left cell of the main table.  This is explained in detail in my other article, if needed.

Nesting a tablix inside another tablix

Open up the properties of the nested tablix and switch to the Filters page.

Filtering the tablix to show only the first 50% of records

The expression in the Value box is this:

=Fields!RecordCount.Value / 2

As you can see, we’re using the count of records to decide which records should be shown.  As we want half of them, we’re dividing by two.  This also assumes that the dataset contains a field with an increasing number.  Out of convenience, I used the ProductKey from the DimProduct table.

That’s the left column completed.  Now select the nested tablix and copy/paste it into the remaining empty cell on the right of the main tablix.  Open up its properties and switch to the Filters page.

Filtering the remaining 50%

Change the Operator as shown in the screenshot, > instead of <=.

Let’s render that report!

Data rendered in two columns, top down!

Looks good doesn’t it?  We’ve got 606 records and the first item in the second column is 304!

Conclusion

In this spin-off on my previous article on rendering data over multiple columns I have demonstrated a method which can be used to render the data top-down and spread over two columns.

Have fun!

Valentino.

Share

Tags: , , ,

Date and TimeIn my previous article I discussed all possible methods to get numbers formatted any way you prefer.

But I didn’t mention a special type of number: date and time values!  So let’s look into these now.

The Format Property

Just as with regular numbers we can use the Format property of the SSRS textbox to tell the report what the output should look like.  But what do we need to type in there?  Well, once again there are some predefined date/time formats which can be used here.

The table below shows a couple of examples of how to format the output of the Now() function:

Formatting Now() through predefined formats and the Format property

And if those predefined formats are not sufficient for your requirements then, guess what, there are some user-defined formats which can be used instead.  Some examples:

Formatting Now() through user-defined formats and the Format property

The FormatDateTime Function

What if you don’t want or just can’t use the Format property?  No worries, in that case you can rely on a couple of functions.  The first one is the FormatDateTime function.

Here’s what it’s definition looks like:

Function FormatDateTime(
   ByVal Expression As DateTime,
   Optional ByVal NamedFormat As DateFormat = DateFormat.GeneralDate
) As String

The first parameter should be obvious, it’s the DateTime to be formatted to string.

The second parameter expects a value of the DateFormat enumeration.  Here are the possible settings and their corresponding output:

Using the FormatDateTime function

The Format Function

Alright, so what if that FormatDateTime function is still too limited?  Then rely on the Format function!  Some examples:

Using the Format function to format a datetime value

As you can see, all possible strings that you can put in the Format property will work here as well.  In fact, the links I gave you earlier lead to subpages of this Format function!

Impact of The Language Property

You should be aware that the exact output of the Date/Time formatters depends on the language settings of the report.  Each report has got a property called Language.  By default this is set to en-US.  Let’s set it to nl-BE (yes, Belgian Dutch) and look at some examples:

Impact of Language property

Impact of System Settings

You should also be aware that this Language property is not the only property that has an impact on date/time value rendering.  The system settings play their role too and have an impact on how the Date/Time report parameters behave.

These are my current settings, Format is set to Dutch (Belgium):

Region and Language settings

With those settings here’s what a Date/Time report parameter looks like in BIDS preview:

A Date/Time parameter with system settings set to Dutch (Belgium)

As you can see, the month and day names or abbreviations are translated to Dutch and the format of the parameter is dd/MM/yyyy.

Here’s what that same parameter looks like when I change my system settings to English (United States):

The Date/Time parameter with system settings set to English (United States)

Conclusion

The above should give you sufficient info on getting those dates and times formatted anyway you’d like.  If not, type your problem down in a comment below and I’ll see if I can help you!

Have fun!

Valentino.

Share

Tags: , ,

Thanks all for attending our presentation about Data Visualization Tips & Tricks at this year’s Community Day.  I hope you’ve enjoyed it as much as we have!

The slides and SSRS project can be downloaded from my SkyDrive through this link.

As said before: let me know if you’re stuck with visualizing something and I’ll see if I can find a trick for it!

Have fun!

Valentino.

Share

Tags: , , ,

The Matrix NumbersOn the forums I frequently encounter questions related to formatting certain things for display in a Reporting Services report.

Formatting questions can be quite broad.  Here are some examples to give you an idea:

How do I convert a datetime to a specific string format?

How do I display an amount with a currency symbol and thousands separator?

How can I display a percentage with 4 positions following the decimal separator?

The internet does have some information available on formatting.  However, this info is spread over several different pages and to be able to find it you need to be aware of a couple of things.  Which is why I decided to write an article that’s dedicated 100% to the topic of formatting stuff in SSRS reports so I can refer to it when applicable.

Introduction

Basically you’ve got two options when formatting.  The first one is using the Format property, which is one of the properties of the Textbox.

The Format property

Or you can decide to not use the Format property at all and write a custom expression that uses some formatting functions to create the value string.  I’ll explain both in combination with all applicable data types.

You should also be aware that the formatting functionality is not specific for SSRS only.  If you’ve got some development experience you’ll probably recognize some syntax.  SSRS is actually just a layer on top of the .NET framework, as far as formatting is concerned.  So in many cases you’ll find interesting info online by searching in the .NET documentation.  I’ll be referring to several .NET-related pages in the text below.

The Query

As with any good report, before we can display anything we first need to write a SQL query.  My data source is the AdventureWorksDW2012 sample database, available at CodePlex.

And here’s the query:

SELECT dd.FullDateAlternateKey as OrderDate
      , f.SalesOrderNumber
      , f.SalesOrderLineNumber
      , f.OrderQuantity * 42000 as OrderQuantity
      , f.UnitPriceDiscountPct
      , f.SalesAmount
      , dc.CurrencyAlternateKey as CurrencyISO
FROM dbo.FactResellerSales f
inner join dbo.DimCurrency dc on dc.CurrencyKey = f.CurrencyKey
inner join dbo.DimDate dd on dd.DateKey = f.OrderDateKey
where UnitPriceDiscountPct > 0

Little sidenote: I’m multiplying OrderQuantity with 42000 to get larger numbers to be able to demonstrate certain things, such as the thousands separator.  Don’t do that in the production version!

Formatting Numbers

Regular Numbers

Let’s start with the easiest of them all: the regular numbers.  Without any formatting specified, numbers are rendered without any “make up”, similar to how the Management Studio shows them in the query Results window.  For a report that’s usually not what we prefer.

I mentioned the Format property earlier.  Now the question of the day is: what can you put in there to format a number differently?  There are some standard format strings that can be used.  Here’s one: type an N in the Format property of a Textbox that displays the OrderQuantity.  When rendering the report you’ll get a number with a thousands separator and two decimal positions:

Formatting a number using standard format string

What if we don’t want any decimals?  Or what if we actually want to display four instead of two?  No problem, precision can be specified by just adding a trailing zero or four to the N specifier: N0 or N4.

Formatting numbers while specifying number of decimal positions

The standard format strings are useful but not very flexible.  If you need more flexibility then there are some custom format strings to be used.  In that case the equivalent of N0 would be #,###.  Here are some more:

Formatting numbers using the custom format string

The double comma that you see in the #,#,,.# example is called the number scaling specifier.  As you can see, for each instance of that specifier the number gets divided by 1000.  This is a very useful method when you want to use minimal space such as in charts.

Using the number scaling specifier to minimize space usage

With that knowledge we can even take it one step further!  (Get a coffee if you haven’t already.)

Let’s first create a calculated field called NumberFormat using the following expression:

=Switch(
    Fields!SalesAmount.Value < 1000, "0.#",
    Fields!SalesAmount.Value < 1000000, "#,.#K",
    true, "#,,M")

Now that we’ve got that field we can use an expression like this:

=Format(Fields!SalesAmount.Value, Fields!NumberFormat.Value)

Note: the Format function in use here is explained a bit further down.

Here’s what that would look like in a table:

Using the number scaling specifier

If you want to specify a different format for negative numbers, don’t worry, can be done as well.  Just enter two custom format strings separated with a semi-colon: #,###;(#,###).  And the same for zero values, add another semi-colon and format string at the end: #,###;(#,###);’-’:

Specifying a different format for negative or zero values

Can you achieve the same without using the Format property?  Sure, as I mentioned in the intro you can also make use of some functions when displaying the value.  One of the functions that can be used is called FormatNumber:

Function FormatNumber(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

NumDigitsAfterDecimal is used to specify precision.

IncludeLeadingDigit has an effect on numbers smaller than one. When set to True 0.6 will render as 0.6 while that will become .6 when set to False.

UseParensForNegativeNumbers will render parentheses around negative values when set to True, otherwise you’ll get a leading dash: (5) or -5.

GroupDigits indicates if the thousands separator should be rendered.

To give that a try, replace the =Fields!OrderQuantity.Value with this expression:

=FormatNumber(Fields!OrderQuantity.Value, 3, True, True, True)

Here’s what that looks like when rendered:

Using the FormatNumber function

There’s something you do need to take into consideration though.  Take a good look at the above screenshot and compare it with some of the other ones shown earlier.  Do you see the horizontal alignment difference? The FormatNumber function actually returns a string while the formatting options through the Format property keep the value numeric and thus aligned right.  That has an effect on the export to Excel, so you may want to experiment a little before delving into using custom expressions.

Another really useful function is called Format. Here’s what it looks like:

Public Shared Function Format( _

ByVal Expression As Object, _

Optional ByVal Style As String = “” _

) As String

It works very similar to the Format property: any valid format string in the Format property will also be a valid string to be passed into the Style parameter of the Format function.

Sidenote: if you’re looking for the MSDN page on a certain function but can’t find it, try this.  First locate the function in the Expression builder:

Locating the Format function in the Expression builder

Then enter the function name followed by the description (or part of it) surrounded with double quotes into the search box of your favorite engine:

How to find a function description

Reference page found!

Leading Zeroes

What if you want your number to display leading zeroes?  That can be achieved by using another standard numeric string formatter: D.  Try using D10 on the SalesOrderLineNumber:

Formatting numbers with leading zeroes

There you go, leading zeroes!

Currency Amounts

With regular numbers covered, let’s switch to currency values.  The standard format string for currency amounts is C.  When specified, the currency amount will be rendered with a leading currency symbol, a space, thousands separator and two decimal positions.

Using the standard format string for currency amounts

The symbol that’s used depends on the report’s Language property unless that property is not set.  I’m not going into localization issues now so have a look at following page for more info if needed: Set the Locale for a Report or Text Box (Reporting Services)

Can you use a custom format string for currency amounts?  Sure, here’s what it looks like: ‘€ ‘#,#.  You actually hard-code the currency symbol into the format string.

Using a custom format string for currency amounts

BTW: those single quotes are optional, you’d get the same result if you leave them out.

Formatting currency values through expressions is possible as well.  The function specifically created for this is called FormatCurrency:

Function FormatCurrency(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

As you can see it is very similar to the FormatNumber function.  The only difference is that it will generate a number with a leading currency symbol.

So what if your data consists of mixed currencies?  In that case the FormatCurrency function is not very useful.  But it can be done!  Let’s demonstrate this using the SalesAmount and the CurrencyISO fields.  In our sample data the CurrencyISO field contains the three-letter ISO code of the currency.

The reference data, a list of currencies accompanied by their symbol, should be available in a database somewhere.  For demonstration purposes I’ll create a new dataset called Currencies using the query below.  So just imagine it comes from a table. :)

select 'USD' CurrencyISOCode, '$' Symbol
union all select 'EUR', '€'
union all select 'CAD', '$'
union all select 'GBP', '£'

We can now use the Lookup function to retrieve the symbol that belongs to the currency ISO code.  Here’s what that would look like:

=Lookup(Fields!CurrencyISO.Value, Fields!CurrencyISOCode.Value, Fields!Symbol.Value, "Currencies")
    & " " & Format(Fields!SalesAmount.Value, "#,0.00")

The expression above would give us following output:

Mixed currencies using correct symbol through lookup on different dataset

Percentages

Let’s conclude this article by displaying some percentages.  We’ll create a new dataset for that:

select 0.33 Pct
union all select 1.5
union all select 0.0045
union all select 42

Once more we’ve got a standard format string that can be used.  Here’s what it looks like if we put this Pct field in a table.  The left column is not formatted while the right has got the standard P formatter in the Format property:

Formatting percentages with the standard P string

Without any precision specified the P formatter uses two positions following the decimal separator, just like we’ve seen earlier.  And we get a space followed by the percentage symbol added to the end of the number.

But what’s really important to note and differs from regular numbers is that the P formatter will actually multiply the value by 100!  That means percentages from 0 to 100 need to be stored in the 0 to 1 range.  Don’t forget to take that into account or your reports won’t make much sense.

Another option to get this percentage formatted is through using FormatPercent function:

public static string FormatPercent(

Object Expression,

int NumDigitsAfterDecimal,

TriState IncludeLeadingDigit,

TriState UseParensForNegativeNumbers,

TriState GroupDigits

)

As it’s very similar to other functions mentioned above I won’t go into much detail here.  Try the following expression:

=FormatPercent(Fields!Pct.Value, 1)

And here’s its result:

Formatting with the FormatPercent function

If you need more flexibility have a look at the % symbol in following format string: #,0.00%

Result:

Formatting percentages through custom string

If you need per mille then then you’ll need to use the per mille sign: #,0.00‰

The per mille formatter

Notice how the per mille symbol multiplies the value by 1000, don’t forget to take it into account!

Tip: if you don’t know how to type that symbol, simply copy it from my expression above.

Conclusion

In this article I’ve explained practically all different options to get your numbers formatted the way you’d like to.

For additional tips have a read through the next chapter in which I handle even more formatting, such as for dates and times.

Happy formatting!

Valentino.

Share

Tags: , ,

« Older entries § Newer entries »

© 2008-2014 BI: Beer Intelligence? All Rights Reserved