none
Passing a filter parameter to ODBC Data Source Mapping RRS feed

  • Question

  • We have a dashboard that has *most* of the data from an Analysis Services cube (including the filters).  But the KPI goals are coming from a relational query.  

    To manage this, we have KPIs on a scorecard, and the data source for the Goal is and "ODBC" type.  This looks like it will work fine, and if we hard-code the filter parameters into the "ODBC Data Source Mapping" dialog, a goal is returned and all is good.

    The problem is that in real life, the filter parameter text needs to be passed back to SQL Server in the where clause.  We can't figure out how to reference the parameter values from the filter selection in the ODBC mapping so the query will return the KPI goal matching the filter(s) the user selected.  The following is the query used in the KPI's target data mapping:

    SELECT goalAmount FROM dbo.ScorecardGoals
    Where metric = 'Revenue'
     and business_unit='Corporate'
     and timeYear = Right(Left(<<Time.UniqueName>>,45),4)
     and timeFrame ='Q1'

    How can one modify this query so that the current filter values are passed with the query?

    Thanks,
    Rob


    Tuesday, December 23, 2008 3:29 PM

Answers

  • If you are going against a SQL Server table then I would recommend using the Tabular list data source and selecting the SQL Server table instead of ODBC.  For the Filter in the dashboard you can link the filter to the scorecard and simply set the Source Value in the Link Options to Display Value instead of Memeber UniqueName.  I must be missing something here as to how you are trying to use this and possibly how the filter is configured.  It almost seems like you are trying to use a SSAS filter value to pass to the non-SSAS source.  If that is the case then make the modification that you need to the value in the Filter Link Formula portion when you are establishing the link to the scorecard.

    It almost sounds like you might have mixed data sources setup in the scorecard, so maybe that is where you are running into issues linking the filter to the scorecard.
    Dan English -- http://denglishbi.spaces.live.com
    • Marked as answer by AseemN Tuesday, January 13, 2009 8:05 PM
    Wednesday, December 24, 2008 1:19 PM