Hello,
I'm wondering if anyone could shed some light on the following error when selecting multiple items in a pivot table.
€śThe database driver does not include necessary capabilities and cannot be used with Excel. Contact your database administrator or driver vendor.€?
Your help is appreciated!
Is it possible to generate automatic refresh of excel 2013 table which displays some table of a power pivot model on file open?? I dont want to use pivottable (which supports this ...)
View 2 Replies View RelatedI keep getting this error when trying to connect users to the OLAP server from
Excel.
"Unable to open connection.
Cannot Connect to server 'OLAP Server Name'.
OLAP server error: The operation failed due to network problems."
When I use this excel locally on my machine it works just fine. Any ideas?
Lets say i have one analysis server project on server A . I have made an excel sheet on desktop B in which i have inserted a pivot table which datasource is Server A's cube . I then have save this worksheet as webpage and save it on Server A. When i run this webpage on server A its working fine with but as soon as when i run this webpage on other PC it give me the error
"The query could not be processed:
The data provider didn't supply any further error information."
what could be the reason .. Please help me i am going mad....
Hi guys,
I want to connect through a pivot table in excel to an OLAP cube I created in SSAS but it gives connection error. But I can connect to tables, databases in SSMS through pivot tables in excel. Any ideas what might be the problem? Some settings maybe etc
Hi all,
In MyDatabase, I have a TABLE dbo.LabData created by the following SQLQuery.sql:
USE MyDatabase
GO
CREATE TABLE dbo.LabResults
(SampleID int PRIMARY KEY NOT NULL,
SampleName varchar(25) NOT NULL,
AnalyteName varchar(25) NOT NULL,
Concentration decimal(6.2) NULL)
GO
--Inserting data into a table
INSERT dbo.LabResults (SampleID, SampleName, AnalyteName, Concentration)
VALUES (1, 'MW2', 'Acetone', 1.00)
INSERT €¦ ) VALUES (2, 'MW2', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (3, 'MW2', 'Trichloroethene', 20.00)
INSERT €¦ ) VALUES (4, 'MW2', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (5, 'MW2', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (6, 'MW6S', 'Acetone', 1.00)
INSERT €¦ ) VALUES (7, 'MW6S', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (8, 'MW6S', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (9, 'MW6S', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (10, 'MW6S', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (11, 'MW7', 'Acetone', 1.00)
INSERT €¦ ) VALUES (12, 'MW7', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (13, 'MW7', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (14, 'MW7', 'Chloroform', 1.00)
INSERT €¦ ) VALUES (15, 'MW7', 'Methylene Chloride', 1.00)
INSERT €¦ ) VALUES (16, 'TripBlank', 'Acetone', 1.00)
INSERT €¦ ) VALUES (17, 'TripBlank', 'Dichloroethene', 1.00)
INSERT €¦ ) VALUES (18, 'TripBlank', 'Trichloroethene', 1.00)
INSERT €¦ ) VALUES (19, 'TripBlank', 'Chloroform', 0.76)
INSERT €¦ ) VALUES (20, 'TripBlank', 'Methylene Chloride', 0.51)
GO
A desired Pivot Table is like:
MW2 MW6S MW7 TripBlank
Acetone 1.00 1.00 1.00 1.00
Dichloroethene 1.00 1.00 1.00 1.00
Trichloroethene 20.00 1.00 1.00 1.00
Chloroform 1.00 1.00 1.00 0.76
Methylene Chloride 1.00 1.00 1.00 0.51
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I write the following SQLQuery.sql code for creating a Pivot Table from the Table dbo.LabData by using the PIVOT operator:
USE MyDatabase
GO
USE TABLE dbo.LabData
GO
SELECT AnalyteName, [1] AS MW2, AS MW6S, [11] AS MW7, [16] AS TripBlank
FROM
(SELECT SampleName, AnalyteName, Concentration
FROM dbo.LabData) p
PIVOT
(
SUM (Concentration)
FOR AnalyteName IN ([1], , [11], [16])
) AS pvt
ORDER BY SampleName
GO
////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the above-mentioned code and I got the following error messages:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TABLE'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'AnalyteName'.
I do not know what is wrong in the code statements of my SQLQuery.sql. Please help and advise me how to make it right and work for me.
Thanks in advance,
Scott Chang
Hi,
I have a problem with a reporting services 2005 report.
The data source is OLEDB for OLAP 9.
The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.
The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the left side of the sheet. This feature is very useful to hide / show lines.
It seems that with the implementation specified in the msdn post above,
It is not possible to export correctly the report in Excel.
So my questions are:
Has someone already encountered this problem?
Are there solutions with a different implementation?
Thanks,
Gurvan
Hola
We are getting symptoms of a 30 second timeout irrespective of what we do, but only on certain machines. Can anyone shed any light on how the connection timeout can be set, as there seems to be a factor overiding any settings we set?
We are working in Excel (2003) connecting to an Analysis Services 2005 cube through a website https://Datasource connection. When we edit the connection string that the file is using (either by forcing use of an exterior .ODC file and editing the settings in there, or by converting the xls spreadsheet into an xml spreadsheet and manually editing the connection string in there) we get no joy: after 30 seconds the error appears:
"Either a connection cannot be made to the [my server address], or Analysis services is not running on the computer specified".
This error is itself rubbish: If a smaller query (one that takes < 30 seconds) is run in the same Excel file and Pivot Table, it connects fine.
What really doesn't make sense is that the exact same file works on some machines and not others. These machines are all inside the same network (and the machines that work have been proven to work on a number of other networks). What's more there is no pattern to the machine capabilities - some newer / faster machines get the error. These machines (that work and do not) have excel 2003 SP3. (Another of the ones that works has Excel 2007).
Changing the connection string to include "Timeout=300" or "Connect Timeout=300" makes no difference, and "Command Timeout" causes a different error so that it wont even attempt to run the query (as opposed to collapsing after 30 seconds of normal progress).
Similarly in the .ODV the tags <ODC:CommandTimeout>, <CommandTimeout>, <ODC:Connect Timeout>, <ODC:Command Timeout>, <meta name=CommandTimeout content=1000> etc. have no effect (the file still gets the 30 seconds error rather than just giving up so no way of working out correct syntax)
The only pattern we can see, and we can't fix this, is that the machines that work have Retail versions of excel, and the ones that do not have corporately licensed versions of excel. We cannot see any difference between them in the Help >Abouts, but thought it might be worth mentioning.
Any help would be fantastically appreciated, we are out of ideas.
Thanks kindly
Amundo
I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.
All worked fine, but now I get this error message:
[ytd_pivot [123]] Error: Duplicate pivot key value "6".
The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.
Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren´t available at this moment while data extracting is still going on?
any hints?
Cheers
Markus
Hi,
I have several pivot tables in Excel that access data to a SQL 2000. We install SQL 2005, we change the ODBC from the 2000 server to the 2005 server. Now when we try to run the pivot tables I've got the following message:
"User 'public' does not have permission to run DBCC TRACEON"
Any idea on how to fix this problem?
Thanks,
Arty
Hi,
Im using Sql server 2000&MS Excel 2003. i've recently moved to Sql server,i was using MS Access.
i've created an odbc connectivity and a View . i've created pivot tables in excel. my intension was once i upload the data into sql server and when i refresh the view/excel pivot, the pivot table should updated automatically with the newly entered data( i used to do the same process in Access).
pls advise me how to update the Excel pivot table.
Rgds
RPU
I have tired for this!
When I use SSIS for extract data from ssas, that means,I use mdx query.
then random error occured.
Hope some one can understand my poor English....
And the Error Info show below.
Code Snippet
Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".
Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.
I am facing problem like;We generate reports thru Excel Pivot table. The data is coming from salescube. Let?s assume that there are 2 products A and B. The user wants tofilter for each product and view ?stock in hand? and ?total sales? forparticular month (as two columns).From the Pivot table filtering, when we choose product A and month ?Jan?,it is showing values for ?stock in hand? and ?total sales? columns for thatmonth.But when we choose product B and month ?Jan?, it is only showing ?stock inhand? value and the ?total sales? column was suppressed/unseen. We knowthat there is no sales transaction for ?Jan? month. But we still want toshow the column title even if there are no records in the database / cube.I am not using any scripting, this pivot table was generated just thruPivot table wizard.Any way to achieve this, please advise me.Advanced thanks for u r help.--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedThe excel pivot table is very powerful for the user, with dragging and dropping fields at will on the web page, but I can only get it work when run from the web server directly. The data is in SS2000 300,000 rows, get external data in Excel, then publish pivot table as HTM with interactivity.
It seems to look for c:myodbc.dsn in the C: drive of the pc the user is on rather than the server, and I cannot use IP Addressing to relate to the dsn file.
any ideas ?
TIA
Neil.
Hey Guys,
I have a cube with a Period Dimension, in which the hierarchy is as follows:
Year
Quarter
Month
Day
Users access the data via a pivot table.
The problem is that whenever a new month comes in, the pivot table doesn't automatically display data regarding the new month. Can someone please help me here.
Thanks,
Surbha
I know that this is an Excel question, but I guess it is much more likely that an SQL person using dynamic pivot tables had stepped on this, rather than any advanced Excel user.
I am exporting a dynamic pivot table to Excel through a Stored Procedure. If the Stored Procedure that executes the dynamic pivot table returns 7 columns in one run, and 4 columns in the following update, then I have 3 orphaned columns that are still displayed in the spreadsheet. There isn't any content related to them, but the empty columns with their headers are bothering enough.
I've been trying to play with the data connection properties, but nothing deletes unused columns from former data executions.
We are using MS Excel 2007 Pivot tables to access en SSAS 2005 Cube. Farly often when we reopen an excel spreadsheet with one or more pivot tables we get an error like this:
Excel found unreadable Content. Do you wish to repair
When we click yes the following log is shown:
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition2.xml part (PivotTable cache)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotTables/pivotTable2.xml part (PivotTable view)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
And all the pivot tables are converted to plain text.
I have read about this in KB 929766 but this do not apply since KPIs are not used.
The KB 943088 is more interesting but after upgrading all user with SP 1 the problem is still there, mostly when we open old excel-files that has been created without SP 1 but opened and saved once with SP 1. After that we are not able to open them at all, either with or without SP 1 installed.
Is there some way to €śsave€? pivot tables from destruction? I can€™t ask the users to rebuild all there spreadsheets that have been created prior SP1. What will happen when there is a new SP for Excel? Rebuild all spreadsheets and pivot tables again?
Hi,
We have a excel 2007 file with a Pivot Table in it. We would like to use the same as data source (But all the source fields as it comes for Pivot). The key issue is that the data to the pivot is from a connection and points to DB - but there is no access to DB. So the Excel is static to us with a PIVOT in it.
Is there a way to get all the data that is avaialble as a source for pivot.
Ex. The select query has co1,co2....co 11 from xxxxx
whereas the PIVot HAS ONLY cO1, CO2 AS ROW, CO3,CO4 AS COL, CO5 AS VALUE AND CO6,CO7 AS FILTER AND OTHERS MISSED OUT IN PIVOT.
THe SSIS as such provide Excel 2007 access to Access OLE DB driver with Excel 12 setting in advanced tab. But not sure whether we can give the range (or) a way around ot get all the data as available in excel regardless of what is used only in the pivot.
Regards,
kart
Hello All,
Our OLAP environment involves an ETL/Data Warehouse/Data Mart server and a cube publisher server.
We would like to learn how to automate the Archival/Restore of OLAP databases. We are currently doing
it manually though OLAP Manager. Any help would be appreciated. Thanks. James.
--
James E. Bothamley
Senior Database Administrator
Dave & Buster's, Inc.
2481 Manana
Dallas, TX 75220
Work
Phone (214) 904-2296
email jbothaml@DaveAndBusters.Com
"Once in a while you can get shown the light
in the strangest of places if you look at it right"
JG 1942-1995 RIP
Wonder if you could help me ?
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
Thanks very much
Mark
We have build a powerpivotmodel that uses a couple of datasources. The user extracts on monthly base an excel file with 20 samples (randomly) from the model to check some aspects of the input. He can put Yes or No into a column if he agrees or not and fill in a remark field. Next this excel file is loaded back into the powerpivot model (in a nightly batch) and the Yes/No field and the remark field are added to the original data. If the user refreshes his excel sample file from the pivot, the remark and YN field will be filled with the values he added. So far so good. One moth later when he extracts the file for a new month a problem arises. Since the excel file only has data for the new month, when the PP-model is reloaded the data for the previous month will be lost. So we need some sort of incremental load for the excel file. Is that possible and is this the best way to handle this situation?
View 4 Replies View RelatedIn an Excel workbook I'm building a report using a PowerPivot data model.
I've a Calendar filter. If I select an year from this filter I need to show in a cell the total number of working days, present in the Calendar table as a column.
have just implement Microsoft Dynamics NAV. and I'm trying to learn how to use this Excellent tool powerpivot add on, But when I'm trying to connect it to any SQL table that support Dynamics NAV I have an error message while downloading the table :"do not have enough memory or I should use Excel 64 bits instead 32 bits".when I check CPU memory Utilization we are just using less tha 20%. I'm working on a remote server and the using Excel 2010.
View 3 Replies View Related
I am trying to create a pivot table in excel using Activex scripting but i am facing some problems in doing so, below is the code that I have written
The line of the code where i am encountering error is highlighted in RED
Set ActiveWorkBook = oExcel.Workbooks.Open (sFile)
Set oSheet = ActiveWorkBook.WorkSheets(1)
oSheet.Range("A1:T1").Font.Bold = True
oSheet.Cells.Select
oSheet.Cells.EntireColumn.AutoFit
oSheet.Range("A1").Select
oSheet.Name = "Data_" & strName
Set oSheet = ActiveWorkBook.WorkSheets(2)
oSheet.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "Sheet1!A$:T$").CreatePivotTable TableDestination:=Range("A3"), TableName :="PivotTable2"
oSheet.PivotTables("PivotTable2").SmallGrid = False
With oSheet.PivotTables("PivotTable2").PivotFields("Supervisor")
.Orientation = xlRowField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Operator")
.Orientation = xlRowField
.Position = 2
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Tag")
.Orientation = xlDataField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Dept")
.Orientation = xlPageField
.Position = 1
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Branch")
.Orientation = xlPageField
.Position = 2
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Location")
.Orientation = xlPageField
.Position = 3
End With
With oSheet.PivotTables("PivotTable2").PivotFields("Type")
.Orientation = xlPageField
.Position = 4
End With
oSheet.PivotTables("PivotTable2").RefreshTable
ActiveWorkBook.WorkSheets(3).Delete
'ActiveWorkBook.WorkSheets(2).Delete
oExcel.ActiveWorkBook.Save
'With ActiveWorkBook
' .SaveAs sFile
' .save
'End With
oExcel.quit
is it possible to replicate this in SSRS I wonder??I have included the code of the fields used and a snapshot of some data, and also how the Pivot looks in Excel.
SELECT
TARNSubmissionID,
ISSBand,
BPTLevelAchieved,
FinancialYearOfDischargeOrDeath,
FinancialQuarterOfDischargeOrDeath,
FinancialMonthOfDischargeOrDeath,
CalendarMonthNameOfDischargeOrDeath,
[code]...
I just installed PowerView and PowerPivot, I made my queries, formulas, I put everything in a PowerView sheet in a Excel Binder.
Other colleagues did the same but worked on a different binder.
Question is: how can i merge those two binders in order to have, in a unique file, all my Powerview sheets?
We want to implement cascading slicers in PowerPivot 2010. Does Excel 2010 have 'Insert Slicer Hierarchy' feature in PowerPivot?
View 5 Replies View Related
The following is a SAMPLE data from an excel spreadsheet. This SAMPLE data has many other fields as date. Here I have only used two date columns i.e. 28 Dec 2006 and 29 Dec 2006
This data needs to be exported into sql server 2005 table which has the fields below where I have placed the data into a table.
How can this be done please?
data:
Ref Sector Name 28 Dec 2006 29 Dec 2006
1 Sovereign RUSSIA 05 null 173.21
2 Sovereign RUSSIA 07 102.99 102.22
3 Sovereign RUSSIA 10 114.33 104.63
4 Sovereign RUSSIA 18 115.50 145.50
...
sql server table
create table tblData
(
DataID int,
Ref int,
Sector varchar(20),
Name varchar(20),
Date datetime,
value decimal(6,2)
)
DataID Ref Sector Name Date value
1 1 Sovereign RUSSIA 05 28 Dec 2006 null
2 1 Sovereign RUSSIA 05 29 Dec 2006 173.21
3 2 Sovereign RUSSIA 07 28 Dec 2006 102.99
4 2 Sovereign RUSSIA 07 29 Dec 2006 102.22
5 3 Sovereign RUSSIA 10 28 Dec 2006 114.33
6 3 Sovereign RUSSIA 10 29 Dec 2006 104.63
7 4 Sovereign RUSSIA 18 28 Dec 2006 115.50
8 4 Sovereign RUSSIA 18 29 Dec 2006 145.50
...
How to repair a corrupted OLAP database?
View 2 Replies View RelatedSome of my calculated members look like "######" in Excel and I believe it is because of divide by zero errors. I have tried right clicking on the table, selecting "Table Options" and "For error, values, show:" I have enter 0. This is not working.
Does anybody know how to get rid of this.
Thanks,
Ruaidhri.
how to combine measures and fields coming from an analytical model (tabular) along with some Excel calculations. Basically I want to provide users with a simple report (to be displayed in SharePoint Excel services) containing charts and slicers. The data comes from a tabular model, and most of the calculations are in the model as well.However there is some little tweaking that must be done. For example I might need additional calculated columns, but I don't feel the need to modify the tabular model for that. I was wondering if I could do this within Excel as well -- but without having to bring all the data through a pivot table, then manipulate it and then show it on the report. So to be clear I do not want any pivot tables lying around, even if on a hidden sheet.
I noticed that when selecting a pivot chart in Excel, at the ribbon menu under "PIVOTCHART TOOLS"/"ANALYSE" there is a group of buttons named "Calculations". One of them is named OLAP Tools.Is it fair to assume that these options will allow me to create new measures at the Excel side, without affecting my tabular model?
All of my client boxes connect nicely to MS OLAP Server except one which fails with the following message: "Cannot connect to the registry on the server computer (servername)" where servername is the name of the server on which OLAP server resides.
Anybody seen this???
One of my excel 2013 power pivot report was migrated from old server to new server after migration i changed the excel power pivot connection string to connect with new server but the workbook connections is still taking the old connection string of old server and there is no option of changing workbook connection string .
I am able to edit the powerpivot connection but workbook connections are not getting updated they are still taking old server connection string.