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.
I'm a having a problem with PIVOT tables. I have a table that stores the results from multiple surveys, (see format below)... SurveyID RespondantID QuestionID Answer ========================================================= 1 1 1 X 1 1 2 Y 1 1 3 Z 1 2 1 R 1 2 2 S 1 2 3 T 14 10 1 A 14 10 2 B 14 11 1 C 14 11 2 D I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)... RespondantID Q1 Q2 Q3 =============================== 1 A B C 1 R S T
The 2 differences I need from a normal pivot query are....
(a) I don't want to summarise my results at all (b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in. So far I have come up with... SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument" Can anyone help?
I NEED KNOW IF SOMETHING KNOW CERTANLY .....ABOUT THIS :************************************************** ********************************PLACE A NAMED SET INTO A PIVOT TABLE : ALL ABOUT THIS..FORMULAS...FORMS...EXAMPLES.... ALL !ANOTHER : GET RELATIONSHIP ABOUT THE FIRST TROUBLE : SEE A NAMED SET FROM ACUBE ( OLAP)************************************************** ************************************************REALLY NEED ALL ABOUT YOU CAN SEND ME.AND WRITE ME ....A OLNLY FIN MANYQUESTIONS IN FORUMS....BUT NO ONE RESPONSES.....APARENTLY TOO MUCH PEOPLEHAVE THIS TROUBLE ...... I KNOW ....NO EVERY DAY .MEMBERS THAT POST IN THISWEBSITE..TAKE REPLY...BUT........, REALLY NEED INFO ABOUT .AS SOON ASPOSSIBLE....MI E-MAIL :Join Bytes!THANKS !.MY BEST WISHES...--hi all !
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
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.
Dear AllI am very new to MS SQL Server and I am wondering is there some toolwhich would allow me to build pivot tables in SQL more easily. At themoment writing a query can be quite challenging and difficult.Is there any software which allows you to do it more intuitively andgives you some visual feedback about query you are building?I would be very grateful for any help with this.wujtehacjusz
WITH TEST AS ( SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID, SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG, T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI INNER JOIN TERMINALGROUP TG ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
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 cant 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?
I work in a medical facility and and client confidentiality prevents loading my pivot tables to sharepoint at this time.
I am creating several reports that combine data from SQL Server, Cerner medical report DB and some lookup tables in Excel.
Everything works great on my desktop but I'm having trouble sharing my work.
Our normal routine is to drop a copy of our excel files in a Pass through file on the server that has strict access controls.
I think my main problem is my supervisor doesn't have powerpivot on his machine. that will be corrected tomorrow.
My question is: When close your powerpivot workbook, do all the connections go with it. If I just drop myproject.xlsx into the pass through will all the links to the varying data sources still be available?
Well the first half of it I am able to retrieve meaning the 'Demand' part by pivoting it from the table request and the next part i.e. 'Supply' is also obtained in the similar fashion.
Tbl_User may contain more skill categories than those mentioned in Tbl_Request. So the output should reflect only those categories that are existing in tbl_Request. How can we combine the both? I have taken both the outputs in two temp tables. Now I would like to know if I can combine them and show it as one output or if there is any other better way of doing it.
I am using a stored procedure which is called for my web application so I didn't go for views. Can someone tell me how to do it.
Product No Grade Quantity A Good A Normal A Bad B Good B Bad C Good C Normal C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity A Good 1 A Normal 0 A Bad 0 B Good 2 B Bad 0 C Good 3 C Normal 0 C Bad 0
I am developing a database in PowerPivot and I am wondering how to create many relationships between the same 2 tables. All relationships must be active.
Let me give you a DUMMY example: let's say that the database has 2 tables, the Employee table and Manager table:
I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.
I can't set them both up as CTEs and query, the statement gets truncated.
I can't use a temp table because those get dropped when the query finishes.
I can't use a real table because the insert statement gets truncated.
Do I have any other good options, or am I in Spacklesville?
I have this pivot table (I only post the static version as the problem only regards the single quotes)
SELECT * from( select DATEPART(year,DeliverydatePackingSlip) as Year, CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early' WHEN DiffPromiseDateFirst = 0 Then '2 - on time' ELSE '3 - too late' END as Delivery from iq4bisprocess.FactOTDCustomer WHERE OTD_Exclusion = 0)a PIVOT ( COUNT(Year) For Year
in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)
declare @title1 nvarchar(20) declare @title2 nvarchar(20) declare @title3 nvarchar(20) set @title1 = '1 - too early' set @title2 = '2 - on time' set @title3 = '3 - too late'
[Code] .....
exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.
I am trying to implement some powerview report in excel 2013 itself (not in sharepoint).
I have implemented a SSAS tabular model and deployed to server. Now my aim is to load this tables in the SSAS tabular database into powerpivot so that i can use this data for creating powerview reports. However when i tried to import data from SSAS, it asks database and MDX query. My requirement is to load the dimension and fact tables to power pivot as it is.
I want to count the rows in the Incident Table by using filters to limit the rows to be counted if they meet the below conditions. I know I need a logical test for each row of the incident table based on the apparatus table’s rows. But, I want to test for each row in the incident table, counting, but not returning a true or false in the overall measure.Something like look at each incident row, test for true or false and then count IF the statement is true. Then go to the next incident row and do the same. The aggregation would be the final count of “true” results.I tried this for MET objective:
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
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]
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)".
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])))
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'?
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?
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?
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.
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 arent available at this moment while data extracting is still going on?
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
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 ...)
