If you're into free training (and you should be if you work as an IT professional), head on over to SQL Lunch to see the current line up of speakers and sessions. Every session is free and are usually scheduled to start at 12:30 EST and last no more than 30 minutes. The best part is it's all virtual! So there's no need to take time out of your busy work day. SQL Lunch is a great resource to increase your knowledge and experience. This coming Monday (8/23/2010) at 12:30 EST, yours truly will be g...
Read More
A fellow BIDN'er posted in the forums reporting some weird behavior in one of their SSRS 2008 reports. Apparently there's a known bug centering around using Me.Value in an expression in a report that features a header and/or footer that contains a Global variable, such as Execution Time or Page Number. The behavior is pretty screwy, so rather than try to explain it, I figure I'd just post some screen shots in case anyone else was experiencing this issue. I created a basic report that uses a tabl...
Well Jacksonville Code Camp 2010 is upon us and yours truly will be hosting a session. On Saturday, August 28th, 2010, Jacksonville Code Camp registration opens up at 7 a.m. The event will be held at the University of North Florida , which has a fantastic facility. My session is titled "Introduction to MDX" . I'll be covering the basics of the Multidimensional Query Language (MDX) and show you how to write you first MDX statement. You'll learn how to perform basic queries against your cube as we...
In SQL Server Analysis Services 2008, we have three storage mode options available to us: Relational Online Analytical Processing (ROLAP), Multidimensional Online Analytical Processing (MOLAP) and Hybrid Online Analytical Processing (HOLAP). There are advantages and disadvantages to each, so I figured I'd take a few minutes to give a quick overview describing the storage modes and laying out some of the pros and cons of each. Relational Online Analytical Processing (ROLAP) The ROLAP storage mode...
Let's say your building an SSIS package with a data flow to move data from one database to another. You decide to use an OLE DB Source. For your Data Access Mode you select SQL Command and copy and paste your SQL statement that you've spent all day working on. Your SQL statement features declaring a table variable, inserting into those variables, and then selecting from those table variables. When you preview the data in the OLE DB Source Editor, you can clearly see the data. When you actually e...
In a recent project I've been tackling for the last couple months, I've been migrating several significant subsystems into a single larger system. In one of the tables I'm populating, there is a field for the previous ticket ID, but in the source table there is no such field. So I needed to lookup the previous ticket number in my dataset so I could migrate the data into the new database. There's a couple different ways to do this in T-SQL, and in this blog I will demonstrate the first (I'll show...
In my previous blog post , I mentioned that there was a performance cost for using the LookUpCube function. Well recently a coworker asked me just home much of a performance hit was the LookUpCube function going to cause. So I decided to conduct a test. I wrote two simple MDX queries (one using the LookUpCube function and one not), then executed the queries (6 times each: 3 w/ cold cache and 3 w/ warm cache) while running SQL Server Profiler and compared the duration times of the query. Here are...
In response to a forum question a fellow BIDN user had posted, I decided to write a blog that covers and expands upon what I demonstrated in my answer. Basically, the question boiled down to being able to query multiple cubes in a single MDX statement. The short answer is yes. To query multiple cubes from a single MDX statement use the LOOKUPCUBE function (you can't specify multiple cubes in your FROM statement). The LOOKUPCUBE function will only work on cubes that utilize the same source databa...
Have you ever wondered how to force a carriage return inside a string within an SSRS expression or how to display a double quote ( " ) as a string? There's actually a fast an easy solution. The Chr() functions returns the character associated with the character code. Say for example, you have an expression using an IIF statement that displays one string or another, but you'd like to force a line break mid-string. A character code of 10 (ex. Chr(10)) will force a line feed effectively moving the ...
If you're like me, you probably have a significant number of servers you access on a regular basis, which means every time you start up SQL Server Management Studio (SSMS) you have to log in to each server typing in user names and passwords and connecting one at a time. I find this very obnoxious and annoying. Thankfully, SQL Server Management Studio allows you to save connections to various servers via Registered Servers, which can include Database Engines, Reporting Services, Integration Servi...
The ROW_NUMBER() function is a great function that was added with the release of SQL 2005 and is also included in SQL 2008. ROW_NUMBER makes it very easy to number your rows across the entire data set or across partitions of your data. Here are a couple examples of ROW_NUMBER() in action: SELECT ROW_NUMBER() OVER(order by LastName) as RowNumber ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[NameStyle] ,[BirthDate] ,[MaritalStatus] ,[Suffix] ,[Gender] ,[EmailAddress] FROM [AdventureWorksDW].[d...
When developing an SSIS package, sometimes it can be frustrating trying to troubleshoot various issues and faults with the package. And if you're not a seasoned SSIS developer, you may not be sure about the best way to solve your problem. Thankfully, Microsoft has seen fit to bless us with some useful tools for troubleshooting. Control Flow Troubleshooting When working in the Control Flow, there are several useful ways to troubleshoot during the development of your SSIS Package: 1) Use breakpoin...
I recently developed a series of reports that allows the user to view recently failed jobs by querying the MSDB database for job history information. By clicking the link "Click here to re-run the job", users can attempt to re-run the failed job right from reporting services. To see a detailed explanation on how to accomplish this, check out Devin Knight's blog here . And if you click the link "View job history and error messages", you can see the history of failures of the selected job and view...
Yesterday I was doing some work with the system tables in SQL Server 2008, specifically the tables related to jobs and job execution history. Those tables store dates and times in seperate fields as integer values, which is horrible for reporting. To remedy this, I put together a simple little function that I can pass the intDate and intTime values to and have it spit out a single datetime value. This way I don't have to write all the TSQL every time I want to convert the date and time into a si...
A common reporting requirement an end user may request is the ability to exclude certain records from a report using a parameter. For example, I've had end users request the ability to hide records where a certain field is 0 or NULL. In this example, I'll be using the Adventure Works database and adding the functionality to hide records where the Previous Year's Sales = 0. There's a couple ways to do this. One way would be to doing all the work in the TSQL. Here is the TSQL I began with: SELECT ...
It's very easy to link to an SSRS report from within another application and pass your report's parameters in the URL. To do this, simply add "&", then your parameter name, then "=", and finally the parameter value. You can follow this format for multiple parameters. Here's an example of what a link to a report with parameters in the URL might look like: http://ServerName/ReportServer/Pages/ReportViewer.aspx?%Report+Folder%2fReports%2fReport+Name&rs%3aCommand=Render&StoreID=12345&...
It doesn't take long for an SSRS developer to run into those pesky blank pages popping up between pages or at the end of their report. Unlike SSRS 2005, 2008 does not automatically ignore the white space in your report. Because of this, you always want to make sure that the size of the body of your report does not exceed your page size. But if you left some extra blank space in the body of your report that exceeds your page size, the blank area will be repeated on the next page, which will expla...
If you ever created any calculations in your cube or in an MDX query that uses division, you've probably run into the issue of dividing by zero (0) or NULL. Dividing by 0 or NULL will show the ugly -1.#INF when browsing your cube from Excel. But being the good developer you are, you don't want your end users to see that. So in order to gracefully handle dividing by 0 or NULL, we can do the following to prevent -1.#INF from exposing its ugly face to your company's executives: WITH MEMBER [Measure...
Today I was working on a report built on a cube and stumbled on to an interesting requirement by some of our client's end users (EU). EU wanted to be able to consolidate multiple, specific members into a single parameter value. For example, if EU selected the parameter item A, they should see all members in the report where the value is actually 1 A and 2 A. So by selecting a single parameter value, EU will have actually selected 2 parameter values and will see the associate members in the repor...
If you've done a couple cube based reports, you've run into the issue of the dates being in string format. Because the dates are strings inside your cube, you can't use the neat little date picker control reporting services gives you for datetime parameters. While you as a developer may understand this issue, your "tech savvy" end users may not. Fret no more. Here is a quick and easy way to use the built in date picker control for parameters in your MDX reports. 1) In the Report Data pane of BID...
Since I've had a few people asking about a list of all the links to each article in my Building a Report from the Ground Up series, I've decided to post the links here on my blog. You can also see a list of the all the SSRS development related articles by going here . Keep in mind that my articles on report development are SSRS 2008 specific, so those of you using 2005 will see some differences between my examples and your screen. Hopefully, down the road, I'll have some time to add some 2005 ex...
So today I was updating our web page with some new images the sales team sent me. I copy and pasted the new files into the folder. When previewing them in VS, they appeared fine, so I deployed the page. Once deployed, the new images just would not appear! So I navigated to the folder to make sure I had keyed in the file path correctly, and I noticed that the file names of the images were green! What the heck? Turns out, a file name in green means the file is encrypted. This new green file name t...
I've been working on my company's site for the past several month now, and we use Devexpress tools, which are way cool, by the way. I wanted to figure out a way to apply conditional formatting to some of the gridviews I was adding to the site, but since I'm a bit of a newb to the world of ASP.NET, I struggled with it. Luckily, my buddy Chris Rock , who is the Jedi Master of all things codable, gave me a hand. Thanks, Chris! To apply conditional formatting to a gridview, you could do something li...
Late last week, I was able to assist a BI Documenter customer who was having an issue opening a .chm file generated by BI Documenter. Every time he tried to open the compiled help file, his screen showed “Action Canceled” and he was unable to view the .chm document ( Fig. 1). Figure 1 So I did some research and came up with a few different possible causes and solutions to this frustrating problem. 1. The first possible cause of this problem could be that any of the containing folders has a “#” c...
Since I've been doing alot of .NET development lately, I also get the opportunity to create my own graphics and images using Adobe Master Collection Creative Suite 3. Adobe makes some great tools that are very versatile and easy to use and I love designing with them. Lately, however, I'd been having some strange issues with some of the Adobe programs, so I set about uninstalling and reinstalling. After popping my CD in and beginning the installer, a little message box popped up with this message...