Year: 2011

Using SSAS formatting in SSRS

Using SSAS formatting in SSRS

December 30, 2011

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.

1 SSAS formatting in SSRS

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 Different type of measures

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

3 Previewing the unformatted data

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

4 SSAS formatting

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 SSRS Format property

5) Now preview the report.

6 SSRS initial format preview

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.

7 SSRS final format preview

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

Posted by SQLJason, 5 comments
Where did my RDL.DATA files go?

Where did my RDL.DATA files go?

November 30, 2011

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. thnking 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)

Posted by SQLJason, 2 comments
Adding a Target line to a Horizontal Bar Chart in SSRS

Adding a Target line to a Horizontal Bar Chart in SSRS

November 24, 2011

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.

Target line in horizontal Bar Chart

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

1

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

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

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

5

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

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

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.

Posted by SQLJason, 11 comments
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
Generating QR codes in SSRS

Generating QR codes in SSRS

October 10, 2011

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. Generating QR code 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 Winking smile BIDS Layout - List 3) Drag and drop an image to the list and select the image properties. 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. How to generate QR code (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. End Result - QR codes in SSRS 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).

Posted by SQLJason, 5 comments
uCertify 70-448 PrepKit Review

uCertify 70-448 PrepKit Review

October 8, 2011

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. 0 uCertify HomePage The very first thing that came to my notice after installing the software was the elegant and user friendly UI. 1 uCertify 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. 2 StudyMaterial The test readiness section tracks your history and gives you an indication of how ready you are to take the certification. 3 TestReadiness Another great feature is the Study Helper which explains the key concepts in each of the six chapters required for MCTS 70-448. 4 Study Helper 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.

Posted by SQLJason, 1 comment
SQLBits 9 – Hangover

SQLBits 9 – Hangover

October 6, 2011

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. SQLBitsLogo
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 JulySad smile) 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. SQLBits Keynote 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. Matt Mason on SSIS DenaliMatt Mason on SSIS Denali 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. Chris Webb on BISMChris Webb on BISM 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. Steve Wharton - Fusion io 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. SQLBits exhibitor space 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. IMG_0103IMG_0104 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. Tomislav, me and Hrvoje at Cavern Pub 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 with games modeled after the ones seen on Slotsformoney.com 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. SQLBits James Bond Casino Night 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. Jason on Spatial ReportingJason on Spatial Reporting 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. Kasper Jonge on CrescentKasper Jonge on 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. DSC_0138IMG_0111 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. DSC_0144IMG_0115 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. IMG_0117IMG_0118 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. IMG_0119 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. Sponsors 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 Winking smile, see below for a hint. Jason, Kasper, Hrvoje and Tomislav As for the winter, I would like to borrow a few lines from Percy Shelley – If Winter comes, can Spring be far behind?

Posted by SQLJason, 3 comments
Fast Track to Spatial Reporting – SQLBits session material for download

Fast Track to Spatial Reporting – SQLBits session material for download

October 2, 2011

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. SQLBits 9 Query Across the Merseyside Speaker 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)

 

Posted by SQLJason, 5 comments
Book Review : MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook

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

September 22, 2011

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. MDX with Microsoft SQL Server 2008 R2 Analysis Services CookbookNeither 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 Smile) 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.

Posted by SQLJason, 1 comment
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. 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.

Posted by SQLJason, 1 comment