SSRS

Making a Tag Cloud with SSRS Rich Text

Making a Tag Cloud with SSRS Rich Text

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.

SSRS Tag Cloud

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:-
My Blog's tag cloud
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
Report Properties
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
Table which calls the report code

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.
Resized tablix

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.
Placeholder properties
7) Click on the Placeholder Properties and then select the HTML option as shown in the image below.
Interpret HTML tags as style
8) With that last step, we are done and on clicking preview, we should see our neat little tag cloud.
Tag Cloud in SSRS
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.
Posted by SQLJason, 13 comments
Adding Maps to SSRS Map Gallery

Adding Maps to SSRS Map Gallery

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.

Add Maps to SSRS 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.

1 Filtered query for Europe

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.

2 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.

3 Source for spatial data

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

4 Choose dataset

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

5 Embed map data in report

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.

6 Report design

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.0Common7IDEPrivateAssembliesMapGallery. Note that the location would be in Program Files and not Program Files (x86) if you are developing in a 32 bit machine.

7 Add report to map gallery

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.

8 View map in map gallery

Note that you will need to move the map to C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServerReportBuilderRptBuilder_3MapGallery 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.

Posted by SQLJason, 14 comments
Review: OfficeWriter v8

Review: OfficeWriter v8

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. reporting-services-diagram 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. OW Sample Report 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. 1 OfficeWriter Toolbar 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 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 Add DB and query  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 Modifying SQL Query  4) Click on Insert Field and select the English Product Name in cell A1 and Order Quantity in B1. 5 Insert fields to excel sheet 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 Save the report 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 Preview report 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. 8 Preview report in IE 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. 9 Open existing report 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. 10 Select existing report 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. 11 Insert fields and chart 4) Click on View to save and publish your reports. You can then preview the report in excel. 12 Report Preview
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.

Posted by SQLJason, 1 comment
Reducing SSRS RDL size by uploading Shapefiles to ReportServer

Reducing SSRS RDL size by uploading Shapefiles to ReportServer

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.

REDUCING RDL SIZE BY UPLOADING SHAPEFILES TO REPORTSERVER

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.

1 map report from Colombia shapefile

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.

2 embedded spatial data in report rdl

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.

3 Shapefile rdl 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.

4 Upload shp and dbf to reportserver

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.

5 Selecting layer data in map layers

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 ReportsCOL_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.

6 Linking the map to the uploaded files in reportserver

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.

7 Modified Shapefile reduced rdl size

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!

Posted by SQLJason, 2 comments
Using SSAS formatting in SSRS

Using SSAS formatting in SSRS

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?

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

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, 12 comments
Generating QR codes in SSRS

Generating QR codes in SSRS

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
Export to Excel upgrades in SSRS Denali CTP3

Export to Excel upgrades in SSRS Denali CTP3

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
Let’s Go Spatial – Session slides for download

Let’s Go Spatial – Session slides for download

So that’s one objective struck off from my list for this year. Yes, I made a presentation on the mapping features in SSRS 2008 R2 for the London BI User Group on 21 June, 2010. The UG meeting was held in the Hitachi office near London Bridge and was attended by around 30 people. A big thanks to Hitachi Consulting for sponsoring the beer / pizza and of course, for allowing us to use their office for the meeting. Another round of applause to Chris Webb (blog)  for organizing this UG meeting and personally, my gratitude for giving me the opportunity to speak. Though my presentation was more focussed on demos, I am attaching the slides that I used for my session – ‘Let’s go Spatial’ along with this post. I will try to upload the rdls of the reports also but that might take some time. So keep checking or ping me if you have got some urgent need for the sample reports used in my presentation. Click to download - Let's go SPATIAL The demos included

  • Creating map reports from ESRI shapefiles
    • How to deal with deployment errors with large shapefiles
  • Creating map reports from Spatial Data
    • How to import shapefiles into your database
  • Visualizing your map reports using measures
  • How to drill up and down your map reports
  • How to do custom aggregation in your map reports
  • Creating bubble maps in your map reports

This was followed by an amazing session on Change Data Capture by the charismatic Duncan Sutcliffe (blog | twitter). Overall a nice day and looking forward for more BI user group meetings.

Posted by SQLJason, 1 comment