Go Premium for a chance to win a PS4. Enter to Win

x

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

Share tech news, updates, or what's on your mind.

Sign up to Post

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within REPLACE functions). Over time this can be quite unreadable. It can also become a maintenance chore where this is not a single one-off effort.

This happened in a recent question here on EE. I'd like to share the solution that came out of that thread. I have written a function called udf_CleanDataFn. It can be invoke within any query as follows:
 
SELECT dbo.udf_CleanDataFn(YourColumn, @Options)

@Options is a varchar(255) field, which can accept any of the following "options":
 
  • ltrim - This will left-trim the value in YourColumn.
  • rtrim - This will right-trim the value in YourColumn.
  • trim - This will both left- and right-trim the value.
  • nocomma - This will remove any and all comma's.
  • nospace - This will remove any and all spaces.
  • nopunc - This will remove all standard punctuations (!@#$% etc)
  • nonum - This will remove all numbers (0-9).
  • noalpha - This will remove all alpha characters (A-Z and a-z).
  • alphaonly - This will remove everything except alpha characters.
  • numonly - This will remove everything except numbers.

Following are a few working examples that will demonstrate the different results . This first example takes out only the commas:
 
SELECT dbo.udf_CleanDataFn('This !  is *  a  13,21  @  test', 'nocomma')
Result:
1
 

Expert Comment

by:Nick Sawe
Comment Utility
ok thanks, i will give it a go. thanks for sharing this by the way !
0
 
LVL 20

Author Comment

by:dsacker
Comment Utility
You're welcome. If it proves helpful, please click the "Helpful" button. :)
0
Ask an Anonymous Question!
LVL 11
Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

This article needs basic knowledge of SSRS and how to create report using SSRS because, we are going to learn something new to send or create report automatically and send it to recipient via email with relevant data, or what is called a Data Driven Subscription.

You can create normal subscription in every version of SSRS but the data driven subscription is part of the Enterprise edition.

Let go step by step to create Subscription.

1. To create subscription first open Report Manage using url : http:://servername/Reports

You will be in the SSRS web site from where you can run reports, create new reports, load Report builder, create subscription etc.

2. We will move to the Report folder from where report is located and open report property by right clicking on that.
Report Manage3. In the below screen, you can see the report properties like Snapshot, Cache, Subscription etc.

All these three features are important to the advanced user. We are not moving to that but giving you some brief introduction of those.

3.1. Snapshot is the feature to generate reports from that report image. Snapshot means "make an image of the report and store in database". It is very useful for those reports in which data is not frequently changed.

By this feature you can render the report faster. To get the feature up you have to select the option "Render report from snapshot" in Processing options.

3.2. Cache is the same as Snapshot except the …
5
 

Expert Comment

by:Rhonda Carroll
Comment Utility
I understand the concept but I am having trouble figuring out where to store the data (the parameters)that the report will use.  i.e. we have a list of 3 customers and a date range;  I need to create a report for each customer and give the report a unique name.  How is the list of parameters stored and updated?  And how do you access the data for the subscription
0

Introduction

On the forums I now and then encounter questions asking for the possibility to display a checkbox control on a Reporting Services report.  And the methods usually presented are either through images or by using a certain font one way or another.  However, as of SQL Server 2008 R2 there is actually a third and interesting alternative which comes really close to actually having a control!

In this article I'll be presenting those three methods.

Screenshots are made using SSRS 2012, and so is the Checkboxes.rdl available for download on my Skydrive.

Checkbox Control, huh?

You may be wondering why people would want to put a checkbox on a report.  After all, reports are not capable of accepting input - except through parameters but that's a different story - and isn't that what a checkbox is all about?

Not entirely.  Reporting Services is not only used for data exploration.  Sometimes people use it to produce data-driven printouts, such as letters or even checklists.  In that perspective, having checkbox control functionality would indeed be useful.

A Silly Scenario

My imagination is failing me a little today so I came up with this silly example: a recipe checklist.  And today we’ll be cooking some pasta sauce!

This is the query that produces the list of ingredients:


Open in new window

10
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
Thank you Nata!  How you make your paste sauce is of course up to you, everyone is entitled to his/her own taste.  In my case, I surely like that meat in there :)
0
 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
Yep :)

Congratulations !! It was a pretty close contest, but yours emerged as the eventual winner.

Check out the "official" announcement : http://blog.experts-exchange.com/ee-blog/announcing-the-nexus-7-contest-winner-and-other-awesome-articles/
0
Recently, I got a challenging work project. The client want to create reports on their own and have users all able to create report as well. We have been using SSRS report but the challenge is how?

We have heard about ad-hoc reporting using SSRS but there is not proper information on web to sort out the issue.

So, having worked on this, I thought I would provide some information on how to create a SSRS ad-hoc report, where users have the capability to create reports on their own, they just need knowledge of how to create report and report structure: i.e. what columns and rows he/she need and what details need to be summarized.

Let's walk through creating an ad-hoc report using SSRS Report Builder+ SSAS.

1. Open Report Manager

First open a report manager in IE 8 or with compatible mode in IE 9 as administrator.
Report Manager

2. Generate Data Model from SSAS data source

We are going to create an ad-hoc report using SSAS cubes so we need to create Data model from existing cube to access in report. It will give a way to select cube with facts and dimensions with hierarchies on report. You can create perspective (view) as require for security reasons. (If you wish to some of user have no permissions to view sales cube and only view/create reports from purchase cube then perspective will come in picture.)
Generate Data Model for SSAS cube

3. Open Report Builder

Now time to create report. Hence, click on the image/ link of report builder to create report and follow the friendly steps to create.
Report Builder

4. Create Report Data Source

Create new data source for report from the published data source from report server.
Report Source

5. Select Data Model for ad-hoc report

2
 
LVL 21

Author Comment

by:Alpesh Patel
Comment Utility
Hi LHerrou,

I have no issue. It's good to give something better to other.

Thanks,

Alpesh
0
 
LVL 21

Author Comment

by:Alpesh Patel
Comment Utility
It's look fine. Thanks for you kind help.
0
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots.

The installation is straightforward, you just pop the SQL server 2008 installation DVD , run the installer, select Reporting services from the "Feature Selections", then select "Named Instance", configure and you're done !
Let’s do it step by step:

Select New or add a feature…..

0
1-SQL server 2008 will do additional tests and install installation support files.

1
2-Select perform a new installation of SQL Server 2008.

2
3-Only Select Reporting Services as that's what we need to install ... and press next.

3
4-Select named instance or you'll get an error ! As SSRS has been installed already for the default instance.

4
5-Confirmation...and required space vs available space.

5
6-Select the service account for this instance's SSRS.

6
7-Installation. SQL server will take around 4 to 8 minutes depending on your machine..& will get you to the completion screen.

You're done...exit the wizard.

7
8-Now since the installation is done, let's configure the instance.
Open Reporting services Configuration Manager by clicking
All Programs---> Microsoft SQL server 2008---> Configuration Tools
Selecting the new instance to configure...

8
9-Hop to the database tab to install or point to an existing DB.
In my scenario I'll create a new DB.

9
1
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only seen in e-mail that has been received in Outlook.

When subscribing to SSRS Reports, and when the e-mails are received, it is entirely possible that you don't or can't see the web links when provided in the results.  Note: The links do indeed work, but cosmetically, they don't appear like a web link in the report.

For example in Outlook (I am using Outlook 2010, but it should not matter the version for this issue), the links are there and they do work fine, but cosmetically they don't show unless you rest your mouse of the link - in this example, it is using the Ticket number:
No link showing on Ticket ID
Now, looking at the Text Properties box we see:
Text Box Properties we need to change
Making the following changes in code:
Style / Bold
=IIF(Fields!Ticket_ID.Value<>"",Bold,"")

Color / Blue
=IIF(Fields!Ticket_ID.Value<>"",Blue,"")

Effects / Underline
=IIF(Fields!Ticket_ID.Value,Underline,"")

Open in new window


Ahh now the results in Outlook:
Result Link in Outlook e-mail
Now this looks correct.

What is going on here?
Style / Bold
=IIF(Fields!Ticket_ID.Value<>"",Bold,"")
This adds in the Bold Style if there to test the existence of the Ticket_ID in SSRS.  If the Ticket_ID does not exist, it would be blank (or null).

Color / Blue
=IIF(Fields!Ticket_ID.Value<>"",Blue,"")
This adds in the Blue Color if there to test the existence of the Ticket_ID in SSRS.  If the Ticket_ID does not exist, it would be blank (or null).

Effects / Underline
=IIF(Fields!Ticket_ID.Value,Underline,"")
0

Introduction

Earlier I wrote an article about the new lookup functions that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the visualization department.  This feature is the Data Bar.  With this new component it’s fairly easy to make your tabular data a lot more visual, and thus easier to interpret.  And here’s how to do it.

I’ll be starting from the report that I created in Your First OLAP Report.  That allows me to focus on the visualization part, without first needing to build a table report.  (Okay, I admit, it’s not 100% the same report – I’ve modified the colors a bit because I felt the green was too dark.) But obviously this method will work with any report that’s showing data in a table.

Furthermore I’m running SQL Server 2008 R2 Nov CTP, 64-bit, and I’m using the BIDS to develop the report.

The final result can be downloaded from Skydrive.

Implementing The Data Bar

The report that we’re using is showing some sales figures grouped in three levels: Country, State/Province and City.  We’re going to add an extra column on the right of the table to contain the data bar.  Let’s first explore the Toolbox pane to discover the new visualization report items.

 The new Reporting Services 2008 R2 report items: Map, Data Bar, Sparkline and Indicator
The new items have been highlighted in yellow.  As you can see, besides Data Bar there’s also Sparkline, Map and Indicator
4
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Voted yes.
0

Introduction

In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report.

I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods explained here will work on any SSRS 2008.  Furthermore I’m using the AdventureWorks2008R2 database, available at CodePlex.

The resulting report, including image files, can be downloaded from my Skydrive.

The Scenario

The marketing department has requested a product catalogue.  This catalogue should contain all products produced by our two daughter companies: The Canyon Peak and Great Falls Soft.  The catalogue should be grouped on company, with the next company's products starting on a new page.

Further requirements are:


  1. Each page needs an image in its header, with even pages displaying a different image than odd pages.
  2. Each company has a logo.  The logo should be displayed in the company’s header.
  3. Each product has a logo.  The logo should be displayed as part of the product details.

A design document containing the expected layout, including all image material, has been provided.

The Data

The following query provides us with all the data needed to produce the report:

SELECT 'The Canyon

Open in new window

2
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Very handy.  Thanks for writing this.  -Jim
0
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
Thanks for the vote Jim!

tvae: it seems that I've missed your question somehow.  It's probably too late now but I'll answer anyway :)

The LargePhoto field stores the actual image, not a url, in a varbinary(max) field.
0
Written by Valentino Vranken.

1. Introduction

The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataset can use a SELECT query, which is the most common way of retrieving data and one that you're probably already familiar with.  But it can also use a Stored Procedure (aka stored proc or SP).

The purpose of this article is to demonstrate how data can be retrieved from a SQL Server database through Stored Procedures defined in that same database, and then displayed in a SQL Server 2008 Reporting Services report.

I'll be using the AdventureWorks2008 sample database available for download at CodePlex.

2. What Are Stored Procedures?

There are actually different types of stored procedure in the context of SQL Server.  The type that I am using in this article is called a "Transact-SQL Stored Procedure".  According to the Books Online, this type of stored procedure is:

A saved collection of Transact-SQL statements that can take and return user-supplied parameters.

If you have experience using a regular programming language such as Visual Basic or C#, I'm sure this sounds familiar.  You can think of a stored procedure as a method that takes any …
13
 
LVL 66

Expert Comment

by:Jim Horn
Comment Utility
Nicely laid out VV
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
Regarding  the 1st advantage of stored procedures: this is often-heard argument, however not valid since SQL Server 7. Since then, SQL Server is caching execution plans of all queries equally, s.p. or not. See article Inside SQL Server: SQL Server 7.0 Plan Caching, and this article where the author actually benchmarked it.

About the only advantage in performance is sending less bytes to the server, i.e. sending execute mysp instead of (potentially long) query.
0
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028

Of course it’s not the best way to use SSRS as it’s a presentation layer and so limited in doing that using Parameters and sub-Reports which will fire a code or a stored procedure to update or delete a record…but IT COULD BE DONE!!

And that’s what I was bargaining about…

1-I started by creating a new project in VS 2008, “Report Server Project” type.
 
 1
2-I’m using SQL 2008 R2 as my testing platform…I’ve created a test DB called “Admin”

CREATE DATABASE [Admin]

3-I created a test table called “Employees”  contains only 3 columns.

Create TABLE Employees
    (
    ID int IDENTITY (1,1),
    Name varchar(50),
    Comments varchar(MAX)
    )



4- Inserted 4 test records…
   
Insert Employees VALUES('Jason', 'Lazy Employee')
    ,('Yousef', NULL)
    ,('John', 'Anything Goes')
    ,('Smith', NULL)

 
 
 2
5-back to VS, Created a Shared Data Source to my Admin DB
 
 3

6-created 3 Blank reports, using the shared Data Source.

1-      To display the records which in the table.
2-      A user summary and confirm screen of which record will be updated and with what.
3-      A done screen, which will call a stored procedure or just have a simple inline T-SQL query.


 4
8
 

Expert Comment

by:Zina Pettitt
Comment Utility
can you do the same for SSRS 2010?
0
 

Expert Comment

by:Zina Pettitt
Comment Utility
How do you do it in SSRS 2010
0
[Webinar] Cloud Security
LVL 11
[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Hi,

I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Builder. Because, all are used to with BIDS to creates report.

The label printing is possible using the Report builder and it is available in SSRS. To get opened the Report builder open SSRS report server and open Report builder Link. see image below:

Reprot Server
REport Builder
When you open the Report builder it will ask to Report Templates (Tablix, Blank etc.) as like crystal report. From the list select the Blank template and start to work as normal BIDS environment.

First set the Report Layout and size of the report.

Set Body width
 

It’s good to set all report properties (different margins) to print report appropriate on paper.

After setting margins for report set the columns for the Label printing report. It’s obvious thing for label printing to set columns per page. i.e If you want to print 3 label on single row then set 3 columns. see image below to set the columns.

Set Reprot Layout
When you set the columns, you will get the view of report as below (3 column view)

Layout
Hope, all settings are completed for the Label printing report. Now, its time to design or place the field which you want to print in label. (Obvious to print name of person, …
1
 
LVL 4

Expert Comment

by:rshq
Comment Utility
Hi
 when i run project only column 1 has data and 2 other columns is empty!
0
Hi All,

I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to be moved on production server. I have tried many ways to move report to productions server. But, all are routine work to publish from BIDS to server. However, I need the easy way to move all reports from Development to Production server.  After long time googling I found the best industry freeware tool, Reporting Services Scripter, on sqldbatips.com.

The Tool is developed by Microsoft MVP (Jasper Smith).

Let's move on tool and dig and see the features.

Tool is available with User Interface and Command-line application.

First set the Parameters to use application for Report transfer. It has facility to layman can set parameters easily. i.e. Objects which you want to export from server, where you wan to export, from which server you want to export etc. etc.. The application export all objects like folder, Data source, Reports, Data set etc.

Set Options
Set Options

Set Options  

Set Options

Set Options

Set Options

Set Options

Set Options
When you click on Get Schema will generate script in the specified folder and create user friendly (.bat) Batch file to load report and objects to another Report Server..

Generate Schema
You can see the generated script folder structure as shown in image.

Output files
4
How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS.

Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps need to be applied. Let's look in details.


1. Variable in SSRS


If you want to use or hold value through out the request then refer the SSRS variables. It is available in any part of report and lifetime of variable is request to server. It reset when new request comes to server.

You can create variable using Menu=>Report=>Report Properties=>Variables

Add variables to SSRS
You can use this variable in calculation of field or anywhere in the report as below:

Use of Variable in Field expression

2. Custom Code in SSRS


Custom Code is very useful where you can not achieve desire result using Sum/Avg/Count/Running Total.

For example in report you are displaying records (Products) those have price > $10 That time you can hide those rows in report but total not eliminate hidden rows in count. In these type of case it's good to have custom code. Let's see how can we create custom code.

You can create custom code at Menu=>Report=>Report Properties=>Code

Sample of Custom Code
Note: Make sure SSRS support custom code in VB only.

Let's time to see how to use the custom code in report fields.

Use of Custom Code function in Field expression

3. Assembly Reference in SSRS

4
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column.

Ok the fun part was "how am I going to control the output to the column from the table? ". So the first thing I started working on was seeing how many characters would fit on a single line of the column that I need to use; and still look nice in the report.

Since the report had alot of data per detail row in the table I ended up with a column width length of 29 characters for this report. What was nice was that on this report the data was being split up by using a comma as a delimiter. Once the width was decided, the next step would be to wrap the incoming data so that each part of the data would wrap by design rather than arbitrary.

My favorite namespace in coding this type of data is using RegularExpressions; the use of RegularExpressions is only limited to your imagination. The code could be used with other applications. You would just have to experiment with it in the project that you are working on.

The code is pretty straight forward when you look at; just place the code in your report code tab properties. The delimiter can be just about any character you want. The sample uses the "comma" character as a delimiter. The InputString is the data that is coming in from you dataset; an example would be …
1
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package?

The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.  

Background:
I have tested this on a SQL Server 2008 R2 system running on a VMWare Windows 2008 machine.  When I first started I tried to use the JET OLE DB provider, but I did not manage to make it work.  I was always having the same error :
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
So, I downloaded and installed on my SQL Server the Office 2007 Drivers and used that instead of JET.

The Office System Drivers proved to be perfect to transfer data between the supported file formats and SQL Server!

Example Export:
 
First you need to create an Excel  template that has the structure of the data you want to export.  For example, I have created an Excel file with two columns: Name and Date.  On my system, I have saved this file as D:\ETLsFiles\Excel\template.xls, which I will refer to below.
Template.xls

As we are going to use a copy of the above file, we need to be sure that SQL Server has been enabled to use xp_cmdshell and we have access to Ad Hoc Distributed Queries.  Here is script to do so:


Open in new window

2
 
LVL 60

Expert Comment

by:Kevin Cross
Comment Utility
Nice work!  

Note for 64-bit SQL Server that since the ACE drive is 32-bit, that you will often get a similar error message to what you got for JET.

Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

If you worked around this, it would be interesting to learn how.  As far as I know though, it will work fine for 32-bit SQL running on 64-bit Windows.  

Anyway, thank you for sharing.  You have my Yes vote above.
0
 
LVL 5

Author Comment

by:perezjos
Comment Utility
Hi,

just to update this, Microsoft has now a 64 bit driver :

http://www.microsoft.com/download/en/details.aspx?id=13255

Thanks
Jose
0
Time Corrections for Reports
Working with a report, we made some interesting discoveries about the time corrections/updates

We are using the following Parameters:
Starting Entered Date (Date) formatted as Data type: "Date/Time"
Ending Entered Date (Date1) formatted as Data type: "Date/Time"
TimeZone formatted as Data type: "Text"
Label: Eastern, Central, Mountain, Pacific
Value: +1, 0, -1, -2
It may also help to set a default value: e.g. -2 for Pacific
Note: It depends on where your data warehouse is located..
Default Values
If we look at the "DataSet Properties…" of DataSet1
 
We need to go to Parameters:
@Date
@Date1
 
Date:
=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Parameters!Date.Value))

Open in new window


Date1:
=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Parameters!Date1.Value))

Open in new window


Click the OK Button
We are going to be changing the following..
 
=Fields!Date.Value

Open in new window

Results are: 8/21/2010 9:18:50 AM

If we look up this ticket we see that the time is not correct (off by two hours).
 
So..  We need to change to:
=DateAdd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Open in new window

Result are: 8/21/2010 7:18:50 AM

Caution: If you make the change to:
=DateAdd("h",Parameters!TimeZone.Value,System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value))

Open in new window

The Entered Date is not correct!
 
Some improvements:
Be able to display a desired TimeZone like Pacific Time and not the server time in GMT, e.g. -8
With Microsoft Access and Microsoft Excel, for example: We can use KERNEL32 to get the user's system time/date

Research..

Date for a Ticket - GMT (Raw Date on the server):
=Fields!Date.Value

Open in new window


Entered Date for a Ticket (adjusted for TimeZone) - GMT:
=Dateadd("h",Parameters!TimeZone.Value,Fields!Date.Value)

Open in new window


Convert UTC to Local Time for an Entry:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Date.Value)

Open in new window


Convert UTC to Local Time (Real Time) for a ticket:

Open in new window

2

Introduction

As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Server 2008 R2 has got some interesting new functionality to facilitate data retrieval from other datasets.

In this article I will show you how exactly this works by using the new Lookup, LookupSet and MultiLookup SSRS functions.

I'm using the AdventureWorks 2008R2 relational database and the AdventureWorksDW 2008R2 data warehouse, available from CodePlex.  The main data is coming from the data warehouse while all the lookups are done on the relational database.

Setting Up A Basic Table Report

In my report I've created a dataset called dsInternetSales.  This dataset is retrieving data from the AdventureWorksDW2008R2 data warehouse using the following query:

select PROD.EnglishProductName, PROD.ProductAlternateKey, PROD.ListPrice,
    PSC.EnglishProductSubcategoryName, PC.EnglishProductCategoryName,
    S.OrderQuantity, S.SalesAmount
from FactInternetSales S
inner join DimProduct PROD on S.ProductKey = PROD.ProductKey
inner join DimProductSubcategory PSC 
    on PROD.ProductSubcategoryKey = PSC.ProductSubcategoryKey
inner join DimProductCategory PC on PSC.ProductCategoryKey = PC.ProductCategoryKey

Open in new window


Using that dataset, I've set up a Table as shown in following screenshot.

 Basic Table Report
6
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
Comment Utility
Nice artilce. Thanks.  

In a report I've more Shared DataSources. Call them as DB1, DB2 and DB3. Each datasource points to different databases from different servers. But the tables and their schemas are common to them. This is applicable for SPs/UDFs as well . Is there any way to fetch records from all of the above Datasources from a RDL and show in report?  

I know this is not the right place to ask the question. As your artilce is related to that I ask here. I would like to get solution for my problem. Please do suggest. Thanks in advance.
0
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
EaswaranP: as far as I can tell you were already given an answer to that question here: Is it possible to fetch records from more than on DB for a SSIS report?
0
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error

With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) that will assist in prohibiting unauthorized entry into SQL Server Reporting Services. Although UAC can be disabled, working within the parameters of the new technology will strengthen the security within your company infrastructure.

There is a new process that you may run into involving SSRS for SQL Server 2008 R2 when used with Vista and/or Windows 7.  There are new UAC security steps that are used during the validation process for Report Manager.
 
 
If you receive the following error when trying to make the connection to your Report Manager:
 
  UAC Error in Report Manager
 
 
Follow the steps below:
 
Go to the report server and navigate to the following;  Start --> Administrative Tools --> Computer Management. Then Under Local Users and Groups you will see the screen below. Add the NT AUTHORITY\Authenticated Users  to the SQLServerReportServerUser group

  Add Authenticated Users to SSRS Group
 
 
Once this is done, open IE browser from the server and go to http://localhost/Reports. This will bring up Report Manager in the Home folder and display a few option.
 
From the Report Manager screen, click on Folder Settings, then go add NT AUTHORITY\Authenticated Users to this group on the root folder
2
 
LVL 5

Expert Comment

by:JermTheWorm
Comment Utility
This is half helpful except that I can't add "NT AUTHORITY\Authenticated Users" to the list
0
 
LVL 5

Expert Comment

by:JermTheWorm
Comment Utility
I should be able to delete my own idiotic comments... I found it. But it makes no difference my users still get asked for administrator password.
0
After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a stored procedure to actually execute the report from, providing the parameters. However this only worked for one parameter, and was kind of a pain for a long term solution.

I found the core of this method was using the path in the catalog table. That is what the file name is based off of. So I have created the stored procedure below to be run every 15 minutes (you can change the timing) the change the path of the reports that were going to be generated in the next 15 minutes and to change back the path of any reports that were previously generated.
 
The only limitation of this is that you cannot access that report directly in the report server during that 15 minutes. Howver, you can alter the script to change this timeframe if you would like to limit that window.

This has only been tested on Data-driven subscriptions on shared report schedules. This may not work in other scenarios as the date fields I use are specific to shared report schedules.

The code I have written works well for my scenario but you may want to change it for yours. The main point is you need to change the path in the catalog table before the emails goes to what you want the filename to be, and you need to change it back afterwards so you can access …
1
Free Tool: IP Lookup
LVL 11
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

1. Introduction

This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.

It demonstrates how to build a report using SQL Server Reporting Services 2008 with data coming from an OLAP cube running on SQL Server Analysis Services 2008.

The OLAP database used in the article is called "Adventure Works DW 2008", available for download at CodePlex.

If you're fairly new to Reporting Services (aka SSRS) and you find that this article is going a bit too fast, I'd like to point you to my other article which explains how to build a report that's retrieving data using regular stored procedures.

2. OLAP <> OLTP

When people are talking about databases, what they are usually referring to are "regular" relational OLTP databases.  OLTP stands for Online Transaction Processing.  As the name implies, these types of databases are built to handle many simultaneous transactions (consisting of actions such as inserts, updates, deletes) in real-time.  I'm sure you're familiar with these types of database so I won't go further into them.

OLAP (Online Analytical Processing) on the other hand is a totally different story.  OLAP cubes are built to answer multi-dimensional analytical queries as fast as possible.  For that purpose, what you can find in such a database are measures (these are the numbers) stored in cubes, and dimensions
9
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
I don't really know Dynamics but I'm fairly sure you'll need to learn the MDX language if you want to create reports with SSAS cubes as source.  A good resource is the blog by Chris Webb: Chris Webb's BI Blog - MDX category

I also recommend to start with some kind of training, either online or class room, because MDX can be rather complex.  And a good book or two.  Chris Webb co-wrote one some years ago but it seems to be out of stock, not sure if it's still being republished.  In any case, books with more than 4 starts in following list should be interesting: MDX books on Amazon
0
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
BTW: please don't forget to click the Yes button next to "Was this article helpful?", as apparently you found it helpful.
0
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function.

Two commonly-used functions in SQL Server Reporting Services are the IIF() and the Switch().  These are two functions of the Program Flow type, or Decision Functions as they are called on this MSDN page.

In case you're wondering why it's so difficult to find a function reference for the built-in functions of SSRS, it's because these are actually Visual Basic functions and Microsoft refers to those for any detailed explanation.  Their references are located at the bottom of this article.

Anyone who's done some programming most likely already knows the if <expression> then <some_code> else <other_code> statement.  If <expression> evaluates to true then <some_code> gets executed, else <other_code>  gets executed.

The IIF() works in the same way.  According to its description it:

Returns one of two objects, depending on the evaluation of an expression.

This is its definition:

Public Function IIf( _
    ByVal Expression As Boolean, _
    ByVal TruePart As Object, _
    ByVal FalsePart As Object _
) As Object

Open in new window


Here's a simple example.

=IIf(Fields!YearlyIncome.Value >= 600,"High","Low")

Open in new window

13
Written by Valentino Vranken.

1. Introduction

In a previous article I announced that I would write a sequel covering how to pass multiple-value parameters from a SQL Server Reporting Services report to a stored procedure.  So that's what I will be writing about in this article.

As usual, I will be using the AdventureWorks2008 sample database (running on SQL Server 2008 SP1), downloadable from CodePlex.

The examples in this article, Part 2, are building further on the result achieved when following the steps described in Part 1, so please refer to the previous article if needed.

2. Passing Multi-Value Parameter To Stored Procedure

As we've already seen in Part 1, parameters can be passed from a Reporting Services report to a stored procedure.  The parameter that was used was just a simple, single-valued parameter.  However, a report parameter can be defined as being multi-value.  Let's set one up!

Our report currently shows a list of employees who were hired after the selected hire date.  One of the columns being shown is the department in which they're active.  We will modify the report so that it's possible to filter the data on department - only the selected departments are to be retrieved from the database.

Creating A Multi-Value Report Parameter

The first step is to create a new report parameter, so right-click the Parameters node in the Report Data pane and select Add Parameter....

 Report Data pane - Add Parameter
11
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
Thanks Jim, always nice to hear from other experts! :)
0
 

Expert Comment

by:Bhagat S
Comment Utility
Great article and easy to follow.

Is there anyway the 'select all' functionality can be added into the stored procedure?
I'm referring to the article: http://blog.hoegaerden.be/2013/12/18/the-select-all-parameter-option-ssrs/

I have added an, OR statement at the end of the WHERE clause as follows:

WHERE (E.HireDate > @HireDate
AND D.DepartmentID IN (select * from list_to_tbl(@DepartmentList))) OR @DepartmentList = -1;

But produces the following error:
Conversion failed when converting the varchar value '-1,1' to data type int.

Any suggestions welcome. Thank you
0
Written by Valentino Vranken.

A while ago I wrote an article called Chart Optimization Tips.  This article explained how to optimize a Column Chart.  Today I have returned to show you some Pie Chart implementation techniques.

As usual, I will be using the AdventureWorks2008 database, available at CodePlex.  The chart itself will be implemented using SQL Server 2008 Reporting Services.

1. Retrieving The Data


The dataset in our report uses the following query:

select SWD.*, SWA.City, SWA.StateProvinceName, SWA.PostalCode, SWA.CountryRegionName, SWA.AddressType
from Sales.vStoreWithDemographics SWD
inner join Sales.vStoreWithAddresses SWA on SWA.BusinessEntityID = SWD.BusinessEntityID

Open in new window


This query illustrates a bad coding practice: never use "SELECT *".  Ideally you should only retrieve the columns that you need for the report.  That will optimize performance when generating the report.  But that is not the goal of this article so I'll leave the query as it is.

2. A Basic Pie Chart


To get started with our Pie Chart I have selected the third icon in the list of Shape charts.  This adds a regular 3D pie chart to the report.

 Select Chart Type window
To set up the chart I dragged AnnualSales from the Report Data pane into the "Drop data fields here" area and StateProvinceName into the "Drop category fields here".

In case you don't see the Report Data view (it has a tendency to disappear now and then), you can open it through the main menu: View > Report Data.

 Report Data Pane
This is what our report looks like in Preview:

 Basic Pie Chart
12
 
LVL 37

Author Comment

by:ValentinoV
Comment Utility
Except that some clients will get dinner somewhere else if you tell them that.  For those, it's nice to know about the technicalities of baking a pie.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Comment Utility
Yeah, I know. Sadly. But if we all stick together and show them how much more value they can get from a chart that suits the situation, they may see the light in the end.

0
For the following example I'll be using SQL Server Reporting Services 2008 and the Adventure Works cube running on SQL Server Analysis Services 2008.  In case you don't have the AdventureWorks databases and cubes yet, they're available at CodePlex.

I could have called the article "How to implement conditional formatting using SQL Server Reporting Services 2008", but I didn't.  Because that's not the only thing what this article is trying to illustrate.  The initial purpose of this article is to show you how you can detect missing fields when retrieving data from an OLAP cube.  On top of that, the article also shows how thresholds can be used to highlight values in a table.

1. Scenario

The sales department has asked for a report that displays the number of product items sold during a selected period.  As the company is active in two different markets, both the internet and reseller numbers should be shown.  The figures need to be grouped by product category, with drilldown to product level through subcategory.

Besides the period filter, it should be possible to filter on product category to limit the number of items shown.

Also, the background of the numeric cells should get a color depending on the value in the cell.  Colors range from red for low sales figures to green for high sale volumes.  The ranges are variable and should thus be configurable using 3 threshold parameters.  Following table shows the ranges as the department has requested them:

 Threshold ranges as requested by Sales Department

2. Selecting The Data

4
The following little narrative will show you a new feature of SQL Server 2008 Reporting Services: support for rich-text.

Once upon a time in a Business Intelligence Development Studio 2005 Reporting Services solution there liveth a Textbox object which developers could use to put text on a report.  It was a fairly simple object, allowing only two ways of programming the text.

The first and most simple way of the two was to type plain and simple text directly into the box:
 SSRS Textbox with simple constant text
And the second, more advanced way, alloweth developers to use an expression to create the text dynamically.  With this they could combine data from several sources into the same Textbox:
 SSRS Edit Expression window
Then developers realized that something was still missing.  Text could be created dynamically but they also wanted to optimize the way the text looketh.  So they selected part of the expression and tried to change some font properties.  But alas, they couldn't.  It was all or nothing:
 Text rendered using some font styles in SSRS 2005
Then arriveth the day of the birth of the successor to BIDS 2005.  As the family's tradition went, it was called the same as its parent.  The only difference was its birth year: BIDS 2008.

And developers started exploring BIDS 2008 and discovereth that the Textbox object had gotten an upgrade.  When editing the content of the textbox, the right-click menu has gotten a new item:
 SSRS 2008 Create Placeholder menu item
That's right, Create Placeholder.  And this was the …
7
 

Expert Comment

by:December2000
Comment Utility
Awesome!
0

SSRS

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.