In case you haven't noticed, there is a problem in SQL Server Reporting Services (SSRS) when you try to have tablix column headings repeat on a new page. For example, I have a report that uses a tablix.
I have a group that is based on GroupType.
When I run the report, Page 1 displays just like what I want. I have my data and all my column headings.
But when I go to Page 2, there are no column headings.
I go back to the tablix properties and I check the box for Repeat header rows on each page. But there is no change in the report display.
So how do I get my column headings to appear at the top of each page? I could place them on the page header, but then I have to add one textbox for each column and make sure it is properly aligned with the columns in the tablix. I prefer adding the column headings in the tablix. But they are not being displayed when I have a new page.
The solution? It’s kind of a trick. The RepeatColumnHeaders property works if there is a column headings area in the report. Unfortunately, the default tablix (and this report) does not contain a Column Header area so setting the property appears not to work. You can identify a Column Heading area if you see the double dotted lines in the tablix. A work around is to add a column group to the tablix. Then delete the group in the grouping pane but choose to leave the associated rows and columns. Now you can move your column header labels to the new cells above the double dashed lines a...
[More]
I just finished reading Foundations of SQL Server 2008 R2 Business Intelligence written by Guy Fouche and Lynn Langit. Lynn Langit previously wrote Foundations of SQL Server 2005 Business Intelligence. Guy Fouche has written Pro SQL Server 2008 Analysis Services along with several other books. This particular book is pretty good at explaining how to use the Microsoft BI toolset, including SQL Server Reporting Services (SSRS), SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Data Mining. I am glad I read it and wholeheartedly recommend it to anyone who is working with SQL Server. It provides ample examples for working with these products and it also delves into Report Builder, Excel, PowerPivot, and MDX. I like the organization of the book and the path it takes in discussing the different tools. The first subject it discusses is OLAP which is the subject that most readers probably are most interested in. So once you have a better understanding of OLAP and how SQL Server can address the subject area, then it moves on to using the other BI tools. The only thing I did not like about the book was that it does not cover Report Builder 3.0. It looks like a previous version of Report Builder. But, as far as I am concerned, that is ok, because I do not plan on using Report Builder. It also omits any discussion of shared datasets which are introduced by SQL Server 2008 R2 and it does not warn about updating the properties under certain situati...
[More]
I previously wrote about the DimTime dimension. That is a pretty easy dimension to create and a pretty universal dimension. Now let’s look at a DimDate dimension table. There are many more decisions to be made than there are when creating the DimTime dimension and to create a useful DimDate dimension, you should get some user input before finalizing anything.
In the DimDate table I am providing here, I set it up for a company that has a fiscal year from April to March. And the fiscal year has the same value as the calendar year for the April 1 date. So in the included stored procedure I use an offset value of -3 to calculate the fiscal period within the fiscal year. (If the fiscal year were to have a value greater than the calendar year for the April 1 date, I would have used an offset value of 9.)
I also incorporate holidays. Holidays are not always easy to calculate, but for this table I make the assumption that the company has the following holidays:
New Year's Day
MLK Day
Presidents Day
Memorial Day
Indepndence Day
Labor Day
Thanksgiving Day
Day after Thanksgiving
Christmas Eve
Christmas Day
When a holiday falls on a Saturday, it is observed on the Friday before; When a holiday falls on a Sunday, it is observed on the Monday after; When Christmas Day falls on Saturday, Christmas Eve is observed on the Thursday before Christmas; When Christmas Day falls on Monday, Christmas Eve is observed on the Tuesday after Christmas. Since holiday observance ...
[More]
I just installed SQL Server 2008 R2. It was not as hard as I thought it might be. I already had SQL Server 2008 installed, so I was facing the choice of uninstalling SQL Server 2008 before installing the new version, or installing SQL Server 2008 R2 side-by-side, or upgrading SQL Server 2008 to SQL Server 2008 R2. It was a little hard trying to figure out which was the best option.
To manage the versions with side-by-side installation, SQL Server 2008 R2 side-by-side installation with previous versions is supported until SQL Server 2000, if you have SQL server 7 on the same machine you have to upgrade it to minimum level of 2000 compatibility. SQL Server supports multiple instances of the Database Engine, Analysis Services, and Reporting Services on the same computer. You can also upgrade earlier versions of SQL Server, or install SQL Server on a computer where earlier SQL Server versions are already installed. By design if the previous version of SQL Server is a default instance then you have to install 2008 R2 as a named instance, as you cannot have more than 1 default instances per server.
For me, I did not want to maintain multiple versions, so I decided to use the upgrade option. Suprisingly, the installation was relatively smooth. With the upgrade option I was not able to change any of the features that were selected for the previous version. When I go to the "Setup Support Rules Check" step, I was surprised to see that one task failed. This is the step where the rule...
[More]
PowerPivot is great! It offers a lot of promise and it is also a great technology for Self-Service BI now! The concept of accessing data from various sources and bringing them together in a PowerPivot workbook is very powerful. And look at the amount of data that it can process. Whoever thought that Excel would ever get to this stage where it could handle more than 65,000 rows at one time? But it is still version 1.0 and as such there are a few issues when using it.
I work with SharePoint 2010 and SQL Server 2008 R2 in integrated mode. Usually I will import Reporting Services data into PowerPivot. This is a great feature. It provide out-of-the-box PowerPivot functionality. SQL Server 2008 R2 Reporting Services provides an Atom rendering extension to generate a data feed from an existing report. For best results, you should have Excel 2010 with the PowerPivot for Excel on your workstation. The PowerPivot client application will launch in response to the data feed export, automatically adding and relating the tables and columns as they are streamed in. It imports SSRS datasets, not the rendering of data. And it allows the user to select the individual report parts that the user might want to import, such as tables, lists, and/or charts.
But I have experienced a few issues with PowerPivot. I attribute them to the fact that it is version 1.0 and that improvements are probably right around the corner. Things like the refresh issue, the lack of security, etc. But here are a coupl...
[More]
I just thought I would display the Thanksgiving prayer I used today. I am Catholic, so I have grown up with only one prayer that I use before meals, regardless of the occasion. Most Catholics know it by heart. "Bless us O Lord for these thy gifts . . ." I decided to try something different today so I used one based on a poem written by Ralph Waldo Emerson. Of course, being Catholic, the first requirement for any prayer before meals is that it is short and to the point.
For each new morning with its light,For rest and shelter of the night,For health and food,For love and family and friends,For everything your goodness sends,Thanks.
In general, SQL Server Integration Services security-related functionality can be grouped into two broad categories, dealing with:
protecting package content (as a whole or limited to its more sensitive portions) from edits or unauthorized access,
restricting the ability to execute packages (including preventing execution of packages that have been modified in an inadvertent or malicious manner).
Packages can be saved in three types of storage locations:
as entries in the msdb database,
as XML-formatted .dtsx files within the file system, or
as an SSIS Package Store.
All are practically identical in the context of protecting package content.
SSIS is designed to be secure. One aspect of this is that any “sensitive” information that is part of the package needs to be protected. And by protected, we mean never, ever stored in clear text. Depending on the destination selected, there are slightly different types of security options available. The primary one is based on the ProtectionLevel package property, which takes on one of six possible values:
"Don’t save sensitive" (strips out the "sensitive" information, like passwords),
"Encrypt sensitive with user key" (encrypts the "sensitive" information like passwords with a specific user key. Forces that user to be the one to run the package. If a different user wants to run the package, the user will have to provide proper credentials),
"Encrypt sensitive with password" (encrypts "sen...
[More]
When creating a data warehouse we will usually create a date dimension. And we will usually need a time dimension, but the time dimension is not as common as the date dimension. The time dimension is used when there is data that needs to be associated with a time level (Hour, Minute, Second.) To view the DimDate dimension, Click here. The time dimension is probably the easiest of all the dimension tables in a data warehouse to create. So I am going to offer my take on it. Here is the way I would define the Time Dimension. CREATE TABLE DimTime ([TimeKey] int NOT NULL , [Time] varchar(11) NOT NULL , [Time24] varchar(8) NOT NULL , [HourName] varchar(5) NOT NULL , [MinuteName] varchar(8) NOT NULL , [HourNumber] tinyint NOT NULL , [Hour24] tinyint NOT NULL , [MinuteNumber] tinyint NOT NULL , [SecondNumber] tinyint NOT NULL , [AMPM] char(2) NOT NULL , CONSTRAINT [PK_DimTime_TimeKey] PRIMARY KEY CLUSTERED([TimeKey]) )GO And here is the stored procedure I would use to populate it. CREATE PROCEDURE dbo.PopulateDimTimeASSET NOCOUNT ONDECLARE @DimTimeKey int , @MaxTimeKey int , @Date datetime , ...
[More]
I have not paid too much attention to the issue of Divide by Zero in SQL Server Reporting Services (SSRS) because I figured it out years ago. But I was just discussing the issue with a colleague and he had not paid much attention to the issue either. But he said he always just used an IIF statement in the expression for the cell to determine if the denominator was null or a 0. When he encountered that situation, he either displayed an “N/A” or a 0 depending on the situation. He used an IIF statement something along these lines: =IIF(Fields!Sales.Value = 0, “N/A”, (Fields!Sales.Value – Fields!COS.Value) / Fields!Sales.Value) I told him that that does not work. If his report encounters a zero or a null, the report will display “#Error” in that cell. Why? The SSRS compiler evaluates the results of an IIF before it assesses the IIF logic. So the compiler will attempt the divide by zero before applying the IIF logic. There are several ways to remedy the situation, but he had a little difficulty in finding the correct solution. As you search through the Internet you could find several incorrect solutions. So I referred him to this other blog site where he got a good explanation. http://www.tsck.net/DasBlog/PermaLink,guid,6fa5a47a-e1eb-48d1-b87e-4e3526a3cd39.aspx
I am using SQL Server 2008 R2. I created an SSIS package and I use an XML configuration file. Everything worked well while developing the package in Business Intelligence Development Studio (BIDS) and when testing it out on the client machine. But when I tried to deploy it to the Production environment, I ran into a problem with the configuration file. I kept getting an error telling me that the package could not find the configuration file. After a lot of research and experimentation, I figured it out.Here is what happened.After building the solution, the deployment utility is created in the default location if the build is completed successfully. Navigate to the deployment folder, C:\SSIS Projects\Packages\Package1\bin\Deployment in our case and we will find the deployment files i.e. the integration services deployment manifest file, the configuration files, and the .dtsx file.Copying of the Deployment Folder to the Target Server: After creating the deployment utility, next step is to copy the deployment folder to the target server. I want to deploy it to the production server. I do not have access so I enlist the DBA to perform the task for me. He sets up a folder on the Production server named D:\SSIS Projects\Dependencies and moves the .dtsConfig file there. He sets up another folder, D:\SSIS Projects\Source and moves the other files there (including the manifest file.) Then he double-clicks the manifest file to launch the Package Installation Wizard.Running the Package...
[More]