Hello, I hope everyone is having a great day. For any beginners in SSRS I am creating this blog to show you can create a data source and a data set to be used in your reports that you’re building.
Now, first you are going to need the SSRS extension installed in Visual Studio. An easy way to do this is if you are already in any type of project in Visual Studio and you’ll notice in the ribbon there is an “Extension” button. You will want to click on that and then click “Manage Extensions…”.
From here the Manage Extensions window will appear prompting you to find the specific extension(s) you are looking for. To make life simpler you can type “Microsoft Reporting Services Projects” into the search bar at the top right of your screen. I already have it downloaded so there is a check mark next to it, but in it’s place there will be a download button when you hover over the extension.
Once you’ve finished the installation process you’ll re-open Visual Studio and click “Create a new project”.
Now, you’ll need to use the search bar at the top of the screen and type in “Report Server”. I don’t understand why I doesn’t come up at the top of the results but you will see it here.
Once you created your project, the next thing you are going to want to do is type “Report Server Configuration Manager” in your Windows search bar at the bottom of your screen.
After you open this application the Report Server Configuration Manager Dialogue Box will open which will start off looking like this.
You are going to need to put in your Server Name and type in a name for your Report Server Instance.
You’ll see now that there are multiple different panes for configuring the Report Server. For now, you are going to want to click on the “Web Portal URL” and click on the URL that is provided.
Now that you are in the Report Server web page, you are going to want to create a new folder to work in.
Go ahead and enter a name and now you have your very own Folder within the Report Server. We are almost done.
Go on inside the folder and click the + New dropdown button again but this time we are going to select Data Source. Once you click on the Data Source button this new page will appear prompting you to set up the properties of the Data Source.
Go ahead and enter a name for the Data Source, and then we are going to skip down to the Connection String text box. What you are going to want to do is type “Data source = “ and then copy over this part of your SQL Server Name.
It should look something like this when you are done, obviously not “PATRIOT-L03” for your data source name. After you type that in click the Test connection button just to make sure it works.
Now you’ll have a Data Source in your folder now.
Next, we are going to walk through creating a Data Set. So… if you guessed that you have to click the +New button again you would be right. Let’s go ahead and click the dropdown menu and select Data Set. When you do this you will have a dialogue box show up saying “We’re opening Report Builder…”. If you don’t have it installed you’ll want to click the “Get Report Builder” button which will take you to a Microsoft where you can just click install. After you go through the pretty straight-forward installation wizard, you will close that window prompting you to get Report Builder and try to make the new data set again. Once you have gone through all this your screen will now have two dialogue boxes. One is the first “We’re opening Report Builder…”, the other is your device asking if you want to “Open Microsoft Report Builder”. Go ahead and open that.
Did I say that creating a new data set is the most complicated part? No? Well it is. Now you will arrive at this lovely dialogue window.
To continue, you are going to want to click the “Brows other data sources…” button at the bottom of the screen.
From there, you will now want to select the folder you just created.
Then you will select the data source that you are going to retrieve your dataset from. And then click “Open” at the bottom right of the dialogue box.
Now you’ll select the new data source and click Create at the bottom right of the box.
Alright… Phew. Now that we are in the Report Builder we are on the last couple steps. I am using a query that I created for this so I am going to select the “Edit as Text” button at the top of the screen.
Now to paste my script into the text box and run it by clicking the Red Exclamation Mark at the top of the screen to see if it worked.
Now all that’s left is to save.
So now, if you go back to the Report Server in your browser, you’ll see that there is now a Dataset and a Data Source.
That took a little longer than I am sure some of you anticipated but it is a good thing to learn how to configure a Report Server, a Data Source and a Dataset for any beginners. Without further ado, I would just like to thank you for taking the time to read my blog today and I hope you have a great rest of your day (or night). Thanks guys!
Bailey McDonald
Data Engineer, Patriot Consulting
Email: bkmcdonald@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: BaileyMcDonald | Company: Patriot Consulting
