I'm creating a dashboard in SharePoint 2010. I've got PerformancePoint, Reporting Services, and everything set up. I want to create a report using data that spans multiple tables. Typically, I use LINQ or a stored procedure to query data across them, but it seems like that with dashboards I only have the option to create a data source that maps to a single table, and not to a stored procedure or SQL query. Is there any way to accomplish this? What are my options and which is the best way?
While designing your cube you have option to setup DataSourceView. Once you have dropped table there you can right click on it and created NamedQuery. You can specify joins between tables in named query and can get data as your requirement.