May 6, 2012 at 4:14 pm
Scenario:
I have a working sql query based excel report AND I have new data that I need to bring into the query which resides exclusively on an excel spreadsheet. I cannot (due to permission restrictions) create a new table in SQL Server and I can't store the excel datasource on the same server as the SQL Server instance (therefore, linked server option isn't viable) and I do not have bulkload permissions on SQL server so can't bulkload date from .csv file and I do not have permissions to reconfigure the instance for ad hoc queries (therefore Select * from OPENDATASOURCE is also not viable).
Does anyone know if it is possible for a query based excel report to join on a table defined exclusively in PowerPivot Excel workspace?
--Quote me
May 7, 2012 at 1:11 am
Hi,
have you considered a Integration Solution(SSIS) wherein the excel file would act as a source and you can later introduce a MERGE transformation to join data as required.
Raunak J
May 7, 2012 at 11:54 am
Yes, I did think of SSIS solution however I ended up converting the Excel spreadsheet into a .csv file and using Microsoft Query to import it as a table. I have now created relationships between this imported table and my existing query (also a table) and generated a report successfully based on defined relationship.
My next problem is related...while this report renders and slices well on my local harddrive, it doesn't once it's uploaded to SharePoint site. If I click on a slicer button I get:
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh: PowerPivot Data
Prior to joining my query table to the table based on imported .csv file, I was able to slice the report just fine.
Doesn't the imported table get uploaded to Sharepoint PowerPivot Gallery along with the query definition?
--Quote me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply