Connecting to SSAS from Report Builder Query Designer – A Connection Cannot Be Made

Introduction

Recently I was attempting to create a dataset in the SQL Server Reporting Services Report Portal. I created my data source, then launched Report Builder to create my Dataset.

Report Builder connected to my SQL Server Analysis Services Tabular database OK, the Test Connection button worked, but when I tried to go into the query designer I kept getting the error:

A connection cannot be made. Ensure that the server is running.

I knew my server was running, I could connect to it and run queries from SSMS (SQL Server Management Studio). I found some solutions that suggested I change my SQL Server Browser service to log on using the Local System account. Tried it, didn’t work.

I found another solution recommending I add a firewall rule to allow inbound traffic on port 2383, but no love there either.

The Solution

It turned out it was all related to the way I’d formatted my data source connection string in the SSRS Report Portal. I had entered it as:

Data Source = acdev;initial catalog = WWI-SSAS-Tabular

When I used the Test Connection button in the Report Portal, it worked fine. It even let me connect when I launched Report Builder. But when I tried to launch the query designer in Report Builder, it gave me the aforementioned error:

A connection cannot be made. Ensure that the server is running.

I came upon my solution by launching Report Builder, and telling it I wanted a data source embedded in my report. On my first attempt I simply copied what you saw above from the Report Builder, and was faced with the same crushing disappointing result.

On the second try I used the Report Builder feature to actually build my connection string. Report Builder produced:

Data Source=acdev;Initial Catalog=WWI-SSAS-Tabular

And by golly, it worked! I was able to use the query builder to create a DAX query.

To be sure I was still sane, I went back to the Report Builder and replaced my connection string with the one above. Still in the Report Portal, I added a new Dataset which launched Report Builder.

I picked the Data Source I’d just updated in the Report Portal, and this time I was able to get into query builder, create a new query, and save it back to the server as a dataset.

Conclusion

I can only guess it was the extra spaces around the equal signs that were messing things up. I’d added the spaces thinking it made it a bit more readable. Readable, but as it turns out non-functional.

Some of you maybe going “you big dummy” at this point, and perhaps justifiably. I still think it’s odd though that the test connection buttons in multiple tools all worked, yet the query designer crashed.

Regardless, I’m happy I was finally able to find the solution. I’d spent almost five hours on this, so hopefully this will save you a little time and get you back to creating queries.

SQL Server 2016 Reporting Services Cookbook On Sale Until Jan 13, 2021

My last book, SQL Server 2016 Reporting Services Cookbook, is on sale until January 13th, 2021.

You can get the E-Book version for just $5 (US) when ordering direct from my publisher.

Now you may be thinking, “2016? That’s oooooold!” But in fact little has changed in Reporting Services since the 2016 release. All of the topics and techniques are still just as valid in SQL Server 2019.

To order your copy of the book, just jump on over to SQL Server 2016 Reporting Services Cookbook | Packt (packtpub.com)

Hurry though, offer is only good until January 13, 2021

Mobile Report Publisher–Dashboards Everywhere

My latest article on RedGate’s SimpleTalk site has just come out: Mobile Report Publisher – Dashboards Everywhere.

In it, I give a quick overview of using SQL Server 2016’s Mobile Report Publisher to create a simple but useful dashboard. Mobile Report dashboards can be assembled easily, and quickly, from SSRS (SQL Server Reporting Services) Shared Datasets.

Reports generated with Mobile Report Publisher are great, as they can be used on, as the name suggests, mobile devices such as phones and tablets, but on websites as well. They fill the need between a traditional report and those analytic reports created by self service tools such PowerBI.

Using the instructions in my SimpleTalk article, you should be able to create your first, simple report. If that wets your appetite for more, I can offer two additional learning paths.

First, there’s my book SQL Server 2016 Report Services Cookbook. In it I have an entire chapter on the Mobile Report Publisher. Being an entire chapter I had more space to go deeper, and provide instructions on pulling data from multiple sources. I also go into the use of other components in the dashboard. You can find the book on both Amazon and my publisher, PactPub’s website.

The second resource is my Pluralsight course What’s New in SQL Server 2016 Reporting Services. The report gives a great overview of all the new features of SSRS 2016 (and still applicable to 2017). The bulk of the course focuses on the Mobile Report Publisher, but I also cover other new features such as the new Report Portal, and the ability to host KPIs right on the portal, among other features.

Don’t have a Pluralsight subscription? No worries, just email me, free@arcanetc.com and I can send you a code good for 30 days during which you can watch my courses, or any of the great courses at Pluralsight!

Opening Port 80 in Windows Firewall to Support Calling SSRS From Another Computer

Recently I was working on another article for RedGate’s SimpleTalk site. As part of it, I had SSRS installed on a Windows 10 computer, and needed to connect to it from another computer. I was having a lot of issues connecting, until I remembered SSRS connects using Port 80, and by default Windows 10 (and previous versions) block Port 80 for incoming traffic.

The solution was to, obviously, open Port 80 on the Windows 10 computer. Doing so was not difficult, but did require quite a few steps, and of course administrator rights on the computer.

First, open the Windows 10 Settings. Then, click on Network & Internet.

On the Status window, click on Windows Firewall.

From here, click on Advanced settings.

If prompted confirm you do wish to make changes. When the Windows Defender Firewall dialog appears, click on Inbound Rules.

Now click on New Rule

In the New Inbound Rule Wizard window, change the type of rule to be Port. Then click next.

On the next window, leave the rule applying to the default of TCP. For the port, assuming you are using the default setup, enter 80 for the port number. If you setup SSRS on a different port then obviously use that port number instead.

On the action page we tell Windows what we want to do if it finds incoming traffic on this port. For this development environment we will take the default of Allow the connection. If you had setup https service on your report server, then you could take the second option of allow if secure.

Next, we need to specify what network type the rule should apply to. For the scenario, I am on a small network, such as you might have at home, and that network was setup as private. Thus I am leaving Private checked on, and unchecking Domain and Public.

Unchecking public is especially important if you plan to take your laptop out to a coffee shop, you don’t want someone trying to hack into your machine via port 80. When done just click next.

On the last screen we’ll give the firewall rule a name, and a description. When done, click finish.

As you can see, the new rule now appears in our Inbound Rules area.

Once you have completed working with your SSRS server, I’d suggest you return here, right click on the rule, and either disable it, or if you know it will no longer be needed, delete it.

And with that you should now be able to connect to the computer running SSRS from another computer on your network.

SQL Server 2016 Reporting Services Cookbook–On Sale Until 2018!

Happy Holidays! As a gift to everyone, PACKT Publishing has put the e-book version of my SQL Server 2016 Reporting Services Cookbook on sale for just $5.

Yes, that’s right, for the same price as a pumpkin spice chai tea latte with extra whip cream, and a quick trip to http://bit.ly/ssrscook, you could be reading this magnificent tome of wisdom.

Now, I can year some of you going “but Robert, SQL Server 2017 is out now, is this still relevant?” Yes, absolutely! Very few changes were made to the 2017 version of SSRS, so everything in this book is just as valid.

And it’s not just my book that is on sale, all the e-books at PACKT are on sale for the same low low price. So after you get done adding the SQL Server 2016 Reporting Services Cookbook to your shopping cart, be sure to check out the other awesome titles on PACKT Publishing.

This book, by the way, makes an excellent companion to two of my Pluralsight courses.

The SQL Server Reporting Services Playbook, https://www.pluralsight.com/courses/sql-server-reporting-playbook, is designed for people who are totally new to SSRS and need to get up to speed FAST.

Already know SSRS, and just want to learn what’s new with SSRS 2016? Then my appropriately named What’s New in SQL Server 2016 Reporting Services course, https://www.pluralsight.com/courses/sql-server-2016-reporting-services, is just the thing for you. It goes over just the new features in SSRS 2016 (and still applicable to 2017) and does it fast. With this short course you can be up and running with 2016/2017.

What? You don’t have a Pluralsight subscription? Well I’m shocked, but OK I can help you out. Just send me an email to

free@pluralsight.com

and I can fix you up with a code good for 30 days during which you can watch my courses, or any of the fine courses at Pluralsight. (If you want to see all my courses, just click the About Me link at the top, or go to http://arcanecode.me).

If you have some free time during the holiday season, and want to avoid the in-laws, then curling up with a great book (like the SQL Server 2016 Reporting Services Cookbook) beside the yuletide fire can be a great way to spend your time!

SQL Server Reporting Services Playbook now live on Pluralsight!

My newest course, the SQL Server Reporting Services Playbook, is now live on Pluralsight! If you are looking to get up to speed fast writing reports in SQL Server Reporting Services, this is the course for you. In roughly two hours you will have enough information to start cranking out reports for your organization.

Playbooks though are a bit different than traditional Pluralsight courses. Playbooks are specifically designed so you can watch just one segment to gather that piece of information you need right now, and not have to sit through the entire course. For example, if you were comfortable with generating line item reports, but need a refresher on how to use charts in a report, you could watch just the 15 minute module on charts.

The playbook format is also great for times when, after watching a course, you need to come back and refresh yourself on one piece. The organization makes it easy to find the specific piece of information you need.

You’ll find the course at: https://www.pluralsight.com/courses/sql-server-reporting-playbook

After watching this course, you may wish to go watch my previous course, What’s New in SQL Server 2016 Reporting Services. ( https://www.pluralsight.com/courses/sql-server-2016-reporting-services ). This course is another brief one which assumes you are comfortable with Reporting Services and want to get up to speed quickly on the new features in 2016.

I for one really appreciate this style of course, as it doesn’t waste time teaching me what I already know about SSRS, and instead just focuses on the things that have changed since the previous version. And let me assure you, a lot has changed in SSRS 2016!

What’s that you say? You don’t have a Pluralsight subscription but really want to view this course? Well no problem my friend, just email me <free at arcanetc.com> and I’ll be more than happy to send you a code that will give you 30 days free at Pluralsight to watch mine or anyone’s courses.

By the way, this is my 13th Pluralsight course, you’ll find the whole list at https://www.pluralsight.com/authors/robert-cain . My courses cover a variety of topics, Reporting Services of course, but also PowerShell, SQL Server Integration Services, and a nifty course introducing you to the concepts of Data Warehousing and Business Intelligence.

SQL Sever 2016 Reporting Services Cookbook has arrived!

I’m proud to announce my latest book, the SQL Server 2016 Reporting Services Cookbook, has been released! This was a real labor of love, it consumed most of my summer and well into the fall.

This book was published via Packt Press, and my coauthor was Dinesh Priyankara (blog | twitter).

In this book we cover recipes for almost all aspects of SQL Server Reporting Services. What’s inside? Just take a look:

Chapter 1 – Getting it Ready – Configuring Reporting Services.

Chapter 2 – Authoring Reports with SQL Server Data Tools

Chapter 3 – Advanced Report Authoring with SQL Server Data Tools

Chapter 4 – Authoring Reports with Report Builder

Chapter 5 – Improving User Experience – New Designing and Visualization Enhancements

Chapter 6 – Authoring Reports with the Mobile Report Publisher

Chapter 7 – Consuming Reports – Report Access Enhancements

Chapter 8 – Reporting Solutions for BI – Integration

Chapter 9 – SharePoint Integration

Chapter 10 – Administering and Managing Reporting Services

Chapter 11 – Securing Reports in Reporting Services

Chapter 12 – Custom Programming and Integration to .NET applications

That’s a lot of great material, over 500 pages of Reporting Services fun.

You can get the book through the publisher site:

https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2016-reporting-services-cookbook

or use this shortcut: http://bit.ly/ssrscook

You can also get it on Amazon.

https://www.amazon.com/Server-2016-Reporting-Services-Cookbook-ebook/dp/B01HY3TC68/ref=tmm_kin_swatch_0?_encoding=UTF8&qid=1480527666&sr=8-6

Or use the shortcut: http://bit.ly/ssrscookbook

Note as of this blog post Amazon has the Kindle version ready, the print version still shows as a preorder, but that will be out shortly. If you want the print version consider going to the publisher site as you can get both the print and e-book version for one low price.

I want to thank my coauthor, Dinesh, who did a great job on his half of the book, as well as in designing the overall contents. Also a shout out to our editor, Amrita, who kept us in line and on track.

Enjoy!

SSRS 2012 Report Manager can’t load Microsoft.ReportingServices.SharePoint.ObjectModel

So I did it again, I broke my SQL Server. Well, sort of. I have a Hyper-V VM of Windows Server 2012R2 I use for development. On it I had SQL Server 2012 Developer Edition with all the latest service packs. I recently needed to do some work with 2014 as well, so installed SQL Server 2014 Developer Edition side by side. Everything seemed happy, until I opened up the SQL Server 2012 Report Manager webpage. It looked OK at first, but when I started clicking on things I started getting this error:

System.Configuration.ConfigurationErrorsException: Could not load file or assembly ‘Microsoft.ReportingServices.SharePoint.ObjectModel’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference

Icky. So a web search turned up one hit, a connect item filed by Brian Judge:

https://connect.microsoft.com/SQLServer/feedback/details/1088671/sql-server-2012-reporting-services-errors-after-installing-sql-server-2014

At the bottom, Brian gives the clue on how to fix the issue when he says:

If I change the redirect to stay on 11.0.0.0 for the following policies then the problem appears to be resolved:

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint.ObjectModel

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint.Server

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint12.Server

C:\Windows\assembly\GAC_MSIL\Policy.11.0.Microsoft.ReportingServices.SharePoint14.Server

Alas, there are no specific instructions on just how to change the redirect. For those not familiar with the way these things work, I wanted to amplify his fix.

First, open a command window in administrator mode. I used the one that came with Visual Studio (the Developer Command Prompt for VS2012).

Next, change directory by using the “cd” command to the first item in the list above. (Click on the pic for a bigger image, should you have poor eyesight).

Using the DIR command, we can see one directory with a version number followed by what appears to be a hash value of some type. Issue another CD into that folder.

Using the DIR command again you will find two files in that folder:

Use notepad to edit the one with the .config extenstion.

When it appears, you will see something like:

Simply change the number in the newVersion from 12, to 11.

Repeat the steps for all four of the folders in the list above.

Next, and this is important kids, you need to stop and restart your SQL Server 2012 Reporting Services service, or simply reboot the computer. After that, your SSRS 2012 Report Manager should start to behave normally again. I’ve also tested the 2014 Report Manager, and it seems to work fine after the changes were applied. (In theory it shouldn’t have been affected, but you can never be too careful).

If you found this post useful, do us a favor. Go to the Microsoft Connect article linked at the top and give it an up vote, so Microsoft will begin to take notice. Also thanks again to Brian Judge (whom I do not know but hope to meet) for filing the original bug and giving the clue to fixing it.

SQL Saturday #111–Atlanta

Today I’m presenting not one but two sessions at the Atlanta SQL Saturday. I wanted to provide copies of my slide decks here.

Configuring SQL Server 2012 Reporting Services

The Decoder Ring to Data Warehousing / Business Intelligence

Hope you enjoyed the sessions, and thanks for coming.

SSRS Training Resources

I’ve been asked to provide links to some useful resources for learning about SQL Server Reporting Services. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.

Books

Microsoft SQL Server 2008 Reporting Services Step by Step – A great beginner book, loaded with good examples.

Pro SQL Server 2008 Reporting Services – This book goes much more in-depth with SSRS, delves into many advanced topics.

Microsoft SQL Server Reporting Services Recipes – 2008 or 2012 version of book. This is a great book, especially if you are doing Business Intelligence reporting. Note Amazon says the 2008 version is no longer available in the US, but I’m betting you can find it in your local bookstore or from other retailers. The 2012 version is available for pre-order.

Applied Microsoft SQL Server 2008 Reporting Services – Great book, like the book above covers many aspects of SSRS including BI reporting. Note Amazon only sells the paper version, you can also get it in PDF format direct from the publishers website.

Professional SQL Server 2012 Administration – I mention this book because I wrote the chapter on SQL Server Reporting Services. I don’t go deep into creating reports, although I briefly cover Report Builder. I do go into configuring SSRS and how to do scale out deployments, the total chapter is around 50 pages.

Blogs

Paul Turley – Paul is an active blogger and fellow Microsoft MVP. He is also co-author of the Reporting Services Recipes book I listed above.

Tep Lachev – An active blogger, Teo is not only a good resource for SSRS but for other BI tools such as PowerPivot. He is also author of the Applied Microsoft SQL Server 2008 Reporting Services book, listed above.

Videos

Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on SSRS.

Pluralsight – Pluralsight has an extensive catalog of courses, including some great SSRS content. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

For a quick link direct to this post, you can use http://bit.ly/arcanessrs

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.

Task Factory–SharePoint Source

Microsoft SharePoint has taken the enterprise by storm. A staggering number of corporations today are using SharePoint as their collaborative and information backbone. A tremendous amount of information is being stored within SharePoint lists.

As such, it’s becoming more and more important to be able to quickly and easily extract the data in those lists and bring it into other platforms for analysis, reporting and storage. SQL Server Integration Services seems like the ideal tool for moving and transforming this type of data around, but accessing data in SharePoint lists is not a trivial task.

Unless of course you have Task Factory. Task Factory is a suite of SSIS components from Pragmatic Works. In this video, we’ll look at the SharePoint Source component.

BI Documenter

When I came to work for Pragmatic Works, I was naturally given the opportunity to use their (well our now) tools. Of all of them I think BI Documenter is my favorite. Boy is this thing complete.

Of course, like some of the other SQL Server documentation packages it will do a great job of reverse engineering an existing relational database. BI Documenter will output either HTML or a compiled help file (CHM) file. It places your database structure into a drill down tree, with all the expected bits and pieces. Tables, columns, stored procedures, functions, all with the code needed to create it.

It doesn’t stop there though. Got Analysis Services? Not a problem. It will generate the same drill down structure that you are used to seeing for a standard SQL Server database. Cubes, Measures, Dimensions, KPIs, Calculations, complete with all the meta data and MDX you could ever want.

Of all the features though, the absolute coolest is it’s support for Integration Services. You can point it at either your SSIS server, or to a file store, or just to the directory with your solution. Here’s a sample that uses the package from my Intro to SSIS presentation:

Not only does it provide detailed info (it appears below the pic) but it will also reproduce the graphical flow! And if that wasn’t cool enough, you can drill down into the other executable parts, such as the data flow.

Here’s a small sample of the details:

It doesn’t stop there either, providing complete support for SSRS as well. Just point it at your Reporting Services server and away it’ll go!

Now, I realize this sounds a lot like a commercial, and since I now work for Pragmatic Works probably more so. Bu I just think this is an awesomely cool product, I can think of a lot of uses for it too. Providing turn over documentation, running it weekly to create version snapshots so you can track changes to your SQL Server, providing reference material for developers, and probably a zillion other things I haven’t thought of. (If you have thought of some, by all means leave a comment, would love to hear how YOU are using this.)

I’m sure I’m not doing the product justification, so if you want to see some video demos head on over to http://pragmaticworks.com/ for more info. And again, I apologize if this sounds like a commercial but I am blown away by how cool this product is so I just had to share.

Disclaimer: I do work for Pragmatic Works, and received my copy of this really cool software as a result of my employment.

Data Warehousing / Business Intelligence at Pluralsight

It’s been another busy month, and I’m pleased to announce my two newest modules were published today on Pluralsight. If you’re not familiar with Pluralsight, they are a training company that has a vast library of training videos.

The description of my new course can be found here:

http://www.pluralsight-training.net/microsoft/olt/Course/Toc.aspx?n=intro-dwbi-course

There are two modules, the first is an introductory session for DW/BI. It’s primarily slides, by the end you’ll have a grasp on the arcane terms around Business Intelligence such as facts, dimensions, surrogate keys, OLAP, and more.

The second module is an overview of the Microsoft tools for doing DW/BI. It starts with the Adventure Works Lite database. From there a data warehouse is built, on top of which an analysis services cube is created. Finally a report will be generated off the cube that meets a specific business need.

Along the way attendees will get to see Visual Studio 2010 Database Projects, along with the SQL Server toolkit: SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services. Attendees will also get a look at SQL Server Management Studio.

Enjoy!

SSRS Quick Tip – An item with the same key has already been added

I was in the process of creating a new report in SQL Server Reporting Services today. I was loading my dataset from a stored procedure, and when I hit the “Refresh Fields” button I recieved the following error:

“Could not create a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct.”

When I clicked the details button I got this further information:

“An item with the same key has already been added.” Here’s a screen shot of my error.

Well this had me scratching my head, as I had made sure to run the stored procedure, and it executed with no errors. After doing some considerable research I finally found a question in the Technet forums that was tangentially related to the error. This gave me the clue to figure out what I had done.

In my stored procedure, I had inadvertantly included the same column name from two different tables. My query looked something like:

SELECT a.Field1, a.Field2, a.Field3, b.Field1, b.field99
FROM TableA a JOIN TableB b on a.Field1 = b.Field1

SQL handled it just fine, since I had prefixed each with an alias (table) name. But SSRS uses only the column name as the key, not table + column, so it was choking.

The fix was easy, either rename the second column, i.e. b.Field1 AS Field01 or just omit the field all together, which is what I did.

As it took me a while to figure this out, tought I’d pass it along to anyone else who might be looking.