SSRS

You are currently browsing articles tagged SSRS.

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: , ,

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: , , ,

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: , , ,

« Older entries

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