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

Report Layout Basics

I thought I would go over some of the basics of report layouts since I have seen a few forum posts about basic layouts. This is by no means a comprehensive overview, but a starting place.

The first thing to realize about the report layout is that the only sections you have to work with are the page header, page footer and the report body. There is no concept of a report header/footer, etc. But also, unlike Crystal Reports, the report sections are NOT tied to data.

Instead, the report body holds the controls that can act as report sections. Here are some ideas:

Report Header

If you need a report header, you can place any control, like a text box, on the body of the report, and populate it with data, static text, etc. If you place these controls inside a rectangle, you can set the PageBreakAtEnd property to true, and then those text boxes will appear on a page by themselves. If you dont use a rectangle in this manner, your report header will be on the same page as the start of your data area.

Report Footer

As with the report header, if you place controls at the bottom of the body section, in a rectangle, and set the PageBreakAtStart to true, you will have these on the last page of the report.

Multiple Body Sections

You can have multiple body sections just by adding multiple controls to the body section of the report. Say that you have 2 datasets in your report and you want to show them in separate sections of the report. Add a table for the first dataset. Set the PageBreakAtEnd property to true.
Then add another table below that for the second dataset. When you render the report, you will have the first table displayed, a page break, and then the second table. Add the report header and footer from above, and you now have a 4 section report!

Using the List Control

The list control is used to repeat a set of controls within it. You can place a list control on your report, set the dataset and the grouping on the list. Then place some text boxes in the list control and populate them with the fields from the dataset. The list will group the data as you set up, and populate the text boxes, repeating for every group.

You can also put multiple tables in the list control. I will do this often when there is a lot of detail data to display, and I do not want to have to worry about the column layouts.

Well, that was some basic layout ideas. I will add more soon

peace

BobP

Rendering SSRS 2005 Reports with the Web Service

Even though Report Manager is a decent way to deliver reports to your users, you may have the requirement to embed the report in an existing .NET application. This is very simple using the web services provided with SSRS 2005.

Here is a code sample that I use to show developers at my clients how to pull a report.

string historyID = null;
string deviceInfo = null;
string format = "Excel"; //Can be XML, NULL, CSV, IMAGE, PDF, HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC
Byte[] results;
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
ReportingExec.Warning[] warnings = null;
string[] streamIDs = null;


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

ExecutionInfo ei = re.LoadReport("/directory/ReportName”, historyID);

ReportingExec.ParameterValue[] rptParameters = new ReportingExec.ParameterValue[1];

rptParameters[0] = new ReportingExec.ParameterValue();
rptParameters[0].Name = "DateFormatID";
rptParameters[0].Value = "fr-FR";

re.SetExecutionParameters(rptParameters, "en-us");


results = re.Render(format, deviceInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

FileStream stream = File.Create("C:\\report.xls", results.Length);
stream.Write(results, 0, results.Length);
stream.Close();

You will need to set up the reference in your project. The URL for this is: http://servername/reportserver/ReportService2005.asmx

This code, which I give to the developers in a web app, demonstrates the basic use of the web services. It loads a report, adds a parameter value, and then renders the report and saves it to disk. If you wanted to render it back to the user on a web page, you would add:

Response.AddHeader("content-disposition", "attachment; filename=File.xls");
Response.OutputStream.Write(results, 0, results.Length);

Response.End();

By taking this code a little further, you could easily send out an email with the report attached. Or another commonly requested feature for SSRS: emailing multiple reports on 1 email.

You could loop thru a database table, reading the reports to send a user, render that report, save the file, attach it to a system.mail email, and then move to the next report. When the looping for that user is complete, send the email, delete the files, and move on to the next user in the table.

I hope this helps clear up web services in SSRS 2005. Most developers that I work with are not familiar with this web service, and this always gets them going in the right direction. This is by far a VERY basic demonstration. There is a lot more to the web services than just this.

For more reading on the web services, visit:
http://msdn2.microsoft.com/en-us/library/ms155076.aspx


peace

BobP

Creating a Top N Report with ‘Others’

No, this is not a blog about “Lost” ;-)

Often times I have been asked to create a Top N report. This is relatively easy, as you can just add a TOP N to the SQL statement, and display the results in the detail of your SSRS table.

However, once in a while I get a request for a Top N report, but include 1 line with a total of the “Others”

After a lot of trial and error, some internet searching, and discussion with other SSRS folks, this is what I came up with and what I use every time now.

First, in the SQL statement I rank my results, and then group that ranking into 2 groups. The 2 groups represent Others and Non-Others.

Here is a sample SQL:

SELECT
Row_Number() Over (Order by Sum(TBL.Number) desc) as Rank,
Case
When Row_Number() Over (Order by Sum(TBL.Number) desc) <= Convert(int,10)
Then 1
Else 2
End as GroupNumber,
Description,
Number
...

Then I put a table in my report, and add a group with the GroupNumber field as the grouping field.

I remove the group footer and put an expression on the Hidden property of the entire row:

=iif(Fields!GroupNumber.Value = 1, true, false)

This hides the row if the GroupNumber = 1 (non others)

I then put the string “Others” into a column, and the sum of the number field into another column.

In the details row, I add an expression to the Hidden property:

=iif(Fields!GroupNumber.Value = 1, false, true)

This hides the detail row if the GroupNumber = 2 (Others)

I then put the description field into the column, and the number field into the other column.

When you run the report, this is what you wind up seeing:

City A 100 <-- This is a detail row
City B 90 <-- This is a detail row
City C 80 <-- This is a detail row
Others 230 <-- This is the group header row.

peace

BobP

Deploying SSIS Packages

Today I am going to talk about easy SSIS package deployment.

I generally am working with multiple data marts, and separate my projects by data mart. However, if I have the same package name in 2 projects, and do a default deploy from SSIS, those files overwrite each other.

You can create subfolders under MSDB in SQL Management Studio when logged into the SSIS instance, however, you have to manually deploy to these sub folders.

Because I am lazy, I wrote a quick batch file to move a project from a development machine to the SSIS machine. The following batch file takes 1 argument, Servername, and copies all of the files from a project to that server. The project path is hard coded in the batch file, so if you want to deploy multiple SSIS projects, just copy the batch file and change the path.

---

@Echo Off

Echo.
Echo.
Echo Test ETL SSIS Package Installation Script
Echo Written by BobP
Echo 7/6/07
Echo.

if %1a == a goto Error

Echo.
Echo.
Echo -----------------------------------------------------
Echo --This will delete any Test ETL files
Echo --on the server, and reinstall from the local machine
Echo -----------------------------------------------------
Pause

REM Remove Existing files and directory on Server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /SourceS %1 /SQL "\TestETL\\"%%~nf /Del
dtutil /Q /SourceS %1 /FDe "SQL;\;TestETL"

Echo.
Echo Preparing to create folder
Echo.
pause

REM Create the Directory
dtutil /Q /SourceS %1 /FC "SQL;\;TestETL"
if errorlevel 1 goto End
Echo.
Echo Preparing to Copy Files to Server
Echo.
pause


REM copy the all project packages to the server
for %%f in (C:\Projects\TestETL\TestETL\bin\*.dtsx) do dtutil /Q /DestS %1 /Fi %%f /C "SQL;\TestETL\\"%%~nf

Echo.
Echo.
Echo Installation Complete!
Echo.
Echo.
Pause
Goto End

:Error
Echo.
Echo.
Echo Missing Servername!
Echo Syntax: Deploy [targetservername]
Echo.
Echo.

Pause

:End

---

I have several copies of this, one for each of my projects, on my desktop. When I want to deploy, all I have to do is double click.

peace

BobP

Minimum Column Size for Interactive Sort

I have found out that in order to display the interactive sort button in SSRS, your table column must be at least 0.375in wide. If it's any smaller, the button will not display.

peace

BobP