Pivot Task Error - Duplicate Pivot Key
Jul 5, 2006
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
View 3 Replies
ADVERTISEMENT
May 19, 2006
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
View 6 Replies
View Related
Jul 8, 2015
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
View 5 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
  HASONEVALUE(dimSalesCompanies[SalesCompany])
  ;IF(
    VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
    ;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
    ;[Sales]
  )
  ;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Oct 14, 2015
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
View 13 Replies
View Related
Aug 17, 2015
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies
View Related
Mar 11, 2015
I have simple pivot table (below screenshot with info redacted) that displays a population number ("N" below), this is the denominator, a cumulative numerator number (below "#") and a simple cumulative percent that just divides the numerator by the denominator. It cumulates from top to bottom. The numerator and percent are cumulative using the below functions. There are two problems with the numerator and percent:
1. When there is not a number for the numerator, there is no value displayed for both the numerator and the percent..There should be a zero displayed for both values.
2. When there has been a prior number for the numerator and percent (for a prior month interval) but there is no number for the numerator in the current month interval, the prior month number and percent are not displayed in the current month interval--see the 3rd yellow line, this should display "3" and "16.7%" from the second yellow line.Here is the formula for the numerator:
=CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory])))
Here is the formula for the percent:
=(CALCULATE(countrows(s1Perm1),FILTER(ALL(s1Perm1[ExitMonthCategory]),s1Perm1[ExitMonthCategory] <= MAX(s1Perm1[ExitMonthCategory]))))/(CALCULATE(COUNTROWS(s1Perm1),ALL(s1Perm1[Exit],s1Perm1[ExitMonthCategory])))
View 24 Replies
View Related
Jul 1, 2015
I have a query that uses the PIVOT function and works fine in SQL 2012. I've been asked to move the query to a database that has the compatibility level set to 80(SQL 2000). I receive an "Incorrect syntax near" error when I try to excute the query on the SQL 2000 database. I would like to duplicate the exiting PIVOT functionality in SQL 2000.The existing query retrieves employee names and the order that the employee should be displayed from a table. The names will appear on the report according to the order that is retrieved from the database. Also, the users have requested that only 5 names appear on each row of the report. This is why the PIVOT function was needed. Below is an example of how the existing query works.
Table
CREATE TABLE [dbo].[EmpGuest](
 [Guest_ID] [int] NOT NULL,
 [Guest_Name] [varchar](80) NULL,
 [Display_Order] [int] NULL
) ON [PRIMARY]
[code]....
View 4 Replies
View Related
Sep 18, 2015
I have data in my Powerpivot window which was generated by a sql query. This data includes a field named 'Cost' and every row shows a value for 'Cost' greater than zero. The problem is that when I display this data in the pivot table all entries for Cost display as $0. At first I thought that maybe Cost was set to a bogus data type (such as 'text) but it is set to ''Decimal Number' so that's not the problem.Â
What is happening and how do I fix it so that my pivot table reflects the values for 'Cost'?
View 3 Replies
View Related
Nov 23, 2015
I have a data table that contains budget and actual data by month. Â I use the data to create a pivot that shows actual results next to budgeted results. Â I need a column that shows that variance between those columns. Â I think my issue is that the "Type" field contains actual and Budget. Â I sum on "Type". Â I can't seem to create a sum since those items are in the same field or am I missing something?
Table design
Month|Division|Subdivision|Type|Dept|Rate|Units|Amount
October|DC|Day|Budget|125|10.00|100|1000
October|DC|Day|Actual|125|10.00|110|1100
Output Design
DC
DAY
Actual
Budget
125 AvgOfRate
AvgOfRate
SumOfUnits
SumOfUnits
SumOfAmt
SumOfAmt
View 4 Replies
View Related
Oct 9, 2015
How to get a list of values to actually display in correct order in either a slicer or when on an axis on a pivot table?
I currently have the below list and have tried to add a preceding numeric (ex. "1. <=0") or preceding blank space, neither of which is visually great. Is there another way?
<= 0
1 - 6
7 - 12
13 - 18
19 - 24
25 - 30
31 - 36
37 - 42
43 - 48
49 - 54
55 - 60
61 - 66
67 - 72
73 - 78
79 - 84
85 - 90
91 - 96
97 - 102
> 102
View 8 Replies
View Related
Apr 13, 2015
I am using excel 2010 and creating pivot table from Power Pivot. I created a pivot table with department slicers. All is good, the problem I am having is whilst in an unfiltered position (ALL) of the slicers (departments) I get 200 pages, now when I click on a given department with say 10 pages, I still get the same 200 pages with the first 10 pages showing the data from the clicked department and 190 blank pages.
All I want is to get a WYSIWYG (What you see is what you get) of what is on the screen as my print, but I am getting extra blank pages right after the data. Â How do I resolve this.
Below are the steps I go thru to printÂ
1. Select slicers in unfiltered position (ALL)
2. Select entire pivot table
3. Select Page layout and select print area.
4. Save
5. Click on Print Preview to preview the print
6. Click on a given department in the slicer and repeat item 5, but this gives me blank pages after the data.
Do I need any other step?Â
View 2 Replies
View Related
Oct 26, 2015
I am trying to find duplicate records based on a set of criteria, and flag one of those records as unique and the other duplicate ones as dup.
I found this suggestion:
IF( CALCULATE(Â
 COUNTROWS( Table1 );Â
 FILTER( Table1;  [Column1] = EARLIER( [Column1] ) && [Column2]
= EARLIER( [Column2]
) )Â
 ) > 1;
 "dup";
 "unique"
)
However, this method flags all duplicates as dup. how I can get to the enclosed results for one particular student?The dup check column shows the desired results. The criterion is any record with the same STUDENT_ID, PROG_DESC, FISCAL_YEAR will be considered as duplicates, but should at least have one record out of that group of duplicates flagged as 1.
STUDENT_ID| ACAD LEVEL |Â GENDER |
PROG_DESCÂ | RACEETHNICITY |
HOME_STATE | ACADEMIC_LEVELÂ | FISCAL_YEAR |
Dup Check
815581 Undergraduate
[code]....
View 6 Replies
View Related
Nov 15, 2011
I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".
View 15 Replies
View Related
Sep 21, 2007
Say, I have the following temporary table (@tbl) where the QuestionID field will change values over time
Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2
I'd like to perform a pivot on it like this: select * from @tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable
...however, I can't just name the [1], [2] values because they're going to change.
Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])
I tried something like this:
for QuestionID in (select distinct QuestionID from @tbl)
but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @tbl Pivot (min(Answer) for Question_CID in (select distinct @QuestionID from @tbl)) as PivotTable
or does anyone know another way to do it?
View 3 Replies
View Related
Jul 8, 2015
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 Related
Apr 29, 2015
I have a pivot table that connects to our data warehouse via a PowerPivot connection. Â The data contains a bunch of comment fields that are each between 250 and 500 characters. Â I've set the columns in this pivot table to have the 'Wrap Text' set to true so that the user experience is better, and they can view these comment fields more clearly.
However, whenever I refresh the data, the text wrapping un-sets itself. Â Interestingly, the 'Wrap Text' setting is still enabled, but I have to go and click it, then click it again to actually wrap the text. Â This is very burdensome on the user, and degrading the experience.
Any way to make this text wrapping stick so that I don't have to re-set it every time I refresh the data?
View 2 Replies
View Related
Oct 5, 2007
All,
I am having difficulty with an SSIS package that is simply to pivot a table, and perform calculations used in reporting.
Background:
A sample set of data is being tested to pivot for reporting purposes. The sample set of data being used is 2226 rows that will pivot to 6 rows having nearly 400 columns.
Problem 1:
This all seems quite simple except that when I try to pivot on one set of data it works perfectly, I try two at a time, again great. However, as I add three or more groups of data from a view (thus creating 3+ pivoted rows) SSIS throws an error of "-1071636293" and points the to the column containing the values to be pivoted. The error output dumps ALL source data rows with the same error code and message, even on data groups that were successfully pivoted and written to the destination DB all with correct mappings (including the noted with the error code)
Running the package on each group of data one set at a time, creating one pivot output row, always works for all data sets without throwing any errors. How is it possible that SSIS would run perfectly on each data set creating one row without any problems/errors/warnings, but when when run on all data at the same time it would fail?
Problem 2:
When running on multiple sets of data creating many pivoted output rows, the error output viewer and log file contain duplicated data rows from the original query. For example if I received the row the pivot id 1001 ONCE in the query from the DB as it is written out to the error file and viewer I see this ID TWICE for each data group. So if each data set were to contain 100 codes to match and I ran this on three data sets, creating an original 300 rows from the db query, the error file and viewer will have 303 rows. This occurs for only different ID's in depending on the data set, but one ID in particular is duplicated for ALL. How could a row be duplicated in the error output in one case but not another when the source data does not contain ANY duplicates?
Problem 3:
When running the package for three sets of data SSIS will note an error and redirect the row to the file I specified with no problem. However, as I run it on 4 or more data sets that cause more errors I receive the message: "The buffer manager attempted to push an error row to an output that was not registered as an error output. There was a call to DirectErrorRow on an output that does not have the IsErrorOut property set to TRUE." Why would the error logging work for one failure but have problems with more?
I am monitoring the package execution by:
1) logging all available output from SSIS to a file.
2) redirecting all errors from the pivot to a file which works only on the small data sets where I am expecting 1-2 rows to be the pivot result.
3) Placing data viewers on all directional arrows from original db query and pivot
Finally, even with all the errors or warnings all data is pivoted as desired and inserted into the destination database as if nothing was wrong, sometimes. Same package, same data, same errors, but delaying the package with breakpoints causes SSIS to either be successful or fail. I'll question that one later as I gather more information.
Any help on one or more of these questions would be a great help...
Thanks.
-1071636293
-1071636293
View 7 Replies
View Related
Aug 6, 2007
i am fallowing this link
http://sqljunkies.com/Article/705F07C3-69FE-4CAF-8CF8-CADBF145F372.scuk
last 15 th i am unable to understatnd the value to set tht pivotkey value for the output columns
INPUT COLUMNS LEGEND ID
CUSTOMER 861
PRODUCT 864
ID
OUPUT COLUMNS SOUREC COLUMN PIVOT KEY VALUE
CUSTOMER 861 ???????????????????
HamQty 864 ???????????????????
MILKQTY 864 ???????????????????
BEERQTY 864 ???????????????????
BREADQTY 864 ???????????????????
CHESSQTY 864 ???????????????????
HAMQTY CANNOT MAPPED WITH PIVOTKEY VALUE ERROR I AM GETTING
PLEASE HELP ME
View 1 Replies
View Related
Apr 21, 2006
Hi :
I am getting the following error message when I am trying to do the Pivot operation.
Msg 265, Level 16, State 1, Line 1
The column name "FirstName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 1
The column name "LastName" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 1
The column 'FirstName' was specified multiple times for 'Pvt'.
My SQL Query is:
SELECT UserID,FirstName,LastName
FROM
(
SELECT UserID,FirstName,LastName
FROM Tempreport AS ATR
PIVOT (
MAX(QuestionAnswer)
FOR QuestionText
IN ([CompanyAddress1],[CompanyAddress2],[CompanyName],[Country],[EmailAddress],[FirstName],[LastName],[PhoneNumber],[PostalCode],[Select the Category that best describes your Industry],[Select the Category that best describes your role],[Select the Category that best describes your title],[Town])) as Pvt
) Result (UserID,FirstName,LastName)
Here is the DDL:
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'FirstName','Testing1')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'LastName','Testing2')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyName','Testing3')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress1','Testing4')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'CompanyAddress2','Testing5')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Town','Testing6')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Country','Testing7')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PostalCode','Testing8')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'PhoneNumber','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'EmailAddress','Testing9')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your Industry','Testing1')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your role','Other')
INSERT INTO Tempreport (UserID,LastName,FirstName,OrderNumber,QuestionText,QuestionAnswer)
VALUES (200253,'Veerman','Mark',2,'Select the Category that best describes your title','Other')
CREATE TABLE Tempreport
(UserID INT,
LastName VARCHAR(255),
FirstName VARCHAR(255),
OrderNumber INT,
QuestionText VARCHAR(255),
QuestionAnswer VARCHAR(255)
)
Drop table tempreport.
Can anyone please let me know what I am doing wrong and how to fix this error?. looks like I cant select the same column inside the pivot and outside the pivot. In such a case what is my alternative?.
Any help is appreciated.
Thanks
M
View 12 Replies
View Related
Jan 5, 2006
Hey, did someone try to use the pivot transform?
It's not what you would call easy to configure...
In BOL there is the following section:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm
I have followed the instructions there and I get an error when I try to map the new output column to the pivotkey:
"Output column "Yellow" (69) cannot be mapped to PivotKey input column"
So I mapped the new output column to the column holding the quantity values. This worked.
May this be a bug in BOL?
Further on I got some strange behaviour of the pivot transform:
My input is the following CSV-Flatfile:
sk1;sk2;pk1;qty
A;1;Yellow;2
A;1;Green;1
B;1;Yellow;5
A;2;Blue;3
A;2;Green;9
A;1;Green;5
If I omit the last line, everything is fine.
When passing the file as is into the pivot transform the output is:
SK1 SK2 Yellow Green Blue
A 1 2 1 NULL
B 1 5 NULL NULL
A 2 NULL 9 3
A 1 NULL 5 NULL
As you can see the key a;1 is duplicate. The cols SK1 and SK2 are my primary key of the destination table.
When the input is sorted by the first two colums the pivot transform throws an Error:
Error: 0xC02020CF at Pivot w sort, Pivot [39]: Duplicate pivot key value "Green".
I expected to have a sum over all Green for the key A;1. It seems that I have to use an aggregate which in my opinion should
be obsolete here.
As a conclusion I have to say that this task is far away from perfect but not bad for a start.
Regards
Fridtjof
View 3 Replies
View Related
Oct 3, 2007
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!
View 21 Replies
View Related
Oct 23, 2014
I would like to have rows presented as columns. That's why I use the PIVOT function at the end.The resultset will be presented in Excel using an external connection to the view.
When I try to save the view I get the error
Msg 4104, Level 16, State 1, Procedure _TEST, Line 47
The multi-part identifier "vk.OppCode" could not be bound.
Code (restricting the columns that I actually have to the relevant columns only):
USE [DBTest]
GO
/****** Object: View [dbo].[_TEST] Script Date: 23-10-2014 17:24:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 1 Replies
View Related
Jun 24, 2015
I am getting this exception "An error occurred while loading the model for the item or data source '<filename.extension'>. Verify that the connection information is correct and that you have permissions to access the data source."" when I click in Power View icon in Power Pivot Gallery SharePoint.I tried steps mention this TechNet article but the issue is still not resolved.URL...
View 2 Replies
View Related
Mar 21, 2006
Hi all.
I moved my OLAP DB to SQL Server 2005, installed Microsoft OLE DB Provider for Analysis Services 9.0 on the client and keep creating Pivot Tables in Excel until...
Grouping - when I try to group couple of members (or even 1 for that matter) I'm getting :
"Intrnal error: An unexpected error occurred (file"mdinfo.cpp", line 3335 function "MDInfo::Init")."
The only reference to mdinfo error I found in OLAP newsgroup where a guy was getting it trying to process the cube. My cube is processed (obviuosly). So, anybody knows what has changed with respect to grouping in PTS?
Michael
View 13 Replies
View Related
Jun 25, 2015
The thing is I can´t make update powerpivot, it raises a generic error. Tried to import into ssas tabular and worked fine.
Is there any way to get a better error detail or debug it someway?
View 4 Replies
View Related
May 21, 2015
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification)Â .
The DAX code for calculating LY Sales is:
=
CALCULATEÂ (
    [Sales $],
    FILTER (
        ALL ( dCalendar ),
        dCalendar[IRIYearNumber]
            = VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.Â
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.Â
View 5 Replies
View Related
Jul 20, 2015
I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text.
Details:
  Value=40
  Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
View 2 Replies
View Related
Jun 11, 2015
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
View 5 Replies
View Related
Jun 27, 2007
Hi,
I've a table like,
ID Key Day Amount1 Amount2 Amount3 Amount4
1 100 1 0.00 7.00 0.00 2.00
2 100 2 8.00 0.00 0.00 0.00
3 100 3 0.00 6.00 0.00 0.00
I want to convert this table using Pivot like,
Key Day1 Day2 Day3 Amount
100 0.00 8.00 0.00 Amount1
100 7.00 0.00 6.00 Amount2
100 0.00 0.00 0.00 Amount3
100 2.00 0.00 0.00 Amount4
How can i do this? Pls explain me with query.
Thanks in advance
Jasmeeta
View 4 Replies
View Related
Apr 4, 2008
Hello all,
I am having some issues. Let me set the scene for you:
Table1:
Table2:
I hope you can get what's going on from those pics.
I'm trying to construct a datatable. the user specifies a date range, a bin no and a screen no. From that information, for each date in the date range, i want to show the added weight of each sieve for that particular bin and screen.
I hope this is at least a little clear.I really could use some help on this. thanks.
View 1 Replies
View Related
Apr 25, 2008
I have a table structure like this
Vendor Date Item
A1 04/21/2008 T1
A1 04/21/2008 T1
A1 04/21/2008 T1
C1 04/21/2008 T1
C1 04/21/2008 T1
D1 04/21/2008 T1
A1 04/11/2008 T1
A1 04/11/2008 T1
B1 04/11/2008 T1
C1 04/11/2008 T1
D1 04/11/2008 T1
D1 04/11/2008 T1
A1 04/1/2008 T1
A1 04/1/2008 T1
B1 04/1/2008 T1
B1 04/1/2008 T1
C1 04/1/2008 T1
D1 04/1/2008 T1
I want the result like
(date calculated from todays date 04/25/2008)
Vendor Count of item Count of item Count of item
last 10days last 20days last 25days
A1 3 5 7
B1 0 1 3
C1 2 3 4
D1 1 3 4
How can i write the query for this. Please help me ,
Mathew
View 11 Replies
View Related
Mar 31, 2008
hi,
how can I use a select statement in the IN part of the pivot sql instead of ([1],[2],[3])
eg,
I want to do :
PIVOT ( sum(numbers) FOR surname IN (Select surname from person)
that doesn't work though.
any ideas.?
thank you
View 1 Replies
View Related
