Kasper de Jonge PowerPivot Blog

By default, a chart in SSRS will automatically position the labels on the X-axis as it best fits.  However, one of these options includes hiding labels when the chart feels there are too many to show.  As you can guess, this is not always what we want.

A example:

When opening up the axis properties for the X-axis on a column chart you get the Category Axis Properties screen.  Like all other property screens, this is also one with several pages.  You see that one of the pages is called Labels and as you want it to show all labels, that’s were you start looking.  Well, stop looking, that’s the wrong place.  The option that you need is located in the first page, the Axis Options, and its called Interval.  This is the interval between each label on the axis, and by default it is set to Auto.  As we want all labels, change it to 1.

I changed the direction of the labels as well, to rotate 90 degrees:

Found at: http://blog.hoegaerden.be/2009/07/20/chart-optimization-tips/

Today i wanted to test a report running under different credentials, since we use integrated security i wanted to run my IE as a different user. When you use SharePoint you can log in as a different user using the menu. But using the report manager this is not posible. Using the Runas command you can run a program, including IE, as a different user. You can do this by using the following command:

If the user that you want to use has permissions to execute IE in your PC, just run the following command:

runas /user:username@domain "C:\Program Files\Internet Explorer\IExplore.exe"

Windows will ask you to enter the password and then will open the IE window. Don’t forget to setup the Security Settings to “Automatic logon…”

If the user you want to use doesn’t have permissions to execute IE in your PC, use the /netonly parameter to only use the credentials for remote access (IE still runs with your default credentials):

runas /netonly /user:username@domain "C:\Program Files\Internet Explorer\IExplore.exe"

Found at this blog:

http://tiagoe.blogspot.com/2007/12/how-to-run-internet-explorer-with.html

The thing i’ve been waiting for to surface is the use of Silverlight in BI. Bart Czernicki has released a book describing the combination of BI and Silverlight, a thing I’m very interested in!

What will you learn from investing in this resource?

Covering the entire scope of BI and applying these concepts to Silverlight applications is simply not realistic in one single resource.  Even if I had the option of writing 750 pages or more, important facets of BI would be missed.  Therefore, I decided to focus on the presentation tier of Business Intelligence applications.   For example, I didn’t think it was fair to focus on the data and services tier with these technologies going through a rapid implementation and tooling evolution (RIA Services, WCF REST, Oslo, ADO.NET Data Services 1.5, etc.).  However, Silverlight’s rendering and client processing engine is mature enough to warrant a guide on how to implement client-side BI concepts.  Therefore, topics like visual intelligence, data visualizations, predictive analytics, collective intelligence, interactive tools, parallel computing, working with large data sets, etc., are covered in my book on the presentation tier.

This book has three intended audience segments and their goals:

• Silverlight Developers – Learn how to extend your Silverlight knowledge in real-world applications.  Learn the basics of Business Intelligence 2.0.
• Business Intelligence Professionals - Get a better understanding of how Silverlight can help you overcome some of the challenges to implement simple BI tools.
• Strategic Decision Makers (architects, CIO, technical director, etc.) – Understand if Silverlight is the right platform to deliver BI software.

Read the entire release post here:

Announcing Next Generation Business Intelligence Software with Silverlight

This is one book I will order asap!

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do a drill through to the database, real easy to setup and use.

Download the assembly here:

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

Great work Brian!

Found at: performancepointblog.com

By accident I heard one of our sales people talk about showing data from a geographical location on a report. I immediately jumped in the discussion and told about the new Spatial datatype of SQL server and the reporting data region Map. Of course when talking to Sales people the immediatly asked for a demo. In this blog post my findings about Spatial data, Reporting map control and Bing maps. My starting point was Robert Brucker’s reporting blogpost, and try to recreate his demo. Had it up and running in no time.

The first thing i had to do was create a Geography data type, since this is the base of the Reporting map control. This was pretty easy since i had some test data with Latitude and Longitude as float in a table. All i had to do to converting these to a geography datatype was:

update Location set GeoPostion = geography::Point(Latitude, Longitude, 4326)

There are several ways of different ways to add point data in SQL Server 2008, i found them at this blog post: http://blog.colinmackay.net/archive/2008/02/07/1812.aspx

Next up was creating some kind of representable chart, i used the steps as described by Robert Brucker to create a report of my data, the one thing that immediatly caught my attention is that the Reporting map control using Bing maps is data aware. The Bing map automatically centers and zooms in at my locations (i used the Person table from Adventure Works to shoot this screenshot, as there are persons from around the globe he centers on the world).  I created a view of the data so i can change its source on a later stage:

Next up was choosing the visualization of the data, i wanted to create a analytical overview so i chose Bubble map:

Since i wanted to show some analytical data i changed the query to point to my Dutch data and added an amount field (which i filled ranomly with values 15 to 2000) using a top 50 of the Netherlands, you can see the data awareness again, the map switched to the Netherlands only.

In the data visualization step i selected the Bubble size property along with a colour scheme to represent my data:

Resulting in the following map in reporting services, so no programming necessary (except the Query of course):

It would be great if users could zoom in on the data .. luckily the data is a layer in reporting services so the data points are reporting objects where you can set properties like an Action:

So i turned my view into a stored procedure with a parameter and using the data awareness of the reporting control i called my own report with a reporting parameter that selected the top 50 in a region (the Id they clicked)

Using the same report for master and detail, this results in the following map when clicked:

Conclusion: Reporting with geographical data has become very very easy and offers some great possibilities to report developers.

Great video about gemini where Donald Farmer and Julie Strauss talk about the inside of gemini:

http://channel9.msdn.com/posts/Charles/Donald-Farmer-and-Julie-Strauss-Inside-Project-Gemini

Most people who have installed a Reporting services with SharePoint know it’s NOT easy and takes a lot of tuning and in depth knowledge of SharePoint, Kerberos and Server configurations.

Microsoft recently released a Troubleshooting guide which you can find here:

http://msdn.microsoft.com/en-us/library/ee384252.aspx

Today i was working with a SSIS package that has to load 22 million rows of data, it takes a Stored Procedure as source. The problems starts when you put the sp into the query window. SSIS immediately starts validating the columns, but he can only do it after loading the SP, which takes a while.

To disable this checking you can turn off ValidateExternalMetadata on the data source or DelayValidation on the data flow level. It now only checks once on run time not on design time. The downsite of using this is that the component is not aware of changes to the metadata of external data sources.

You can set DelayValidation to True on package elements whose configuration is not valid at design time to prevent validation errors. For example, you may have a Data Flow task that uses a destination table that does not exist until an Execute SQL task creates the table at run time. The DelayValidation property can be enabled at the package level, or at the level of the individual tasks and containers that the package includes. Normally you must leave this property set to True on the same package elements when you deploy the package, to prevent the same validation errors at run time.