Tuesday, October 30, 2007
Dynamic images in reports
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!
Multi Language Reports
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
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.
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:
From LabelText (NOLOCK)
Where LanguageID = @Language
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) & ":"
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:
And if you wanted the colon:
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.
Friday, October 26, 2007
And I thought I had too much time on my hands...
I really am speechless... Laughing too hard...
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.
Monday, October 22, 2007
Data Based Security in Reporting
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.
Friday, October 19, 2007
Formatting Zero Values
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:
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.
Thursday, October 18, 2007
2 Shortcomings of Multi Valued Parameters in SSRS
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…
Wednesday, October 17, 2007
Is the business view of IT changing?
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.
Monday, October 15, 2007
Passing SSRS Report 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.
Thursday, October 11, 2007
Multi-Language Tips in SSRS
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:
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:
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:
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!
Wednesday, October 10, 2007
3 Lessons on Deadlines and Business Requirements
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?