SSRS report with click-through detail hosted in PerformancePoint Services

Here's a situation:
You have a Reporting Services report (SQL 2008 or higher) hosted in SharePoint PerformancePoint Services (SHPT 2010 or higher). You are interested in adding a click path to that report which will open a detail report in a new window. How do you do it?

Well, the short answer is, you don't.  Not in PPS, at least.  This can be done in standalone reports but, per Microsoft, this behavior is not supported in PPS.  The referenced URL will NOT open in a new window through SharePoint/PerformancePoint Services.   So.  That's awesome.  Not. 

How did I arrive at this conclusion?

I was working on getting a detail report to open from a PerformancePoint-hosted SSRS Map report.  I wanted the states in my US map to have a clickable action that would open up another SSRS report.  I was initially able to get the report and click-through action to work by using a simple Go To Report action.  But the detail report opened by the Go To Report action comes up in the same browser window -- which could cause some navigational confusion for users.

So I wanted to see if I could force the detail report to open in a new Window - which I could.  Instead of "Go to Report", I used the "Go To URL" action instead.  The technique for this is all over the intarwebz, but I found these two articles to be the most directly useful:

• http://www.bidn.com/blogs/BrianKnight/ssis/972/ssrs-action-to-open-a-url-in-a-new-window
• http://www.bidn.com/blogs/domhorton/bidn-blog/1454/sql-server-reporting-services-url-parameters

Before you even look at those posts, here are the basic moves:

• You'll need to assemble a good, working URL for your report -- either in Report Manager or in SharePoint (if you're running in Integrated Mode).  I found this to be the trickiest part.  Hint: you want to open the Report using a ReportViewer.aspx.
• You'll then need to include that URL in the dataset in your main report.  In my case, I also needed to pass a parameter (hence the second link above).
• Then you need to set up the Go To URL action using that assembled URL.  Despite what the links say, I cut out the javascript "window.open()" script code -- providing the URL alone did what I needed.

So, delightful.  I had my detail report opening in a new window in the Designer and via Report Manager.  But, as soon as I deployed my newly tricked out reports and tried them in PPS, there was fail.   So I scouted around for a long time and found this: http://go4answers.webhost4life.com/Example/open-window-does-not-work-ssrs-report-20883.aspx

Scroll to the last comment to see my sad discovery.

Is there a workaround?  Yes, but it's lame.   Users can use the right-click menu when clicking on the map report, and select "Open in New Window" or "Open in New Tab" to achieve the same results.  But expecting users to navigate like that is a poor substitute for something that, you know, just works.

So the bottom line is that PPS does not give you 100% "pass-thru" SSRS behavior when using a Reporting Services report type in a dashboard. 

SQL Server 2012 Data Tools "Gotchas"

I've been working in SQL 2012 the past couple weeks on a new project, and I'm finally getting past the "new car smell"/honeymoon period.  Over the last 2-3 days I've encountered some more subtle and insidious changes of which I'm not a fan. At all. 

To be sure, I am mostly OK with the cosmetic changes in the toolset.  The exception there being SSIS, because I loved how it was all "the light is green, the trap is clean" in 2005 and 2008.  Yeah, it's a little less cartoony now.  But I love cartoons, so that's not exactly value-add to me... Also, I don't see any real difference thus far between BIDS and the spankin' new "SQL Server Data Tools".  Feels like the same thing (subject-are-limited version of Visual Studio) with a new name.

And, you know, I generally expect things to change between software versions.  Especially when you're dealing with Microsoft.  :)   But I feel like anytime a software "upgrade" or new version removes or deprecates commonly used functionality, that's a step in the wrong direction.  

Obviously, there are exceptions.  For instance, since we're talking about MS, I'm quite cool with Windows 8 and its new touchscreen oriented interface.  Because, honestly, do you really love the mouse that much?  Seriously, though, I think it looks great and is very usable once you get over the slight learning curve required to deal with any new OS. 

Where I have problems is with things that are maybe a little more under the covers.  Things that you take for granted because they are integrated into a given piece of software's basic workflow and usability profile.  And this is where I feel like there are shenanigans at play in SQL 2012.

I discovered this week, to my chagrin, that automatic metadata refresh seems to have been surgically removed from the SSIS 2012.  Say you build a data flow including some components you will likely use again in the project -- like, say, a Lookup on a Date dimension.  In the 2005/2008 world, you could copy/paste the component(s) into a new Data Flow, hook them up, and SSIS would update the pasted component's metadata and -- BLAMMO -- reuse!   Right?  Well, not so much in 2012.  It won't automatically refresh any metadata.  Another example:  You create an OLE DB component, drop a SQL query in it and continue on your merry way.  But the DBA changes one of the columns in your from a VARCHAR(50) to a VARCHAR(100).  In 2005, the next time you open the package, SSIS would identify this and prompt you to accept the metadata update so your OLE DB source is in sync.  Handy, yes?  Well, kiss it goodbye.  That's all manual in 2012, from what I'm seeing so far.

A more straightforwardly bald-faced Surgical Feature Removal exists in SSAS, and it SOOOO rubs me the wrong way.   The cube browser in SQL Server Data Tools no longer supports hierarchical display.  That's right: when you browse the cube, your hierarchical dimensions are displayed as a flat table.  I mean, COME ON!  Seriously?  If I wanted a G-D table, I'd write a query!  Microsoft's responses online have mumbled something about "use Excel to view hierarchy-style data", but I'm not trying to hear that, honestly.  Excel is all fine and good for users, but I want a DEVELOPMENT TOOL that has SUPERPOWERS.  Not something that has to be "fixed" or "completed" via Excel.

And that may be just the tip of the iceberg.  I haven't touched SSRS 2012 yet.  And now I'm afraid to.  Between the giant fumble with PowerView (doesn't work with multidimensional?  Really?  Seriously?) and these things I'm encountering, I'm a little bearish on working with SQL 2012 BI stuff. 

How about y'all?  Anything in the SQL 2012  BI toolset sticking in your craw?