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?

MDX query across multiple Role-Playing Dimensions in SSAS 2008R2

Here is what I would consider to be a magic post:  http://richardlees.blogspot.com/2011/06/linkmember.html

Mr. Lees does the Lord's work here, and surely helped me out of a jam.  I was trying to figure out a way to collect a single measure across multiple role-playing dimensions.   Everything I tried was epic fail, including the solution I initially arrived at which was to create 3 separate queries and let SSRS sort them out....

To set the scene, an example of the problem I was having would be:
- I want to find the numbers of New Widgets, Sold Widgets, and Improper Widgets, by month over the last 12 months (Rolling 12 Month totals).
- In my SSAS cube I have a Measure for Widget Count, and I have 3 role-playing Date dimensions -- Released Date, Sale Date, Inspection Failed Date -- all based on a single Date dimension living in the underlying Data Mart.
- Appropriately filtering and slicing the Widget Count by any of the above dates yields correct results.  But since the cube believes (techically properly, I guess) that those role-playing dimensions are all actually different dimensions, I cannot get a good slicer Axis out of only one of the date dimensions.  So, in a nutshell

In other words, I couldn't get into a single query/dataset all 3 of the desired measures. This was because I really needed to query a single measure across 3 different role-playing dimensions.

So, in comes Mr. Lees.  He introduced me to the magic of LinkMember().  And thus I quickly solved my problem.   LinkMember allowed me to create a Calculated Member that gave me totals aligned with each of the Role Playing Dimensions I was looking at.  Booyah, granny!

Collaboration or Groupthink?

Saw this was posted on Facebook by a colleague at work:
http://www.nytimes.com/2012/01/15/opinion/sunday/the-rise-of-the-new-groupthink.html?_r=2&ref=opinion

The whole notion of calling out "teamwork" as such a basic value kind of gives me a chill.  Goose bumps even... 

I can't speak for you, gentle reader, but I can safely say that the absolute worst work I ever did in either my academic or professional careers was in group projects.  I can recall exactly two projects in the working world where my entire software team met or exceeded my own and our customer's expectations.  The only times I've ever been on "teams" that consistently worked were bands.  And that's only happened like 2-3 times in over a dozen bands. 

I even remember in school thinking that the best possible outcome of group work was usually mediocrity.  The usually random mix of group members and their general engagement levels meant that one or two people in the group would usually take on 85-90% of the work.  I see this pattern echoed in virutally every large company I have consulted or contracted for.

Certainly there are needs which can only be served by teamwork. Huge workloads of all kinds require more resources, which can most usefully be coordinated in teams.  Another dimension would be skill sets required for the work, which may also need to broken down team-wise.  But in terms of a need for teamwork involving more than one mind focused on a single problem (i.e. "brainstorming"), the statistics say that this usually does not result in a better solution.  Note the important exception pointed out by the author about collaboration (specifically remote collaboration, as enabled by technology).    fostering collaboration between nominally independent individuals seems to have significant worth. 

I look at this as the true endgame of the software "collaboration wave" seen in business IT right now.   It's easy to dismiss the buzzword, or consider it a forced mandate for "groupthink."  But empowering knowledge workers with data, allowing them to build ideas and evolve them into useful business tools -- that is a powerful force that is already working. 

RDL won't open in Design View in BIDS 2008? No problem.

You may find yourself working on an SSRS report in BIDS 2008. You may ask yourself, "Why can't I re-open this report in Design View?  Why is it this ugly line of non-formatted HTML?"

You may also find yourself behind the wheel of a large automobile, or living in a beautiful house, with a beautiful wife. But that's neither here nor there...

I have had this problem multiple times, and every time I have to look up the answer on the MS SQL Server forums.  But today, they were down for maintenance, so I was up the creek.  Therefore, I decided that once I refound the answer, I'd blog it, and provide links and a summary explanation. 

This puts it in a nutshell, and is where I eventually find the answer (usually).  So credit where credit's due:  http://blog.hoegaerden.be/2009/03/28/the-datatype-attribute-is-not-declared/

And here's a brief explanation:

There is a (known) issue within BIDS 2008 where it will serialize a Report Parameter's XML incorrectly.   If your report has an Available or Default value, BIDS will add an attribute called "DataType" in to one or more of your parameter's Value tags.  This effectively invalidates the entire RDL, because the Value tag doesn't actually have that attribute.  You try to open an RDL in Design View that's been saved this way, and the deserializer can't do it.  Ka-boom.  You receive a long horizontal scroll of unformatted HTML.  Yay.

The solution is:
Simply delete those attributes where they are found. 
Do a Find on "<Value DataType=" in the HTML and delete the attribute (and its value) wherever it turns up.

Hope this is helpful.  Now that I've written this out, I will expect myself to remember it....

BI Estimation - "A more appropriate metahpor"?

http://peterjamesthomas.com/2009/03/18/a-more-approprate-metaphor-for-business-intelligence-projects/

So this leads me to suggest a different metaphor for BI projects. Major elements of them are much more like archaeological digs than traditional building. The extent and importance of a dig is very difficult to ascertain before work starts and both may change during the course of a project. It is not atypical that an older site is discovered underneath an initial dig, doubling the amount of work required.

That's so obvious it kills me that I hadn't thought of it before.

Let the DB Handle It

Perhaps I'm pointing out the obvious, but software developers don't always seem to experience data the same way that, say, a DBA or a BI developer does.

I'm not saying that they don't process in rows and columns, or that they all have Dyscalculia or something.  I'm saying that I frequently detect a reluctance in developers to let the database handle it.  I theorize that this issue can come from a number of possible sources, but is usually a symptom of childhood trauma related to set-based math...

The rationale provided is often one that I consider an artificial barrier in this day and age, which is the old saw that "business logic can't go in the database".  In my decade-plus of experience maintaining, designing and building business software, most of the databases I've encountered have been fairly inextricable from their front-end line-of-business apps from the beginning.  And most of these apps are not going, say, make a switch between platforms or OS's without a wholesale rewrite of the code, anyway.  So using PL/SQL packages or a set of T-SQL stored procs to help encapsulate logic is not a very serious crime.  As long as it's documented, of course.  Also, the best iron in the whole solution always goes to the DB machine. Why not leverage it to accomplish the work of the application? Save some clock and RAM utilization in your web farm and let the database do it.

Another scenario is when the RDBMS is considered a "Mystery Hole," where strange, nigh-impossible things happen in the darkness and hum of the server closet.  I find this understandable enough from developers. I don't see much evidence that good data theory is being imparted on software dev students.  And SQL itself, as a language, operates in a different paradigm from any procedural or OO language, and it requires those nasty set math skills to become accomplished with it.  Advanced SQL techniques do take years to learn, and even to recognize when they can be brought to bear. 
That said, a Pivot operation takes a lot more Java or C# code to implement than it does SQL.  And frankly, being good with SQL means bever having to write a loop. 
It makes sense (to the point of being mandatory, IMHO) for software developers to learn how data works, how SQL and databases work, and when you can leverage them for processing power by letting the database handle it.

A variation on this "Mystery Hole" thing is where it's not about being unfamiliar with the DB environment, but instead it's all about control.  The developer simply can't have any of the logic NOT in code that she has her hands in.  Maybe I'm wrong but, on a big enough project, that way lies madness, methinks.  And again, it fails to heed the wisdom of "rendering unto Caesar that which is Caesar's."  For certain tasks/functions, it's often more expedient and design-time and run time to just let the DB handle it.

Now, I have encountered on many occasions scenes where DBAs and application developers are at odds.  Usually over who gets to do what in whose environment.  In many of these, I wouldn't be surprised to find one of the syndromes above involved.  But there are many times when the obstinacy and regimentation of a DBA will make developers want to punch them dead in the face rather than get involved in DB coding.   So it's certainly a two-way street, and I'm not going to applaud overly-controlling DBAs any more than overly-controlling Developers.  Clearly both parties are on the same team, and should be cooperating to solve the business goals they support, yes?  Rather than playing parochial games over fictitious territories?

Maybe the fact that I'm partial to such compromise (or even averse to conflict) is why I ended up in the BI world.  But as an application developer I always felt it was critical to have insight into the DB layer in order to be a well-rounded architect-level software professional. And if you could get it, working knowledge of the the network/infrastructure layer, too. 

But let's not get ahead of ourselves.  I suppose it's enough to expect that software developers know when to -- well, you know.