Analysis :: Limiting Sum Function To Only Evaluate The Data In Slicer
May 22, 2015
I am having trouble limiting a sum function to only evaluate the data in the slicer. I need a sum of data calculated per employee and when there is a slicer of one employee my measure still evaluates all employee. For example:
WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM([Employee].[Employee].members, [Measures].[Actual Ovt Hours])
select
{
[Measures].[TotalEmpHours],
[Code] ....
The above reports the overtime for one employee in [Measures].[Actual Ovt Hours], I expected [Measures].
[TotalEmpHours] to be the same as there is only one employee in the slicer select. The real measure is the minimum of two values for each employee.
This is a simplified example but I think it should work.Is there another construct to iterate the employee set? I have no control over the query as it is from a pivot grid. If the filter (employee) is leftmost in the row area, then it works because the employee ends up in the row part of the query.
SELECT NON EMPTY ORDER({[Bi Employee].[Employee Key].CHILDREN}, [Measures].[Bi Measure Count], DESC) ON AXIS(0), [Measures].[Bi Measure Count] ON AXIS(1) FROM [mycube] WHERE ([Bi Rank In Trip].[Rank Key].&[1], [Bi Date].[Year].&[2015], EXCEPT({[Bi Route].[Route Key].CHILDREN}, {[Bi Route].[Route Key].&[ 1005]}))
Because I want all Route_key except ' 1005'. But based on the results (which are not good), its clear to me that this function do not act the way I think. How can I exclude Route Key ' 1005' otherwise?
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 am trying to evaluate dates within my sub queries for A,B,C,D. If there is a count > 0 in the 'A' category, I need to evaluate if the servicedates in B,C,D are within 30 days of 'A'. How would I code this within my current code?
select customernumber, SUM(CASE WHEN category = 'A' then 1 else 0 end) 'A', SUM(CASE WHEN category = 'B' then 1 else 0 end) 'B', SUM(CASE WHEN category = 'C' then 1 else 0 end) 'C', SUM(CASE WHEN category = 'D' then 1 else 0 end) 'D'
[Code] ....
My BEFORE the case statement:
Customernumber A B C D SERVICEDATE 123456789 1 0 0 0 2012-02-07 123456789 0 0 1 0 2012-02-28 123456789 0 0 1 0 2012-03-06 123456789 0 1 0 0 2012-12-17
My output after case statement:
Customernumber A B C D 123456789 1 1 2 0
What I need my output to look like after case statement:
Customernumber A B C D Service w/in 3 mnths 123456789 1 1 2 0 2
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 am looking a way to use a tail funcion to achieve something like this
If you see the picture i want obtain in one row the total amount of the month over a selected range. If I select months between 1 and 12, i want the values only for the month 12, if a select months between 1 and 10 I want only de value for October and so on.
I tried this simple mdx query:
Select Tail([Id Fecha Orden Pago].[Mes].allmembers,1) on Rows , {[Measures].[Total Gastos Orden Pago]} on Columns From [CUB_DGM_TestEnamiDW] WHERE [DIM Programa Fomento].[Nombre Programa Fomento].&[RECONOCIMIENTO DE RESERVAS]
I am here with a doubt in MDX. I want to calculate the Binomial Distribution for one of the Measure in my cube. The requirement is to create a new calculated measure in the cube which calculates the binomial distribution for an inbuilt measure.I am using SSAS 2012 Standard Edition.
WITH MEMBER [Measures].[Parm1] AS [Member].[Member Deceased Year].currentmember.member_caption MEMBER [Measures].[ResultValue] AS ([Month].[Calendar].[Month].&[20150531], [Measures].[Member Count]) SELECT ({[Measures].[Parm1],[Measures].[ResultValue]}) ON COLUMNS, ORDER([Member].[Member Deceased Year].[Member Deceased Year], [Measures].[Parm1], DESC) ON ROWS FROM [TXERSDW]
Now from the Above query I am resulting with NULL as Parm1 values, I need to replace with a value of 12-31-9999 in the Parm1 if there are NULLS.
I would like to provide users a way to dynamically select the dimension by which they analyse data in a pivot table. In a simple example, my fact table has 3 columns: product type, colour, volume. The pivot table consolidates the volume, and a slicer allows the user to display the sum of volume either by product type or by colour.
The solution I have come to so far involves creating a table with a single column that contains all the single values of product type and colour. There are 2 relationships between the fact table and this lookup table: one active on the product type column, one inactive on the colour column.
A 3rd disconnected table provides a way to select which of the 2 relationships is the active one for the consolidation metric. The example is available here.
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
I’m trying to build a calculated member (see below script) using “Except” function but I get an error result:
#Error The function expects a string or numeric expression for the argument. A tuple set expression was used.
My idea is to take a measure and exclude 2 members from the dimension.
I tried using “Aggregate” but got the error:
#Error Query (3, 1) Aggregate functions cannot be used on calculated members in the Measures dimension.
Please note that my measure is ACD that is already calculated average in olap and I can’t use AVG function instead of Aggregate. What can I do?
With
member [Measures].[AppOrig All Roaming] as ( Except( [Source IP Location].[Country Name].[All].children,{ [Source IP Location].[Country Name].&[Colombia]
I am using following Query to get data from Cube for some dates and Quarters at single time, but I am getting an error "Two sets Specified in the UNION function have different dimensional".
WITH MEMBER [Measures].[Data Value1] AS [Measures].[Average Value] SELECT {UNION( ([DimPeriodType].[Period Type Key].&[1],[DimDate].[Date Key].&[20150428],[DimEnergyCalculated Ratio].[Calc Ratio Key].&[1],[Measures].[Data
Can somebody on the SSRS product team double check me that I've written that up right? That seems like a pretty important change and I can't see it mentioned in BOL anywhere.
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value]) returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
I am trying to get the last 60 days before a month starts. I have a set that is returned from the query below :
SELECT non empty [Measures].[TRANSACTIONS Count] on 0, non empty ([TRANSACTIONS].[Days].[Days], [TRANSACTIONS].[Transaction Month].[Transaction Month]) on 1 from [cube]
I can get the cummulative count of last 60 days before month 2 by hardcoding the day of transaction of start of month like below :
WITH MEMBER [Measures].[Cumm Account Count] AS ( AGGREGATE( [TRANSACTIONS].[Days].CurrentMember:NULL ,[Measures].[TRANSACTIONS Count]) )
SELECT non empty [Measures].[Cumm Account Count] on 0, non empty [TRANSACTIONS].[Days].&[3]:[TRANSACTIONS].[Days].&[3].lead(60) on 1 from [cube];
and for subsequent months by using the dates that the following month starts. How can I achieve the above result without having to use the day numbers, I tried to use the tail function (to get the months and star date) but it wont work because the range function accepts members only...
I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.
With MEMBER [Measures].[YTD Actual] AS Aggregate ( PeriodsToDate ( [DimDate].[CalendarHierarchyDateLevel].[Calendar Year] ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER )
Note that the Store Number is selected, but the Store Region is not, it's just the result of cross-filtering. Regional Sales incorrectly matches National Sales. If I then select the Region, the measures work:
I'm actually using VBA to change the Store Number slicer, as the end users don't want to select the region, then scroll through a list of store numbers. They just want to enter a store number and hit enter. I've tried a few things in DAX and VBA, and failed.
I havea flat file source.All of the coulmns in the source file are string of 5o length( default). There going to be couple of numeric columns like beginning balance, end balance etc. Now I have to evaluate those column to see if there is any column has more then two digit after decimal point. I have to do it through vb.net scripting in SSIS.
Can some one please give me some suggestion how can I achieve it?
hi I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I am just wondering about the Discretization function available on analysis service server (which can not actually discretize data into user-defined expressions) . Isnt it redundant ? I mean since users can discretize their data more meaningfully based on their own expressions in database engine of SQL Server 2005.
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?
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 have a table which has expression as a column and the data looks similar to the following .
ID (int) Expression (nvarchar data type)
1 8*(1/2)-6
2 278*(1/4)-2
3 81*(3/5) +4
I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)
Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this.
I am unable to cast/convert nvarchar to float/int
Any sample code would be greatly appreciated.
This a very urgent requirement for me.Please get back
I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:
We want to start a job remotely on another SQL7 server.
innocently we thought that should be no problem with sp_start_job.
We used the command listed below:
use msdb go EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'
The strange thing is, that sp_start_job ignores the @server_name at all.
Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance. sp_start_job seems to search locally for the given job_name, only. Consequently if we enter the remote server's name the job fails as the jobname is not present on the local sqlserver.
All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.
Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.
I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.
Here is the vb version i use in Ms Access...
getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)
If IsNull(strFormula) Or strFormula = "" Then getFormula = dblHours Exit Function End If
I've been tasked with taking over the support for a client's SQL Serverdatabase. I'm not a DBA by profession, I'm a software developer whouses SQL Server as a database designer.The clients have reported that the server is running out of disk spaceand examination shows that the log files for several of the databasesare at 5Gb or more.After reading around the subject I suggested the following sequence ofoperations:-- Select the name of the database you want to shrinkUSE MyDB-- Dump unwanted transactionsdump tran MyDB with truncate_only-- Get the name of the logfileSELECT * from sysfiles-- Having examined the rows returned by this use the log file....-- Shrink the file to required size (in MB)DBCC SHRINKFILE('MyDB_log', 10)Is this a reasonable approach? Please bear in mind that I'm pretty newto this, and I have many other tasks to do besides manage the server.A previous DBA has set up good maintenance plans etc. so everything isbeing properly backed up (well, I think it is)If this IS a good approach, would it be reasonable to do this on, say,a monthly basis as a scheduled job? Obviously the stepSELECT * from sysfileswhich gives us the physical name of the log file would be removed andthe job would operate explicitly on each log file for each database inturn.Many thanks for reading.William Balmer.
