Some facts:
- SQL Server Reporting Services were great in the past. SSRS 2008 are better.
- MSDN is great. MSDN has lots of details. Sometimes it’s too much if you are under pressure.
- Google usually gets me the information I want faster. Especially when I need to get something done quickly.
All in all this post is for those of you that need to learn how to use SQL Server Reporting Services fast or need to solve the above mentioned problem fast, without knowing too much about the details. I read a lot of and like these introductory posts and so now I’m writing another one, here goes.
I assume you have SQL Server Reporting Services already setup (by your grand one-and-only unmissable sys-admins or by yourself, but in the latter case you probably are not the target audience for this) and you have access to the all required tools. Talking about tools, there are three you should know of:
– RSConfigTool – (Start menu > SQL Server 2008 > Configuration > Reporting Services Configuration): What do I need it for: Troubleshooting, Finding Urls, Configuration Report Server. Having problems trying to connect. See Additional Tip #1.
– Report Manager – (http://<yourserver>/reports): What do I need it for: Managing your reports (you won’t actually see anything on a clean install yet)
– Business Intelligence Studio – (Start menu > SQL Server 2008 > SQL Server Business Intelligence Development Studio): What do I need it for: Creating and editing your reports
and of course you will need SQL Server Management Studio for managing the data the report is based upon.
(side note: This tutorial is based on the 2008 version, can’t say how many differences there are between this and the older versions.)
1. Create your views
There are ways to aggregate your data in the report itself, but it’s easier and cleaner to use SQL views to provide the data for the report and use Reporting Services only for the report. So go to SSMS and create your view.
2. Create a new BIDS (Business Intelligence Development Studio) solution based on the “Report Server Project” Template. Add a new report by clicking on the Report node in the Solution Explorer. Next we need to connect to a data source. Give it a name, click edit and connect to your database.
3. Click next and the Query Designer appears. Here you enter the T-SQL statement for your data. Ideally it’s just a “SELECT * FROM yourView” (or select each column distinctly). You can test it using ‘Run Query’. You can also choose to use data from a stored procedure if you like, just select the appropriate option.
4. Design your report. You can add elements to the report surface, by right clicking on the center surface and select them from the insert menu. The most important are Header, Footer and Table. In the Header and Footer you can add Textboxes with text or by right-clicking on them and choosing expressoin you can choose some variable input. For example page number, report name etc. In the table you can drag columns from the “Report Data” (on the left showing the data source you just added). When you are finished, you might have something like this. Click preview to get a glimpse at what your boss might get.
5. Now let’s deploy it to the report server. First we must set the report server url. Right-click on the solution in the Solution Explorer and click on Properties. You will see the following dialog. Change the TargetReportFolder value to something you wish and provide the TargetServerURL as http://yourserver/reportserver (not just /report!). Press save. Now right-click on the solution again and select Deploy.
6. When it finishes open the url http://yourserver/report (not /reportserver!). You will see something like this. Go ahead and click on Test.Reports (or whatever you specified as TargetReportFolder) and then click on your test report. You will be greeted with the web version of your report.
7. Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.
8. Now you can go to Subscriptions. Click on email. Don’t have email as an option? See Additional Tip #2. Enter the recipient information as you wish and select a schedule for the sending. If you press save and encounter an error that ‘SQL Server Agent is not running’ either figure it out yourself what the problem could be or read Additional Tip #3 :). Once you have finished, lean back and let SSRS make your boss happy and in turn make you happy.
So we have seen the absolute basics of report creation. A quick and dirty introduction. This should cover your first encounters with SSRS. Hope it helps someone!
Stop reading here unless you are Troubleshooting…
Additional Tip #1:
I try connecting with SSMS (Management Studio) and it won’t connect. Maybe you renamed the server? Open RSConfigTool, go to Database > Change Database > Choose an existing report server database. Just choose the existing one, and let it run through it’s config. That should repair everything and you should be able to connect using SSMS again.
Additional Tip #2:
You don’t have email in the options for delivery in the subscriptions dialog? Probably it’s not configured. Thankfully that is very easy. You start the RSConfigTool. Connect and go to the Email tab. There you just specify a sender address and the SMTP server you would like to use. Click apply and voila. The server will automatically restart and the now the email option appears.
Additional Tip #3:
Saving the subscription results in an error message saying SQL Server Agent is not running. This is a DB or sysadmin issue, but just fire up SQL Server Configuration Manager (in the Start Menu under SQL Server 2008 > Configuration) then click on SQL Server Services, right-click on the SQL Server Agent > Properties. Go to the service tab, change Start Mode to Automatic and press OK. On the next reboot it will start automatically, but you still need to start it once for now. Right-click on the ‘SQL Server Agent’ and press Start.
Tales from the travelling tech founder 
does this work with the sql sever STANDARD edition or just
Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.
does this work with the sql sever STANDARD edition or just ENTERPRISE edition? I have standard edition and i see where it doesnt support the data driven subscriptions but can i still use email subscriptions?
Now your Boss will not want to go the web page to get that report, so let’s look at sending it to him per email automatically. First we need to change the security settings for connecting to the data source. In the report page, click on Properties > Data Sources. Change the “Connect using” to “Credentials stored securely in the report server” and specify an account that has access to the data. Also check “Use as Windows credentials…”.
hi there,
this is what my current host (www.asphostcentral.com) did to install the Reporting Service 2008 for me.
1. They asked me to provide a Report Server and Report Manager URL, e.g. http://www.myDomain.com/RS for Report Server and http://www.myDomain.com/RM for report Manager
2. They begin to install the SSRS 2008 by specifying the above URL on the Web Service URL and Report Maanger URL
3. They did create the Report Server Manager database for me
4. Under the Service Account, they use my FTP details as the login details
5. Once this is done, they created a user called “my_FTP_Username” on the Report Manager interface and assign the role: BROWSER and PUBLISHER for me.
They then came back to me and informed that everything is up and running. I tested it out and yes, it was working perfectly. The MOST common issues that I can see is that usually you guys fails to assign the correct role to the FTP User. If you have an FTP User called: “abc” then, the BROWSER and PUBLISHER roles must be assigned to “abc” user.
With just $4.99/month, I strongly believe that asphostcentral.com is worth a try. They did charge some fees for SSRS 2008, but it certainly recommended as there is no headache whatsover :)
I am a happy camper with them :)
P.S: I have not tried SSRS 2005. I jumped straight to use SSRS 2008 because I want new technology on my portal and I strongly believe that the setup of SSRS 2005 will be less about the same with the SSRS 2008.
P.S2: If you are setting the SSRS on your LOCAL PC, that would be even easier. Why? Just assign your SSRS user an Administrator rights and everything will be working fine. If you do not know how to add your user as an Administrator, please go to your “Computer Management” and add the user under the “Administrator” groups. Fixed all :)
Hey hey, nice (dirty?) little intro to this topic. This was exactly what I needed. Though here and there one sentence more or another picture would even increase the joy.
Anyway, thanks a lot!
Excelent article (very simple = very quick).
Some notes
TargetServerURL : can be get from RSConfigTool (Administrador de configuración de reporting services – > Dirección URl del servicio web) i suppose = Url web Service address in english.
From that Url you can browse to the report you want see, after deployment of course.
I have my site with SSRS2008 hosted at asphostcentral.com and everything work perfectly since day one. They give me an access to the reportServer and reports directory and I can publish and manage my reports remotely.
Two thumbs up to you guys!
Excellent tutorial. I appreciate when you explain what functions will be needed for. Thanks guys!
hi
I had Report server 2008 Configure on one machine
so when use
following URL :
http://level2:8080/ReportServer/fm
I can able to view my report on same machine
but when I try to access same url from different machine on same network i cant able to open report
I dont have domain name setup for that network so how can i resolve this problem.
i would be really helpful for me at this movement
thank you
Hi,
Can SQL Server Reporting Services 2005 can use SQL Server 2008 instance?
Many Thanks,
Ramakrishna
Can reporting services print invoices or forms? There is a page per record rather than tables. Say
Name: customer 1
Address : address 1
transaction details in table format for customer 1.
Name: customer 2
Address : address 2
transaction details for customer 2 etc.
Thanks for this, yes it did help!
Nice article. Just the facts. Yay.
This is awesome….exactly what I needed. Thanks a lot.
Some facts:
* SQL Server Reporting Services were great in the past. SSRS 2008 are better.
* MSDN is great.
I have a few of my own “facts” regarding those items that don’t seem to agree with yours.
Great tutorial! Even a noob like me could follow it! (I’ve been using Reporting Services for maybe a couple of days).
A ten or fifteen minute tutorial would be most welcome!
Thats like Hit the ground and run!!!!!!!!!!!!!!!!
right on the target.
Short n sweet
Thanks for the tutorial!
Everything works as expected up to the point where I need subscriptions. I don’t have the subscriptions tab on my local server.
Any ideas why I’m missing the tab? Thanks!
Rick: that’s correct, you can set up email subscriptions with Standard but you need Enterprise to set up data-driven subscriptions. With DDS you can put the recipients in a table and dynamically pull who gets what, otherwise you must manually set up every recipient (and parameters) and update them when someone changes jobs. It’s very tedious if you have a lot of recipients, but it might be something you can hand off to an admin of some sort if you trust them.
Is it possible to launch (display) a report from within a .NET application? What controls should I use for that?
nice one
Thanks for this. However, I have IIS 7 running on my computer, which seems to conflict with SSRS 2008 R2. I’ve found a hopelessly complicated Microsoft article on the subject – http://technet.microsoft.com/en-us/library/bb630449.aspx – but would appreciate if you could make things intelligible!
Quite helpfull for begners got a lot of information from this thanks a lot as well
eric
ericcisco
Well done.Thanks much for this
Excellent assistance
Hi,
I need to run this SQL query in order to obatin from SCCM database a report that will generate the existence of specific Software product on machines deployed with the SCCM client:
Select SYS.Netbios_Name0, SYS.User_Name0, SP.ProductName, SP.CompanyName, SP.ProductVersion
FROM v_GS_SoftwareProduct SP
JOIN v_R_System SYS on SP.ResourceID = SYS.ResourceID
WHERE SP.ProductName = @variable
Order by SP.ProductName, SP.ProductVersion
now I need to add information from another view so I can see the system type (desktops or servers)
I figured the view I need to pull the data from is:
v_GS_COMPUTER_SYSTEM and the column is SystemType0
what I don’t know is how to marry these the info from these two views into a report.
Any help?
Thanks.
Thanks for the post,
But i have a question, is there any way to do the same process but the account password should not expire.
The problem i have is, I have 50+ reports and they take long time to load and data has to be refreshed every day so i am subscribing to the reports daily and dump the files(mhtml and xl) to a folder and i use those file on a website. so using a account where the password expires i a big problem. any help will do thanks
Thanks for an excellent tutorial……
please help me
sql server 2008 developer edition is working for ssrs or not
pls give the confirmation details
thats an excellent tutorial short and to the point….thanks buddy
Excellent tutorial, thanks…
Great tutorial…concise yet very relevant…
Awesome and great tutorial for beginners!! Thanks
Thank you, Alex. Mucho useful.
Thank you for a very concise step by step walkthrough!
thanks a lot
thnk u
great tutorial, our SQL Server DBA ran away and as the Oracle DBA I am getting swamped with enquiries for help. Never used SQL Server in my life and this tutorial was great. Clear concise helped me fix a problem Thankyou
It helped a lot someone. :) Thank you.
Adorei seu post mas estou com outro tipo de dúvida.
Quando estou inserindo um relatório tipo MATRIZ, eu seleciono o que ira nas paginas, colunas, linhas e detalhes. Quando eu seleciono um valor na pagina, o meu relatório vem dividido. Por exemplo: – Eu tenho 5 cidade onde tenho diversos campos. Então o meu relatório vem 1 cidade em cada pagina.
Como faço para montar um grafico para cada cidade no mesmo relatório. Para cada pagina que indica as cidade.
Então o meu relatório ira vir 5 paginas com 5 gráficos indicando cada cidade.
Como faço isso?
Hi Buddy,
Thanks a lot for gr8 help.
but m facing problem while deploying it. It asked Credentials. What ever i set i was try to give it.
But not succeeded yet. Could you please tell me which credentials we need to give?
Either System OR SQL server Account or any other ?
Please help me ASAP.
Very useful. The one problem I ran into was during deploy – there was a security issue running the Report Manager: “xxxxx does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”
The way to fix this, is to turn off User Account Control (in Control Panel, search for UAC), reboot, configure your own user to have Publish rights, then turn UAC back on again.
Sigh…. im using it with Visual Studio >.< howw….
Useful information.
Thanks