Monday 19 January 2009

SQL Server Reporting Services - Report Builder 2.0

SQL Server 2008 Reporting Services (SSRS 2008) includes the Report Designer and Report Builder 1.0 tools, which you can use to create reports ranging from the very simple to the highly sophisticated. Report Designer is hosted in Business Intelligence Development Studio (BIDS), so you get all of the fine control you need to get your reports exactly as you want them. However, BIDS is a daunting environment for non-developers and it requires a considerable investment on the part of the user in order to become proficient in its use. Report Builder 1.0 (RB 1.0), on the other hand, is targeted at non-developers such as information workers, who need to be able to create ad-hoc reports quickly and easily. It provides easy to use drag and drop functionality in a Microsoft Office-like environment and shields users from the complexity of the underlying data source(s) by enabling them to interact with report models. A report model is an abstraction layer that sits over the top of a data source and exposes the underlying data using business-oriented language that is more meaningful to the end user.

Report Builder 2.0 (RB 2.0) is a new tool that was made available as a separate download at the end of October 2008. It offers various improvements over RB 1.0, but it does not replace it directly; you can use both tools side by side, if required. Whereas RB 1.0 is a ClickOnce application that can be installed by users from the same report server web site where they access their reports, RB 2.0 is a standalone application that must be installed separately where required. (RB 2.0 is scheduled to be released as a ClickOnce version as part of SQL Server 2008 Service Pack 1 – you’ll then be able to choose whether version 1.0 or 2.0 is installed when users click the link on the report server web site).

So what does RB 2.0 offer that RB 1.0 doesn’t? Well, firstly the Office-like theme is continued, but the look and feel are more like Office 2007; for example, there is a ‘ribbon’ in place of the older style toolbar (Figure 1). Whilst this is a matter of personal preference, I see this as an improvement.





Figure 1 – The Report Builder 2.0 user interface

Report creation wizards
RB 2.0 simplifies report creation with the new wizards for creating table, matrix and chart based reports, which guide you through the process. You can select a data source (see below), drag and drop is used to add fields to the report and you can arrange fields into columns, rows and values by using selection boxes. Once you’ve created a basic report, you can easily modify it by adding data regions including lists and gauges (new in SSRS 2008) and report items including images and text boxes.
Access to shared data sources
One of the drawbacks of using RB 1.0 is that it requires a report model to be created in advance so that it can interact with it. RB 2.0 can also access report models, keeping report creation easy for information workers, but it can additionally work with other data sources directly. These can be shared data sources that already exist or embedded sources that you create using RB 2.0 itself. The report creation wizards include connectors for a wide variety of sources (Figure 2). Once you have created your source, you can select the data that you wish to extract by using a text based editor (or in the case of SQL Server connections, a graphical editor).




Figure 2 – Data source properties dialog box


Editing of reports stored on the report Server
RB 2.0 supports the editing of reports that are stored on the report server. This enables users to use RB 2.0 to customize reports that were created and published by using Report Designer or RB 1.0. RB 1.0 is limited in this respect as reports that you create using RB 1.0 and then open and modify using Report Designer cannot then be opened again in RB 1.0, which is somewhat restrictive. Now, a developer can create and publish a sophisticated report using Report Designer, and users can access and modify the report themselves using RB 2.0 or Report Designer without any problem.

Summary
Because of these (and other) improvements, RB 2.0 will be attractive not only to information workers, but for developers too. True, it doesn’t support the full range of functionality that BIDS does, but it supports most of the major items – and it has the added benefit that you don’t need to install BIDS in order to create advanced reports.
For more information on Report Builder 2.0, visit Books Online at http://msdn.microsoft.com/en-us/library/dd207008.aspx

16 comments:

Dumb DBA said...

Any idea how to launch 2.0 from the report builder button within a reporting services website instead of the old 1.0 version?

Thanks!
David Hay

Martin Ellis said...

Hi David, Thanks for your question. The functionality you're after is called 'ClickOnce' by MS, and it was not available with RB 2.0 when it was first released. However, the good news is that installing SQL Server 2008 SP1 adds this, and that it's pretty straightforward to set up. Full instructions are not yet in Books On Line, but the SQL Server Team blog (http://blogs.msdn.com/sqlrsteamblog/) has a basic set of instructions for both native and SharePoint mode configuration. You can find these under the title 'Some Help with Report Builder 2.0 ClickOnce'.

Regards,
Martin

Dumb DBA said...

Martin,

Thanks so much. Found my issue. I installed Analysis and Reporting services after I had applied SP1 to the database engine. Rerunning the SP1 install allowed me to upgrade the two services and then change the custom url!

Martin Ellis said...

Hi David, no problem. Glad you've managed to get it working!
Regards,
Martin

Unknown said...

I have created report model and published it. Now, I dont want to use ReportBuilder Link from Report Manager, is there any way so, that I can get that link on webpage or any win application.

Thank you very much helping me,
Mahesh

Martin Ellis said...

Hi Mahesh, thanks for your question.
Report Builder 2.0 is available as a ClickOnce application that launches through Report Manager or by using the following URL in your browser: http://servername/reportserver/reportbuilder
/ReportBuilder/ReportBuilder_2_0_0_0
, where servername is replaced by the name of the machine where Report Builder 2.0 is installed. See this article http://technet.microsoft.com/en-us/library/dd795302.aspx for more detail.

You can also download Report Builder 2.0 as a standalone application by downloading it from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en

Hope this helps,
Regards,
Martin

Unknown said...

Hello Martin,

Thank you very much for your time and help.

Mahesh

Unknown said...

Again, thanks for your help Martin,

one quick que: how to set up Model item based security

I know this method:
using SSMS - connect to Report Server
Select particular model then select Model Item Security by right click-property. We can assign user/role based security to any item.

Now, my que is how can I change from UI/webpage, basically I do not want by my self, I can create webpage and give it to user to do so.

Thanks,
Mahesh

Martin Ellis said...

Hi Mahesh,
In order to achieve what you want, I think you'd need to use the Report Server Web API security extensions. I don't what your development skills are like, but you can make a start with the Report Server Web Service here:
http://technet.microsoft.com/en-us/library/ms152787.aspx
Good luck!
Regards,
Martin

Unknown said...

Hi Martin,

you know what both the time, I have posted and side by side I was researching the same and I got some and the other better ways.

But I feel more confidence when I saw your input and which matches with it.

Thank you very much for your time and effort on this.

Mahesh

Anonymous said...

Hi

We have RB1 & 2 installed, but find only users who are Farm Admins can run RB2. We add users who are content owners under sharepoint but still no joy.
I assume its permissions related but dont know where exactly to look as its an odd problem.

We have SSRS 2008 SP1 and WSS SP2.

Any help appreciated.

Martin Ellis said...

Hi Anonymous!

You need to give your users the correct permissions on the report server. This article outlines the basic requirements: http://msdn.microsoft.com/en-us/library/dd220428.aspx

The other thing to look out for is the existence of 'deny' permissions, because these will override any 'allow' permissions that you have granted to your users / groups.

Hope this helps,
Regards,
Martin

Anonymous said...

Hi Martin,

Thanks for your help.

Well I have added the user as Collection Admin, and still no good.

I removed user from site admin, add user as Full Control in Owners group to the site, still no joy - will only open RB1.

As a local admin or farm admin I can open RB2 no problem.

Any idea exactly where inside sharepoint or report server I would look to check if there was something else wrong? The reports data source uses SQL credentials to connect to the SQL database and these work fine as I have tested them.

I'm starting to think its a bug.
I'm so damn frustrated....

Jess said...

I deploy report model to the report server where i have my sql server installed. How can I access that model from the standalone report builder 2.0 that i installed on my desktop?

When I try to add new Data Source and select "Use a shared connection or report model" it doesn't allow me to browse to the server, only to Recent Sites and Servers folder that has no items in this list.

Jess said...
This comment has been removed by the author.
Unknown said...

Hi Jess!

Looks like you are very close to access the model you have deployed.

If you are the "System Administrator" or "System User" on report server then you should be able to see "Report Builder" link on Home page - on horizontal pan(New Folder-New Data Source-Upload File-Report Builder).

If you are not able to find the link then here is the direct link: just replace [SERVERNAME] and you should be good to go.

http://[SERVERNAME]/ReportServer/ReportBuilder/ReportBuilder.application

Regards,
Mahesh