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.

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.

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

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

In Action: Technical Preview of Power BI Reports in SSRS

Yesterday the SSRS team made the announcement that we can now consume Power BI reports inside Reporting Services. You can upload your Power BI files (.pbix) to the Report Manager, or deploy them to the Report Server from Power BI Desktop if you like.
To start off, I really like the features and the things you can do, however I also found some issues I will be talking about later.

How to get started

The SSRS team did a pretty good job describing How to get started with the Technical Preview, so I won't repeat that here, just read their blog.
A few caveats:

Seriously Microsoft #Azure, this error message is not helping. pic.twitter.com/wCFsSmZW2h

— Koen Verbeeck (@Ko_Ver) October 27, 2016

• Your virtual machine name in Azure needs to be all lowercase letters, in Chrome the error message displays correctly, but in IE the error message you receive is not very helpful, according to Koen.
• I used West Europe as my Azure Region, so I had to login to the RDP as WESTEUROPE\nvvroenhoven

After having gone through the setup of your Azure VM you'll have to wait 10-20 minutes for your VM to be provisioned and you're good to go.

What does it look like?

After logging in to your VM you can open the report manager via the SSRS Preview shortcut on the desktop and you end up with an overview of the available KPI's, Mobile reports and Power BI Desktop Reports available on the server:

Open a Power BI Desktop report

Clicking on one of the PBI Desktop Reports gives you the Power BI report (which was created with 1 page) in the report manager interface:

Edit a Power BI Report

You can edit the reports in Power BI Desktop by clicking on the 3 dots in the upper right corner of the report tile in the web portal and clicking "Edit in PowerBI Desktop". By the way, I believe there's a space missing between Power and BI :-).

When you open the report in PBI Desktop you also get a pop-up saying they're busy opening your report, the links in that pop-up are still on the ToDo list I guess because they are pointing to https://go.microsoft.com/TODO :-)

SSAS Live connection

As stated in an earlier announcement by the SSRS team: "This preview supports Power BI reports that connect "live" to Analysis Services models – both Tabular and Multidimensional (cubes). We plan to extend support to additional data sources in a future preview." You can see this by looking at the Data Source settings and in the lower right corner of the report in PBI Desktop. As a consequence of connecting live to SSAS you are not able to edit the data model or adjust any queries.

Saving a PBI Desktop file to SSRS

When you save the file you have the option of saving it to disk or directly to SSRS.

When you choose the option of SSRS make sure to type in http://localhost/reports instead of https for this VM.

If you've already saved it once to SSRS and click save (or CTRL+S) afterwards in PBI Desktop, it keeps this setting and saves it automatically to SSRS.

Manage a Power BI Report

You can also manage the report item with the following properties:

Manage Data sources

For now, only live connections to SSAS are supported, but that will certainly change in the future. You can also use credentials to connect to the SSAS instance, but for now I'll use the default settings. 

At my first attempt to test the connection I got an error message, the next attempts did work correctly, no idea why that happened.

Manage Security

You can also customize the security for a specific report, before doing so you get a confirmation dialog box to confirm your settings differing from the parent folder:

After I clicked Customize security, but didn't alter anything, I started to run into some issues I'll describe here.

Issues with the Technical Preview (released October 27th)

Test connection

I encountered this error when I tried to test the connection to the SSAS Tabular cube. The second attempt did work correctly. I made no changes to the settings, I only clicked around a bit.

Customize security

After clicking the button "Customize security" I tried opening the report (I renamed the report to Sample Sales Report 2) again but the Power BI logo kept displaying.

Opening the report in PBI Desktop also didn't work and resulted in the following error:

Also when I open the .pbix-file from explorer and try to save it to SSRS I get the following error:

Any of the other reports (where I didn't alter the security settings) opens and saves perfectly fine. So in my opinion these errors all relate to the same issue with the security settings.

After undoing the security changes, so clicking on "Use same security as parent folder" everything was working fine again.

Conclusion

All in all some great new features have been added! I bumped into some minor issues that I'm sure will be resolved in later versions.

Suggested reading: MDX Date Picker, MS Certification Paths, Dynamics 365

I found some posts, news and came across some issues you might be interested in.

---

I'm creating some reports for a client and am working with SSAS Tabular. I therefore have to deal with MDX based parameters and I wanted to have my date parameter to resemble a Date Picker as you normally would have when using SQL Server as a source:

• Calendar Date Picker for MDX based SSRS Reports on MSSQLTips.com

---

Microsoft has changed their MCSA and MCSE exams by aligning to industry-recognized areas of competence. There's no need anymore to recertify every (three) year, but you can re-earn you certification each year. Due to the fact that the technology changes faster than a few years ago, (SQL Server had changes in the way updates were rolled out earlier this year, Azure changes almost every week) they decided to switch to a more aligned model for certification.

Certification paths

The Data Management & Analytics Path is most appropriate for me and in detail looks like this:

What this means is you will have to earn one of the MCSA's listed here and then take one elective exam to earn the MCSE. You can then re-earn the MCSE each year by taking a new elective exam and in this way adding knowledge every year.
The Microsoft Learning team is already developing a solution where some exams will be eligible for a retake every (calendar) year with rapidly changing technologies, for example Azure.
You can find more information here:

• Microsoft Streamlines Technical Certifications by Larry Kaye on Born To Learn
• Top 12 Questions About the New Streamlined Certification Paths by Larry Kaye on Born To Learn
• Microsoft Certification Changes by James Serra on his blog

---

Since I made the switch to Pulse I started working with Dynamics AX, Microsoft's ERP system. This Tuesday Microsoft had a First Look event at the AXUG Summit 2016 on (the new) Dynamics 365 for Operations, which is the new name for Dynamics AX. Dynamics NAV is also renamed and is now called Dynamics 365 for Financials.

I came across a very good blog by Fredrik Sætre (b | @AxFredrik) which summarizes the First Look event very well. He speaks about the Common Data Model that will be replacing the Entity Story introduced earlier in AX 2012 R2 and will be the core of Dynamics 365, for AX, NAV and CRM.

SSRS: Go Back Button in an Expression

ProblemLast week I was making a drillthrough report in SSRS for one of my clients and I had the need to make a (browser-like) Back button, to go back to the parent report for example. I know I did this before and it had something to do with history and (-1) but I couldn’t recall the syntax. So I thought I’d finally capture the solution in a blog post!

Solution
The key to the solution is the <Action> property of the <Text Box Properties>, which you’ll have to set to <Go to URL> and edit the expression with the following code:

=”javascript: history.go(-1)”

Do mind the casing here, because it’s case sensitive!

SSRS: Sorting in Particular Order in a Report

This week I had the need to order values in a matrix in SSRS in a particular order. The names need to be ordered in alphabetical order, except for one name that always needs to come first. I’ll show you how I did it with a sorting expression in the Group.
I’ve taken a (light-weight) version of AdventureWorks for the examples.

Let’s take the Sales.ProductCategory and Sales.Product tables as an example. I’ve counted the number of Products of the 4 main (parentless) ProductCategories:


The sorting in the Row Group Name is automatically done from A-Z. If you want it in any other order you’ll have to set it explicitly. You can either pick a(ny) column and set it to sort A-Z/Z-A or you can enter an expression. We’re now going to implement the latter.
When you click on the arrow of the Row Group Name in Row Groups box at the bottom of the screen and click on Group Properties… you’ll end up with the following screen:


We want to leave the existing sorting expression as it is, because we can use it later. Add another row and click on the fx button of the new row to open up the expression dialog box:


By entering the formula you see in the picture above, you’re saying to the sorting engine: Whenever the value of the field Name is equal to “Clothing”, give it a sort order of 0, otherwise give it a sort order of 1. When ordering from A-Z 0 comes before 1 so “Clothing” always comes before the rest. You can adjust this to your needs ofcourse to make a specific item come last, or sort all the items in particular order by nesting IIF statements.
When you click OK and click on the up arrow to sort on this expression first you end up with the following sorting options:


Sorting on the Group is first done on the expression, so “Clothing” comes before anything else, second alpabetically on the Name, so the rest of the values are placed after ”Clothing” from A-Z. This gives me the same values as before, but now sorted differently:

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• SQL Server Problem Solving Methodology by @grrl_geek
• Counting Things That Didn't Happen by @PowerPivotPro
• New to SSRS? Build Your First SQL Server Reporting Services Report by @grrl_geek
• More advanced with SSRS? @sqlJason does a great job explaining Bullet Graphs in SSRS: Part 1 and Part 2. Part 3 will follow shortly to complete the series
• In case you haven't heard of Power BI by this time, check out this post by @Technitrain, it sums it all up pretty well
• For those who love to write blog posts, but can't seem to find the time should check out Speedblogging

Hopefully you can get somehting useful out of here!

You can also follow me on Twitter: @NickyvV

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

• Why won't my PowerPivot for Excel 2010 workbook upgrade to Excel 2013?
• The sessions and speakers for PASS Summit 2013 have been announced, check it out here!
• Firestorm: The story of the bushfire at Dunalley: A so-called "Interactive" by The Guardian
• PowerPivot: Relationship may be needed but I already have one?! by @PowerPivotPro
• SQLServerCentral Runs sp_Blitz - Query Plans
• A Sample SSRS Dashboard and some Tips & Tricks by @SQLJason

Hopefully you can get somehting useful out of the links!

You can also follow me on Twitter: @NickyvV