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]
Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema. I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better: TableName FieldName Xpos Ypos ---------- --------- ----- ----- FruitTable FruitName 10 20 VegTable VegName 10 40 FruitTable FruitColour 20 10 (Thats not the real data of course) What I need is a calculated field which returns the value of each field from each table – probably by constructing a dynamic sql statement(?) It would look something like this: Select @FieldName From @TableName Where bla bla bla – don’t worry about the where clause. The completed dataset will hopefully then look like this: TableName FieldName Xpos Ypos FieldValue (calculated field) ---------- --------- ----- ----- --------- FruitTable FruitName 10 20 Oranges (result of: Select FruitName From FruitTable Where....) VegTable VegName 10 40 Parsnips (result of: Select VegName From VegTable Where....) FruitTable FruitColour 20 10 Green (result of: Select FruitColour From FruitTable Where....)
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function: Main query:Select TableName, FieldName, Xpos, Ypos, dbo.GetFieldValue(TableName, FieldName) As FieldValue From tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))
RETURNS nvarchar(255) AS BEGIN
Declare @SQL nvarchar(max) Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName
sp_executesql @SQL??
return ???
END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance
Hi all, I have a sql problem i'd like to put to the masses because its driving me crazy! Before I start, this is a database i inherited so I cant change the schema.
I have a table which holds field information for a form, namely the table name, column name and some other irrelevant stuff (X/Y coordinates for printing onto a document). Here is some sample data to explain better:
What I need is a calculated field which returns the value of each field from each table €“ probably by constructing a dynamic sql statement(?) It would look something like this:
Select @FieldName From @TableName Where bla bla bla €“ don€™t worry about the where clause. The completed dataset will hopefully then look like this:
I have tried creating a scalar-valued function which takes TableName and FieldName as parameters and creates a dynamic sql string, but i cannot seem to execute the sql once I have built it. Here is a general idea of how I was trying to use the function: Main query:Select TableName, FieldName, Xpos, Ypos, dbo.GetFieldValue(TableName, FieldName) As FieldValue From tblFieldAndPosition---------------Function: CREATE FUNCTION GetFieldValue (@TableName nvarchar(255),@FieldName nvarchar(255))
RETURNS nvarchar(255) AS BEGIN
Declare @SQL nvarchar(max) Set @SQL = 'Select ' + @FieldName + ' From ' + @TableName
sp_executesql @SQL??
return ???
END ------------------------- The alternative to getting this data all out at once is contructing the sql statement in code and going back to the database once for every row - which i really dont want to do. If anyone has had a situation like this before, or can point me in the right direction I will be very very grateful. Hope thats clear. Thanks in advance
Below is my query and returned rows. I am pulling unique dx codes for each account. In the case below I have 14 returned rows, each having a unique dx_key (second column), which I need to display across the report horizonaly versus vertical. (see below example)
I came across the post below by Manivannan.D.Sekaran on using the PIVOT function but I can't quite get the hang of it. Can someone give me some help please?
12451538 117782 20190 Hodgkin's disease, unspecified site, extranodal and solid organ sites 12451538 117783 5789 Gastrointestinal hemorrhage, unspecified 12451538 117784 5990 Urinary tract infection, site not specified 12451538 117785 2768 Hypopotassemia 12451538 117786 2449 Unspecified acquired hypothyroidism 12451538 117787 28529 Anemia of other chronic disease 12451538 117788 3051 Tobacco use disorder 12451538 117789 53540 Gastritis without mention of hemorrhage 12451538 117790 53190 Gastric ulcer without hemorrhage or perforation, without mention of obstruction 12451538 117791 5589 Noninfectious gastroenteritis and colitis 12451538 117792 56210 Diverticulosis of colon without mention of hemorrhage 12451538 117793 2113 Benign neoplasm of colon 12451538 117794 V1259 Personal history of diseases of circulatory system 12451538 117795 V011 Contact with or exposure to tuberculosis
Below is an example of a pivot table from the help in SQL2005. My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], [198], [223], [231], [233] ) ) AS pvt ORDER BY VendorID
I have the following data:Product Type Hours Controllers Development 105.0Controllers Research 1.0Controllers Sustaining 24.0How do I use SQL statement to change it to the following?Product Development Research SustainingControllers 105.0 1.0 24.0Thanks.DanYeung
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]
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
I have a DATESMTD function which is not working. Â This is what is happening, if there is no row data for the month it creates a month to date total similar to the year to date total instead of zero. Â See below my formula:
MTDSUM:=CALCULATE(SUM('Combined Years Dataset'[Net]),DATESMTD('Combined Years Dataset'[Period2]),'Date'[date])
Period 2 is a column with dates (end of monthdates) in a table called Combined Years Dataset.
So, if I have $200.00 data for Aug and no data for Sept, the system puts in 200.00 as the month to date  for Sept instead of zero.  What can I do to make the system insert zero in the month to date column instead of the $200.00.  What am I doing wrong in the formula.
In 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.
I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.
=switch(HASONEVALUE('s1JudgeIds'[JudgeName]), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])), values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])
I've encountered an issue where the ALLSELECTED function works fine unless I use a date hierarchy, i.e. year, quarter, month, date, for the rows in a pivot and then use a year slicer, select one or more years individually, and the clear the filter on the slicer. The year(s) that I selected in the slicer remain at 100% in the pivot instead of returning to the subtotals for the unfiltered slicer.
This only occurs when I use a calendar hierarchy for rows and a date field for the slicer, either from the hierarchy or a regular date field.Below are images of the normal behavior and then the result after following the steps above. Can't figure out why the calendar hierarchy is causing the issue since it works for all other time functions, etc., and follows best practices such as contiguous dates, etc.Â
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.Â
I am relatively new to SQL-Server. I was working in a company that used SS2005. Now I am with a company and client where SS2K is my only option. I know SS2005 had Analysis Services which allowed building hypercubes but I never got the chance to learn about it or use it. I am not that familiar with what SS2K has.
I have no access to any OLAP tools. I have MS-Access and MS-Excel. My SQL-Server database has to crunch a table with thousands of rows.
Having worked with MS-Access and MS-Excel for a long time, I know how powerful creating a pivot table is. I heard that SQL2005 had a T-SQL PIVOT function but never had the opportunity to use it.
Know I have the need to do this in SQL2000.
I have a lot of data so the method shown in BOL for cross-tab won't help me. The client changes the "column" and "header" categories on a regular basis, so I need something that will allow me to NOT hard code them but choose them dynamically like one would do in a hypercube.
I have more than 5 categories for "row headers". I have currency for the data to aggregate. I have "pay periods" (26 of them) for my "column" headers, but not all 26 will be present until Dec 31, so that part has to be dynamic.
Question: Is there any feature in the standard S2K that allows one to create anything like a pivot table without massive coding (T-SQL function or technique or something like a hypercube ability)?
Can you point me to something on the web that would explain how to get started?
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.
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.
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 aren´t 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 ...)
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?
Hi, i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same
SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC
Hi everyone, I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.
Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY") Proposed solution: lock(MyDataLayer) ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X") if(ID==null) ID=1 MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)") unlock(MyDataLayer) (This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)
Do you think the preceding solution is equivalent to the original? Do you know something better?
Equivalent should mean here, not necessarily generating the same ID's, but maintaining functionality and consistence all over the database.
