Some Random Thoughts

PowerPivot Training

2012-05-25T14:15:00.000-04:00

A quick word to all those guys in the USA who are looking to get trained in PowerPivot. PowerPivot is the buzz word in the industry right now and companies are drawn to it like bees to nectar because of it’s in-memory and self-service BI capabilities. My company, Mariner in partnership with SQLBI are offering PowerPivot workshops and you can find more details in the flyer below. Feel free to reach out to me in case you have any questions.

Forward to a friend \| Visit our website \| View as a webpage
	PowerPivot Training

PowerPivot Training Comes to NC!

		Public Course Now Available

Be a PowerPivot Power User In 2 days, learn the basics concepts and progress to complex data models and DAX formulas. At the end of the training, you will know how to use PowerPivot in the real world. Download the workshop outline. Ideal for Business & Data Analysts, IT Pros It is designed for people like you eager to use PowerPivot’s full capabilities. You'll learn the concepts and techniques needed to implement PowerPivot and make critical business decisions using the most popular BI tool on the planet, Excel. Learn from Leaders In partnership with SQLBI, Mariner offers PowerPivot workshops in the southeastern US. Offered on-site at your location or at these new public venues. Mariner is the exclusive provider of the PowerPivot Workshop in the southeastern US *Register by June 13, 2012* to guarantee your seat. Mariner is 1 of just 2 companies in the US with access to the training materials developed by PowerPivot gurus and authors, Alberto Ferrari and Mark Russo. This exclusive partnership enables us to run these workshops for you.

Details

Price
$1,300 per student
includes workshop and training manual

Date & Time
June 18 & 19, 20128:30am-5:00pm
(with an hour for lunch)Location Aloft Hotel-Ballantyne 13139 Ballantyne Corp Pl
Charlotte, NC
Get directions

Prefer another city?
Let us know!


		Your Trainer, Javier Guillén

Javier is a Business Intelligence Specialist (MCTS / MCITP SQL Server 2008 BI) at Mariner, dedicated to helping people maximize SSAS & PowerPivot (including MDX and DAX). A Microsoft Community Contributor, you may have seen him speak at PASS events and most recently at Carolina Code Camp. Check out his blog, connect with him on LinkedIn or follow him on Twitter.

About Us

For more than a decade, Mariner has been helping clients leverage their data - from any data source to any device - through the use of business intelligence, analytics, data warehousing, business scorecards and performance management dashboards. Mariner serves clients in a variety of industries including utility, healthcare, education, media and manufacturing sectors. Singularly focused on providing analytic solutions, we help business people organize, use, analyze and share data to improve sales, customer service, logistics, productivity and financial insight. Mariner is a Microsoft Certified Gold Partner with competencies in Business Intelligence and Data Platform. Mariner-Insight to Achieve. www.mariner-usa.com

Changing Granularity of Leaf Level Calculations in SSAS Tabular

2012-05-02T12:27:00.001-04:00

My company is awesome!!! Everyday that I go to work, I discover something new and exciting from the people around me and come back home thinking – wow, today was just legendary! The best part of the day is when I get to discuss ideas with my colleagues, it is so fulfilling intellectually. So last month, I was having one of those discussions with PowerPivot/Tabular expert and my colleague, Javier Guillen (blog | twitter) on Last Non Empty functionality in multi-dimensional and one thing led to the other. Before we knew, we had discussed a lot of interesting things in both multi-dimensional and tabular about the Last Non Empty functionality and I am pretty sure that you will hear a lot on that field from us soon. Meanwhile, I decided to blog a quick post on one of the basic principles that we will be using for that.

Leaf level calculations in multi-dimensional usually impact the performance and hence, are advised to be done in the ETL part. You might be lucky to get away with such sort of calculations in your DSV also as named calculations. But changing the granularity of such calculations from leaf level to an intermediate level usually requires it to be done in the ETL if we are using the multi-dimensional mode (unless you want to impact the performance by a Scope statement or god forbid, a cross join in the calculation script). In scenarios like prototyping where we are still discovering about the data, changing the granularity of the calculations can be expected and it implies a lot of time lost in re-working the ETL. That is where the Tabular mode is such a big boon and I will be illustrating that fact in the following example.

Consider the AdventureWorks database where there is Order Quantity for the Products and Customers, and the client would like to see the distribution of products across the customers. Now you can make a simple numerical distribution measure which is the number of customers that have data for that product by the total number of customer.

The DAX formula is given below

NumericalDistribution:=100 * SumX (
Values ( Customer[CustomerID] ),
If ( [Sum of OrderQty] > 0, 1, 0 )
) / DistinctCount (
Customer[CustomerID]
)

Let us look at the results when I analyze the same measure for all the Territories filtered by the Category – Accessories and Bikes

Now, let us assume a hypothetical situation where the goal of this year for the Country Sales Managers is to make their customers buy different products and the client wants to analyze the performance of the Sales Managers based on a new Distribution KPI. The numerical distribution measure is not a good indicator for this as it just checks whether the customer has order quantity or not for either Accessories or Bikes. A better indicator might be to assign a weight to each customer based on the different number of products that has order quantity by the total number of products. The formula for the new distribution is given below

The new distribution takes the sum of weights for each customer and divides it by the total number of customer. The DAX formula for the same is given below

Distribution:=If (
DistinctCount ( Customer[CustomerID] ) = Blank ( ),
Blank ( ),
100 * SumX (
Values ( Customer[CustomerID] ),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
) / DistinctCount ( Product[ProductID] )
) / DistinctCount ( Customer[CustomerID] )
)

The results for the above formula is given below

We can see that the distribution values have become very low because of the low weight, as it is almost impossible to expect a customer to buy every product. So the client can come back and ask to calculate the distribution at the level of the Country instead of Customer. This will ensure that the distribution is more appropriately calculated. The new formula for the distribution will become

The DAX formula is given below

Distribution:=If (
DistinctCount ( SalesTerritory[CountryRegionCode] ) = Blank ( ),
Blank ( ),
100 * SumX (
Values ( SalesTerritory[CountryRegionCode]),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
) / DistinctCount ( Product[ProductID] )
) / DistinctCount ( SalesTerritory[CountryRegionCode] )
)

The results for the Distribution formula with the changed granularity analyzed by Country Code and Territory Name is given below

Now, the client may again come back and ask for the formula to be re-calculated on a territory level, as that looks more appropriate for them after seeing the data. Take the case of US for example. The distribution value for US is 96.03 but the average distribution based on the territories is 91.90. So the changed formula is shown below

The DAX formula is given below

Distribution1:=If (
DistinctCount ( SalesTerritory[Name] ) = Blank ( ),
Blank ( ),
100 * SumX (
Values ( SalesTerritory[Name]),
Calculate (
DistinctCount ( SalesOrderDetail[ProductID] )
) / DistinctCount ( Product[ProductID] )
) / DistinctCount ( SalesTerritory[Name] )
)

The result of the formula is given below and we can see that the new values takes the average from the territories at the country level.

Now after making all these changes, the client is happy with the end results. The important thing to note here is that we had this whole hypothetical process completed within half an hour and not days. Consider how much time we would have required to accomplish this in the multi-dimensional mode! Now if you are still in the mood for more DAX after this, make sure that you run by Javier’s latest blog where he uses a similar technique to mimic the MDX Scope behavior in DAX.

I am speaking at…

2012-04-16T20:29:00.001-04:00

And so, it has been an eventful 3 months since I reached the US, and I must say I am really settling in well to my new job and life here. Also, it has been a full two years since I started blogging and a big shout to all those lovely people out there who have been tolerating me and my “random thoughts”. I decided that it is time to start meeting all those great guys around me and what better way than to come and speak at an event near you?

So just as an FYI, I would like to let you know that I am speaking at the following events :-

1) Charlotte SQL Server User Group : Thursday, April 19, 2012 from 5:30 PM to 7:30 PM http://apr2012cssug.eventbrite.com/

2) SQL Saturday 118 (Madison, WI) : Saturday, April 21, 2012 from 9:30 AM to 10:30 AM http://www.sqlsaturday.com/118/schedule.aspx

3) SQL Saturday 130 (Jacksonville, FL) : Saturday, April 28, 2012 from 3:45 PM to 4:45 PM http://www.sqlsaturday.com/130/schedule.aspx

4) SQL Saturday 129 (Rochester, NY) : Saturday, May 12, 2012 from 10:15 AM to 11:15 AM http://www.sqlsaturday.com/129/schedule.aspx

Don’t forget to come and talk to me if you are attending any of these events, nothing makes me more happy than getting to know my readers better. Well, actually I am lying, a mug of beer makes me happier. But then there is no reason why we can’t have a talk over a beer, right?

Finding Nearest Stores using SSRS Map Reports

2012-03-26T19:49:00.001-04:00

April is turning out to be a busy month for me. Apart from taking the beta SQL Server 2012 certification exams and some reviews, I am also speaking at the Charlotte SSUG and at SQL Saturday #118 (Madison) and #130 (Jacksonville). I have also submitted at a couple of other SQL Saturdays, so if you do see me speaking at a SQL Saturday close to you, don’t forget to give a shout! As my topic for the first two sessions are on Spatial Reporting in SSRS, I was preparing for it and that is when I thought of posting one of my demos as a blog.

This post will teach you how to implement a report to find the nearest stores using the spatial features in SQL Server and SSRS. As some of you might know, I am living in Charlotte and Harris Teeter is one of the grocery stores that I frequent. For this demo, I would be finding some of the nearest Harris Teeter stores from a list of my hangouts. Follow the instructions below to replicate it:-

1) Create the table structure for storing the locations of Harris Teeter.

CREATE TABLE [dbo].[Harris Teeter](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]

Also, create the table structure for storing a list of the hangouts.

CREATE TABLE [dbo].[MyHangouts](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Location] [varchar](255) NULL,
    [Latitude] [float] NULL,
    [Longitude] [float] NULL,
    [PostCode] [varchar](20) NULL,
    [GeoL] [geography] NULL
) ON [PRIMARY]

2) Load some sample data for Harris Teeter stores in Charlotte.

INSERT INTO [dbo].[Harris Teeter] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Quail Corners - 204','NC 28210-5803',35.116603,-80.858247),
('Old Towne Mall - 294','NC 28226-7159',35.117117,-80.824515),
('Morrocroft Village - 160','NC 28211-3570' ,35.152698,-80.825796),
('Park Selwyn Terrace - 35', 'NC 28209',35.1616695,-80.8492303),
('Colony Place - 4','NC 28226',35.106549,-80.806327),
('Park Road - 218','NC 28209-2229',35.1767066,-80.8510191),
('Cotswold Mall - 208','NC 28211-2802',35.177524,-80.801119),
('Arboretum - 30','NC 28226',35.096321,-80.78463),
('Myers Park - 12','NC 28207',35.1901493,-80.8231644),
('Kenilworth Commons - 61','NC 28203',35.2026843,-80.8455712),
('Ballantyne Commons - 11','NC 28277',35.05313,-80.848995),
('Stonecrest Shopping Center - 66','NC 28277',35.059911,-80.816675),
('Providence Commons - 45','NC 28277',35.066452,-80.7717459),
('Sardis Crossing - 171','NC 28270',35.138476,-80.740138),
('Central Avenue Location - 201','NC 28205-5108',35.219757,-80.809982),
('The Shops at Blakeney - 27','NC 28277',35.036336,-80.806711),
('Uptown Charlotte - 205','NC 28202-1603',35.2330664,-80.846148),
('Rea Village Shopping Center - 40','NC 28277',35.052441,-80.770867),
('The Shoppes at Ardrey Kell - 317','NC 28277',35.02431,-80.847881),
('Plantation Market - 147','NC 28105-6725',35.082745,-80.732972),
('Steele Croft - 88','NC 28278',35.103305,-80.990847),
('Matthews Township - 157','NC 28105',35.125179,-80.710001),
('Mintworth Commons - 174','NC 28227',35.172913,-80.709081),
('Weddington Corners Shopping Center - 343','NC 28104',35.023379,-80.760665)

For getting the data, I used the Harris Teeter website to get the address of the stores in Charlotte and then used this site to geocode the address to latitude and longitude. Using the same method, I also populated the MyHangouts table with the data below

INSERT INTO [dbo].[MyHangouts] ( [Location], [PostCode], [Latitude], [Longitude] )
VALUES ('Home','NC 28210',35.1537875,-80.8502022),
('Office','NC 28211',35.1493742,-80.8272008),
('PetSmart','NC 28217',35.1385661,-80.8764557),
('The EpiCentre','NC 28202-2538',35.225324,-80.842187),
('Library','NC 28211',35.1513557,-80.8225257)

3) Once that is done, we will have to convert the Latitude and Longitude to spatial data of type geography. Execute the code below for the same:-

UPDATE [Harris Teeter]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

UPDATE [MyHangouts]
SET [GeoL] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326)

4) Create a new report and a datasource pointing to the database where the tables are stored. Create a dataset query named DST_Hangouts for selecting the data from the [MyHangouts] table.

5) Create a new report parameter which will source data from DST_Hangouts. The value will be the spatial field - GeoL while the label will be the Location.

6) Create another report parameter – RP_Cnt which will be of integer data type. This will be used for specifying the number of closest stores you want to see.

7) Make another dataset query – DST_HT which will filter the list of hangouts based on the parameter selected. This dataset query will be used in the map to show the selected hangout.

8) Now, make the pivotal dataset query of this post – DST_TopHT which will be used in the map to calculate the closest stores from the selected hangout

SELECT TOP (@RP_Cnt) Location, GeoL.STDistance(@RP_MyLoc) AS distance, GeoL, Latitude, Longitude
FROM [Harris Teeter]
ORDER BY distance

9) Optionally, we can also include a spatial query – DST_Circle which will draw a circle of 1 km diameter around the selected hangout so that we get an idea of the scale.

SELECT ID, Location, Latitude, Longitude, PostCode, GeoL.STBuffer(1000) AS GeoLocation
FROM MyHangouts AS H

I have filtered this dataset by the selected hangout in the Filters tab of the daataset properties.

10) Now drag and drop a map from the toolbox and then add two point layers based on the datasets - DST_Top HT and DST_HT. Also add a Bing maps layer and a polygon layer for the dataset DST_Circle.

You can see that I have used the marker type as PushPin for the DST_HT point layer and circles for the DST_TopHT point layer to differentiate both of them. It would be good to add a table also which will show the top stores and the distance.

11) Now preview the report and you should be able to see the top N stores nearest to your selected hangout.

You can change the selections or the top count value and see that your report changes accordingly. You will also notice that the Bing Maps layer is data aware and centres / zooms dynamically based on the data.

You can do much more like visualizing the colour of your points based on the distance or any other measure in your warehouse, which will be helpful in making a decision. For eg, there might be a store which is not the closest but is running a sale as shown in the image below.

I have visualized the colors of the stores based on the distance, and in addition to that, stores running a sale are shown with a thick black border. So from this, I can see that the nearest store is at Morrocroft Village, but if I drive an extra 2 miles, I can shop at the Park Selwyn Terrace where I can save some money potentially. The choice of colors or the visualization in itself might not be appropriate, but hopefully this helps to explain the available features. As this turned out to be a pretty long post, I haven’t included all the steps as I normally do. If you do feel lost, feel free to mail me and I can send a copy of the report rdl to you. Time to give my aching fingers some rest now

Overlapping Charts in SSRS using Range Charts

2012-03-18T21:15:00.001-04:00

How fast time flies! Another new version of SQL Server has been launched and it seems like it was only a few days ago that SQL Server 2008 was released. Those days, I was mainly focussing on SSRS, and I can’t express in words how happy I was when I saw all the new features in SSRS 2008 and 2008 R2. Today, I was reading through a great post by one of my favourite bloggers, Hilmar Buchta on SSRS Bar Chart Tips and Tricks and this prompted me to add a chart tip of my own here – Overlapping charts in SSRS.

A quick look into this requirement and many of the BI professionals would say that it is not possible to implement this chart in SSRS. Quite often we underestimate the power of SSRS (I myself didn’t know how to implement some of the charts that Hilmar mentioned in his post) and hence I feel it is important to share and increase awareness through blog posts and other mediums. Follow the instructions below to replicate this chart:-

1) Make a new report and use the query below to make a new dataset.

SELECT        '2009' AS Year, 'Q1' AS Quarter, 45 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q3' AS Quarter, 25 AS Sales
UNION ALL
SELECT        '2009' AS Year, 'Q4' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q1' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q2' AS Quarter, 30 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q3' AS Quarter, 35 AS Sales
UNION ALL
SELECT        '2010' AS Year, 'Q4' AS Quarter, 60 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q1' AS Quarter, 55 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q2' AS Quarter, 50 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q3' AS Quarter, 65 AS Sales
UNION ALL
SELECT        '2011' AS Year, 'Q4' AS Quarter, 75 AS Sales

2) Make a column chart and use Sales in the Data Values, Year in the Category and Quarter in the series.

3) Right click on the Year category group and check the category group name. Rename it to a user friendly name like Year as shown below

4) Create a new calculated measure by adding a new data value and enter the code below

=Sum(Fields!Sales.Value, "Year")

5) Change the chart type of the new measure to Range Column. Ensure that the chart type of the Sales measure is still column. Also bring the Sales measure to the bottom as shown in the image below

6) Change the color of the Range Column as Aqua (or any color you like) from Automatic, so that all bars of the range column have the same color.

7) You can enable the data labels for the Sales measure and disable the legend for the new calculated measure. Now if you preview the report, you will get the required result.

You can use the same technique (with a little modification in the dataset query) to implement charts like shown below

Hopefully, this post will help in getting your creative juices flow when you are faced with a tough SSRS chart requirement!

Heat Maps for SSRS using Map Control

2012-03-10T12:34:00.001-05:00

I have always been inspired by a particular quote from Arthur C Clarke - “The only way of finding the limits of the possible is by going beyond them into the impossible”. The more you think of it, the more you make sense out of it. If you decide in your mind that something is impossible, you will never even try to make it happen. Consider the example of the 10-second barrier in athletics. Rather than a physical barrier, it has always been more of a psychological barrier. How else can you explain the significantly high numbers of athletes that have broken the barrier in the last 2-3 years when compared to the 20 years between 1968 and 1988? Being mindful of that, I have always tried to be open when discussing requirements and always keep a secret list with me of the requirements that I have not been able to fulfil. One of my earliest list items was that of a squarified Heat Map (or Treemap as they are generally called) in SSRS. Even though there is no out of the box way to do it in SSRS, it was said to be possible by using the map controls and has been shown by Teo Lachev a long time ago. I had even progressed as far as making a T-SQL procedure which calculates the coordinates of the heat maps but it just wasn’t that elegant enough to be used in a production system. However, a recent blog by Richard Mintz on the Squarified Heat Maps has finally made me strike off this requirement from my list as completed.

To implement heat maps in your reports, follow the steps below:-

1) Download the HeatMap.dll file from here and save it in a location in your hard-drive, say C:\HeatMap (You should see the Download option on the File Menu). This dll file is the compiled version of the code given in Richard’s blog.

2) Open SQL Server Management Studio and then execute the following code in the database that you want to create the assembly.

CREATE ASSEMBLY HeatMap from 'c:\HeatMap\HeatMap.dll' WITH PERMISSION_SET = SAFE

3) After that, execute the following code to register the stored procedure

CREATE PROCEDURE dbo.CreateHeatMap (@Width real, @Height real, @SqlStrng nvarchar(4000))
AS
EXTERNAL NAME HeatMap.StoredProcedures.TreeMapGeography

In case you get an error saying that CLR is not enabled run the code below to enable CLR before creating the procedure again

EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
go

4) Now create a new report and a new dataset. For this report, I am going to be using a SQL query from the AdventureWorks database which will give me the Subcategory Name and Order Quantity. Order quantity will be the measure which will determine the size of the rectangles within the heat map. Enter the code below for the dataset

exec dbo.CreateHeatMap 20, 25, 'select sum(FIS.OrderQuantity), EnglishProductSubcategoryName
from dbo.DimProductSubCategory SC
inner join DimProduct P
on SC.ProductSubcategoryKey=P.ProductSubcategoryKey
inner join dbo.FactResellerSales FIS
on FIS.ProductKey =P.ProductKey
group by EnglishProductSubcategoryName
order by sum(FIS.OrderQuantity) desc'

The first 2 parameters within the CreateMap procedure are used for setting the width and the height of the heat map and this can be customized as per your needs. The third parameter is basically the select statement which should return the measure used for determining the rectangle sizes as well as the names used for labelling the rectangles within the heat map. Ensure that the Text option has been selected for the query and it should look like shown below

5) Now make another dataset which will have the analytical data used for visualizing the rectangles with colours. For this demo, I am using a MDX query from the AdventureWorks cube which will return the Subcategory Names and Reseller Gross Profit.

6) Drag and drop a Map report item from the toolbox to the design layout and select SQL Server Spatial query as the data source. On the next screen, select the spatial dataset which we had created in Step 4.

7) On the next screen, ensure that Geo is the spatial field and the Layer type is Polygon. Click next.

8) Choose Color Analytical Map the map visualization and click next. Then choose the dataset that we created in step 5 as the analytical dataset.

9) Specify the match fields as Name and SubCategory as shown in the screenshot below and click on Next

10) In the next screen, choose the field to visualize as Reseller Gross Profit and click on Finish.

11) Now if the report is previewed, you can see a nice heat map which will have the dimension size based on the Order Quantity and the colours visualized based on the Gross Profit.

If are interested in map/spatial reports, you might want to check out some of my related posts here.

Update (11/03/2012)

To make it culture insensitive, the code has been modified and a new version of the compiled code has been posted here.

Making a Tag Cloud with SSRS Rich Text

2012-03-05T12:35:00.000-05:00

One of my first blogs here was based on the Rich Text functionality in SSRS 2008. However I found very little use of that in my projects and did not explore that area much. However a recent question in the forums made me rethink on the way I thought about the rich text functionality.

The question was whether we could create tag clouds in SSRS. A tag cloud (word cloud, or weighted list in visual design) is a visual representation for text data, typically used to depict keyword metadata (tags) on websites, or to visualize free form text. 'Tags' are usually single words, and the importance of each tag is shown with font size or colour. This format is useful for quickly perceiving the most prominent terms and for locating a term alphabetically to determine its relative prominence. I have attached an image of my blog’s tag cloud below:-

Follow the steps below to reproduce the solution:-

1) Create a new report and use the query below for the dataset so that we can get the keywords as well as the count

SELECT        'SSRS' AS Keyword, 36 AS Cnt
UNION ALL
SELECT        'SSAS' AS Keyword, 26 AS Cnt
UNION ALL
SELECT        'MDX' AS Keyword, 20 AS Cnt
UNION ALL
SELECT        'Interview Questions' AS Keyword, 18 AS Cnt
UNION ALL
SELECT        'Personal' AS Keyword, 17 AS Cnt
UNION ALL
SELECT        'Activities' AS Keyword, 16 AS Cnt
UNION ALL
SELECT        'SQL' AS Keyword, 15 AS Cnt

Name the dataset as DataSet1

2) Click on Report on the top menu, and then click on Report Properties. Select the code tab and then paste the code given below

Dim public SMax as Integer = 7
Dim public SMin as Integer = 1
Dim public HtmlTag as String = ""
Dim Public FontSize as Integer = 5
Public Function GetFontSize(ByVal NMin AS Integer, ByVal NMax AS Integer,ByVal Num AS Integer) AS Integer
FontSize = (SMin + ((Num-NMin) * (SMax-SMin)/(NMax-NMin)))
Return Num
End Function
Public Function BuildHtmlTag(ByVal Keywrd as String, ByVal Num as Integer) AS String
HtmlTag = HtmlTag & "<font size=""" & FontSize & """>" & Keywrd & " (" & Num & ") </font>"
Return Keywrd
End Function

Public Function DisplayHtml() as String
return HtmlTag
End Function

It should look like below once that is done

3) Drag and drop a table to the layout. Enter the header of the first column as KeywordCount and in the expression for the data value, enter the code below

=Code.GetFontSize(Min(Fields!Cnt.Value, "DataSet1"), Max(Fields!Cnt.Value, "DataSet1"), Fields!Cnt.Value)

Now, enter the header of the second column as Keyword and in the expression for the data value, enter the code below

=Code.BuildHtmlTag(Fields!Keyword.Value, Fields!Cnt.Value)

Make sure to place the table in the top left corner and ensure it looks like below

4) Change the Border Style property for both the columns to None from Solid. Then resize the columns and rows of the tablix to be as small as you can (but still in the top left corner so that it is the first report item to be evaluated when the report runs). The end result should look like shown below.

Ideally, after this the tablix should be hidden when the report is previewed. You might also want to set the font colour to white and delete the headers in case the tablix is still shown. Now when you view the deployed report in Internet explorer, the tablix might push other report items down or to the left. So care should be taken to align your report items in rectangles.

5) Drag and drop a textbox wherever you want into the report and set the width of the textbox as required. Then enter the following expression

=Code.DisplayHtml()

6) Click on OK. Then select the expression and right click as shown in the image below.

7) Click on the Placeholder Properties and then select the HTML option as shown in the image below.

8) With that last step, we are done and on clicking preview, we should see our neat little tag cloud.

The tag cloud can also be sorted on the basis of keywords or keyword counts also by just sorting the results of the tablix. The code can also be modified to show different colours for each of the keywords and do further more stuff, as all you need to do is to generate the html tags. An example is given in the original forum post. Now I really deserve a mug of beer after this!

Note: I don’t really know much of VB.net coding, so optimization tips for the code part are welcome in the comments section.

Adding Maps to SSRS Map Gallery

2012-03-01T21:06:00.001-05:00

I love working with map reports and I don’t miss a chance to fiddle around with it. In fact, I am such a big fan that I think there would be very few sessions (in English, of course!) involving SSRS and maps that I would have missed, both online and live. Also, this is one of those topics that can make me reply on the SSRS forum even when I am at my lazy best. So the other day, someone was asking for a map of Continental Europe that could be used for making his SSRS report. As usual, I tried to go to the site that I always turn when I need a shapefile – http://diva-gis.org. But this was one of those rare occasions where I couldn’t find what I wanted.But that is when I thought of an alternative – create a map of Europe from my database (which already had a world map), embed the spatial data in a report and then send him that report so that he could add it in his Map Gallery.

The source for the maps can either be a shapefile or spatial data. Though I am picking the spatial data as the source for this demonstration, the process of adding a map to the map gallery is going to be similar for both the type of sources. Follow the steps below on how you can add an existing map in your report to the Map Gallery:-

1) I had already got a table called dbo.World which has the spatial data at a country level for all the countries of the world. I ran a query to filter out only for Europe (minus Russia as it was taking up the entire map space, no offence meant) and got the following result.

2) Create a new report and name it Europe.rdl. Make a new data source and dataset after that. Use the same query that was used above as the dataset query.

3) Drag and drop the Map report item from the toolbar and select the SQL Server spatial query as the data source in the map wizard. Click on next.

4) Choose the existing dataset in the next screen and click on next.

5) In the next screen, remember to check the tick box for Embed Map Data in this Report option.

6) After that, keep on clicking next till you reach the Finish button. Click on Finish and you should get the result as shown below.

7) Now save the report and then copy the report rdl from it's source location to C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\MapGallery. Note that the location would be in Program Files and not Program Files (x86) if you are developing in a 32 bit machine.

8) Now you should be able to see the map of Europe in the map gallery when you create a new map in any other report on the same system.

Note that you will need to move the map to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\ReportBuilder\RptBuilder_3\MapGallery if you need to use within ReportBuilder 3.

Now this rdl that has been created can be shared and that is what I intended to send to the person in the forum, but I found a project in Codeplex which already had a map for Europe - MapGallery of Reporting Services in SQL Server 2008 R2 and sent him a direct link to this. Have a look at the site and also contribute to the project if you have some shapefile which is not there already, so that others can also benefit from it.

Review: OfficeWriter v8

2012-02-21T07:23:00.001-05:00

As a BI consultant specializing in SSRS, I have had lots of frustrations and hard times because of Excel. Every now and then, I have some or other business user coming up to me and asking for some feature which is there in Excel but not in SSRS. If you have been following my blog, you would already know that I am more of a work-around man, trying to find some alternative for features which are not supported out of the box. But when it comes to Excel related features, most of my attempts end in disappointment. So naturally, my ears perked up when I was asked to review a plugin which claimed to build SSRS reports using excel and word.

So I downloaded OfficeWriter v8 and spent close to a week playing around with it. Even though I encountered some minor quirks (v8.0 doesn’t run on the 64 bit version of Office 2010 yet – luckily I had a home pc with a 32 bit version of Office; got some minor issues when editing and deploying an existing SSRS report with shared data sources – got around it by setting the data sources once again from the report manager), overall I have been very pleased and of course, excited at the different prospects that this plugin opens up. You can quickly have a look at one of the reports created with OfficeWriter by clicking on the image below.

Let me note down quickly what I thought of it:-

Advantages
1) XLSX support – Right now, xlsx support is available only for SQL 2012, but with OfficeWriter, you can take advantage of all the features including overcoming the 65,326 row limit (listed here) in your current version of SSRS.
2) Full excel features support including charts and pivot tables – Since excel is fully supported, you can make use of all the charts, macros and other features which are there in Excel but not in SSRS.
3) Ability to have 2 views for a report - If we are editing a report with OfficeWriter from an existing report made through BIDS, then we will have 2 views for it – one which is displayed in report manager and other would be when exported to Excel. It can be helpful in scenarios where you view a fixed report and want to drill down and play around with the data further, in which case you can export to excel and then use a pivot table.
4) Charts are not rendered as images when exported to excel. The export to Excel feature in SSRS will return images rendered as a static image. However, with OfficeWriter, you get the chart exactly as you designed in Excel with all the the interactivity and even tooltips.
5) Can enable subscriptions, security – The tight integration with SSRS ensures that we can use subscriptions and other security features available in SSRS for the OfficeWriter reports.
6) Workbook Protection – You can password protect your workbook or lock your cells for edit, which is an often requested feature by SSRS power users for subscription.

Limitations
1) Can’t build MDX queries in OfficeWriter, will have to make in BIDS and then use them.
2) Will not work on the 64 bit version of Office, as of now.
3) This is not a limitation actually and is by design, but I couldn't help wishing if only the charts and data designed through OfficeWriter in excel would display properly in SSRS also.

Even though OfficeWriter has more features including integration with Word and SharePoint, I have reviewed only the integration with SSRS for Excel part. For folks who are further interested in this, I will show you how to create a sample report using OfficeWriter and post some useful links below it.

Creating / Editing a sample report

For the demo purpose, I am using OfficeWriter v8.0, the 32 bit version of Excel 2010 and SQL Server 2008 R2. Once the installation of OfficeWriter is done, you can find a new toolbar like shown below in the Add-Ins tab of Excel.

Now, there are two ways in which you can proceed from here -
I) Create a new report in Excel with OfficeWriter
II) Edit an existing report created with BIDS (Business Intelligence Development Studio) and deployed to the server

The steps to create a new report is shown below:-

1) Click on Add Query in the OfficeWriter toolbar and enter a name for your query.

2) Add a new Database connection if there is none existing, enter the server information and add the required tables for the SQL query. Once that is done, select the required columns. In this example, I have connected to the AdventureWorks database and used the English Product Name in DimProduct table & Order quantity measure in the FactInternetSales.

3) Even though you can group using the OfficeWriter features, I edited the SQL query directly to group by the English Product Name, as shown below and click on OK. Close the query editor after that.

4) Click on Insert Field and select the English Product Name in cell A1 and Order Quantity in B1.

5) Click on View. This will prompt you to enter the name of your report and save it in your local drive. Click on Save and a new dialog box opens which will prompt you to enter the name of your report server. Click on OK to publish your report to the report server.

6) Once that is done, the report preview would be shown in Excel. You can click on Close Report View to go back to the designer.

7) You can also preview the report from the report manager, which will display a message that the report was made in OfficeWriter and would display correctly only when exported to Excel with OfficeWriter.

Editing an existing report is also simple but you would not be able to edit the queries made through BIDS. Follow the steps below to edit an existing report.

1) Click on Open Report and then click on Retrieve as shown in the image below.

2) Enter the report server url and click on Refresh. The list of existing reports would be displayed below and you can select from them.

3) Insert the fields after selecting the datasets (notice that Add Query and Edit Query is disabled). You can also include charts as shown below.

4) Click on View to save and publish your reports. You can then preview the report in excel.

Useful Links
1) OfficeWriter Home Page
2) OfficeWriter tutorials and Documentation
3) OfficeWriter FAQ
4) OfficeWriter System Requirements
5) OfficeWriter Download Evaluation version

P.S.
This is a paid review. However, I have tried to maintain a totally neutral review, and if there is any point that you disagree with me, I would be pleased to hear about in the comments section.

Set the Slice on your SSAS Cube Partitions now!

2012-01-31T15:02:00.001-05:00

I am pretty sure that most of you would have heard or read about this at least once in your SSAS career, but how many of you actually do set slices for your partitions? Well, I am also guilty of the charge and seldom did set slices till a recent issue.

Situation : Duh, that’s strange!
It’s a sunny day in Charlotte and I was just playing around with SQL Server Profiler for learning some performance tuning stuff. I executed this simple MDX query on the sample Adventure Works DB for some purpose which is beyond the scope of this post -

select {[Measures].[Internet Sales Amount]} on 0,
{[Ship Date].[Calendar].[Month].&[2008]&[4]} on 1
from [Adventure Works]

Now while I was going through the Profiler trace, I noticed something strange – three partitions (2005, 2006 and 2008) were being read while I expected just one partition (for 2008) to be read as my query just had April 2008 on the rows.

Now, for people who are not familiar with Adventure Works cube, there are four partitions for the Internet Sales measure group, one for each year from 2005 to 2008 as shown below -

I decided to take a detour from my actual purpose to investigate this issue.

Debugging : Need to dig up that grave!
I was aware that SSAS partitions generate automatic data slicers for each partition and these information are present in the info.*.xml file of the partition data file (you can find them usually at C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data folder followed by the cube, measure group and partition name if you haven’t changed the default settings. Note that I am using SQL Server 2008 R2). I decided to have a look at these three partition data files.

As seen above, there was clearly a difference between the partitions of 2005 / 2006 and 2008 (which are having the names Internet_Sales_2001.0.prt / Internet_Sales_2002.0.prt and Internet_Sales_2004.0.prt. These names are the partition IDs and can be seen in the ID property of the partitions)

It seemed like the auto-slices were not getting created for the 2005 and 2006 partition while it was being done for the 2008 partition. This was further confirmed on opening and inspecting the info.*.xml file with notepad.

Knee-jerk Solution : Or is it?
I went into research mode on Google and soon came up with a property in the msmdsrv.ini file which limits the creation of auto-slicers based on the number of rows in the partition – the IndexBuildThreshold property.

By default, the value is 4096 rows and both the partitions for 2005 and 2006 had less than 4000 rows. This explained why the auto-slicers were not getting created and I modified it to 1000. Now on reprocessing the measure group, I was able to see that the auto-slicers were generated in the partition data files and only the correct partition was being read in the profiler trace.

Actual Solution : The real deal!
I decided to gloat over my findings to my friend and colleague, Javier Guillen (blog | twitter) who was sitting in the next room. When I described the problem statement to him, the very first question he asked me was whether I had set the partition slices, and I was like – sheesh, I missed on that cardinal rule, as usual. I was under the impression that as long as your partition is for a single data member (as in this case – a single year), it should work. But later I found out that partitions for which autoslices are not generated will be scanned anyway, unless the partition slices are explicitly defined. I went back and set the slices for each partition correctly to their respective years and also reverted the IndexBuildThreshold property to the original value of 4096.

Once this was done, I processed my measure group and found out that only the correct partitions were being read from the trace files.

Reading Materials
I took some time to read and learn more about partition slices, thought you would be interested in it too if you made it this far.

1) Get most out of partition slices - Mosha Pasumansky
2) SSAS Partition Slicing - Eric Jacobsen
3) Partition Slice Impact on Query Performance - Jesse Orosz

Using TraceEvent for MDX Debugging

2012-01-22T22:52:00.001-05:00

Do you know how it feels like to hear an old song and have a whole lot of memories come flooding back to you? Well, something sort of similar happened to me today. I was doing my weekly reading and going through a brilliant post by Microsoft MVP - Greg Galloway (blog). That was when I noticed that he had released a new functionality to the Analysis Services Stored Procedures (ASSP) Project on Codeplex. I decided to test this new functionality and was thinking of a scenario where I could use it, when I remembered about an old post in the Analysis Services forum that I intended to blog about but never did.

Now the new addition to the ASSP toolkit is the TraceEvent class. Quoting from the ASSP project page -

“The function in the TraceEvent class is FireTraceEventAndReturnValue. It can be used to detect when Analysis Services is evaluating an expression since the function merely fires a trace event that appears in Profiler as a User Defined event, then returns the integer value you pass in. This function can be used in a scenario such as determining when the cell security expression is evaluated.

Here is an example MDX query displaying the usage of this function:

with member [Measures].[test] as ASSP.FireTraceEventAndReturnValue(99)
select [Measures].[test] on 0
from [Adventure Works]

The signature of the function is:
FireTraceEventAndReturnValue(int)”

Having a base in programming, it was natural for me to expect a sort of “watch window” when I started learning MDX. I wanted to view the values and the change in them while the query was being debugged, but sadly there was none. The TraceEvent class will help alleviate that pain to some extent and I will be showing it in the next few minutes. For the purpose of demonstration, I would be taking the two queries from the AS forum post I mentioned before -

I) PROBLEM

Query 1

withmember [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].members on 1from [Adventure Works]where ([Product].[Product Categories].[Agg])

Query 2

select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].members on 1from [Adventure Works]where (TopCount([Product].[Product Categories].[Product].members, 3, [Measures].[Internet Sales Amount]))

Results

For a MDX beginner, you would expect the results to be same for both queries, as you are just moving the formula of the calculated member in Query 1 to the WHERE clause in Query 2. The reason for the difference is because calculations overwrite current coordinates while axes don't overwrite the current context. So when the first query is being executed, the current coordinates are overwritten and executed in the context of the query axis which means it will check the top 3 products for each of the month and display the result. However, in the second case, you don't have any calculations defined and hence the current coordinates would be the one in the WHERE clause, which is the Top count of products across all months. (You would get a better idea by reading the original post in the forum)

Enough of theory and let us see whether we can debug and understand the working of the mdx queries using the TraceEvent class. For the purpose of seeing the results, we will modify the queries as shown below and see the results in the Profiler.

II) DEBUGGING THE PROBLEM

Query 1 for July 2005

withmember [Product].[Product Categories].[Agg] as Aggregate(TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].&[2005]&[7] on 1from [Adventure Works]where ([Product].[Product Categories].[Agg])

Profiler Results for Query 1

Even without knowing the theory part, you can find out that the measure gets evaluated at 4, which is after the axes get evaluated (1, 2 and 3 in the image above) and hence, the top 3 products would be calculated for the current context of July 2005. I have not pasted the entire trace here but if you run it, you can see that the measure values for each product for the month of July 2005 is returned in the Integer Data column. I have marked the top 3 values in red and you can see that this corresponds to $261,231 which is what we saw in the results for the first query.

Query 2 for July 2005

select {[Measures].[Internet Sales Amount]} on 0,[Date].[Calendar].[Month].&[2005]&[7] on 1from [Adventure Works]where (TopCount([Product].[Product Categories].[Product].members, 3, ASSP.FireTraceEventAndReturnValue([Measures].[Internet Sales Amount])))

Profiler Results for Query 2

Here, you can see that the measure gets evaluated at 1, which is before the axes get evaluated (3 and 4 in the image above). I have also marked the top three values for the products, and on close examination, it can be found to be the top 3 values for products across all months.

So once the top products are found, the axes are evaluated and then the results are serialized in 5, which gives us the value of $221,852 which is what we saw in the results for the second query.

III) SUMMARY

Even without knowing the theory, we were able to debug the MDX queries and find out why there was a difference with the help of the TraceEvent function. This technique can be elaborated and used across variable scenarios for query debugging. In short, the TraceEvent functionality is a great function to have when you don’t know why you are getting some particular results in your queries. And yeah, don’t forget to thank Greg for this!

Reducing SSRS RDL size by uploading Shapefiles to ReportServer

2012-01-15T11:45:00.000-05:00

I don’t know about you guys, but I am really petrified of public speaking. So I always try to read about it and it really gives me some comfort to know that I am not alone. In fact, if your fear of public speaking is between mild anxiety and complete terror, you are said to be well within the normal range. I would not be exaggerating if I said that some studies show that there are people who rate their fear of public speaking as more severe than fear of death. Last year was when I finally decided to embrace my fear and I managed to speak at a local user group event in London and also at SQLBits (trust me, it wasn’t that hard as I thought it would be). And since now is the time everyone makes resolutions, I was trying to take some hint from 50 New Year’s Resolutions for Public Speakers and watching my presentation video from the last SQLBits. Seeing it, I realized I had missed mentioning an important point regarding map reports and hence decided to blog about it before I get too lazy.

People who are familiar with map report development would already be aware of the size issues when dealing with shapefiles (If you are a complete newbie on map reports, I would strongly recommend you to go through my presentation video). Most of the shapefiles are in MBs and since this data is embedded in the report RDLs, the size of the RDL also goes into MBs. The problem as well as the solution are demonstrated below:-

1) Make a simple report from a shapefile by selecting the Basic Map option in the Map Wizard. I haven’t linked the shapefile to a dataset for simplicity purpose.

2) Now you can verify that the shapefile data has been embedded in the report by right clicking on the report in the solution explorer, and selecting the view code option.

You can see that the territory names are there in the rdl code.

3) Now deploy the report to the report server and then, download the rdl to check the size.

You can see that the rdl size is 3.11 MB. The shapefile that I had used for this report was 2.4 MB.

4) Now to solve this issue, we will have to upload the shapefile (.shp and .dbf files) to the report server. For this, navigate to the folder in Report Manager and click on upload. Then browse to the shp and dbf files and click ok.

5) Once this is done, go back to the report in BIDS and click on the map twice to bring the map layers panel on the right. Right click on the polygon layer and select Layer data option.

6) Now change the option from Data Embedded in Report to Link to ESRI Shapefile. Now give the location of the files in your report server (In this case, \Blog Reports\COL_adm1.shp). Note that the location has to start with a slash followed by the folder names within the report manager and then the file name of the shp file.

7) Once this is done, you will not be able to view the results in BIDS. You can view the report code to confirm that the map data is not embedded in the rdl now. Just deploy the report after that and preview it in report manager to ensure that it is working.

8) Now, download the rdl from the report server and you can see that the size has been considerably reduced, from over 3 MB to 12.2 KB.

It would actually be a good practice to store your shapefiles in the report server at the end of your development. This way, the shapefiles can be reused by multiple reports if needed. Also, you would not need to open BIDS in case an updated version of the shapefile becomes available later. This technique can also be used for report images though I see little benefit from it as usually the images are only a few KBs. As for performance, I quickly checked a couple of times and the rendering time was a little less using this technique. Maybe I will do a detailed performance test and post a blog on it later. Till then, adieus amigos!

A New Year and a New Job

2012-01-08T12:37:00.001-05:00

For people who are connected with me, this would not come as much of a shock but yes, with this post comes a big change for me. By the time this post gets published, I would have already moved on from MindTree Ltd and started work at Mariner, my new work place. This would also mean that I have changed my base from London, UK to Charlotte, USA.

I have lots of fond memories about MindTree and the decision to part was indeed very difficult. I will definitely miss my colleagues there and wish them all the very best for the future. At the same time, I am also very excited to join Mariner and look forward to working and learning from some of the most talented people I have known. Hopefully, I will also be able to contribute something.

The new year has got me a new job and I am looking forward to a lot of things. I would like to end this post with a New Year wish that I had read sometime back, for all of my readers-

I hope you will have a wonderful year, that you'll dream dangerously and outrageously, that you'll make something that didn't exist before you made it, that you will be loved and that you will be liked, and that you will have people to love and to like in return. And, most importantly (because I think there should be more kindness and more wisdom in the world right now), that you will, when you need to be, be wise, and that you will always be kind. I hope that in this year to come, you make mistakes.Because if you are making mistakes, then you are making new things, trying new things, learning, living, pushing yourself, changing yourself, changing your world. You're doing things you've never done before, and more importantly, you're Doing Something. Whatever it is you're scared of doing, Do it. And I hope, somewhere in the next year, you surprise yourself!

Using SSAS formatting in SSRS

2011-12-30T15:40:00.000-05:00

Christmas time is one of my most favourite times of the year – the time when you can just lay back, relax, enjoy some quality time with your family and catch up with your old friends. While this Christmas time has been a little hectic for me considering that I changed my base from London, UK to Charlotte, USA (I promise to say more on that in a later post), I still found time to catch up with family and friends. So I was talking with an old colleague of mine, and as is with most techies, we ended up discussing work after some time (now I know what the idiom means – All roads leads to Rome). Well, I should warn you that my friend is not a great fan of SSRS, and he was cribbing about SSRS and the difficulties he faces when dealing with it. Being a SSRS enthusiast, I couldn’t resist giving solutions or workarounds to most of the problems he said. One of his major concerns was about the formatting in SSRS. He was totally annoyed that the decimal/currency formatting he did in SSAS did not carry over to SSRS and that he had to modify potentially many reports to replicate the formatting change. That is when I jumped in and said that you could achieve the same in SSRS if you want to.

To demonstrate the solution, I am using the AdventureWorks database. Follow the steps below to reproduce the solution:-

1) Create a new SSRS report and use the query below to create a dataset
SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit Margin], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works]

Note that [Measures].[Internet Sales Amount] is a measure expression, [Measures].[Internet Gross Profit Margin] is a calculated measure and [Measures].[Internet Order Quantity] is a base measure.

2) Create a simple matrix in SSRS using the fields above and preview it.

You will notice that the measures are not formatted while the same measures in SSAS are formattted, as clear from the cube browser preview.

3) Now, to get the same SSAS formatting in SSRS, you will have to modify the MDX query to include the cell property – format_string as shown below.
SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Gross Profit Margin], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Product].[Category].[Category].ALLMEMBERS ) } ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMAT_STRING

4) Once the query is modified, click on each of the 3 textboxes that need to be formatted, and enter the following code in the format property
=Fields!<name>("FORMAT_STRING")
E.g., for the Internet Order Quantity, the code and image is given below
=Fields!Internet_Order_Quantity("FORMAT_STRING")

5) Now preview the report.

We can see that the format for Internet Order quantity is correct, but the other two has been overwritten by Currency and Percent. This is because the format strings in SSRS and SSAS is not the same for all formats. To correct this, I have used custom code in the next step.

6) Click on Report menu and then select Report Properties. Copy and paste the following code into the custom code tab

Dim public RSFormat as String
Public Function FindFormat(ByVal ASFormat AS String) AS String
If (ASFormat="Currency") then
     RSFormat="c"
ElseIf (ASFormat="Percent") then
     RSFormat="p"
ElseIf (ASFormat="Standard") then
     RSFormat="0"
Else
     RSFormat=ASFormat
End If
return RSFormat
End Function

P.S. : I have taken care of the frequently used format strings, but if there is any format that I have missed, that can be easily added to the code with a ElseIf statement.

7) Now click on each of the 3 textboxes that need to be formatted, and then replace the code used in step 4 with the following code
=Code.FindFormat(Fields!<name>("FORMAT_STRING"))
E.g., for the Internet Order Quantity, the code and image is given below
=Code.FindFormat(Fields!Internet_Order_Quantity("FORMAT_STRING"))

8) Preview the report and now you should be getting the same format that was in SSAS also.

The advantage of using this technique is that you would not need to touch your reports if you change the formatting in SSAS. This would ensure that the formatting used in your reports is standardized and consistent with what has been defined in your cube. Also, if you have multiple reports, you could compile the custom code and deploy the assembly to a server. This way, you would not need to duplicate the custom code in each of the reports.

References

1) Retrieving Cell Properties
2) How to Get Extended Properties with SSAS OLE DB Provider

Where did my RDL.DATA files go?

2011-11-30T09:56:00.001-05:00

Today morning, I was reading an old post on Clearing SSRS Query cache by Alex Whittles (blog | twitter). That is when I decided to try out something and opened up my most recent project to find some rdl.data files. Surprisingly, I did not find a single rdl.data file although I had more than 10 reports which had been run multiple times. Even a full search of my laptop failed to yield results for those elusive files. That is when I decided to investigate further into the matter.

The rdl.data files are usually present for each report and are used only in the development environment. When we preview the SQL Server Reporting Services (SSRS) reports in Business Intelligence Development Studio (BIDS – the development environment), the query results are cached and stored as the rdl.data files in the report folder. This cache will be used the next time the reports are previewed and is the reason why the report seems to display faster. This will help in developing reports faster as we don’t have to wait a long time for the report to be displayed. However, this could also mean that the data would be stale when we are previewing the report in BIDS.

Now, coming back to the issue, I quickly noted my version of SQL Server (which is 2008 R2) and checked for updates. I realized that I had not applied the Service Pack 1 for SQL Server 2008 R2 and quickly searched the internet for a bug, which I found here. So all I had to do was to apply the Service Pack 1 which I downloaded from this link. Once the Service Pack was applied, things got back to normal and I was able to generate the rdl.data files when the reports were previewed.

P.S. : Kindly note the version to which this fix applies – Microsoft SQL Server 2008 R2 Reporting Services (without any patches/updates/service packs)

Adding a Target line to a Horizontal Bar Chart in SSRS

2011-11-24T07:38:00.001-05:00

This is usually the time of the year when I get to my laziest best. The cold weather and the approaching holiday season brings out the sloth in me and I have a huge backlog of articles I need to read as well as tend to. But somehow I have pulled up my socks and managed to come up with this article on how to add a target line to a horizontal bar chart in SSRS.

For the benefit of people who are not so familiar with the horizontal bar charts in SSRS, there is no out-of-the-box functionality for achieving a target line (unlike the column charts, where we can implement a line chart as detailed here). So when this question was raised in the MSDN forums, I put on my thinking cap and suggested a solution based on having the image of a line as a marker, something like shown below

The steps are mentioned in the forum discussion here. But a disadvantage of this approach was that it wouldn’t accommodate an increase or decrease in the number of members in the Y axis. For e.g., if the line image was designed for 5 members, then an increase in the number of members would mean that the image would spill over the X axis. Similarly, a decrease in the number of members would mean that the lines fail to reach the X axis. So I came up with an alternate solution using striplines and the steps are given below:-

1) Make a simple bar chart with a category and measure. In this example, I have used Year as the category field and Cnt as the measure value.

2) Click on the X axis to select it, then right click and unselect the option ‘Show Major Gridlines’. This will remove the gridlines on the chart.

3) Click on the X axis to select it, and then select the collections button in the StripLines property

4) Click on the Add button in the ChartStripLine Collection Editor. The properties should appear as shown below now.

5) Now change the BorderStyle property as Solid and enter the field or the number at which you want the target line to come in the IntervalOffset property as shown below

6) Select OK and click on the preview button. You should be able to see the target line in the bar chart as shown below.

7) You can increase the width of the target line by changing the BackgroundColor property to the BorderColor (in this case Black) and by increasing the StripWidth property in the ChartStripLine Collection Editor shown in step 5.

A disadvantage of this method is that the target line appears behind the bars. Apart from this, it works pretty well and you have a clever workaround the next time someone insists on having a target line on their bar charts.

Tracking Data over Time Visually in Power View aka Project Crescent

2011-10-18T17:22:00.001-04:00

All right, I know most of you Microsoft BI fans would be gushing over the new announcements made in PASS Summit 2011 last week. Now onwards, Project Crescent will be officially known as Power View, SQL Server Denali as SQL Server 2012, Juneau as SQL Server Data Tools and finally, we have a mobile BI strategy! (you can read the official press release from here and some more details on Power View from here). I decided to celebrate the occasion by blogging about one of the new features I particularly like in Power View in CTP3 version – tracking data over time visually.

For this demonstration, I have used the computer in SQL Server Virtual Labs. This is a great place to kick-start your SQL Server 2012 journey. Apart from the great tutorials, I also access it when I have to test out some new feature. Follow the steps below to create a Power View report with the ‘track data over time’ functionality:-

1) Go to the SharePoint site and click on Data Sources. Then click on the small dropdown icon on the right of the data source name and select the Create ‘Crescent’ Report option as shown in the image below.

2) If this is the first time you are opening the Crescent report, there is a chance that you might get the following error -
An error occurred while loading the model for the item or data source. Verify that the connection information is correct and that you have permissions to access the data source.

This is a bug within the current CTP and this should go off if you refresh the address bar of the browser.

3) Select Category under Product and Quantity under Sales. A table will be displayed in the layout with the values of Category and Quantity.

4) Click on the Scatter chart option as shown in the image below.

5) Resize the chart to occupy the full space. A scatter chart requires 3 measures – for the X axis, Y axis and the size of the bubble. Since we have already selected the Quantity measure, we just need to select 2 more measures for the Y axis and size. Select Revenue and # Products.

6) Once that is done, select Year under Date and this will automatically be taken as the Play Axis of the scatter chart. You should be able to get the graph as shown below.

7) Give a title and then click on the save button on top left corner. Enter the destination path as well as the file name and then click ok to publish the report in SharePoint.

8) Go to the destination path specified in the step above and click on the report name to preview it. Click on the play button to see visually how the Category changes over the year.

You can also click on a particular category and track the changes for that particular category over the year.

Isn’t that sweet and simple? Coming from a manufacturing domain, I can already think of lots of scenarios where this can be used (e.g.: tracking price changes, profitability, revenue, stock, sales, etc over time).

Of late, I have come to realize that some people face difficulties in following blogs by just reading the steps or by seeing a few images. So, I have taken a conscious decision to video-blog the steps whenever I can. You can find the video for this blog below

Tracking Data over Time in Power View

Let me know your feedback about this initiative and whether it is helping in conveying my message better. Cheers!

Generating QR codes in SSRS

2011-10-10T07:58:00.000-04:00

Over the short span of my career, I have seen many people get burnt out and change their careers from technology to some other field. It is easy to get disillusioned with a job that requires you to sit on a chair the entire day and "code" the same stuff. At the same time, I have also met people who are so passionate and enthusiastic about what they do and are still coding away to glory after 15-20 years. What makes those people different, and why don't they get bored of the "same, old mundane coding stuff"? The answer is simple if you do get to observe them - their creative spirit. To quote Edward De Bono, "Creativity involves breaking out of established patterns in order to look at things in a different way." For these people, every day teaches them something new and they like to challenge their limits. They make out new things from the same stuff by looking at it from a different angle. I always make it a point to imbibe this quality in the people I mentor, and try to put across some puzzles where they have to reuse their previous learning in a new way. One of my recent questions was on how to display QR codes in SSRS.

On asking this question, most of the answers involved in getting some custom code written (but no-one knew how to write the code) or to get some plug-in from third party vendors (which involved licence costs). But the team had to put on their thinking caps when I said that the only thing they would require is access to the internet.

For demonstrating the purpose, I have made a report which will generate business cards for all the employees in my fictional company with QR codes. Follow the steps below to generate the report:-

1) Create a dataset with the following query

SELECT        'Jason Thomas' AS name, 'www.road-blogs.blogspot.com' AS URL, '00447574713732' AS mob
UNION ALL
SELECT        'Jay Thomas' AS name, 'www.beyondrelational.com' AS URL, '67891' AS mob
UNION ALL
SELECT        'Jean Elizabeth' AS name, 'http://twitter.com/de_unparagoned' AS URL, '12345' AS mob

Instead of this, you can also use information from your database tables or SSAS cubes. For the sake of simplicity, I am directly hardcoding my dataset.

2) Drag and drop a list into the layout. Then insert the name, URL and mob details into the list. Also add a company logo to make it look jazzy

3) Drag and drop an image to the list and select the image properties.

4) Go to the General tab and select the Image Source as External. Then click on the expression and enter the following expression

="http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + Constants.vbcrlf + Fields!URL.Value + Constants.vbcrlf + Fields!mob.Value

This is shown in the image below.

(You can append any string after the URL though I am using the Name, URL and mobile here)

5) Click on preview and you should be able to get the desired results.

The secret of getting this done is by using the Kaywa site which will accept parameters and output the QR code for that as an image. Now there are plenty of sites online which will generate QR codes as well as barcodes in this format, and you can use any one of them for doing the same. And since you have already learned before that it is possible to display External images in SSRS, generating QR codes or barcodes is just an application of that knowledge.

Disclaimer
This report was developed just for having some fun with SSRS and for educational purposes. For people who are going to be using this for commercial purposes, I would ask them to go through the conditions of the site which generates QR code (for eg, Kaywa states it’s use should be only for non-commercial reasons).

uCertify 70-448 PrepKit Review

2011-10-08T07:13:00.000-04:00

Ok, let me put it up very bluntly - I am not a big fan of all those test and preparation sites for certifications that keeps floating around. The main reason for my dislike is that the questions are usually the braindumps of the exams which have been leaked out and many people can cheat their way to a certification just by studying the dumps (I still don't get it why the questions are never changed or shuffled on a regular basis for the Microsoft certifications). This eventually affects the reputation of the certification as well as the credibility of the people who have been certified. And for people who have done it the hard way, it could mean that their efforts have gone in vain. This was the first thought that came to my mind when uCertify asked me to review their test preparation software for 70-448 recently and I was certain that the product would face the ire of my blog if this was going to be another dumps based test site. Luckily it didn’t.

The very first thing that came to my notice after installing the software was the elegant and user friendly UI.

The UI has got separate sections for tests, study materials and progress tracking. The tests can be taken in two modes - learn mode (where you can review your answers immediately and read the notes also for it) and test mode (where you would be able to see your answers and score only at the end of the test). uCertify has neatly classified the tests so that there is a diagnostic test in the beginning to assess your level, practice tests to improve your skill levels and the final test (which I think is a bit harder than the real one).

The study material section is also pretty handy as it contains all the key concepts in one place.

The test readiness section tracks your history and gives you an indication of how ready you are to take the certification.

Another great feature is the Study Helper which explains the key concepts in each of the six chapters required for MCTS 70-448.

Now, coming to the pros and cons of the 70-448 PrepKit

Pros
1) Extremely strong and user friendly UI which I believe is shared for all the uCertify PrepKits.
2) uCertify’s strong policy against Braindumps
3) Comprehensive set of questions with study notes (300+) which are neatly classified
4) Test readiness feature gives an indication of when you should appear for your exams
5) Money back policy if you don’t pass within your first attempt
6) Good simulation of the test environment.

Cons
1) Even though the overall quality of the questions are good, there are some areas of improvement. For eg, questions with pictures of the actual development environment are less.
2) The How To section (visuals explaining how to do something) and Articles section are pretty much empty for 70-448.

Overall
My experience suggests that the 70-448 exam is a piece of cake for people with at least 2 years of experience in SSIS, SSAS and SSRS and they would pass the exam by buying and studying the official book - MCTS Self-Paced Training Kit (Exam 70-448). But for people who would want to get a feel of the tests before the actual exam or for those who don’t have an all-round work experience in the three sections (SSIS, SSAS, SSRS), I would recommend uCertify 70-448 PrepKit. It’s a good investment considering that you will get your money back if you don’t pass within your first attempt. And also, you can be proud of the fact that you tried to pass the exam honestly and not by depending on the illegal dumps.

P.S.
I am not being paid by uCertify to write this review nor am I getting any benefits out of the sales made through this review. However, my readers can avail a discount of 10% (in addition to any existing sale) on any test preparation software from uCertify by putting the discount code 'UCPREP' in the shopping cart.

SQLBits 9 - Hangover

2011-10-06T05:36:00.001-04:00

At the time of writing this post, I can see yellow leaves falling to the pavements and feel the cold breeze kissing my cheeks. No matter how hard I try to resist it, reality is dawning on me that the summer has gone by and that the winter is finally settling in (yeah, I know some of you might argue that we never had a summer. After all, it's been the coldest summer in 18 years). Can't believe that it was only a week before many of us were sweating in the big halls of the Adelphi Hotel in Liverpool. Oh sheesh, I did it again! I tried to avoid it but just like the winter, here comes the familiar feeling of hangover.

This was my third SQLBits and like always, I had been counting down the days from the time SQLBits was announced. Well, this time I had an additional reason - I was presenting a session at SQLBits. More than that, I had also got a free entry on Friday because I was a speaker (now that calls for a big HURRAY!). Even a loss of pay on Friday couldn't deter me from attending the conference on Friday (I had already exhausted my annual leaves for my wedding in July) and I reached there right in time for the keynote.

Even though the agenda suggested that I would be seeing cloned versions of Steve Tramack in both the Aintree and Lancaster halls using the latest Microsoft & HP technologies (check the agenda here), all I got to see was a bigger room with the wall removed and one Steve Tramack. Or wait, I thought I saw two people on the stage and it definitely wasn't two Steves. Being a victim of the hardships involved in installing and configuring SharePoint myself, I totally appreciated the idea and thought behind the HP Fast Track Solutions for Microsoft SQL Server. The best part is that you can configure a system within a couple of hours what would have definitely taken some weeks. Overall, it was a brilliant start to SQLBits.

After that, I went to Matt Masson's (twitter | blog) session on 'What's New in SQL Server Denali for SSIS'. The Facebook social life manager demo was really cool and unique, and also had a peek at many of the new features of SSIS.

Next on my list was ‘Basic BISM’ by Chris Webb (blog). This was an introductory session on Business Intelligence Semantic Model and was extremely helpful to people like me who were more or less on the start of the learning curve. Chris also touched upon the advantages and disadvantages of the tabular mode in this session.

Then I went for the ‘Myths and Monster of Flash’ session by Fusion-IO where Steve Wharton (twitter | blog) presented some of the characteristics and mechanics of the NAND flash.

Later I spent some time walking around the stalls and talking with the exhibitors. It was so interesting and informative at the same time that I actually spent a lot more time than I intended to and missed the next session as a result.

The next session was one of my most awaited ones – ‘iPad and Powerpivot-Mobile Business Intelligence in action’ by Jennifer Stirrup (twitter | blog). I had been planning to catch hold of Jen’s sessions for a long time and finally made it this time. Not to say, I had a great time learning about mobile BI and some of the best practices in visualizations.

One of the other things I was really looking forward to was meeting Hrvoje (twitter | blog) and Tomislav Piasevoli (twitter | blog) who were coming down to SQLBIts all the way from Croatia. So after the sessions, I met up with the Piasevoli brothers and we headed to the Cavern Pub for washing down our throats. Had an extremely great time with them as is evident from all the photographs.

We were keeping a tab on the time as we did not want to be late for the much anticipated event of the night – James Bond Casino Night. It really was a great experience walking in to the hall which had now transformed into a casino and we started gambling with the fake money provided to us. Most of the people were appropriately dressed in their James Bond attires also, which made the experience even better. I am sure all of us would have left the place declaring this as the best post event party in SQLBits till date.

The next day, I had to wake up at an unearthly hour as I had to rush for my 8.10 am session on ‘Fast Track to Spatial Reporting’. The demo material for my session are available for download here. Got a few pics of my session too, thanks to Tomislav.

The next three sessions I attended was definitely the highlight for many in this version of SQLBits. The first of the series was ‘Building Great Models for Crescent’ by Kasper Jonge (twitter | blog). His session was jam packed with demos and being a reporting person myself, it was really nice to watch all the new features in Crescent.

The next of the series was ‘Vertipaq vs. OLAP: Change your Data Modelling Approach’ by Marco Russo (twitter | blog). Most of the perceptions that I had built about data-modelling was challenged and I was forced to see them in a new light when I am modelling in Vertipaq.

Alberto Ferrari (twitter | blog) was next on the stage with ‘Many-to-Many Relationships in DAX’ and I was seriously amazed at how he presented such a complex topic in such an easily understandable format. This was one of the moments where I wished if only I had such people as my professors when I was studying in my University.

I checked in to the ‘Lightning Talks’ soon after, something which I wanted to do in last time’s SQLBits itself but couldn’t because it was clashing with another session. It was great to see the likes of Kasper Jonge, Martin Newman, Andrew Fryer, Richard Douglas, Ed Vassie, Neil Hambly and Tobiasz Koprowski share the same stage.

The last session of the day was kept aside for Duncan Sutcliffe’s (twitter | blog) session on ‘Kerberos-All You Need to Know in One Hour’. As usual, Duncan just blew everyone apart with his unique presentation style and all in all, it was one of the most comprehensive sessions on Kerberos that I have seen.

Soon we had the customary post event party and lots of prizes were announced from Kindles to Xboxes. A big thanks to all the sponsors without whom SQLBits would not have been possible.

Also a moment thanking all the guys behind this - Simon Sabin, Christian Bolton, James Rowland-Jones, Martin Bell, Darren Green, Chris Webb, Allan Mitchell, Tim Kent, Chris Testa-O’Neill and all the helpers. A big, BIG, BIG thanks to all of you guys.

Once the party was over, all that was left for us to do was to reminisce the last two three days and wait eagerly for the next version of SQLBits. Well, myself, Hrvoje, Tomislav and Kasper also found something else to do while reminiscing , see below for a hint.

As for the winter, I would like to borrow a few lines from Percy Shelley – If Winter comes, can Spring be far behind?

Fast Track to Spatial Reporting - SQLBits session material for download

2011-10-02T14:31:00.000-04:00

So one more objective struck off from my annual list. Even though I had nightmares where my laptop crashed just before my session, nothing untoward happened and I managed to complete my first session at SQLBits without any problems.

The session was scheduled on Oct 1 at 8.10 am in the morning (at a time when I would normally be snoring in my bed). There was a brief moment of confusion when I discovered around 7.30 am that my laptop did not have the VGA port for plugging in the projector (the new DELL systems just have the HDMI and mini USB ports). Luckily, James Boother (twitter | blog) and Neil Hambly (twitter | blog) helped me get a converter and salvaged the situation. I was able to complete all the demos within the stipulated 1 hour time though I thought I had to race at some parts. Anyways, I will be blogging about my overall SQLBits 9 experience in detail soon. Till then, feel free to download the materials that I used in my session below:-

Session slides – contains the PowerPoint presentation (2 MB)
Report RDLs – contains all the report files used in the demo (12 MB)
Database backup – contains the spatial database used for the reports (41 MB)
Demo notes – short notes describing the demo (5 KB)

Book Review : MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

2011-09-22T15:58:00.001-04:00

What do you do in your evenings when you are sent off from your base location on a month long consulting assignment? Well, I had an option to soak up the old Beatles numbers in the Cavern Pub (the birthplace of Beatles) every evening, now that I am in Liverpool. But a particular book got me hooked this time and every weekday was just a restless wait for the evening to come. My readers, you might be misled by my words in thinking that I am reading the latest Dan Brown thriller but trust me, I am not exaggerating when I say that I couldn’t keep this book down till I completed it.

Neither the book nor the author needs introduction. The book has already been lauded by most of the industry experts like Chris Webb, Marco Russo, Vincent Rainardi, etc. Tomislav Piasevoli (MVP | blog | twitter) has been one of my favourite bloggers and I have been following him for quite some years now. So it wasn’t quite surprising that I have been following up on the book updates ever since I saw the RAW e-book version (something like a beta release, just has a few chapters) on my PackT online subscription during the month of March. And luckily for me, the launch of the book coincided with my consulting assignment. I have spent quality time with the book most of these evenings (in fact, so much that I think it made my wife jealous ) and I am thoroughly impressed with the format of the book.

Walking back the memory lane, I always yearned for a SSAS book that had examples of common design and implementation issues and was overjoyed to find the same in Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. Once I went through this book completely, I tried to find a similar one for MDX too but in vain (found one for SSRS in the process - Microsoft SQL Server Reporting Services Recipes for Designing Expert Reports). Well, I am glad to say that the search has finally ended with the advent of the MDX cookbook by Tomislav. The book follows a simple and easy to understand structure throughout the 9 chapters :

- Getting ready :- getting the pre-requisite queries ready for the task
- How to do it :- how to do the task
- How it works :- how the task works
- There’s more :- alternate ways and more information on the task
- See also :- links to related information and tasks

Once I got familiar with the format, I was just reading the ‘Getting ready’ part and trying to solve the task on my own. After that, I would compare my answers with the next 2 sections and it enriched my reading experience. Though the initial chapters were relatively easy, I was scratching my head as the chapters neared the end and I have learned a lot of new things through this book. This is not really intended to be your first MDX book but is a MUST HAVE once you have tasted a bit of MDX. All you MDX developers, you are definitely missing out on a great book if you haven’t read this. Now if you are not sure where to buy it from, here is the link. The Kindle version is also available in Amazon and you can download a sample from here to get a better feel of how the cookbook and its recipes are structured. As for me, I have just added it to the favourites on my blog’s Amazon widget.

Export to Excel upgrades in SSRS Denali CTP3

2011-08-11T10:21:00.001-04:00

I don’t know whether it is the same with you guys but for me, it is a bit hard to get on track and start work after a long vacation. Usually, the initial few days are spent idle before the computer and if I don’t make an effort to control it, my moods become crankier than the schoolboy who doesn’t want to go to school. However this time has been a bit easier for two reasons: (a) My wife who is a constant source of encouragement (benefits of being newly wed! ) and (b) release of SQL Server Denali CTP3. There has been lots of interesting and new features to test out and this has become the proverbial carrot for me to get out of my sluggishness. This article is about one of those new features in SSRS Denali CTP3 – the feature upgrades in exporting a SSRS report to Excel.

Before I start listing down the new features, I would like to show you guys a screenshot of the new developer environment.

Yes, the old BIDS has given way to a new mean Visual Studio 2010 environment and I totally dig the blue background. Isn’t it cool?

Now before I get distracted with the other features, it is high time I say the upgrade is – SSRS renders a report to the native format of Microsoft Excel 2007-2010. The format is Office Open XML. The content type of files generated by this renderer is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet and the file extension of files is .xlsx. The benefits of this over the previous .xls format (Excel 2003) are listed below:-

1) Max columns per worksheet increased from 256 to 16,384

If you are exporting a report with more than 256 columns in a worksheet to the normal .xls format in Denali or in SSRS versions prior to Denali, you will get the following error message

You will no longer see this error message if you are exporting to the .xlsx format.

2) Max rows per worksheet increased from 65,536 to 1,048,576

Similarly, if you are trying to export a report with more than 65,536 rows in a worksheet to the normal .xls format, you would be greeted with the following error message.

However, this will no longer be an issue when you export to the new .xlsx format.

3) No of colours allowed in a worksheet increased from 56 to approx. 16 million

I am sure this is a very welcome move for many. Prior to Denali, you couldn’t get more than 56 colours in a worksheet but with the new .xlsx format, this is very much possible. For eg, consider the following colour matrix that I made in BIDS.

Now when I export it to .xls (Excel 2003) format, I get the following output in excel

We can see that the entire last row is blacked out due to the colour limit. However, if we export it to the .xlsx format, we get the proper output.

BUG ALERT

Even though the excel export worked as expected in the case of pre-defined background colours for each cell, it did not show up the correct results when I used custom code to show the background colours for the members in a group.

For eg, the following output came up properly in .xls format

But when the same report was exported to .xlsx format, it started showing the last colour (which is Green) for every cell.

This has been raised as a bug in Connect. Vote for it if you want it to be rectified fast.

https://connect.microsoft.com/SQLServer/feedback/details/683691/ssrs-denali-export-to-excel-colors-not-being-displayed-correctly

4) ZIP compression

The .xlsx format has ZIP compression, hence the size of the files generated would be lesser. For eg, I generated a report with 250 rows and 250 columns in both .xlsx and .xls formats. The .xlsx format was just 25KB while the .xls format was 35KB in size. This difference may become more evident and important in the case of large reports.

That’s all from me this time folks. Hopefully, I will be back with some more of the new features.

And here I announce the good news…

2011-08-03T09:48:00.001-04:00

To all my loving and caring readers who had been wondering where the hell did I get lost the last month, I have two good news for you.

1) I got hitched!

Yes, I had been on a vacation last month and meanwhile managed to upgrade the status of Ms Neethu Sebastian from long-time girlfriend to wifey . The wedding was a lot of fun but hectic to the core (roughly translates to – enjoyed it but never doing it again).

2) My session got selected for SQLBits 9!

Yes, I will be speaking at SQLBits 9 – Query across the Mersey. Just got the confirmation from the SQLBits team that my session – Fast Track to Spatial Reporting has been selected. The session would be held on Oct 1, Saturday from 8.10 to 9.10 am. Please click on the agenda below for more details.

P.S. : If you are attending SQLBits 9 in Liverpool, make sure that you drop in and say a hi to me

SSAS Best Practices–Session slides for download

2011-07-04T13:11:00.001-04:00

Looks like I am on a session taking spree, this was my third one in 3 weeks. This time, I decided to collate some of the best practices in SQL Server Analysis Services and present it for the BI minds in my company, MindTree Ltd. This is one subject that just doesn’t finish in 1 hour, and even though I tried to cut down a lot of points, I ended up spilling by almost 15 minutes. A big thanks to Emil Xavier for organizing the session and for arranging the logistics, everything was prim and proper. Anyways, without boring you guys further, here goes the session slides. Feel free to pass it around!