Jorg Klein : SSRS

SQL Azure Reporting is announced!

jorg — Thu, 04 Nov 2010 14:54:27 GMT

With SQL Azure Reporting Services you can use SSRS as a service on the Azure platform with all the benefits of Azure and the most features and capabilities of premise. It’s also possible to embed your reports in your Windows or Azure applications.

Benefits of the Azure platform for Azure Reporting Services are:

Highly available, the cloud services platform has built-in high availability and fault tolerance
Scalable, the cloud services platform automatically scales up and down
Secure, your reports and SQL Azure databases are on a safe place in the cloud
Cost effective, you don’t have to set up servers and you don’t have to invest in managing servers
Use the same tools you use today to develop your solutions. Just develop your reports in BIDS or Report Builder and deploy to Azure

Disadvantages are:

SQL Azure databases are the only supported data sources in the first version, more data sources are expected to come
No developer extensibility in the first version, so no custom data sources, assemblies, report items or authentication
No subscriptions or scheduled delivery
No Windows Authentication, only SQL Azure username/password is supported in the first version, similar to SQL Azure database. When SQL Azure database gets Windows Authentication, Azure Reporting will follow

Despite the disadvantages of the first version I think SQL Azure Reporting Services offers great capabilities and can be extremely useful for a lot of organizations.
I’m really curious about the CTP, which will be available before the end of this year. You can sign up for the SQL Azure Reporting CTP here

Read more about SQL Azure Reporting here

Start / Stop SQL Server, SSIS, SSAS, SSRS and SQL Server Agent at once with a batch file

jorg — Tue, 06 Jan 2009 21:50:00 GMT

A lot of developers have SQL Server and its different services running on their PC or notebook to develop or test BI solutions. Unfortunately this slows down your system quite a lot. To speed things up when not using SQL Server, I used to stop and start each service manual quite often.

Recently I found out that it's possible to start and stop all services at once with a simple batch file. It now only takes a couple of seconds instead of a few minutes and some annoying steps.

Copy/paste the following in a .txt file and rename it to .bat to make it a batch file, execute it by double clicking the file.

START SCRIPT:

NET START "SQL Server Agent (MsSqlServer)"
NET START "MsSqlServer"
NET START "MsSqlServerOlapService"
NET START "ReportServer"
NET START "SQL Server Integration Services"

STOP SCRIPT:

NET STOP "SQL Server Agent (MsSqlServer)"
NET STOP "MsSqlServer"
NET STOP "MsSqlServerOlapService"
NET STOP "ReportServer"
NET STOP "SQL Server Integration Services"

SSRS – Matrix that adds a new column each time 5 rows are filled with data

jorg — Wed, 06 Aug 2008 12:31:00 GMT

What if you want a dynamic list of values in a matrix but with a maximum of 5 rows. How do you create a matrix like this? I thought this should be an easy job but I found out it was not really simple…

I tried to create a matrix like this for a dynamic list of countries. In this blog I will explain how you can achieve this with a few simple steps.
1. You need to create an MDX(I used a SSAS datasource) query that returns the list of countries with a numbering:

2. Next thing you need to do is create a matrix:

3. Next and last thing you need to do is the following:

Use the following expression for the row group: =(Fields!Country_Number.Value - 1) Mod 5
Use the following expression for the column group: =Floor((Fields!Country_Number.Value - 1) / 5)

Result:

SSRS – Static column headers in a Matrix

jorg — Wed, 16 Jul 2008 12:28:00 GMT

How do you create a static column header centered above your dynamic columns? One way to try achieving this is to place a textbox above your dynamic columns. One thing is for sure, the textbox will never be on the perfect centered location and what if the number of dynamic columns grow or shrink?

Thing you need to do is to create a static column group. You do this by adding a new column group to the matrix and give it a static expression, for example: =”static”
Now make it the top group by clicking Up for the static column group on the Groups tab of the matrix’s properties. You can also achieve this by just dragging the column group up in the layout view.

The result, a centered and perfect aligned column header with the text “YTD” above some dynamic columns containing years:

SSRS - Custom expressions for subtotals in a matrix

jorg — Thu, 06 Dec 2007 21:10:00 GMT

If you want custom expressions for your subtotals in a matrix, for example to calculate an average instead of the default sum, you need to use the InScope() and Iif() functions in your data field…

When you create a matrix with SSRS you get the following default groups:
A row group named: matrix1_RowGroup1
A column group named: matrix1_ColumnGroup1

With the normal functionalities you can’t change much on the behavior of your subtotals in your matrix. When you create a subtotal it calculates a subtotal and that’s about it ;-)

If you use the following expression in the data field of your matrix you can take full control on the behavior of all your subtotals:

=Iif(InScope("matrix1_ColumnGroup1"),

Iif(InScope("matrix1_RowGroup1"),

"In Cell",

"In Subtotal of RowGroup1"),

Iif(InScope("matrix1_RowGroup1"),

"In Subtotal of ColumnGroup1",

"In Subtotal of entire matrix"))

Replace "In Cell", "In Subtotal of RowGroup1", "In Subtotal of ColumnGroup1" and/or "In Subtotal of entire matrix" with the expressions or fields that you want.

For example, if you want to calculate an average:

Replace "In Cell" with Sum(Fields!Amount.Value)

Replace "In Subtotal of RowGroup1" with Avg(Fields!Amount.Value)

More information about the InScope() function on MSDN

SSRS – Invalid row heights, BUG?

jorg — Fri, 26 Oct 2007 12:38:00 GMT

Because I got a few reactions regarding the screenshots below (people thought something was wrong with the screenshots) please note that :

Some of the screenshots below look awful because I selected all the text in the matrices with CTRL-A. I did this to make the differences in row heights clear to see. I also made the numbers in the matrix unreadable.

Problem:
As you can see in the screenshot below the row height of rows that contain empty cells differ from the rows where all cells contain data. Screenshot below is taken from IE(with all rows selected), when I run the report in BIDS preview it renders fine!

I think this is strange if you look at the properties of the rows during the problems:

Can Grow and CanShrink are set to False so you won’t expect cells heights to grow or shrink. It seems the problem is that SSRS gives empty cells a row height of 0,25(the default) and I use a row height of 0,20. Result is that everything goes fine until there are rows with empty cells. What’s also very strange is that when I made a print of the report, the row heights were all fine!!

Solution:
When I faced this problem I just tried a couple of things and it seemed the padding property gave an outcome. When I run the report with the following properties for the rows it renders fine and there are no problems:

As you can see in the screenshot from IE below, the row heights are fine now!

Conclusion:
It seems that when you
a) have a matrix with cells that can be empty
b) you use a row height that’s smaller then the default of 0,25
you need to use the following properties for your rows in the matrix to prevent problems:

· Padding: 2,2,0,0
· VerticalAlign: Middle
· CanGrow: False
· CanShrink: False

What makes this strange to me is that a print of the report and preview of the report in BIDS did not show differences in row heights but Internet Explorer did. This problem occurred with IE 7 and a SQL Server 2005 installation with SP and updates installed.