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

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

Dynamic images in reports

Another question that comes up a lot is how to dynamically change the images in a report.
I do this with client logos, and even the company logos that I put into report. Companies change their logos quite frequently, and you don’t want to update hundreds of reports when the logo changes.

What I usually do is to put the logo out on a web server that is accessible to the report server. Then put the URL in a database table. This can be a static "config" table, or it could be your client table. (I use a table called Config, with 2 columns, ItemType, Item. I have a proc called GetConfigItem @ItemType. I pass in the item type and get back the item.)

Then I create a dataset and a parameter for each logo path that I need. The parameter is populated from the dataset.

Add an image control to your report (since you will populate the URL from a parameter, you can put the image control in the header/footer as well as the body)

Set the source to External, and the Value property to an expression: =Parameters!LogoPath.Value
If you set the image control to Fit Proportional, the logo should resize automatically.

Now if you want to change the logo, you can place the new logo out on the web server, change the URL in the database, and all reports are updated!

peace

Multi Language Reports

Well, I promised to write about handling multiple languages in SSRS so I thought I would at least get started on that. Judging from the visitor stats, that seems to be what a lot of people are looking for.

As you may already be aware, SSRS does not inherently support multiple languages. And what I am about to demonstrate does not apply to the data, just the labels and text that are pre-populated on a report.

What I do is use database driven report text. I populate a parameter with the label text, and then use code to retrieve the label. Since a parameter is nothing more than a key/value list, this works quite well.

First, I create a master Label table. This will be the list of labels that I will have available to me. I have another table called Language. This table obviously holds my list of available languages. And I have a third table called LabelText. This table holds the actual text in the different languages that will be placed on the report.

Here is the script:

CREATE TABLE [dbo].[Label](
[LabelID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](900) NOT NULL)

CREATE TABLE [dbo].[Language](
[LanguageID] [int] IDENTITY(1,1) NOT NULL,
[Language] [varchar](100) NOT NULL,
[Active] [bit] NOT NULL)

CREATE TABLE [dbo].[LabelText](
[LanguageID] [int] NOT NULL,
[LabelID] [int] NOT NULL,
[LabelText] [nvarchar](max) NOT NULL)

I set the LabelText up as NVARCHAR to support Unicode character sets.

Then I populate the Label table with all of the labels that I will need on the reports. Here is a short example:

71 % Total
75 Account
120 Address 1
121 Address 2
122 Address 3

I also populate the Language table with the languages that will be available:

1 English 1
2 Spanish 0

The LabelText table really does all of the work. That contains the label text in the language of choice.

I am fortunate enough to have a very good background as a software developer, so I created a web page to populate these two tables and the LabelText table. I let the business unit populate this table since my grasp of other languages is not the best.

Once those are set up and populated, it’s time to turn to the report design.

Create a dataset called Languages. Use it to retrieve the rows in the Language table.

Select LanguageID,
Language
From Language
Where Active = 1

Since populating all of the labels in a new language may take some time, we only want to load languages that are set to Active. This gives the business unit time to load the text, without giving the user the ability to select that language on a report.

Create a parameter called Language. Populate it with the values from the Languages dataset. Pick a default if desired.

Now create a dataset called Labels. This dataset is going to pull back the rows from label text for the selected language:

Select LabelID,
LabelText
From LabelText (NOLOCK)
Where LanguageID = @Language

union

Select LabelID,
LabelText
from LabelText lt (NOLOCK)
Where LanguageID = 1
and Not Exists ( Select 1
From LabelText z (NOLOCK)
Where LanguageID = @Language
and z.LabelID = lt.LabelID)

This SQL pulls back all of the labels for the language selected, and then, for any labels missing in that language, unions the labels in language 1 (In my case, ID 1 is English)
This way you always have a label returned.

Go back to the report parameter screen and create a new parameter called Labels. This parameter should be internal, multi-value, and an integer.

For the available values, populate from the Labels dataset, and do the same for the default values. This gives us a parameter filled with the label ID and text, with all of them selected by default. Since inside a report, you can only “see” selected parameter values, and not the populated values, this is an important step.

In the report properties window, under the code tab, add code similar to this to loop through the selected values in the Labels parameter, and return the text for the ID passed in. Since this parameter is loaded with the translated version, this is what will populate your labels.

Public Function GetLabel(Parm as Parameter, LabelID as Integer, Optional AddColon as Boolean=False) as String

Dim t as Integer

For t = 0 to Ubound(Parm.Value)
If (Parm.Value(t) = LabelID) Then
if AddColon = true then
Return Parm.Label(t) & ":"
else
Return Parm.Label(t)
end if
End if
Next t

Return ""

End Function

Notice the AddColon parameter. I put this in because I have a lot of fields in my reports that are setup like a data entry form: Label: Value. Since I didn’t want to type +”:” in every textbox, I just added it to the routine. You can add as much custom code in here as you want. This will make your job easier in the long run.

The last step is to use your label table as a lookup (I usually load it into Excel and sort it alphabetically) and add this formula to every label textbox:

=Code.GetLabel(Parameters!Labels, 105)

And if you wanted the colon:

=Code.GetLabel(Parameters!Labels,2, True)

And that’s it! Now you have multi-language report labels. On the downside, it makes the report harder to maintain, but if the requirement is multi-language, this works very well.

Keep in mind; this will not work on parameter labels. I have not yet found a way to translate these. Probably the only way to do it is to have a custom front end, and create your own parameter display using a combination of your own code and the SSRS web services.

Many thanks to Brian Welcker for the initial idea of using a parameter for the labels. (http://blogs.msdn.com/bwelcker/archive/2007/07/11/laser-guided-missiles-report-localization-through-parameters.aspx)
I was orignally calling a C# assembly to populate each label from the database. That generated way too much database activity.

peace

And I thought I had too much time on my hands...

Ok, so I was perusing the internet and came across this:

http://www.geekologie.com/2007/10/japanese_lady_produces_unique.php

I really am speechless... Laughing too hard...

Peace

XML/RDL Editor

I am about 60% done with a project to help me edit RDL files. For those of you unfamiliar, these are the XML files in SSRS that control everything about the report.

My goal in this project is to be able to drag and drop nodes between 2 list views, delete nodes, copy/paste nodes, etc.

I am doing this because I have a need to copy standard parts of reports between almost 100 reports now. And that number is only going to grow in the future.

My question: is there already a tool out there that will do this? I searched before developing, but did not find a decent one.

And, if I did finish this, and thought about distributing it, would anyone else out there use it?

On another subject, last Saturday, my project manager’s wife was hit and killed by a drunk driver. She left behind Jim and their 3 sons. (http://voluntaryredneck.mu.nu/archives/244552.php) Please keep them in your thoughts and prayers.

Peace

BobP

Data Based Security in Reporting

I sometimes wonder if anyone actually reads these blogs :-P
Well, here is my chance to find out.

I am going to ask for feedback.

In my position as a Business Intelligence Consultant, I have been asked before how to implement data based security (DBS). By DBS I mean the ability for an admin user to restrict a row of data from a user based on a value in the database.

For example, I, as an end user of a report, can only see transactions that occurred within my region: Georgia; whereas my peer across the country can only see transactions in Colorado.

I could add a where clause to every report, but what if the admin wants the ability to filter by ANY field in the database.

I have a solution that works quite well, but I am interested to know if anyone else has run into this request, and how they have solved it.

I am going to post my solution later on this week.

Peace

Formatting Zero Values

This entry is more for my memory than anything else, but I am sure someone else out there can use it.

Did you know that you can custom format a zero value in a report?

From what I have seen in the MSDN forums, not too many people do. Most people, and myself included, first set up a visibility formula like this:
=iif(Fields!Data.Value = 0, true, false) <-- Sets hidden to true if value is zero.

And that does work great, unless you have any kind of formatting or borders in your cell. Then it hides them too.

The better workaround is to use the Format property on the cell and use a custom format string.
The custom format string has 3 parts separated by semicolons:
Positive value format; Negative value format; Zero format

So if you wanted to hide the zeros in a decimal number cell, the format string would look like:
#,###.##;-#,###.##;””

You could also use that to further document the zero value:
#,##0.0;(#,##0.0);”Zero”

The only limitation that I have found, and I am still looking for a workaround is if you are writing a multi-locale report. If you change the Language property, the number formatting will not change.

I will post an update once I find a way to do this.

Till then…

Peace

2 Shortcomings of Multi Valued Parameters in SSRS

Wow. What a terrific morning!

We are getting some rain that is so desperately needed (They say the reservoir that feeds Atlanta will dry up in 90 days if we don’t get some substantial rain soon. http://abcnews.go.com/GMA/story?id=3730145&page=1), I had an awesome rehearsal/jam with my band last night, and our production servers are ready to go online to be configured!

So this morning I am going to discuss 2 shortcomings of the multi-valued parameter (MvP) without getting irritated.

The first shortcoming, and in my opinion the most important, is the inability to pass a MvP to a stored procedure with out having to parse it out in the procedure. (See this blog for more information http://otechnology.wordpress.com/2007/04/02/processing-csv-strings-t-sql/ )
I would think that since Microsoft was writing these 2 products together, they could have done a little collaboration and figured out a way to pass this. So far, I have not seen anything showing that this is being fixed in 2008, but I may have missed something. There is an entry in Connect about this, but there has been no activity (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=305511 )

For a workaround on this, I just put the SQL statement in the report. I know that forces a compile every time the report is run, however the processing time to parse up the parameters is much more than the compile.

The second shortcoming is the Select All. I know that in SP1 some developer removed the Select All check box from MvPs and the entire SSRS community replied with a unified roar. I was one of them. So in SP2, the Select All was returned with a collective sigh of relief. BUT there is still one thing missing from the Select All. The ability to handle the All in the SQL query without listing out all in an IN clause. For example, if I have a list of airports of the world in a MvP, my Where clause looks like this: Where AirportCode in (@AirportCode)

Now, if the user selects Atlanta, the SQL becomes: Where AirportCode in (‘ATL’)
However, when the user chooses Select All, the SQL is a concatenation of every value in the MvP. So even though we know that there is no need to even filter on that parameter, the SQL is formed in a way that causes the query to slow way down, even on an indexed field.

Well, here is my workaround for that. And while it is NOT elegant, it is effective.

Suppose you have 1 MvP called Contacts.

I set up a 2nd MvP called AllContacts. I populate it with the same dataset as Contacts, and then set the default values to the same dataset as well, effectively selecting all by default. I make this parameter internal.

I then setup a third parameter, type of integer, names AllContactsSelected. I also make this internal. It is NOT multi value and there is nothing in the available values. For the default value I use this expression: =iif(Parameters!Contacts.Count = Parameters!AllContacts.Count,true,false)

In my SQL where clause I add this:
Where (@AllContactsSelected = 1 or Contact in (@Contacts))

Make sure that you put it in that order, otherwise it will try to evaluate the IN clause. This way it will see that @AllContactsSelected = 1 and not even execute the IN.

Well, that’s all for now. I sure would like to see these things fixed in SQL Server 2008, but I am not expecting it.

For now, I will just go enjoy the rain…

Peace

Is the business view of IT changing?

Today, I am going to stray from SSRS again and discuss business.

In my normal batch of morning email, I came across a link to an article
(http://www.networkworld.com/news/2007/101607-noncertified-it-pros-better-paid.html) with the headline being: Non-certified IT pros earn more than certified counterparts: survey.

Being a non-certified professional, I was really happy to see that. But after reading it, I realized that the headline was not the only point to the article. It was just the one that would catch people’s eye. For me, the interesting message is that companies are tired of “techies.” They really want business people that can do a technical job.

I have been working with business units for most of my career. My first true technology job offer was: “I can’t get any information out of IT; I want you to work for me, the CFO, and provide me with the reports that I need and can understand.” I understand from the business side how frustrating it is when you ask for a report, and a developer responds with technical jargon about primary keys and cross server queries. The average business person glazes over when they hear the first technical term.

Don’t misunderstand me, I am a technical person. I am a geek. I love the inner workings of almost anything, not just software or gadgets. But first and foremost, I am a business person. I started my first business at age 7, selling lemonade. I made almost $10 in one day! (And I did it without a PC, without a large un-wieldy database application that took 2 years to implement and never worked right, without a tech-head telling me that I shouldn’t even be in business because my network platform was not on the cutting edge.) I have started and run several successful businesses outside of the technology realm, and a couple within. I have been in management, and in the ranks. I have seen a lot from the business side.

So it was refreshing for me to read that more business people want this IT re-alignment. But, I have been hearing this for almost 20 years. Are things really starting to change?

One can hope.


Peace

Passing SSRS Report Parameters in an URL

There is a lot of discussion on the forums about passing parameters to an SSRS report using an URL however one of the items that you rarely read about is the passing of multi valued parameters in an URL.

To do this, just pass the name/value pair, separated by the ampersand.

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

As long as the parameter Parm1 is set to multi value, and it contains those three values (VALUE1, VALUE2, VALUE3) all three will be selected.

The only exception to this rule is if one of the name/value pairs is a null (Parm1:IsNull=True)
That causes SSRS to ignore the rest of the values passed.

Remember to pass the Value, and not the Label.

Peace

Multi-Language Tips in SSRS

In looking through the forums at Microsoft, I often see questions pertaining to the formats of dates, number and currency in a report.

In my latest gig with a multinational travel management company, I have had a lot of exposure to the multi-language/culture formatting.

I will cover multiple languages in a future blog, and today focus on the formatting of dates, numbers, and currency values.

To start with, Microsoft has a good page explaining each of the terms used by them to customize the user interface to the user’s regional setting:

http://www.microsoft.com/globaldev/getwr/steps/wrg_lclmdl.mspx


SSRS handles formatting the display of dates, numbers and currency by adding a property to a report and textbox: Language. This is misleading since it really does not change the language except on the verbose date formats. For example, changing a number from the US format: 1,234.56 to a French format: 1 234,56 really isn’t a language change, just a locale change, according to the link above.

The language property displays the locale description, and behind that, the locale code.
For example, the property displays English (United States), and the value behind that is en-US.

I found a good list of these locale codes here:

http://msdn2.microsoft.com/en-us/library/system.globalization.cultureinfo.aspx

I put these into a database table, so that I can use them as a parameter in my reports.

In the reports that I create, I have a dataset called Locales in which I call a stored procedure that returns the locale code and the description, sorted by description.

I then create a parameter called Locale. This is a String parameter that is setup to read available values from the Locales dataset, with the code as the value and the description as the label.

Depending on the report target audience, I set a non-queried default value, usually en-US.

In the report properties window (NOT the properties that show up when you select the menu items: Report, Properties. You have to click on the outlying yellowish area of the report designer and press F4), I set the language as an expression: =Parameters!Locale.Value

Now you should format all of the textboxes that contain dates, numbers and currencies.

You should use the standard formatting strings rather than hardcoding a format into the format property. For example, for a short date format, use d rather than d/M/yy, as this will always format the date d/M/yy no matter what you select for the language property.

Here are some standard formatting codes:

Date/Time: http://msdn2.microsoft.com/en-us/library/az4se3k1(VS.71).aspx
Numeric: http://msdn2.microsoft.com/en-us/library/dwhawy9k(VS.71).aspx

Note that some of the formatting strings are “InvariantCulture.” This means that they will not be affected by the language property.

NOTE: When changing locales on currency fields, all this does is change the formatting of the displayed number. It does NOT do any currency conversion. For this reason, I usually do not format currencies as currency, but as a N2 (Numeric, 2 decimal places) and then display the ISO currency code (USD, EUR, JPY, etc) to indicate which currency is being displayed. I also use a separate parameter to select the currency based on this ISO code. Since the ISO currency codes and the Microsoft locale list do not tie in any way, it makes it a lot more difficult to use the language properties to format currency changes.

The way parameters are displayed is controlled by the browser regional settings. There is no way in reporting services to control that. However, the regional settings on a browser should be set to the way the user likes to see the dates, numbers, etc, so you should not have to worry about this setting.

Well, that wraps up this tip. I hope that someone out there benefits from this. I have done a lot of research, as well as a lot of work on my own, to put this together. I use this extensively on existing reports and it works very well.

Let me know what you think!

Peace…

3 Lessons on Deadlines and Business Requirements

Well, here I am again, back after a "Got to have it now, pull out all the stops" project has been completed.

Let me give you a quick overview:

I had a client come to me on Sept 27th with an urgent request:

"We have a new system going into beta with several very important clients and we need a quick data mart and 2 reports. And we need them by next Tuesday!"

So with a fair amount of skepticism, I accepted the task. After all, this was a client that I have been dealing with for a while, and it was only 2 reports.

So, using SSIS and SQL 2k5, I put together the data mart (I had already been working on some data marts for this client, so I was familiar with their business and data model) and started looking in depth at the reports.

OOOOOPS!! Guess I should have done that first. By looking at the reports I could tell that the users had asked for some reports, the deadline aware project manager talked to the developers, who told the client that they only had time for 2 reports, not the 33 the users originally needed.

This was not 2 reports. It was "let's take all 33 reports, combine all of the parameters and fields and put them on 1 report, and then add 1 because we are allowed to."

So, I naturally did what any decent consultant would do. I balked. Not in a bad way, not rude or condescending, just factual:

While this 1 master report will work, is it really fulfilling the users needs? Do they need 28 parameters in the parameter bar in the SSRS report manager? It takes up 80% of the maximized browser window. How do they view the report? Also, the report is very slow, because you are joining not only fact tables and dimensions, but also different atomic level fact tables. Let's talk to the users to see what they really need.

So we did. And in doing so, we found out that they had indeed been told that they could only have 2 reports. So:

• Lesson #1: If your users tell you they need X functionality, and you restrict them to only Y number of items, they WILL find a way to get X into the Y.

In talking with the user, we also found out that next Tuesday was NOT a hard fast deadline.

• Lesson #2: Given the choice between crap in 2 days and gold in 2 weeks, most users will take the gold.

By giving the users my recommendations, they came to appreciate that someone was looking out for their best interests. They felt comfortable telling me their goals, their needs, as it pertains to these reports. I built up a relationship with these people. In turn, I made sure to try and understand what they needed, and provide it for them.

• Lesson #3: Users are just people trying to get their job done. Respect that.

And they will get it done with or without your software, reports, data, etc. It's your job to make theirs easier, and to contribute to the well being of the company as a whole.

These seem to be pretty basic lessons, right? However, as a consultant, I see these same mistakes being made everywhere. And yet it surprises me every time. We, as an Information Technology industry, should never let these lessons slip from our minds. They are fundamental to our jobs, and to our industry as a whole.

In summary, the users defined 4 reports that would meet their immediate needs, and I was able to produce those reports for the users in a couple of days, and everyone is happy. Sounds like a cheesy movie, huh?

Peace

FUN with SSIS Script Tasks and 64bit deployment

I am developing my SSIS packages on my 32 bit, Windows XP Pro SP2 laptop. Everything works fine. My project has a parent package and 26 child packages.

In the child packages, I have a script task that sets the version number into a variable. Nothing big, 1 statement:

Dts.Variables.Item("User::Version").Value = Dts.Variables.Item("System::VersionMajor").Value.ToString() & _
"." & Dts.Variables.Item("System::VersionMinor").Value.ToString() & _
"." & Dts.Variables.Item("System::VersionBuild").Value.ToString()

So I created a "template" package that had my basic tasks in it, and started each child package from this template.

Everything worked great on my laptop.

Deployed to our 64-bit production platform and I get the following error when running the package:

Precompiled script failed to load. The computer that is running the Script Task does not have a compiler capable of recompiling the script. To recompile the script, see the Microsoft Knowledge Base article, KB931846 (http://go.microsoft.com/fwlink/?LinkId=81885).

Naturally I follow the link in the error, after cleaning up the water that I spewed from the surprise I got from a very detailed error message in SSIS.

It did not apply to me... I had SQL Server SP2 installed. I even re-installed after reading it, just to make sure. Did not fix it.

Found this forum thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=591709&SiteID=1

Read thru it... Had NO script tasks set to False for pre-compile.

After looking thru my packages, confirming Precompile was set to True, I had an epiphany...

Since I started from a template, I bet the script tasks were NOT being compiled, since I never opened them.

So I went thru all 27 packages, opening up each script task (Control Flow) AND component (Data Flow), and then the script window. Closed the script window, and pressed ok on the script task window.

Re-built and deployed... tested OK!

So, if you use a "template" package with a script task or component, make sure you open the actual script screen and close it again to force a re-compile.

BobP

Welcome to my SSRS blog!

Hello!

Welcome to my SSRS (and sometimes other subjects) blog.

I have been working with Microsoft Reporting Services since mid 2003, and the SQL Server 2005 BI tools since mid 2005.
I am constantly coming up with work-arounds, and never think to share that knowledge.

Well, here is my attempt.
I hope someone out there benefits from this blog. And remember, if you have a question, do not hesitate to ask it!

Thanks!

BobP