Tracking Data over Time Visually in Power View aka Project Crescent

Tracking Data over Time Visually in Power View aka Project Crescent

October 18, 2011

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.

1 Create Crescent Report

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.

2 First time error when opening Project Crescent report

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

3 Crescent report layout

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 Selecting Category and Quantity

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

5 Selecting scatter chart

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 Selecting measures for scatter chart

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 Select Year as the Play Axis

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 Publish reports

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.

Track Categories over Years

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

Track Selected Category

Isn’t that sweet and simple? Smile 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!

Posted by SQLJason, 2 comments
Export to Excel upgrades in SSRS Denali CTP3

Export to Excel upgrades in SSRS Denali CTP3

August 11, 2011

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! Winking smile) 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. Denali xlsx feature Before I start listing down the new features, I would like to show you guys a screenshot of the new developer environment. Denali dev 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 Max columns error in excel SSRS 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. Max rows error in excel SSRS 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. Denali SSRS colour matrix Now when I export it to .xls (Excel 2003) format, I get the following output in excel Denali SSRS colour matrix xls format 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. Denali SSRS colour matrix xlsx format 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 Denali SSRS bug xls format But when the same report was exported to .xlsx format, it started showing the last colour (which is Green) for every cell. Denali SSRS bug xlsx format This has been raised as a bug in Connect. Vote for it if you want it to be rectified fast. 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.

Posted by SQLJason, 1 comment