How to remove blank page produced from a matrix in SSRS?

I created an End of Month report that has many matrixes, graphs and tablixes in SSRS 200R2. Matrixes tend to produce a blank page(s) when previewed in a print layout. This is a very annoying experience for report developer.

It requires a bit of trial and error to remove the blank pages from matrixes. The trick is to contain the matrix in a rectangle and tick off the “Keep contents together on a single page, if possible” option from the rectangle. Then ensure that the size of the rectangle is within the size of the matrix with no white spaces. Then try to reduce the size of the row group column and/or the colum group columns and followed by resizing the body width. Keep doing this till it stops producing blank pages.

The following blog is very useful.

http://snippetsandhelp.blogspot.com.au/2012/01/ssrs-removing-blank-pages-in-your.html

 

Read Full Post »

MDX report takes 10 times longer to run after deployment to a client server???

My MDX report runs in seconds on my local machine but it takes so long to render on my client server. What’s going on there? It took me huge amount of time to troubleshoot. The SQL Server Profiler trace shows a repetivite mdx query execution. It executed the same query over and over. Rebuilding the cube does not help. The only difference is that the cube structure has changed to include new dimensions and new measures. Also rather than adding 4 measures manually one by one,  the existing measure group was readded to include the new measures automatically.

I then thought what if I browse the cube and note the response time it takes to bring back some data. To my surprise, the values that it brings back when sliced by Year are all of the same value. It means that the relationship between this fact table and the Date dimension has lost. So I quickly check the data source view of the cube and the relationship has indeed been dropped for some weird reasons. I then manually map the relationship and check everything else. All good and finally the report runs in seconds! 😀

I reckon this is a bug within SSAS 2008R2.

Read Full Post »

Blank page when a tablix is hidden conditionally in Report Services 2008 R2

My report has many sections. Each section may have tablix and/or graphs. When I output the report in PDF, it produces a blank page after a certain section. If this section is removed, the blank page is gone.

This section contains 2 tablix but it will only display one of them depending on which parameter combination gets selected. So each tablix has a conditional visibility expression set to either display or hide according to the rule.  If the second tablix is removed, the blank page is gone. However, I need both tablix in this section. I then placed both tablix in a rectangle and ensure that the “Keep contents together one a single page, if possible” option is UNTICKED.  The blank page is gone!! 😀  I spent a significant amount of time trying to resolve this!

 

 

 

 

Read Full Post »

SSIS 2008R2 Error – OLE DB Source – Value does not fall within the expected range

I added 5 more columns into my data source view used in my OLE DB Source. When I tried to update the OLE DB Source, it throws out the “Value does not fall within the expected range” error.  This error does not tell you much. I could execute the view and it returns with the correct result successfully. 

I created a new OLE DB source and then pointed it grab data from the view and it works perfectly.  I then deleted the existing OLE DB Source but my SSIS package froze.

This SSIS package is fairly big and it contains a lot of lookups and i suspected it froze because of this.

So in summary, I am not able to refresh the existing OLE DB Source and nor delete it.

Instead of trying to fix the existing Data Flow or spending time googling, I resolved to recreate the whole Data Flow from scratch.

This is a very annoying bug! 😦

Read Full Post »