locked
Pass Filters to Analytic Charts from Different SSAS Databases RRS feed

  • Question

  • We're building a dashboard with Analytic Charts from two cubes in separate Analysis Services databases.  We have two filters (month and business channel) that are common to the cubes/reports and have the same data values for the members, but different naming conventions for the attributes (data structures for the sources and role-playing dims).  We're trying to create a filter on the dashboard that will pass the filter values for the selected member name display values to the reports from both cubes.   Basically I need to be able to filter on one or more month keys, say 201201, 201202 when applied to [Month Dim].[Month Key] in one cube and [Received Month Dim].[Month Key] in another.  However, when running the dashboard, we are either getting "Error running the data source query" or "Unable to get filter values from "<Filter>".You do not have permission to view this data."  or "The function expects a tuple set expression for the 1 argument.  A string or numeric expression was used."  We've tried using both MDX Query and Member Selection filters, along with some combinations of filter type and connection formulas, to no avail.   Any assistance with this would be greatly appreciated. 

    Monday, March 26, 2012 3:10 PM

All replies

  • In your Filter Link connection formula try using:

    strtoset('[Received Month Dim].[Month Key].&[' + <<SourceValue>>.Uniquename) +']')
    This would only be used for one analytic chart. Filter should be based on [Month Dim] dimension.


    http://dailyitsolutions.blogspot.com/

    Tuesday, March 27, 2012 1:57 AM
  • Thanks for the info, that looks like the right track.  Unfortunately I'm still getting the following error:

    Unable to get filter values from "Filter".You do not have permission to view this data.

    Same filter object being used for the other cube works fine (no connection formula required for those reports).  Our accounts used for Sharepoint services, app pools, etc, all have permissions to the data sources and ownership permissions to the performancepoint content, so I'm somewhat doubtful that this is actually a security issue.  Help!

    Update:  If I narrow to a single filter value with data, I get the following:

    Unable to get filter values. 

    The filter connection formula is invalid.

    I've tried a few tweaks to the above formula, but no luck so far.  Have you found any good documentation on these connection formulas?  I've just found bits and pieces here and there.

    Thx!

    Tuesday, March 27, 2012 1:39 PM
  • Unfortunately there is not much information on connection formulas. The way to go is to create the same MDX in query editor under Management studio. Everything that works in query editor should work on connection formula like member values, unique names, values etc.

    To troubleshoot I would start with attaching a SQL Profiler to SSAS and run the filter. This would give you the MDX passed from PPS. Copy it over to the editor and fix the syntax. I would say you are getting issue with how the value is being converted to a memberuniquename probably mismatch names, Profiler should help.


    http://dailyitsolutions.blogspot.com/

    Tuesday, March 27, 2012 1:55 PM
  • That's a good idea.  I'll give it a try and report back.

    Thx!

    Tuesday, March 27, 2012 2:49 PM