How to build SSRS Drill Down Report using BIDS

A drill down report is very useful for tables containing layered data. The report will be displayed with the top layer at first, and those layer can be expanded later to show more detailed data.

This is the design of the drill down report:

I used AdventureWorksDW2012 as my source DB. You can search Google to get the free database. Following sql script is used to get the dataset:

SELECT  DimOrganization.OrganizationName,
    DimDepartmentGroup.DepartmentGroupName, DimDate.CalendarYear,
    DimDate.EnglishMonthName, DimDate.MonthNumberOfYear,
    SUM(FactFinance.Amount) AS SubTotalAmount
FROM  FactFinance
    INNER JOIN DimDate ON FactFinance.DateKey = DimDate.DateKey
    INNER JOIN DimDepartmentGroup ON FactFinance.DepartmentGroupKey = DimDepartmentGroup.DepartmentGroupKey
    INNER JOIN DimOrganization ON FactFinance.OrganizationKey = DimOrganization.OrganizationKey
GROUP BY DimOrganization.OrganizationName, DimDepartmentGroup.DepartmentGroupName,       DimDate.CalendarYear, DimDate.EnglishMonthName, DimDate.MonthNumberOfYear

You should add at least 2 columns with one numeric column of SubTotalAmount for the report when you create a tabular report following the report wizard.

Next, right click on the data part of the above report design area, and choose Add Group, and Parent Group.

A Tablix Group window will popup, choose CalendarYear. Note you need to check "Add group header" to show the Group Name.

After Calendar Year Group column has been added, you can choose Organization Name and SubTotal Amount Column and right click on them to add another Group for English Month Name. Once again you need to check "Add group header" to show the Group Name. This will add one row for the group header so which would be very useful to choose the group during the design phase.

Applying same way you can add Group Organization Name and Department Group Name. You need to check "Add group header" to show the Group Name every time.

Now you need to do hide/show trick. Go to Visual Studio with BIDS menu, choose REPORT, and click View, and check Grouping.

Following Row Groups Info will appear at the bottom of Visual Studio.

Right click on the Group you want to hide, English Month Name, Choose Group Properties. A Group Properties popup window will appear. Choose Visibility on the left pane, and choose Hide. Check "Display can be toggled by this report item:" , and choose Calendar Year.

You can do same for the following 2 groups.

Now the last step is to add Total. Right click on the  Calendar Year Group, choose Add Total, you can add total for all years. Right click on the English Month Name, choose Add Total, you can add Total for one year.

Note, Group would be ordered by name by default, so August would be the first month always. This is not what we want. On the Row Groups Info, choose English Month Name, right click on the Group Properties. Group Properties popup window will appear. Choose Sorting on the left pane. and Choose MonthNumberOfYear column. Then your Month Group will be in the right order.

The final design would be this:


This would be how the report result finally look like.

SSRS Report Is Blank Under Chrome and Safari

Under Chrome and Safari, SSRS report would just show parameter part but not report content, while under IE, report worked fine.

This error happened because Chrome and Safari render overflow:auto in different way than IE.

SSRS HTML is QuirksMode HTML and depends on IE 5.5 bugs. Non-IE browsers don't have the IE QuirksMode and therefore render the HTML correctly

The HTML page produced by SSRS 2008 R2 reports contain a div which has overflow:auto style, and it turns report part invisible
<div id="ctl31_ctl10" style="height:100%;width:100%;overflow:auto;position:relative;">...</div>

The HTML page produced by SSRS 2012 used ct130_ct109 instead.

Solution:

1. Find ReportingServices.js, for SSRS 2008, by default it is in the directory C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js. For SSRS 2012, by default, it is in the directory  C:\Program Files\Microsoft SQL Server\MSRS11.xxx\Reporting Services\ReportManager\js\ReportingServices.js, while xxx is the database name. 

2. For SSRS 2008, add following js code to above file:

//Fix to allow Chrome to display SSRS Reports
function pageLoad() {    
var element = document.getElementById("ctl31_ctl10");
if (element) 
{
    element.style.overflow = "visible"; 
} }

For SSRS 2012, add following js code to the file:

//Fix to allow Chrome to display SSRS Reports
function pageLoad() { 
    var element = document.getElementById("ctl32_ctl09");
    if (element) 
    {
        element.style.overflow = "visible";         
    } 
}

Note for SQL 2008 R2 SP1, the div name is ct131_ct109 ninstead. So if above solution doesen't work, you can use Chrome "Inspect Element" function to check the javascript source to find the right div name with overflow:auto and made corresponding fix.

Source:
http://stackoverflow.com/questions/5968082/ssrs-2008-r2-ssrs-2012-reports-are-blank-in-safari-and-chrome

How to add Chart to your SSRS report

You can add charts to your SSRS reports.

A typical chart contains 3 parts: Category Groups, Series Groups, and Values. You can treat a chart like a matrix, which has columns, row, and data. Category Groups in the chart is like columns in the matrix. Series Groups in the chart is like rows in the matrix. And the values in the chart is like data in the matrix.

Right click on the white part of the report design page, and choose Chart then a default chart will add to your report. 

Right click on the chart you just added, a Chart Data window will popup so you can choose Category Groups, Series Groups, and Values. 

For example, I need to compare two years sales data month by month. I put following SQL script into the dataset:

SELECT Sum (dbo.FactFinance.Amount) AS SumCount

    ,  dbo.DimDate.MonthNumberOfYear

     , dbo.DimDate.EnglishMonthName

     , dbo.DimDate.CalendarYear

FROM  dbo.FactFinance

    INNER JOIN dbo.DimDate on dbo.FactFinance.DateKey = dbo.DimDate.DateKey

Where dbo.DimDate.CalendarYear = @Year1 OR dbo.DimDate.CalendarYear = @Year2

GROUP BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear,

    dbo.DimDate.EnglishMonthName

ORDER BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear

I set CalendarYear as Category Group, and set EnglishMonthName as Series Group, and SumCount as Value. 

Also I need to order Series Group by MonthNumberOfYear, so I right click on the EnglishMonthName under Series Group, and choose MonthNumberOfYear as following.

The report is done. The source DB is from Microsoft AdventureWorksDW2012. The result would be like this.

Source:

https://msdn.microsoft.com/en-us/library/dd239351.aspx

Fact Table VS Dimension Table: Could a table be both?

Fact tables are the large tables in your data warehouse schema that store business measurements. Fact tables typically contain facts and foreign keys to the dimension tables. Fact tables represent data, usually numeric and additive, that can be analyzed and examined. Examples include Sales, Cost, and Profit.

Dimension tables, also known as lookup or reference tables, contain the relatively static data in the warehouse.Dimension tables are usually textual and descriptive and you can use them as the row headers of the result set. Examples are Customers or Products.

Could a table be both Fact table and Dimension table? The answer is No. Fact table is a core part of Star Schema. The Fact table will associate with multiple Dimension tables, to which a Star Schema formed. Note Fact table data can be aggregated while Dimension table can not be aggregated. Fact table data are not supposed to be updated and Dimension data could be updated sometimes. SSAS even has size limit for dimension table.

Dimension tables can have parent dimension tables, and thus formed Snowflake Schema. However, designers attempt to avoid this kind of design since it causes more joins that slow performance. 

Developer sometimes feel confused for some tables as they looked like both Fact and Dimension table. For example, User with accumulated points, the table would be divided into User part, which is Dimension table, and accumulated points, which is Fact table.

How to Resolve SSAS Service Failed to Start Issue

After you installed SQL Server 2012 on your Windows Server, if you changed default directory which stored the SSAS database, your SSAS Service will fail to start.

If you go to windows event viewer, check the event with mistake, you will find associated error information like this:

This error happened because the account which launch the SSAS service doesn't have enough privilege to write the new directory.

So the fix is easy. Simply go to the directory, right click on the directory, choose properties, and select Security Tab, add the account which will launch the SSAS service, and click OK. After you successfully added the account, choose the account, and click on Edit button,  add Full Control and Modify for the account, finally click OK.

Then you can restart your SASS Service successfully.

I think this is SSAS package cliche at SQL Server 2012 with BI option. It seemed Microsoft never thought to fix it. :)

Deploy your SSRS reports to Report Server through Report Manager

Deploying your SSRS reports using Report Manager has various difference than using BIDS/SSDT. Its admin centered not developer centered. With BIDS, you can set data source directory and report directory within project properties. Using Report Manager, however, you need to create those directories by yourself.

• First, Go to Report Manager URL using Reporting Services Configuration Manager. e.g. http://SERVERNAME/reports.

• Second, Click the New Folder button, following screen will popup.

• Third, Fill the Name textbox, e.g. Report Manager Deployment Example and click OK. The Report Manager now displays the new folder. 

• Fourth, Click on the folder Report Manager Deployment Example to enter the folder, and then click on the above New Data Source button to create and configure your data source as follows. 

The following are the main points about creating a data source:

1. You have to enter the connection string; there is no helper dialog
2. The Connect using option selected is Windows integrated security; this means the report executes using the Windows credentials of the caller; the caller must have at least read permission on the database
3. The Credential stored securely in the report server option along with checking Use as Windows credentials when connecting to the data source is a good choice when you don't want to give the users running the report read permission on the database; rather you specify User name as DOMAIN\USERNAME and give just that account read permission on the database

• Fifth, After clicking OK to finish creating your data source, click the Upload File button to deploy a report. Fill in the dialog as shown below by navigating to the report that you want to deploy. I navigated to the folder where our BIDS project was saved and picked the file ReportDesignerExample.rdl (reports are stored in XML files with a .RDL extension): 

• After clicking OK to deploy the report, you can click on the report in the Report Manager to run it.  

Resouce:
http://www.sqlcircuit.com/2012/11/ssrs-different-ways-of-deploying-reports.html
https://www.mssqltips.com/sqlservertutorial/247/report-manager/

How to resolve SSRS Report URL Access Denied Error

During the process of report deployment, you want to publish your finished reports and thus you go to SSRS Configuration Manager, and choose Web Service URL on the left pane, and then click on the URL link on the right side. A new web browser page would popup and your report hub main page will appear. Everything worked just as expect. However, If you copy the URL link of your report main page and close the web page, and open a new web page, paste the copied URL link. Guess what would happen? You would suffer SSRS Report Service ERROR like this: "The permissions granted to user 'your domain\your user name' are insufficient for performing this operation. (rsAccessDenied)".

Why this happen? The first time worked fine because the windows account used by SSRS Configuration Manager was used to access the report main page. However, if you close the web page, and opened a new one to access the same report main page, the underline windows account, which is your own  account, will be used instead, and this user doesn't have any access right to the report. so the above error will surely happen.

How to resolve this common error?

Open SSRS Configuration Manager, and choose Report Manager URL on the left pane, click on the URL link on the right side and a new web page would popup to show your Report Manager URL.

Click on the Folder Setting on above buttons.

Click on the New Role Assignment, and enter yourdoman/yourname showed on above error, and check all available roles.

Now your account has been assigned enough roles to run the reports.

Deploy your SSRS reports to Report Server through BIDS/Report Builder/SQL Server Data Tools

After you finish your SSRS report, you need to deploy it to let other users to use it. You can use the built-in deployment features in SQL Server Data Tools (SSDT) to publish the reports to a report server.

Before you can publish a report, you must have permission to do so. Permission is determined through role-based security that is defined by your report server administrator. Publishing operations are typically granted through the Publisher role.

SQL Server Data Tools (SSDT) provides project configurations for managing report publication. The configuration specifies the location of the report server, the version of SQL Server Reporting Services installed on the report server, whether the data sources published to the report server are overwritten and so forth.

Right click on Solution of your project name upon Solution Explorer, and choose Properties, following window will popup.

By default, SQL Server Data Tools (SSDT) provides three project configurations: DebugLocal, Debug, and Release. The default configuration is DebugLocal. You typically use the DebugLocal configuration to view reports in a local preview window, the Debug configuration to publish reports to a test server, and the Release configuration to publish reports to a production server.

Right click on your project name upon Solution Explorer, and choose Properties, following window will popup.

Go to the report server URL to check whether Report server URL is working fine or not.

Right click on your project name upon Solution Explorer, choose Deploy,

To verify whether report deployed successfully or not, go to Report server URL click on the My Report folder link and run the report.

Source:
https://msdn.microsoft.com/en-ca/library/ms159270(v=sql.120).aspx
http://www.sqlcircuit.com/2012/11/ssrs-different-ways-of-deploying-reports.html

Surrogate key

Surrogate key is a key that has no business meaning. It is used to be compared with natural key such as SIN. For example, you create AddressID for Address table, which is meaningless, thus an  example of surrogate key. 

Useful Casino Terms

The handle: The total amount of money bet.

Buy-in: Amount of cash used to play a game.

Comp: Short for complimentary; a freebie offered by the casino for previous play. Comps are special gifts and services given out by casino to encourage players to gamble. Note play more and longer would give more is the no.1 principle. Comps include free alcohol, beverages, meals, and even attached hotel rooms, free airfare, limo rides, and show tickets.

Drop: Money used to buy chips at the gaming tables and dropped into the Drop box.

Bet: A wager on an unknown outcome; the wager is at risk of being lost.

Gross Winnings: Total return on wagers including the original bet.

Wagering Requirement: A condition applied to a promotion that affects how a player is able to spend the winnings generated by that promotion. For example, a $50 Sign Up Token has a twenty times wagering requirement, therefore, wagering requiremet is 20x50 = 1000.

Held Funds: Monty used to play eligible games but cannot be withdrawn from a player's account until the wagering requirement is met.

High rollers: A gambler who wagers large amount of money.

Useful SQL Server Scripts

List names of all tables in a SQL Server 2012 schema

For MySQL SHOW TABLES;

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'schema_name';

Use schema_name; 
Select * from Information_Schema.Tables
-- Where Table_Type = 'BASE TABLE' /*Filtered out View*/
Order by Table_Name;

Retore SQL Server 2012 database from different database with different filenames and locations

If you have one existing database, now you want to create another database, you can backup the first one, and use the backup file to create another database.

• First, open SSMS under Administrator.

• Second, using SSMS to backup the existing database. 

• Third, copy the created backup file to the directory that your new database account have full accessing privilege. For example, if your new database instance is named as dw_dev, it should be by default C:\Program Files\Microsoft SQL Server\MSSQL11.dw_dev\backup\mydb.bak, unless you changed the default directory to somewhere else.  Note you should copy your DB backup file here because this is the only place your database account have full access privilege.
  
  You can also use SQL Server Configuration Manager to get the account name of your database service. And you can add the full access privilege for the specific directory of the backup file to the account name so that you don't need to do above copy.

• Forth, using following script to get physical file names for the data and log files of your database file.

RESTORE FILELISTONLY FROM DISK = 'C:\AdventureWorks.BAK'
GO

Query result would be like:
LogicalName  PhysicalName
mydb              D:\Data\SQLEXPRESS\mydb.mdf
mydb_log       D:\Data\SQLEXPRESS\mydb_log.ldf

• Fifth, using following script to get physical file names for the data and log files of your database file.
  
  Note even you used Restore, there should be no same new database exist, otherwise, you need to delete the database before executing the following script.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH MOVE 'AdventureWorks_Data' TO 'G:\SQLData\AdvnetureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'H:\SQLData\AdvnetureWorks_Log.ldf'
GO

Resource: https://www.mssqltips.com/sqlservertutorial/122/retore-sql-server-database-to-different-filenames-and-locations/

Configuring SQL Server 2012 remote connection

After you successfully installed SQL Server 2012, you need to configure it to enable remote connection.

First, you need to set UDP port for SQL Server 2012. if you have multiple instances on your SQL Server, you need to have individual port for each instances.

• Run SQL Server Configuration Manager.

• Go to SQL Server Network Configuration, choose your sql instance.

• Click on the TCP/IP from the right pane, and select Properties

• Verify that, under IP2, the IP Address is set to the computer's IP address on the local subnet, and the Enabled is set to Yes for each connection.

• Under IPAll, make sure that TCP Port is set to 1433. Here 1434 is used because 1433 has been assigned to another instance.

• When you want to connect to SQL Server 2012 instance from your local remotely, using IP address and port instead of named instance.

SQL Server Data Warehouse and Index Statistics

During the data loading of data warehouse, huge amount of source data were poured into and after it's finished, you need to redo index statistics to gain high query performance.

Statistics are automatically created for each index key you create.

If the database setting autocreate stats is on, which is by default setting, then SQL Server will automatically create statistics for non-indexed columns that are used in queries.

You can go to your Statistics node in your SSMS, right click > Properties, then go to Details. Below is a sample of the stats and histogram that’s collected for one of the tables in my database.

Another option is you can use DBCC SHOW_STATISTICS WITH HISTOGRAM

The default settings in SQL Server are to autocreate and autoupdate statistics.

Notice that there are two (2) options with the Auto Update statistics.

• Auto Update Statistics Asynchronously on the other hand means, if there is an incoming query but statistics are stale, SQL Server uses the stale statistics to generate the execution plan, then updates the statistics afterwards.

• Auto Update Statistics basically means, if there is an incoming query but statistics are stale,SQL Server will update statistics first before it generates an execution plan.

You can check statistics comparing your “Actual Number of Rows” and “Estimated Number of Rows”. If these numbers are (consistently) fairly close, then your statistics are up-to-date and used by the optimizer for the query. If not, time for you to re-check your statistics create/update frequency.

There may be cases when you want to disable statistics update temporarily while you’re doing massive updates on a table, and you don’t want it to be slowed down by the autoupdate.

Source: http://blog.idera.com/sql-server/understanding-sql-server-statistics/

Comparing SQL Server 2012 Versions

	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
	Cross-Box Scale Limits
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine)1	Operating System maximum	Limited to lesser of 4 Sockets or 16 cores	Limited to lesser of 4 Sockets or 16 cores	Limited to lesser of 4 Sockets or 16 cores	Limited to lesser of 1 Socket or 4 cores	Limited to lesser of 1 Socket or 4 cores	Limited to lesser of 1 Socket or 4 cores
Maximum Compute Capacity Used by a Single Instance (Analysis Services, Reporting Services) 1	Operating system maximum	Operating system maximum	Limited to lesser of 4 Sockets or 16 cores	Limited to lesser of 4 Sockets or 16 cores	Limited to lesser of 1 Socket or 4 cores	Limited to lesser of 1 Socket or 4 cores	Limited to lesser of 1 Socket or 4 cores
Maximum memory utilized (SQL Server Database Engine)	Operating system maximum	64 GB	64 GB	64 GB	1 GB	1 GB	1 GB
Maximum memory utilized (Analysis Services)	Operating system maximum	Operating system maximum	64 GB	N/A	N/A	N/A	N/A
Maximum memory utilized (Reporting Services)	Operating system maximum	Operating system maximum	64 GB	64 GB	4 GB	N/A	N/A
Maximum relational Database size	524 PB	524 PB	524 PB	524 PB	10 GB	10 GB	10 GB
	High Availability (AlwaysOn)
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Server Core support1	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Log Shipping	Yes	Yes	Yes	Yes
Database mirroring	Yes	Yes (Safety Full Only)	Yes (Safety Full Only)	Witness only	Witness only	Witness only	Witness only
Failover Clustering	Yes (Node support: Operating system maximum	Yes (Node support: 2)	Yes (Node support: 2)
Backup compression	Yes	Yes	Yes
Database snapshot	Yes
AlwaysOn Availability Groups	Yes
SQL Server Multi-Subnet Clustering	Yes
Mirrored backups	Yes
Hot Add Memory and CPU2	Yes
Database Recovery Advisor	Yes	Yes	Yes	Yes	Yes	Yes	Yes
	Scalability and Performance
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Multi-instance support	50	50	50	50	50	50	50
Table and index partitioning	Yes
Data compression	Yes
Resource Governor	Yes
Partition Table Parallelism	Yes
Multiple Filestream containers	Yes
	Security
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Basic Auditing	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Fine Grained Auditing	Yes
Transparent database encryption	Yes
Extensible Key Management	Yes
User-Defined Roles	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Contained Databases	Yes	Yes	Yes	Yes	Yes	Yes	Yes
	Replication
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
SQL Server change tracking	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Merge replication	Yes	Yes	Yes	Yes (Subscriber only)	Yes (Subscriber only)	Yes (Subscriber only)	Yes (Subscriber only)
Transactional replication	Yes	Yes	Yes	Yes (Subscriber only)	Yes (Subscriber only)	Yes (Subscriber only)	Yes (Subscriber only)
Snapshot replication	Yes	Yes	Yes	Yes (Subscriber only	Yes (Subscriber only)	Yes (Subscriber only)	Yes (Subscriber only)
Heterogeneous subscribers	Yes	Yes	Yes
Oracle publishing	Yes
Peer to Peer transactional replication	Yes
	Management Tools
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
SQL Management Objects (SMO)	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SQL Configuration Manager	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SQL CMD (Command Prompt tool)	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SQL Server Management Studio	Yes	Yes	Yes	Yes	Yes	Yes
Distributed Replay – Admin Tool	Yes	Yes	Yes	Yes	Yes	Yes
Distributed Replay – Client	Yes	Yes	Yes	Yes
Distributed Replay – Controller	Yes (Enterprise supports up to 16 clients, Developer supports only 1 client)	Yes (1 client support only)	Yes (1 client support only)	Yes (1 client support only)
SQL Profiler	Yes	Yes	Yes	No2	No2	No2	No2
SQL Server Agent	Yes	Yes	Yes	Yes
Microsoft System Center Operations Manager Management Pack	Yes	Yes	Yes	Yes
Database Tuning Advisor (DTA)	Yes	Yes	Yes3	Yes3
	RDBMS Manageability
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
User Instances					Yes	Yes	Yes
LocalDB					Yes	Yes
Dedicated admin connection	Yes	Yes	Yes	Yes	Yes (under trace flag)	Yes (under trace flag)	Yes (under trace flag)
PowerShell scripting support	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SysPrep support1	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Support for Data-tier application component operations – extract, deploy, upgrade, delete	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Policy automation (check on schedule and change)	Yes	Yes	Yes	Yes
Performance data collector	Yes	Yes	Yes	Yes
Able to enroll as a managed instance in a multi-instance management	Yes	Yes	Yes	Yes
Standard performance reports	Yes	Yes	Yes	Yes
Plan guides and plan freezing for plan guides	Yes	Yes	Yes	Yes
Direct query of indexed views (using NOEXPAND hint)	Yes	Yes	Yes	Yes
Automatic indexed view maintenance	Yes	Yes	Yes	Yes
Distributed partitioned views	Yes	Partial. Distributed Partitioned Views are not updatable	Partial. Distributed Partitioned Views are not updatable	Partial. Distributed Partitioned Views are not updatable	Partial. Distributed Partitioned Views are not updatable	Partial. Distributed Partitioned Views are not updatable	Partial. Distributed Partitioned Views are not updatable
Parallel indexed operations	Yes
Automatic use of indexed view by query optimizer	Yes
Parallel consistency check	Yes
SQL Server Utility control point	Yes
Contained Databases	Yes	Yes	Yes	Yes	Yes	Yes	Yes
	Development Tools
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Microsoft Visual Studio Integration	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SQL Server Developer Studio	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Intellisense (Transact-SQL and MDX) 1	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SQL Server Data Tools (SSDT)	Yes	Yes	Yes	Yes	Yes
SQL query edit and design tools1	Yes	Yes	Yes
Version control support1	Yes	Yes	Yes
MDX edit, debug, and design tools1	Yes	Yes	Yes
	Programmability
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Common Language Runtime (CLR) Integration	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Native XML support	Yes	Yes	Yes	Yes	Yes	Yes	Yes
XML indexing	Yes	Yes	Yes	Yes	Yes	Yes	Yes
MERGE & UPSERT Capabilities	Yes	Yes	Yes	Yes	Yes	Yes	Yes
FILESTREAM support	Yes	Yes	Yes	Yes	Yes	Yes	Yes
FileTable	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Date and Time datatypes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Internationalization support	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Full-text and semantic search	Yes	Yes	Yes	Yes	Yes
Specification of language in query	Yes	Yes	Yes	Yes	Yes
Service Broker (messaging)	Yes	Yes	Yes	No (Client only)	No (Client only)	No (Client only)	No (Client only)
Web services (HTTP/SOAP endpoints)	Yes	Yes	Yes	Yes
TSQL endpoints	Yes	Yes	Yes	Yes
	Integration Services
Feature	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
SQL Server Import and Export Wizard	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Built-in data source connectors	Yes	Yes	Yes	Yes	Yes	Yes	Yes
SSIS designer and runtime	Yes	Yes	Yes
Basic Transforms	Yes	Yes	Yes
Basic data profiling tools	Yes	Yes	Yes
Change Data Capture Service for Oracle by Attunity	Yes
Change Data Capture Designer for Oracle by Attunity	Yes
	Integration Services – Advanced Adapters
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
High performance Oracle destination	Yes
High performance Teradata destination	Yes
SAP BW source and destination	Yes
Data mining model training destination adapter	Yes
Dimension processing destination adapter	Yes
Partition processing destination adapter	Yes
Change Data Capture components by Attunity	Yes
Connector for Open Database Connectivity (ODBC) by Attunity	Yes
	Integration Services – Advanced Transforms
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Persistent (high performance) lookups	Yes
Data mining query transformation	Yes
Fuzzy grouping and lookup transformations	Yes
Term extractions and lookup transformations	Yes
	Master Data Services
Feature	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Master Data Services database	Yes	Yes
Master Data Manager web application	Yes	Yes
	Data Warehouse
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Create cubes without a database	Yes	Yes	Yes
Auto-generate staging and data warehouse schema	Yes	Yes	Yes
Change data capture	Yes
Star join query optimizations	Yes
Scalable read-only Analysis Services configuration	Yes
Parallel query processing on partitioned tables and indices	Yes
xVelocity memory optimized columnstore indexes	Yes
	Analysis Services
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Scalable Shared Databases (Attach/Detach, Read only databases)	Yes	Yes
High Availability	Yes	Yes	Yes
Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL)	Yes	Yes	Yes
	BI Semantic Model (Multidimensional)
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Semi-additive Measures	Yes	Yes	No1
Hierarchies	Yes	Yes	Yes
KPIs	Yes	Yes	Yes
Perspectives	Yes	Yes
Actions	Yes	Yes	Yes
Account intelligence	Yes	Yes	Yes
Time Intelligence	Yes	Yes	Yes
Custom rollups	Yes	Yes	Yes
Writeback cube	Yes	Yes	Yes
Writeback dimensions	Yes	Yes
Writeback cells	Yes	Yes	Yes
Drillthrough	Yes	Yes	Yes
Advanced hierarchy types (Parent-Child, Ragged Hiearchies)	Yes	Yes	Yes
Advanced dimensions (Reference dimensions, many-to-many dimensions	Yes	Yes	Yes
Linked measures and dimensions	Yes	Yes
Translations	Yes	Yes	Yes
Aggregations	Yes	Yes	Yes
Multiple Partitions	Yes	Yes	Yes, up to 3
Proactive Caching	Yes	Yes
Custom Assemblies (stored procs)	Yes	Yes	Yes
MDX queries and scripts	Yes	Yes	Yes
Role-based security model	Yes	Yes	Yes
Dimension and Cell-level Security	Yes	Yes	Yes
Scalable string storage	Yes	Yes	Yes
MOLAP, ROLAP, HOLAP storage modes	Yes	Yes	Yes
Binary and compressed XML transport	Yes	Yes	Yes
Push-mode processing	Yes	Yes
Direct Writeback	Yes	Yes
Measure Expressions	Yes	Yes
	BI Semantic Model (Tabular)
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Hierarchies	Yes	Yes
KPIs	Yes	Yes
Semi-additive Measures	Yes	Yes
Perspectives	Yes	Yes
Translations	Yes	Yes
DAX calculations, DAX queries, MDX queries	Yes	Yes
Row-level Security	Yes	Yes
Partitions	Yes	Yes
In-Memory and DirectQuery storage modes (Tabular only)	Yes	Yes
	PowerPivot for SharePoint
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
SharePoint farm integration based on shared service architecture	Yes	Yes
Usage reporting	Yes	Yes
Health monitoring rules	Yes	Yes
PowerPivot Gallery	Yes	Yes
PowerPivot Data Refresh	Yes	Yes
PowerPivot Data Feeds	Yes	Yes
	Data Mining
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Standard Algorithms	Yes	Yes	Yes
Data Mining Tools (Wizards, Editors Query Builders)	Yes	Yes	Yes
Cross Validation	Yes	Yes
Models on Filtered Subsets of Mining Structure Data	Yes	Yes
Time Series: Custom Blending Between ARTXP and ARIMA Methods	Yes	Yes
Time Series: Prediction with New Data	Yes	Yes
Unlimited Concurrent DM Queries	Yes	Yes
Advanced Configuration & Tuning Options for Data Mining Algorithms	Yes	Yes
Support for plug-in algorithms	Yes	Yes
Parallel Model Processing	Yes	Yes
Time Series: Cross-Series Prediction	Yes	Yes
Unlimited attributes for Association Rules	Yes	Yes
Sequence Prediction	Yes	Yes
Multiple Prediction Targets for Naïve Bayes, Neural Network and Logistic Regression	Yes	Yes
	Reporting Services Features
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Supported catalog DB SQL Server edition	Standard or higher	Standard or higher	Standard or higher	Web	Express
Supported data source SQL Server edition	All SQL Server editions	All SQL Server editions	All SQL Server editions	Web	Express
Report server	Yes	Yes	Yes	Yes	Yes
Report Designer	Yes	Yes	Yes	Yes	Yes
Report Manager	Yes	Yes	Yes	Yes	Yes
Role based security	Yes	Yes	Yes	Yes	Yes
Word Export and Rich Text Support	Yes	Yes	Yes	Yes	Yes
Enhanced gauges and charting	Yes	Yes	Yes	Yes	Yes
Export to Excel, PDF, and Images	Yes	Yes	Yes	Yes	Yes
Custom authentication	Yes	Yes	Yes	Yes	Yes
Report as data feeds	Yes	Yes	Yes	Yes	Yes
Model support	Yes	Yes	Yes	Yes
Create custom roles for role-based security	Yes	Yes	Yes
Model Item security	Yes	Yes	Yes
Infinite click through	Yes	Yes	Yes
Shared component library	Yes	Yes	Yes
Email and file share subscriptions and scheduling	Yes	Yes	Yes
Report history, execution snapshots and caching	Yes	Yes	Yes
SharePoint Integration	Yes	Yes	Yes
Remote and non-SQL data source support1	Yes	Yes	Yes
Data source, delivery and rendering, RDCE extensibility	Yes	Yes	Yes
Data driven report subscription	Yes	Yes
Scale out deployment (Web farms)	Yes	Yes
Alerting2	Yes	Yes
Power View2	Yes	Yes
	Reporting Services : Report Server Database Server Edition Requirements
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Use this edition of the Database Engine instance to host the database	Standard, Business Intelligence Enterprise, editions (local or remote)	Standard, Business Intelligence Enterprise, editions (local or remote)	Standard, Enterprise editions (local or remote)	Web edition (local only)	Express with Advanced Services (local only).
	Business Intelligence Clients
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Report Builder	Yes	Yes	Yes
Data Mining Addins for Excel and Visio 2010	Yes	Yes	Yes
PowerPivot for Excel 2010	Yes	Yes
Master Data Services Add-in for Excel	Yes	Yes
	Spatial and Location Services
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Spatial indexes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Planar and Geodetic datatypes	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Advanced spatial libraries	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Import/export of industry-standard spatial data formats	Yes	Yes	Yes	Yes	Yes	Yes	Yes
	Additional Database Services
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
SQL Server Migration Assistant	Yes	Yes	Yes	Yes	Yes	Yes	Yes
Database mail	Yes	Yes	Yes	Yes
	Other Components
Feature Name	Enterprise	Business Intelligence	Standard	Web	Express with Advanced Services	Express with Tools	Express
Data Quality Services	Yes	Yes
StreamInsight	StreamInsight Premium Edition	StreamInsight Standard Edition	StreamInsight Standard Edition	StreamInsight Standard Edition
StreamInsight HA	StreamInsight Premium Edition

Source: http://letrasandnumeros.com/2012/12/10/sql-server-2012-enterprise-vs-business-intelligence-vs-standard-vs-web-vs-express-with-advanced-services-vs-express-with-tools-vs-express/