SSRS: Easily calculate the difference between two fields/textboxes with an expression

This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:

=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value

SSRS-difference-between-two-textboxes-fields-values-variances-expression
Simply lookup the textbox’ name in the Properties-window:

SSRS-difference-between-two-textboxes-fields-values-variances-textbox-properties-name
This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.

Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.








SSRS – Dataset: (Warning: Possible performance impact)

Today I just looked at it I guess, SSRS is actually complaining a lot:

Dataset: (Warning: Possible performance impact)

Every report @parameter that refers to a cube-related dataset somehow warns you every time…but why? Anyone an idea?
Even Google couldn’t give me an answer on that…

The cube’s performance is great, especially when just selecting a subset of values from a very small dimension (< 15 records).

ssrs-dataset-warning-possible-performance-impact

Any idea why SSRS does that??
Leave a comment!








MDX Cheat Sheet – Calculated Measures

Here’s an addition to the MDX Cheat Sheet I posted earlier:
https://clinthuijbers.wordpress.com/2014/05/03/mdx-cheat-sheet/

But this one focusses on SSAS/MDX Calculated Measures (e.g. Full Year SUM() or YTD())

Hierarchy:
For the demo, I’m using a Year(2014)/Month(201401) hierarchy in my SSAS cube which looks like:

Dim Period Hierarchy

How it looks like:
MDX Cheat Sheet - Calculated Measures - MDXCode1
MDX Cheat Sheet - Calculated Measures - MDXCode2

Here are the results:
MDX Cheat Sheet - Calculated Measures

Download it!:
MDX Cheat Sheet (Calculated Measures)








AdventureWorks 2014 download (link)

Microsoft released the new AdventureWorks for SQL Server 2014.


By default, sample databases and sample code are not installed during the installation of SQL Server and you have to search, find and download the sample databases.

Sample databases

On Codeplex you can download the AdventureWorks databases for SQL Server.

Relational Databases (OLTP):
AdventureWorks2012 Data File
AdventureWorks 2012 OLTP Script
AdventureWorks2012-Full Database Backup.zip
AdventureWorks2012_Data.zip
AdventureWorks2012 CS Data File
AdventureWorks 2012 CS OLTP Script
AdventureWorksLT2012_Data
AdventureWorks 2012 LT Script

Databases (DW)
AdventureWorksDW2012 Data File
AdventureWorksDW2012Images

SSAS MD
AdventureWorks Multidimensional Models SQL Server 2012
Analysis Services Tutorial SQL Server 2012

SSAS Tabular
AdventureWorks Tabular Model SQL Server 2012
AdventureWorks Internet Sales Tabular Model SQL Server 201

Here are some other samples for SQL Server (thanks to Hennie de Nooijer):
SQL Server 2014 RTM In-Memory OLTP Sample. Samples of the new features of SQL Server : esource Governor IO, Online Operations, and the Columnstore Index.
Adventure Works for SQL Server 2012
SQLSkills databases
Arcane Code. Upgrade for SQL Server 2014
AdventureWorks for SQL Azure



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group








SSISDB 2012 – Catalog Indexing Recommendations

Hi there!

Noticed that you’re SSISDB reports are starting to get really slow and thus annoying?
Well…it’s time to fix that! Found this post by Phil Brammer that fixes that by creating new indexes:
http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/



ssisdb-reports-slow


More info by Microsoft on this issue:
http://support.microsoft.com/kb/2829948

Also…on my server the total execution time of the SQL Agent Job ‘[SSIS Server Maintenance Job]‘ (by ##MS_SSISServerCleanupJobLogin##) went from 125 to 22 minutes! Make sure to reorganize/rebuild the newly created indexes.

Data Compression
Don’t forget to apply data compression (which will save you storage and decrease your I/O thus increase performance)!
https://clinthuijbers.wordpress.com/2014/08/01/ssisdb-2012-data-compression/








SQL 2014, where is my BIDS?

Today I stumbled upon this post by Koen Verbeeck:
http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/mssqlserver/sql-2014-ctp1-where-is/

Oh wait sorry, I meant SSDT. Or was it SSDTBI? To avoid confusion about the developer tool to create BI solutions, Microsoft has changed its name a few times. You know, to make it less confusing. Here’s a nice overview:

BIDS or Business Intelligence Development Studio. The most non-confusing name of the bunch. This piece of software was delivered to you when you installed SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2.
SSDT or SQL Server Data Tools. Introduced with SQL Server 2012. However, you have a tool with the same name for creating database projects, which you had to download and install separately (don’t get me started on which names this tool had before. Mildly schizophrenic to say the least.) This development environment uses the Visual Studio 2010 shell and you use it to build BI solutions solely for SQL Server 2012. So two tools with the same name. One for database projects, one for BI projects. One to download, one that came with the SQL Server install media. Enter confusion. Read more about it on the blog of Jamie Thomson (blog | twitter): More SSDT naming confusion. Also read his blog posts about the database project part of SSDT, they are mighty interesting.
SSDTBI or SQL Server Data Tools – Business Intelligence. In an effort to swiftly put a stop to all this confusion, SSDTBI was released with Visual Studio 2012. It’s basically still BIDS – or SSDT from the SQL Server installation media – but now with BI appended to make its purpose more clear. This environment uses the Visual Studio 2012 shell obviously, but is still used to create BI solutions for SQL Server 2012. Great deal of fun when you have two developers on the same project: one with SSDT (Visual Studio 2010) and one with SSDTBI (Visual Studio 2012).
Each tool exists in two flavors: either you already have Visual Studio and the BI templates are installed into your existing installation, or you don’t have Visual Studio and SQL Server install a shell for you, only capable of creating BI solutions. Less confusing now? OK, let’s move on to the real point of this blog post: which development environment do we use in SQL Server 2014 CTP1? (Which is just released in case you missed it)



Join us at LinkedIn!
Don’t forget to join the SQL Server 2014 group on LinkedIn and stay updated!:
linkedin-sql-server-2014-group