Surprised!Sometimes we take silly little things we do in our daily life for granted and assume everyone else is aware of them too.  That’s where we’re wrong: as I’ve found out, they aren’t!  Not always anyway.  That brought me to the idea to start this Silly SQL blog series.  Each post will explain one little thing I do or use regularly that makes my life easier.  Here’s the first one!

Last week I noticed a co-worker making a lot of keyboard noise while implementing a Data Flow Transformation in an SSIS ETL package.  When I turned around to have a look at his screen I saw he was working on an OLE DB Destination, nothing wrong with that.  Basically he was hitting the down arrow followed by TAB twice, down arrow again and so on in order to set up matching input columns with destination columns.  This method worked because we decided to put the incoming fields in the same order as the columns in the destination table and we also gave them the same name using aliases in the source query.

However, for tables with over 200 fields this method is quite tiresome (and annoying for colleagues unless they’re using a headset).  Nice as I am I decided to help him out.  I asked him if I could borrow his mouse for a second and then right-clicked in the grey area in between the two tables:

The Map Items by Matching Names functionality

You should have seen his face when he saw that window appear, and even more when I selected Map Items by Matching Names!  Apparently this is some functionality that’s been hidden really well because in that same week I caught another co-worker in exactly the same situation.  And these are not junior profiles I’m talking about!

If you’re now thinking “Hang on, I never have to set up the matches myself?” that may be true!  If you’re always creating new packages and the names are matching then BIDS will set up the matching fields automatically when you open the Mappings page.  But we are working with previously-defined templates to speed up development.  In that case BIDS will not set up the matches so that functionality shown above really comes in handy!

A fast way to find out if all fields have been matched is to click the Input Column header:

Click Input Columns header to put unmatched items on top!

This will order the items with the unmatched ones, recognized by <ignore>, on top!

See, the things you take for granted aren’t always that for others, as proven here.

That’s it for now, let’s see if I can come up with another silly thing for the next post!

In the meantime: have fun!

Valentino.

Share

Tags: , ,

One of my presentations last year covered the topic of automating SSRS deployments through the RS Utility

In that presentation I covered how to create data sources and how to deploy shared datasets and reports.  What I didn’t cover is how you’re supposed to deploy other items, such as images or PDFs.  Luckily I got inspired once more through a forum question, so here’s the script for exactly that!

For the interested reader: the scripts from my session can be downloaded through this follow-up post: Automating SSRS Deployment: Download.

The script below demonstrates how to deploy a PDF but the same technique can be used for any file type.

    'The following global variables need to be passed in through the rs command:
    'Dim name As String = "CatalogItem.pdf"
    'Dim parent As String = "/Test"

    Dim reportFile As String = "scripts\resources\" & Name

    Public Sub Main()
        Console.WriteLine("Running script DeployPDF.rss")

        Dim overwrite As Boolean = True
        Dim fileContent As Byte() = Nothing
        Dim warnings As Warning() = Nothing

        'Common CatalogItem properties
        Dim descprop As New [Property]
        descprop.Name = "Description"
        descprop.Value = ""
        Dim hiddenprop As New [Property]
        hiddenprop.Name = "Hidden"
        hiddenprop.Value = "False"

        'PDF-specific property
        Dim mimeTypeProp As New [Property]
        mimeTypeProp.Name = "MimeType"
        mimeTypeProp.Value = "application/pdf"

        Dim props(2) As [Property]
        props(0) = descprop
        props(1) = hiddenprop
        props(2) = mimeTypeProp

        Try
            'Read file from disk
            Dim stream As FileStream = File.OpenRead(reportFile)
            fileContent = New [Byte](stream.Length - 1) {}
            stream.Read(fileContent, 0, CInt(stream.Length))
            stream.Close()

            Dim item As CatalogItem
            item = RS.CreateCatalogItem("Resource", Name, Parent, overwrite, _
                                        fileContent, props, warnings)

            If Not (warnings Is Nothing) Then
                Dim warning As Warning
                For Each warning In warnings
                    Console.WriteLine("Warning: {0}", Warning.Message)
                Next warning
            Else
                Console.WriteLine("CatalogItem: {0} published successfully with no warnings" _
                                  , Name)
            End If

        Catch e As IOException
            Console.WriteLine(e.Message)
        Catch e As SoapException
            Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText _
                              + " (" + e.Detail.Item("Message").InnerText + ")")
        End Try
        Console.WriteLine()
    End Sub

The most important line in all of the above is the one that calls the CreateCatalogItem method.

The first parameter of that function is called ItemType.  To find out what the possibilities are we need to look at the ListItemTypes method:

Item Type Description
Component A report part.
DataSource A data source.
Folder A folder.
Model A model.
LinkedReport A linked report.
Report A report.
Resource A resource.
DataSet A shared dataset.
Site A SharePoint site.
Unknown An item not associated with any known type.

 

A PDF is a resource so that’s the one we need!  When we specify Resource as value for the ItemType parameter the function expects an additional property through the Properties array parameter.  This differs from the deployment of a report or dataset.  The additional property is called MimeType and for PDFs the MIME type is application/pdf.

The script above can be called from a batch (.cmd) file, here’s a possibility:

@echo off

::Script Variables
SET REPORTSERVER=http://YourServer/ReportServer

::default location on 32-bit machines -> SET RS="C:\Program Files\Microsoft SQL Server\100\Tools\Binn\RS.EXE"
::default location on 64-bit machines -> SET RS="C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\RS.EXE"
SET RS="C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\RS.EXE"

SET LOGFILE="SSRS_DeployLog.txt"
SET SCRIPTLOCATION=scripts
SET TIMEOUT=60
SET ENDPOINT="Mgmt2010"

::Clear Log file
IF EXIST %logfile% DEL %logfile%

ECHO Starting deployment to %REPORTSERVER%

::Write Log Header
ECHO Starting deployment at %DATE% %TIME% >>%LOGFILE%
ECHO SCRIPTLOCATION = %SCRIPTLOCATION% >>%LOGFILE%
ECHO REPORTSERVER   = %REPORTSERVER% >>%LOGFILE%
ECHO TIMEOUT        = %TIMEOUT% >>%LOGFILE%
ECHO ENDPOINT        = %ENDPOINT% >>%LOGFILE%
ECHO COMPUTERNAME    = %COMPUTERNAME% >>%LOGFILE%
ECHO RS             = %RS% >>%LOGFILE%
ECHO. >>%LOGFILE%

::Run Scripts

ECHO ...deploying catalog items...

%RS% -i "%SCRIPTLOCATION%\DeployPDF.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v name="SomePDF.pdf" -v parent="/Test" >>%LOGFILE% 2>&1

::Finish
ECHO. >>%LOGFILE%
ECHO Finished deployment at %DATE% %TIME% >>%LOGFILE%
ECHO. >>%LOGFILE%

ECHO Deployment finished!  See %LOGFILE% for details.
PAUSE

That’s it for today, have fun!

Valentino.

Share

Tags: , ,

TSQL-Tuesday#050: The automation storyAutomation, whoah, what a subject for our monthly T-SQL Tuesday party!  This month’s event is hosted by SqlChow.  Head over to his post for more details on this month’s topic and to find out what T-SQL Tuesday party actually is, if needed:

T-SQL Tuesday #050: Automation, how much of it is the same?

Automation, what would the world be without it?  I have no idea but it would definitely not be the same!  And what would my job be without it?  Again I don’t know but it would definitely not be the same!

As a Business Intelligence consultant automation is a big part of the task list!  How else would we be able to build a data warehouse (DWH), if it wasn’t through automation?  Our ETL processes are all about automation: retrieving data (Extract), converting and remodeling it into something useable (Transform) and pushing it into the DWH (Load).  Without automation we would never be able to go home at 5PM and still couldn’t get it all done!

And it doesn’t end there, even for report delivery we use automation now and then: reports can get exported to a shared folder or emailed to a bunch of people through subscriptions.  You just set it up once and user-friendly output gets generated as scheduled!

Before I switched to the BI world – regular readers know that I used to be a software developer – automation was a big part of my job too: automating the import of flat files into an accounting application, ensuring money transactions got approved (or rejected) automatically, …  Automation is a big part of a software developer’s life too!

So as you can see it’s not only DBAs who would/could benefit from automation, practically everyone active in our IT domain can use it here and there!

Let’s see if I can share something more about automation, something which you might find useful in your daily job.  Ow yeah, do you see that Must-Have Downloads title in the right bar on this site, right underneath my tag cloud?  It mentions two of my favorite tools: SSMS Tools Pack and BIDS Helper!

SSMS Tools Pack

This great SSMS add-on, developed by Mladen Prajdic, should contain something useful for everyone using SQL Server.  My most favorite functionality is the Script Grid Results item when right-clicking the results of a query:

Script Grid Results

Using that functionality you can generate a script that inserts data into a table, real handy if you quickly want to move some data over into another table or obviously if you just want to script out your data!  Here’s your typing automated for you!

BIDS Helper

This is a must-have add-on for Visual Studio (BIDS) that adds a lot of additional functionality for SSIS and SSAS (both multidimensional and tabular) and even a bit for SSRS projects.

My favorite of them all: Smart Diff!  Using the regular Compare function on an SSIS package or SSRS report doesn’t produce the most readable result.  Since these are XML files they are a bit hard to read to humans.  And they contain item positioning info which obfuscates it all even more.  Smart Diff is smart enough to only highlight the parts in which you as developer are actually interested.  Have a look at the following documentation page and the screenshots will immediately clarify what I mean: Smart Diff

Other really useful additions are:

Sort Project Files: how annoying if your SSIS packages are not displayed alphabetically, right?  With this BIDS Helper functionality you can just right-click the SSIS Packages node and choose Sort by name!

Expression and Configuration Highlighter (SSIS): adds colored triangles on connection manager icons to indicate that they are configured through package configuration.  Same for tasks and connection manager which have an expression on them in a property somewhere.  Very useful while debugging packages you’re not familiar with!

And there’s much more, have a look at the main documentation page for the full list.

I hope the above info helps you in getting a couple of your daily tasks improved, or perhaps even some of your daily annoyances removed.  In my case it surely did!

Have fun!

V.

Share

Tags:

You all know that report parameters can be populated through a query, right?  And multi-value parameters get this magical (Select All) item as first one in the dropdown list.  I used the word magical in the previous sentence because this (Select All) item is not a real one.  It’s a bit like a fata morgana: you can click it but it doesn’t really get selected.  It only appears that way in the dropdown.  But you can’t write an expression that tests if the (Select All) item is selected because it doesn’t really exist in the array.  The only thing it does is it selects all items when it gets clicked.

Little side note: as usual there’s a workaround to check if all items are selected.  I’ll mention the details on that later.

Sometimes people want to avoid using this functionality and implement their own "All" item.  Even more so in the case of a single-value parameter which doesn’t get an auto-generated (Select All) item.  And that’s what this article is all about!

Setting The Scene

Once again I’ll use the AdventureWorksDW2012 free sample database and write some product-related queries.

The Parameter Query

Let’s use the following query in a dataset called ProductCategory to populate a multi-value parameter called ProductCategory, type integer:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory

ID is used as Value field while ProductCategory serves fine as Label.  To have all values selected by default we can use that same dataset:

Selecting all parameter values by default

When rendering the report we now indeed see a dropdown list with a (Select All) item:

image

To come back to the workaround mentioned in the intro: you can compare the count of the selected items array with the count of the whole dataset.  When equal then all items are selected.  Here’s what that looks like in an expression:

=IIF(Parameters!ProductCategory.Count = COUNT(1, "ProductCategory")
    , "all selected", "not all selected")

Fetching Some Data

Like in any regular report we’d like to display some data.  Let’s keep it simple and show a list of product subcategories based on the selected categories:

select cat.EnglishProductCategoryName ProductCategory
    , sub.EnglishProductSubcategoryName ProductSubcategory
from dbo.DimProductSubcategory sub
inner join dbo.DimProductCategory cat
    on cat.ProductCategoryKey = sub.ProductCategoryKey
where cat.ProductCategoryKey in (@ProductCategory)

Adding an "<All>" item to the parameter

The first step in setting up a custom "<All>" item is adding a simple UNION ALL to the query that fetches the parameter list.  That goes like this:

select ProductCategoryKey ID
    , EnglishProductCategoryName ProductCategory
from dbo.DimProductCategory
UNION ALL
select -1, '<All>'
order by ProductCategory asc

I’m using -1 as ID for the "All" record because I know -1 will never exist in the database.  And I’ve included < > characters in the "All" label for two reasons: to nicely distinguish it from the other items as being a bit special and it ends up on top of the list when sorted alphabetically!  Neat huh?!

This also changes how you set the default parameter selection to "All": just set it to –1:

Setting the parameter default selection to the custom "All" item

Taking the "<All>" selection into account

Alright, the next step is taking the "All" parameter item into account in the dataset that retrieves the subcategories.  Here’s what the WHERE clause will need to get changed to:

where cat.ProductCategoryKey in (@ProductCategory)
    or -1 in (@ProductCategory)

In case of a single-value parameter there’s no need to use the IN operator.  For those situations the WHERE clause can be simplified to this:

where cat.ProductCategoryKey = @ProductCategory
    or @ProductCategory = -1

Detecting if "All" is selected in an expression

The expression to check if "All" is selected also changes, we no longer need to use the COUNT function.  Instead, we can use the Array.IndexOf VB function:

=IIF(Array.IndexOf(Parameters!ProductCategory.Value, -1) > -1
    , "all selected", "not all selected")

Checking if the "All" item is selected in a multi-valued parameter

How does this work?  Because ProductCategory is a multi-value parameter, its .Value property returns an array of selected items.  The second parameter of IndexOf is the value we’d like to search for.  As our report parameter type is integer, this needs to be an integer as well.

When the item is not found in the array IndexOf returns -1, otherwise it returns the zero-based index position of the item.

Of course, in case of a single-value parameter this check can be simplified to the following:

=IIF(Parameters!ProductCategory.Value = -1
    , "all selected", "not all selected")

Conclusion

This article demonstrates how to implement a custom "All" item for both single and multi-valued report parameters meant for filtering data.  It also shows how these values can be used in report expressions.

Have fun!

V.

Share

Tags: , , ,

SQL Server 2012 Reporting Services BlueprintsAs if preparing for SQL Server Days wasn’t sufficient to keep me fully occupied, I was once again involved in the process of book publication…

And no, I didn’t write it – though I had been asked for it many months ago – no, that not-to-be-underestimated task was for Marlon Ribunal (b|t) and Mickey Stuwey (b|t).

My role was Technical Reviewer so I read and commented on all chapters regarding their technical content.  But that’s not all, I also pointed out typos, my drive to perfection can’t just ignore those :)

The book which I reviewed is called SQL Server 2012 Reporting Services Blueprints and is published by Packt Publishing.

So if you’re (relatively) new to SQL Server Reporting Services this should be a good read for you.

The book is mainly aimed at report developers though I’m sure it wouldn’t hurt if administrators had a read through it too.  In fact, a couple of the chapters contain material that should be known by every SQL admin who needs to support BI deployments.

The book starts with the basics and gradually builds up to designing more complex reports.  This gives everyone a good chance to understand the basics before delving into more complex material.  By the end of chapter 6 you’ll have used all components needed for SSRS report development, which is a good thing!

So go out, get a copy and start reading about John Kirkland’s tale!

Happy reading!

Valentino.

Share

Tags:

« Older entries

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