Friday, November 17, 2017

SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function

A while ago I ran into an issue when creating an SSRS report. It was a bit of a cryptic error message for me at first, as you can see below. After struggling with it for a while I finally found the issue and I thought I could as well share it so you can find it faster than I did. :)

Problem

When running my report I encountered this error message. It talks about a Hidden expression of Tablix1, and that the scope parameter used is not valid. After finding the solution the message was kind of clear to me, but the thing is, when running the report at first I was a little lost.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Report error

Solution

It is very obvious the message is about Tablix1, so I start with looking at aggregate functions I am using in that tablix. Finally, I end up checking the visibility of the tablix and I notice the expression for the display option of the tablix.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Display options

This feature is used to show or hide the tablix based on an expression. You can see the expression I used below.

nickyvv.com - SSRS: The Hidden Expression For the Tablix Has a Scope Parameter That Is Not Valid For an Aggregate Function - Expression Editor

As the Expression dialog states, you have to Set the expression for: Hidden. This actually means the following: if the expression evaluates to true, the tablix will be hidden. So if the expression evaluates to false, the tablix will be shown.
The problem however, is in my scope parameter: “DataSet1”. This scope parameter is optional for an aggregate function that can evaluate the aggregate function over that parameter. It can be a Row or Column group, a data region or (as it is here) a dataset.

TL;DR

The problem is, when I started with the report I entered this expression, but afterwards I altered the name of the dataset. This makes the above Hidden expression for the tablix invalid because Dataset1 doesn’t exist anymore.
So after updating the name of my dataset in the expression for the tablix my report ran just fine again.


If you have any remarks or questions, feel free to contact me on Twitter:
@NickyvV

Friday, November 3, 2017

SSMS Status Bar Colors

I want to share this quick post for setting the color of the status bar in Management Studio (SSMS).

This post is also part of the #SQLNewBlogger challenge that Ed Leighton-Dick (@eleightondick | b) started back in April 2015:

TL;DR – In April, I’m challenging myself to write (and publish!) here regularly, and I’m extending an open challenge to other new bloggers to do the same.

You can read about his idea and the origin in his blog post, for my post on SSMS keep on reading here!


In SSMS, you can go to Tools > Options > Text Editor > Editor Tab and Status Bar.

image

In the Status Bar Layout and Colors part, you can set the default colors for Single server connections (the standard) and Group connections. The colors you set here are for all connections when you don’t explicitly set another color for the current connection

One-time Connection Color

When you want to set a different color for a one-time connection you can do so in the Connect to Server dialog:

Nickyvv.com - SSMS Status Bar Color - ConnectToServer

Click on Options >> and go to the Connection Properties tab.

Nickyvv.com - SSMS Status Bar Color - ConnectionProperties

In the bottom, select User custom color: and click on Select… You can select your own color there
Just keep in mind that setting the property here will keep it until you deselect it the next time. So if you don’t want this color to be applied to all subsequent connections you make, you’ll have to uncheck the box the next time you connect to a database via this connection box.

Registered Server connection

Instead of connecting to a specific database via the Connect to Server dialog, you can “save” a server/connection as a registered server. You can do this via Registered Servers > New Server Registration. On the Connection Properties tab you can also apply a custom color here as you see in the picture below, so you don’t have to undo applying the color when you’re connecting to other servers.

clip_image001


If you like this type of basic posts be sure to check out the #SQLNewBlogger hashtag on twitter.

@NickyvV

Friday, October 20, 2017

Suggested Reading for October 20, 2017

I came across some nice articles and blog posts in the past weeks I wanted to share:
  • The purpose of this series is to help others succeed.  The authors have all ridden the elevator of success to the top and received their MVP awards to reflect that fact.  Now it is our responsibility to send that elevator back down so others may benefit from our experience.  As an indication of our intent, all profits from the sale of this book are to be donated to charities.

I hope you can get something useful out of here, feel free to let me know if you find other useful links that I should include next time!

@NickyvV

Friday, September 29, 2017

Saying Goodbye to Pulse and Hello to Van Lanschot

I'm starting next monday, October 2nd, at Van Lanschot (@VanLanschot | web) in Den Bosch. Unfortunately I didn't have any holidays left so yesterday I had my last day at Pulse, and today I'm attending SQLSaturday Holland (which will have a separate post) where I can meetup with some SQLFamily! Then one day to switch contexts and get started on Monday: I'm very excited!

Pulse
I have to say I enjoyed my time at Pulse. It therefore wasn't an easy decision to leave. I really liked my colleagues and the opportunities they gave me. But recently I discovered that the time I spend commuting just isn't worth it anymore. I like what I do at work, but I love my family more.

Van Lanschot
That's when this opportunity came along. It's a job internally at Van Lanschot, so no consultancy for different clients anymore. I'm stationed in Den Bosch, around 25 minutes from home!
I will be a Business Intelligence Consultant at the Data Management team, and will (still) mainly be working with Microsoft technologies like SQL Server and Power BI.

Thursday, July 27, 2017

Book Review: The War of Art by Steven Pressfield

My Reading Journey

A while ago I created a new page on my blog: My Reading Journey. On this page I’m keeping track of the books I read throughout the year. There are around a dozen books on it that I read in previous years. Starting this year, and maybe I’ll cover a few other books I already read, I’m going to do a short review of the books. Or at least the ones that are related to my professional career.

I recently completed reading The War of Art: Break Through the Blocks and Win Your Inner Creative Battles by Steven Pressfield.

My 3-word summary: Beating resistance & procrastination

Theme

The War of Art tries to help readers overcome Resistance. It comes in many flavors as you can see in the mind map I created. The author gives various ways of beating through this resistance and ultimately getting things done.

nickyvv.com - Book review The War of Art

Opinion

I really liked the book, especially the first two parts of the book where resistance is explained and what I can do to beat resistance and turn into a pro. I think I already am a pro… in procrastination. I have to be honest and admit that many of the properties of resistance where quite familiar to me.
Since I started planning my daily activities a bit more I see I can be much more productive. Also focus on finishing one task at a time. Just set out what you want to achieve for the day and work towards that with priorities. You can use the Pomodoro technique if that works for you, I use Pomodairo for that. But the point is: just use anything that makes you more productive, whether it’s a tool, a framework or just common sense.

In the third part the author gives his opinion as to what he believes is the reason that I can overcome my fears.At some points he lost me a little bit, just because I don’t think there’s anything “in a higher realm”.

Author

Steven Pressfield also wrote Do the Work and Turning Pro, similar books to this, all centered around the topics of resistance, prioritization, creativity and empowerment, although this was his first nonfiction book. He wrote quite a few novels before this one (e.g. The Legend of Bagger Vance was adapted for screen, starring Matt Damon, Will Smith and Charlize Theron a.o.).
The title is not to be confused with The Art of War, written by Sun Tzu in the 6th century BC which is said by many to be the decisive work on military strategies and tactics of its time.

Thursday, July 20, 2017

TFS: Undo Check-out From Other User

Out-of-the-box, there is no functionality to undo check-outs from other users in Team Foundation Services. But there is an option with TFS Power Tools for VS, available from VS 2012.

  1. Go to Tools and Extensions and Updates.
  2. In the Online Visual Studio Gallery search for Power Tools
  3. Download the extension, close VS and install the extension.

Wednesday, May 31, 2017

Power BI Report Server

I just want to share this short post with anyone that is not familiar with the SQL Server Slack channel at: https://sqlcommunity.slack.com.
If you want to join the Slack channel, visit Brent’s (@BrentO) post: How to Join the SQL Server Community Chat in Slack.

With the introduction of Power BI Report Server on May 3rd, a lot of questions arose.

The particular question that started the thread I want to share was whether it is possible to upgrade an existing SSRS instance to Power BI Report Server. You can read Adam Saxton’s (@GuyInACube) answer to this and other interesting questions in the screenshot below.

nickyvv.com - Power BI Report Server Slack Channel

Friday, April 21, 2017

SSDT Error Message: Unable To Cast COM Object of Type…

Just a short blog post today explaining a problem (and solution) I ran into when working at a client recently. Hopefully it is helpful for anyone out there.

Problem

I recently got this error message at a client when trying to open an SSIS solution in SSDT.

Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSObject100'

The reason could be a badly applied Service Pack or CU for example, but I’m not entirely sure about that (source).

Solution

Before starting, make sure you close any open Visual Studio instance.
In a Command Prompt go to the following path (be sure to start the Command Prompt as Administrator):

C:\Program Files\Microsoft SQL Server\120\DTS\Binn\

where 120 is you SQL Server version number.
Reregister the dts.dll by executing the command:

regsvr32 dts.dll

image

If all goes well you should see the following message pop up:

image

I found info here.

Happy coding :)

Tuesday, April 11, 2017

T-SQL Tuesday #89: The Times They Are a-changing

TSQL2sDay150x150

This months T-SQL Tuesday is hosted by Koen Verbeeck (@Ko_Ver) on the topic how the cloud will change and maybe has already changed our jobs as data professionals.
This idea was inspired by Kendra Little’s (@Kendra_Little) blog post Will the Cloud Eat My DBA Job? I listened to the podcast version of her post and here are my 2 cents to the party.

 

Monday, March 20, 2017

Dynamics 365 Tech Conference 2017 Keynote day 1

I finally came around posting the first blog of the D365 Tech Conference. I’ll be posting some more content and an overview of the conference along the next few days.

Points covered

Roadmap AX from 90's to 2017+

Challenges in ERP deployments

  • Change
  • Business process
  • Data quality

clip_image001

Customer showcase: Maersk Container Industries
Implementation in China, Chile and Denmark with 500+ users.

Customer showcase: Peet (Australia).
Financial management with Power BI and MR.

 

D365fO roadmap

Fall 2016 release
Analytics and mobile experience
10 workspaces with PBI dashboards
Mobile expense reports

Spring 2017 release
Common Data Service updates

clip_image002


View into product insights
with Olga Mulvad:
IoT, Social listening, PBI
Product Explorer, impl. with CDS

Deployment options

  • Cloud
  • Cloud + edge (hybrid)
  • Local business data

Demo: Operations take place in Edge, eveything can be viewed and reported in the cloud

Friday, March 10, 2017

Dynamics 365 Tech Conference Here I Come

nickyvv.com---Dynamics-365-Tech-Conf[1]

Conference booked: check!
Conference scheduled build: check!
Hotel booked: check!
Bags packed: check!
Flight checked-in: check!
Devices charged: check!
Power converter: check!
Excited? Check!

Really excited?! Check check!

I’m all set! Just trying to get some sleep tonight would be great. My flight goes at 10 AM, but my colleagues pick me up around 5 AM(!), so it’s going to be a short night! We’ll be arriving tomorrow around noon (local time) in Seattle so it’s gonna be a long day tomorrow.

I wish everyone that’s also going a very good conference, make the most out of it for yourself!
If you like, you can reach me on Twitter @NickyvV and we can grab a cup of coffee somewhere.

Tuesday, January 31, 2017

The Dynamics 365 Tech Conference 2017



I was very excited when I received an e-mail from my employer a few weeks ago which stated I am one of the 5 people that have the chance to go to the Dynamics 365 Tech Conference 2017! I immediately checked my agenda and I would be home just in time for my wife’s birthday, so that’s a go! The location will be the same as last year: Seattle. The conference will be held from March 12-15 and I will be going with 4 other colleagues from Pulse and 3 from AXtension.


nickyvv.com - Dynamics 365 Tech Conference 2017
There will be a business consultant, two functional consultants and a developer joining me, so we’ll make up a nice mixed group. We will have a short tuning session before we leave to make sure we aren’t all in the same rooms together to make the most of the sessions.
I am particularly interested in the Reporting and Analytics track about different reporting tools and Power BI, but also in the Entity Store, Continuous Delivery and the Common Data Model (or Common Data Service) in general.


In the next months I hope I find the time to blog some more about the upcoming conference, I will definitely record my findings during and after the conference!

The flights have been booked and the ESTA application process has been completed. I just hope there will not be any more restrictions like the recent ones the @POTUS declared. We will arrive on the Saturday prior to the event so we’ll have a little spare time left to discover Seattle. I heard the Boeing Museum and the Space Needle are good for a visit. If you have any other recommendations please let me know.
If you’re also going, do let me know in the comments or on Twitter (@NickyvV), I hope to see you there!