Adatis BI Blogs

Oooh - See the pretty report ;)

Users love pretty things! Especially upper management who have minions to actually make sense of the numbers behind the reports for them ;)  Out of the box Reporting Services reports look anything but pretty! Here's few tips to make your bosses happy :) Use a background image as a border for charts. The image behind the chart below is a simple png file of only 8kb in size.  To make this work you need to use a rectangle object with the background image set to your image and the chart dropped in the rectangle Use custom palettes for your chart series.  To do this set up a new table with two columns, one that relates to the key's in your data source and one with the hex codes of the colours you want to use.  Then in your recordset join the id to your data.  Once you are returning the hex value for each row to your RS dataset you can use this to define the series colour: 1) Right click on chart -> Properties 2) Select the data tab then edit the value 3) Select the appearance tab then the series style button 4) Select the fill tab then use an expression to 5) Set the color property to your dataset field 6) Click OK, OK, OK, OK (phew!) I've uploaded a sample project to show how this is done.  Use black borders and lines sparingly (silver is always a good one). Put RS in Sharepoint Integration mode - the toolbars and filters are much nicer and auto-hide (sort of!).  Make sure you consider the limitations of this mode first though. Avoid using 3d perspectives in your charts - this is the easiest way to make your charts fugly (IMO)!

Analysis Services Stored Procedures - Views

Whilst not changing nappies and singing lullabies, Sacha is developing a .Net mdx helper class that behaves very much like an ADOMD command and runs an mdx query that returns a data table.   "Why not just use ADOMD?" I hear you ask - as Sacha mentions there are a number of reasons such as the fact that the data types are not returned (needed by .Net controls like Chartfx) and top level hierarchies ("All Time" etc ) are not always returned dependant on your query layout.  And in fact Sacha's little helper (ho ho ho) does use ADOMD but rather than use the returned table it parses the XML file that is generated.  This XML is actually quite simple once you get your head around the layout and is in fact very rich containing all sorts of metadata that is not returned in by ADOMD Other nice functionality includes the ability to return a number of cube and database properties (Description etc, last update, last processed); But the functionality I really like is that the class uses a stored query definition which can contain parameters much like a SQL stored procedure.    Sach and I have had a number of conversations over the past few days about why there is no concept of this in AS2005 (or 2008?) and haven't come up with a valid reason.  Surely people out there want to be able to re-use the same mdx queries (with different parameters).  How cool would it be to be able to use "exec mdxsp_GetResults(Param1, Param2)" in various mdx editors.  At current the query defs are stored in text files but we're looking at alternatives such as using msdb or the even the xmla cube def itself. In fact I'm surprised that no-one has done this already - or have they?? Very interested to know if people think this is something worthwhile? NB - if you found this blog looking for the excellent AS Stored procedures project it's here

Visual Studio/BIDS Auto Hide Speed

If you use auto hide panes a lot you can change the speed they take to show and hide - the default setting is too slow in my opinion.  Select Tools->Option->Environment->General Then you can slide the scroller to your preference