Timeouts in SSRS 2005 Web Service and Report Viewer Control

One of the questions that I get asked a lot is how to prevent timeouts when running or exporting large reports using the SSRS web service or the .NET report viewer control.

With the report viewer control, this is fairly simple; the problem is that the property is hiding in the property tab. In the property tab, expand the ServerReport property, and set the timeout value to a large number or to -1 (infinite timeout)

For the web services, the ReportExecutionService instance contains the property Timeout.

In my previous post showing how to use the web service, I neglected to add the timeout property.

ReportExecutionService re = new ReportExecutionService();
re.Credentials = System.Net.CredentialCache.DefaultCredentials;
re.Timeout = -1;

Also, when working with the web service in SSRS 2005, you have to keep the IIS timeouts in mind. For example, the session timeout by default is 20 minutes. If you have reports taking longer than that, you would need to increase this setting in IIS. However, if your reports are taking that long, I would consider using SSIS to pre-process the report data on a schedule and then run the report against the pre processed data. That is a much more elegant solution.

peace

WANTED: Your Opinion and Thoughts...

Ok, So I have been a bit slack in the blog department.

I have been finding it hard to break out of the everyday rut of work to post anything.

Well, that has to change. I need to get out of this rut, to challenge myself, to expand my horizons. After all, the project I am working on is quite interesting, why not share?

So I have been thinking... Is anyone out there interested in a daily or weekly email newsletter for BI? Maybe a forum, etc? User donated articles, etc? Sort of like Code Project, except for BI? A place where I can get everyone involved, rather than just myself.

I would like to hear some feedback, as well as thoughts or ideas.

Meanwhile, I have to get back to work. I have an article on the ForEach SSIS Task almost complete. I will try to post it today.

peace

CERN Powers Up The Large Hadron Collider Overnight

Well... On Wednesday, at 3:30AM EDT, the Large Hadron Collider will be powered up.

I guess we will know shortly if it will unleash many tiny black holes that will consume the Earth.

Or better yet, a time machine :)

http://www.foxnews.com/story/0,2933,419404,00.html

peace

Look out for black holes...

Determining if a File Exists in SSIS Using Wildcards

In some instances I have had to deal with files that did not have a set name. In these cases, I have a handy script file to determine if the files are there.

(Another way to handle this is to use the ForEach task and use a wildcard in there, but I am going to blog about that next week)

First, I add a variable to my package. I usually call this variable FileExists and set it as a Boolean, with a default value of False.

I then add a script task to my control flow. In the Read/Write variable property, I add User::FileExists.

In the script editor, I use this script:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim di As DirectoryInfo = New DirectoryInfo("c:\")

Dim fi As FileInfo() = di.GetFiles("*.txt")

If fi.Length > 0 Then
Dts.Variables("User::FileExists").Value = True
Else
Dts.Variables("User::FileExists").Value = False
End If

Dts.TaskResult = Dts.Results.Success
End Sub

End Class

This searches c:\ for a file *.txt. The Length property of FileInfo is the number of files found. If this is greater than zero, I set the FileExists variable to True.

After closing the script editor, and clicking OK on the script task property window, I am ready to set up my precedence constraint.

I then add my next task to the control flow and drag the flow control from the script task to the new task. I right click on the constraint and select edit. I select Expression in the Evaluation Operation drop down box. For the expression I use: @[User::FileExists]==true. This way, the only way the next task is executed is if files exist. If the don’t exists, the package ends gracefully.

You could add another task to log the fact that there were no files, connect the script to that task, and set the expression to: @[User::FileExists]==false.

peace

How to Use the SSIS Script Component as a Data Source

Recently I had the pleasure of working with a data source that returned all of the transactions with 1 stored procedure. However, this meant the stored procedure returned 14 result sets, and an output parameter.

The question was how to get the 15 result sets into SSIS. The answer: Use a Script Component.

Here is the path I took to handle this:

Making the Connection
All connections in SSIS are handled thru connection managers. We will have to have a connection manager to connect to the source database to execute the stored procedure.

In the Connection Manager tab of SSIS, right click and select New ADO.NET Connection…
Setup your connection properties, click on Test Connection. Once you have a good connection, you are ready for the next step.

Setup a Script Component
I added a script component to my data flow task. When asked if it was a Source, Transformation or a Destination, I selected Source.



I then added a total of 15 outputs. (14 for the result sets, 1 for the output parameter) To do this, I clicked on the Inputs and Outputs tab, and clicked on the Add Output button until I have 15 outputs.

Then came the fun part: adding, naming and typing all of the columns for all of the outputs. On the same Inputs and Outputs tab, I selected the first output, renamed it to the result set name. Then I opened up the output in the tree view, and expanded the Output Columns folder. I clicked on the Add Column button until I had as many columns as the first result set.



Once the columns where in the tree view, I selected the first one, changed the name, set the data type and size, and moved onto the next column, until they were complete.

Then I did the same for each output in the component.

The final step here is to configure the script component to use your newly created connection manager. To do this, click on the Connection tab and add a new connection. Set the name, and then in the middle column, choose your connection manager.



Scripting the Outputs
The next step is to tie together the stored procedure and the script component outputs. To do this, click on the script tab and click the Design Script button to open the scripting window.

I added 2 subroutines to handle opening the connection and executing the stored procedure:

Public Class ScriptMain
Inherits UserComponent

Private connMgr As IDTSConnectionManager90
Private Conn As SqlConnection
Private Cmd As SqlCommand
Private sqlReader As SqlDataReader


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.Connection ‘This is the connection to your connection manager.
Conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

Dim cmd As New SqlCommand("Declare @SessionID int; Exec spgTransactions @SessionID OUTPUT; Select @SessionID", Conn)
sqlReader = cmd.ExecuteReader

End Sub

The AcquireConnections subroutine is called by SSIS when it is ready to open the database connections. I override it to make sure the database connection is ready to use.

Likewise, the PreExecute is called when it’s time to get the data. (This should clear up some of the long running PreExecute issues out there.) I open our SQL Reader here and execute the source stored procedure.

Now comes the fun part. Linking the result sets to the output columns.

This is done in the CreateNewOutputRows subroutine:

Public Overrides Sub CreateNewOutputRows()

'Invoice Header
Do While sqlReader.Read
With Me.InvoiceHeaderBuffer
.AddRow()
.InvoiceNumber = sqlReader.GetInt32(0)
.InvoiceDate = sqlReader.GetDate(1)
'etc, etc, etc for all columns.
End With
Loop

sqlReader.NextResult()

'Invoice Detail
Do While sqlReader.Read
With Me.InvoiceDetailBuffer
.AddRow()
...

'more outputs and more columns
' until we get to the last result set which will be the output parameter (SessionID)

sqlReader.NextResult()

'Session ID
'We know this result set has only 1 row
sqlReader.Read
With Me.SessionIDBuffer
.AddRow()
.SessionID = sqlReader.GetInt32(0)
End With

sqlReader.Read 'Clear the read queue

End Sub

This code goes thru each result set in the SQL Reader and assigns the value of the result set to the output column. I did not show all of the columns or all of the outputs since it’s the same concept for each.

Once that is done, I clean up after myself:

Public Overrides Sub PostExecute()

sqlReader.Close()

End Sub

Public Overrides Sub ReleaseConnections()

connMgr.ReleaseConnection(Conn)

End Sub

This closes the SQL Reader and releases the connection to the database.

Once this is done, close the script window and click on OK on the script component properties.

The script component will now have multiple outputs that you can select from when linking it to another data flow component.

Conclusion
I hope this will help you when you need to return multiple result sets from stored procedures into SSIS. If you are familiar with VB.NET coding, you should pick this up easily, and even if not, the example at least gives you the basic steps and something to copy from.

peace

CodeProject

Hug a Developer...

This would be a lot funnier if it weren't so true...




What are your thoughts?

peace

SSIS 2008 Sets Record

First of all, hello from the depths of an enterprise ETL project!

I haven't been working on reporting at all in the last few months, dedicated to ETL and optimization.

Second, I realize that this article is from February, but I thought it was a really good read.
http://blogs.msdn.com/sqlperf/archive/2008/02/27/etl-world-record.aspx

peace

Removing Recent Projects in .NET

I recently had a need to cleanup my "recent project list" in BIDS.

Since you cannot do this from the .NET UI, you will have to resort to messing with the registry.

*** Serious problems might occur if you modify the registry incorrectly!!! ***
*** Modify the registry at your own risk!!! ***

That being said... Open up RegEdt32 using the Run command from the start menu.
Navigate to:

HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\8.0\ProjectMRUList

(for .NET 2003, use 7.1 instead of 8.0, for .NET 2002, use 7.0)

Then, from the right hand window, select the values that you do not want, and press delete. The value names are File1, File2, ...

Be aware that you will need to renumber these to be in order from 1 on...
If they are not in exact sequential order, they will not load on the start page. This is not documented on the Microsoft KB article (http://support.microsoft.com/kb/919486)

peace

View of 1999...From the 1960's

Here is another interesting view of the future from the mid 60's... again, they seem to have gotten the concepts correct, but not the implementation...

And they also didn't see the social norms changing. The wife does the shopping while the husband grimaces and pays for it!




From:
View of 1999 from the '60s

peace

BobP

New Small SSIS Features in 2008

I have posted on Microsoft connect to request new features and have met with about a 75% success rate.

Among my requests:

Hide a column in a matrix

Problems with autohide tool boxes


Here is a good write up on 2 new features for SSIS that Jamie Thomson requested via Connect and they will be in SSIS 2008:


http://blogs.conchango.com/jamiethomson/archive/2008/03/20/ssis-some-new-small-features-for-katmai.aspx

peace

BobP

A view of 2008... from 1968

This is an interesting article from "Modern Mechanix" magazine from 1968.

Click here for article

I find it very interesting that missing one small piece of information skews the entire article. For example, he talks about "Not every family has its private computer. Many families reserve time on a city or regional computer to serve their needs." and "TV-telephone shopping is common." He did not see a computer in every home that is connected to every other computer via the internet.

But apart from that, it is a good article, and many of the concepts are a reality today, although not in the same form as he anticipated.

peace

Bob

ReportingService2005 Web Service

Well, I know it’s been a while, but I am finally back after the holidays!

The next phase of our reporting project is about to take off, and I had a little down time while waiting on requirements.

So, using the ReportingService2005 web service, I created a report deployment application in C#.

It allows me to select the directory of my RDL/RDS files, and then select my report server and any folder on that server.

Then it deploys the selected reports to the report server with the one button click.

I can then change the report server, and deploy the same reports to that server. This is helpful if you have multiple systems that should stay in sync. Using dynamic web service connections allows me to change the target server within code.


Working with the web service was rather straightforward, and allowed me to do everything I needed to do as far as deploying reports and data sources.
However, what I wanted to write about was a catch that had me stumped for a few minutes.

When you use the CreateReport method of the web service, the data sources are not linked to the server data sources. So what I did was use the GetItemDataSources method to get the report data sources, built a new data source in code, representing the server data source, and then used the SetItemDataSources method to update the report data sources.

DataSource[] dataSources = rs.GetItemDataSources(TargetFolder + "/" + ReportName);

foreach (DataSource ds in dataSources)
{
if (ds.Item.GetType() == typeof(InvalidDataSourceReference))
{
string dsName = ds.Name.ToString();

DataSource serverDS = new DataSource();
DataSourceReference serverDSRef = new DataSourceReference();
serverDSRef.Reference = "/Data Sources/" + dsName;
serverDS.Item = serverDSRef;
serverDS.Name = dsName;
DataSource[] serverDataSources = new DataSource[] { serverDS };
rs.SetItemDataSources(TargetFolder + "/" + ReportName, serverDataSources);
}
}

This nicely updated the report data sources to use the server shared data sources.

All in all, the 1½ days I spent writing this app were well worth it. I learned about the deployment methods of the web service, and kept my coding skills up, plus I have a nice deployment utility for reports!

peace

SSIS Derived Column IIF/IF Expression

This is for my memory, but I figure if I have a hard time remembering, maybe someone else will...

For those of you who are trying to remember how to do IF/IIF functionality in the SSIS Derived Column control:

({comparison} ? {truepart} : {falsepart})

So, if I want to evaluate the similarity from a fuzzy lookup, it would look like this:

(_Similarity > .78 ? ID : "")

Maybe someday Microsoft will standardize all of the expression languages in their BI tools!

peace

Disappearing Parameter Defaults

I ran into an interesting problem today with default parameter values.
I had a report with 6 parameters. One of them, it happened to be Language, had a default value of 1 in the parameter window. The rest had values from datasets. Once deployed, if you chose a data driven parameter, the language default would disappear and ask you to select a value. This was very inconvenient, as it was a hidden parameter in this report.

After googling and looking in the forums, I realized this is an ongoing issue in SSRS. So I had to come up with a workaround.

I played around with it for a while, and figured out a way to make it work. I created a dataset called DefaultLanguage, with the SQL:

Select 1 as ID

I then set the default for the language parameter to that dataset.

Now when any other parameter is changed, the default language stays selected!

peace

Parameter Prompts Go Prime Time

I just was reading Teo Lachev's blog and saw this entry: Parameterized Parameter Prompts.

SQL 2008 will allow expressions on the parameter prompts!!! This means that in my article Multi Language Reports, you will be able to update the prompts to different languages. :-D

Check out Teo's other blogs too... He has been working with the new features in SQL2008, something that I hope to be able to get around to soon.

peace

Javascript in Jump To URL

You know that if you right click on a textbox, select Properties, and then click on the Navigation tab, you can put an URL into the Jump to URL textbox, and when the user clicks on the data in that textbox it will jump to that URL in the same window as the report. But did you know that you can use javascript in there as well?

This allows you to open the link in a new browser window or display a pop up a message box.

I will sometimes use pop up message boxes on the column headers to define what the data definition is. This is the syntax:

="javascript:void alert('This is a test column header alert')"

To open the link in a new window, use syntax similar to this:

="javascript:void window.open('http://servername/reportserver/pages/reportviewer.aspx?%2freportfolder%2fReport+Name&Parameter1=" & Fields!Field1.Value & "&Field2=" & Fields!Field2.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

peace

And now, for something completely different...

Came across this today and was totally blown away...

Enjoy!

http://www.youtube.com/watch?v=yf56jYDv2fc


peace

More About Multi-Value Parameters

Just for fun, I am going to talk about multi value parameters again. In this blog, Shortcomings of Multi-Value Parameters, I discuss two of the shortcomings. Now I am going to talk about how to use them in detail.

OverviewMany times you will need to have a parameter that allows the user to select more than one value. In this instance, when you are creating the parameter, check on the Multi-value check box in the Report Parameters window.


Available Values… Or Not!If you have a set list of items that you want the user to be able to select from, you can establish this list in the Available Values section of the Report Parameters window. You can either use a dataset, or type in static values.

To use a dataset to populate the available value, create the dataset in the data tab, making sure that you assign field names. Then in the Report Parameters window, click on your parameter, click on the From Query radio button in the available values section. Then select your dataset name, value column and label column. The value is what will be returned when referencing @Parameter in your SQL query, and the label is what will be shown in the drop down parameter. You can access both of these values in expressions:

Parameters!Parm1.Value or Parameters!Parm1.Label

You can have a multi-value parameter with or without available values. For example, you may have a parameter for Color, with the available values being red, green and blue. In this case, when you preview the report, you will have a drop down check list, with Select All, Red, Green and Blue listed. Users can click on one or more checkboxes, and select the colors they want to filter on. (If you have SP1, you will not get the Select All check box. The fix for this is SP2.)

If your multi-value parameter does not have any available values assigned to it when it is set up, your users are presented with a drop down multi line text box. The users can enter values, separated by a carriage return, and SSRS automatically formats it for an IN clause in SQL. The great thing about this: Users can copy a column of data from Excel and paste into the textbox! I had a client that worked with loans. They always needed information based on a list of loan numbers that someone had in an Excel spreadsheet. I developed a report that have a multi value parameter, they would copy the loan number column out of the spreadsheet, paste it into the parameter, run the report, export to Excel. It saved them from having to copy, look up, format, etc.

When using a Multi-value parameter, you cannot have a null value returned. This means that the user will have to select or enter at least 1 value from the parameter list. When setting up the parameter, you will get an error if you try to select the Allow null value check box. If there are no available values, the user must enter at least 1 value into the textbox.

Using in SQL
If you are going to be passing a multi-value parameter to a stored procedure, you will have to pass it as 1 string, and parse it in the stored procedure. See the blog referenced above for more information about this.

However, to use your multi-value parameter in a dataset SQL statement, just use the IN clause:

Where ItemID in (@Item)

SSRS will automatically add the commas and format it for you.

There is no way for you to tell if all of the values have been selected, or if the Select All was clicked on. This is an issue if you have a lot of values because the IN clause is worthless, but SQL still executes it, cost precious time and resources. If you expect to have a long list of values, or if you think that all of them will be selected most of the time, there is a workaround documented in the blog referenced at the top of this article.

Using in ExpressionsTo determine how many values have been selected or entered, use the Count property:

=Parameters!Parm1.Count

To reference a value directly, use:

=Parameters!Parm1.Value(arrayindex)

where the array index is less than the count.
If you want to display the list of values selected/entered, use the Join function:

=Join(Parameters!Parm1.Label, “, “)

The second argument is the delimiter; what character string is displayed between each parameter entry. You could also reference the parameter value.

Using in Code
You can reference multi-value parameters in embedded code as well. See my article about multi language report labels for a look at how that is used.

Passing in URL
To pass the multi-value parameter values in an URL to report manager, repeat the parameter name. For example:

http://server/reportserver?/dir/Report&rs:Command=Render&Parm1=VALUE1&Parm1=VALUE2&Parm1=VALUE3

That’s all for now. I think this gives a lot of information. Hope it helps!

peace

CodeProject

Dynamic Images in Reports (Slight Return)

Since dynamic images in reports are by far the most read topic of my blog, I am going to add some more information about them.

The last post that I did about dynamic images concentrated on adding logos to the header, etc. This time I am going to talk more about adding images in the report body. Some of this may be redundant, but it may also give you some ideas for future reports.

I am going to discuss adding an image preview/click thru example.

Since you can read the URL from the database in the main dataset, you can put an image in every row of a table. On that control, you can set the sizing property to fit or fit proportional, and get a thumbnail image. You can then add a Jump to URL expression to have the user be able to click on the thumbnail and display the full size in a new browser window.

="javascript:void window.open('" & Fields!ImageURL.Value & "','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Right click on the image control and select Properties. Then click on the Navigation tab and click on the Jump to URL radio button. Paste the above code into the expression text box. You can change the size, or leave out the width and height properties.

This works great if the images are stored on a web server. (In my humble opinion, images should not be stored in the database, but that is a discussion for a different day. Feel free to comment on this!) But what if the images are stored in the database?

Well, you would have to create another report, adding a lone image control, and maybe a caption text box. Add a parameter so that you can pass an image ID from the first report to this report.
Create the dataset, using the parameter as a filter, to select the Image field from the database.

Select ImageField, Caption from Images Where ImageID = @ImageID

When you add the image control to the report, select the dataset and field name, and put in the mime type. Put the caption field in the caption text box.

Back in the original report, right click on the image control and select Properties. Click on the Navigation tab and select Jump To Report. Select the new report that you created. Click on the parameters button and assign the Image ID parameter with the image ID read from the database in the dataset.

The only drawback with doing it this way is that it does not open in a new window.

To open a report in a new window, use the Jump to URL select again, and then put this in the expression box:

="javascript:void window.open('http://servername/reportserver/pages/reportviewer.aspx?%2fImageView&ImageID=" & Fields!ImageID.Value & "&rs:Command=Render','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=730')"

Now it will open the image report in a new browser window.

If you would like more information on dynamic images, or if I can explain something a little clearer, post a comment or drop me an email.

peace

Data based, Row Level Security in Reporting

Overview

Last month I promised an article on data based security. Well, after I ignored it for a month, someone asked for it. So here is what I am currently implementing in a project for a client.

I import several transactional systems into a data mart of my own design. It is a star schema, and utilizes 4 fact tables and about 20 dimensions. One of the dimensions handles the primary focus of our reports. Let’s call it Orders. Each individual order is assigned an OrderKey that ties all of the fact tables together.

To implement security, I assign OrderKeys to report user groups. The assignment is based on rules setup thru a front end .NET web application, and can be used to filter orders to users based on any field in the data mart. Then the report SQL checks to make sure that every order is in that security table for the user pulling the report.

The Details

I have setup a data dictionary containing all of the metadata that I need to build a query for the fields. The admin application displays the fields to the admin user, and the admin user selects a field, an operator (=, <, >, <>, Like, etc) and then enters a value.

So for instance, the admin user would select the CostCenter field, select the = operator, and then type in 1234. Stored in the database is the line: CostCenter = 1234.

Once the admin user is done, they select a menu item to “build” the rules. Using the data dictionary, the application builds SQL statements to filter the OrderKeys. The statements look like this:

Insert into Security
Select Distinct a1.*
From
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimProduct d (NOLOCK)
on f.ProductKey = d.ProductKey

Where d.ProductID <> '555666') a1
Inner Join
(Select 1 as ProfileID, f.OrderKey as KeyVal
From factOrderLine f (NOLOCK)
inner join dimMFG d (NOLOCK)
on f.MFGKey = d.MFGKey

Where d.MFGID = 'PG') a2
On a1.KeyVal = a2.KeyVal

Because I am doing inner joins, this acts as an AND clause. So in the above query, I would get all orders that have a product ID not equal to 555666 and everything where the manufacturer ID is PG.

The resultant list is a profile ID, and then order key itself.

I put this into the Security table.

When I code the SQL in the report, I add an EXISTS statement to the WHERE clause:

and OrderKey in (Select OrderKey from Security where ProfileID = @ProfileID)

The @ProfileID parameter is being passed in by the app calling the reports. It is set when the user logs into the web front end.

That in a nutshell is how I do data based row level security.

This is a huge topic, and I have only scratched the surface in this blog. As I work on it some more, and add new features, I will blog them as well.

In the meantime, if you have any questions or suggestions, please email me!

peace