Select
Non
Empty{[Measures].[Exposure
Amount]} On
Columns,
Non Empty TopCount(
( AddCalculatedMembers(
[Code] ....
As a result I only get 29 records. One record in the middle is missing. If I change to Topcount 31 I get 32 records, where the missing record is also included.
If I remove the Zero supression I receive 30 records as expected. For me the MDX looks good. I will try to reproduce on AW.
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
So I have an MDX query in an SSRS data set. Here is my MDX query:
Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses)
Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run.
How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
I have managed to use the BI Wizard for time intelligence and added YTD and MTD successfully. I notice the values returned are empty, and I think this is due to the fact that all the test data I use is many years old. What's the simplest way to resolve this issue so that I can see that these MDX functions return correct values? Changing the system date on this company laptop is not an option.
As i have to handle the empty result set from and open query call to linked analysis server in dynamic SQL. If there is no data returning from the query then i just wanted to display message with no data.In current scenario it gives me below the error.
Msg 7357, Level 16, State 2, Line 13 Cannot process the object "MDX QUery".
The OLE DB provider "MSOLAP" for linked server "CO1BMXPSQL08" indicates that either the object has no columns or the current user does not have permissions on that object.
Running the query in SSMS, Im using something like this to test
WHERE ( [SalesPerson].[SalesPerson].&[17] , [Department].[Department].[All] )
I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.
I have a query that is counting the number of applications gotten for the day.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID, COUNT (MerApp.ApplicationID)AS [Number of Apps] FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamID WHERE MerApp.LastUpdateOn BETWEEN @strStartofDays AND @strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1 GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription
LastUpdateOn is the days the table is updated
Since it's just a 24hr count I should have to use BETWEEN in my where clause. What is another way to count for 1 day.
I have this simplified query below to illustrate what I'm looking for. I would like to get the data for the time between the two dates in the where clause ('01-05-2015' and '10-03-2015'). In SQL it would be a BETWEEN. I'm looking for the MDX equivalent.
I have these dates hard coded but what I eventually will be doing is an @BeginDate / @EndDate parameter in an SSRS report.
SELECT [Measures].[Ship Resale S&D Run Rate] ON COLUMNS, [Branches].[Region].[Region] ON ROWS FROM [Sales] WHERE [Time].[Date].&[01/05/2015]:[Time].[Date].&[10/30/2015] *WHERE @BeginDate : @EndDate
When I am executing below MDX query, it's giving correct result with out any issue
SELECT NON EMPTY { [Measures].[Daystorecieve] , [Measures].[PO Recieved], [Measures].[Post Award Milestone PO Analysis Count], [Measures].[Powith80pct Received]
[Code] ....
After the successful execution of the above query, I am trying to filter on my measure group [Measures].
[Daystorecieve] values not equal to "0". With minimum number of the dimension selection my query executing fine.
Please find the below query.
SELECT NON EMPTY { [Measures].[Daystorecieve] , [Measures].[PO Recieved], [Measures].[Post Award Milestone PO Analysis Count], [Measures].[Powith80pct Received] } ON COLUMNS,
[Code] ....
But, when I am trying to execute with total number of dimensions. It's running long time and giving out of memory exception. Is there any way to apply where condition on my measure group like where [Measures].[Daystorecieve]<>0.
I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING: SELECT Active_Activities_temp.* FROM Active_Activities_temp LEFT JOIN ACTIVITY_CORE_LISTING ON Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv WHEREACTIVITY_CORE_LISTING.cde_actv is null ORDER BY prtcpnt_id So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.
The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.
What code can I implement to do what I need to do? Thanks so much.
I am trying to count the columns of a MDX query but I haven't yet found a solution. Below is the query
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS FROM [XCUBE] WHERE ( [TIME].[Year Quarter Month].[Quarter Name].&[Quarter 1, 2015], [CustomerType].[CustomerType].&[Money], [Measures].[Total X] )
With result as
All r k l j g l 7000 1 7 8 5 4 3
The All counts the total of the [Measures].[Total X] and I want to measure the number of columns that presented at the result. Unfortunately my dimension Customers doesn't connect with time dimension...
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select {} on 0, nonempty ( {([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])} , {([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])} ) on 1
I would like to count the working days of a spesific time range and then find the Average Daily Dispatches.Currently the time range is at WHERE statement. how to count the dates in a month range. I prefer not to add a new calc Member.
WITH MEMBER [Measures].[Working Days] AS COUNT(Date.[Working Date].&[1])--Doesn't work MEMBER [Measures].[Average Daily Dispatches] AS [Measures].[Total Dispatches]/[Measures].[Working Days] SELECT [Measures].[Average Daily Dispatches] ON 0 FROM [cube] WHERE ( [Date].[Month].&[2015-01-01T00:00:00]:[Date].[Month].&[2015-08-01T00:00:00] );
I have a MDX query , where I have a date Range in where clause.
I want to replace it with Cuurent Date and Last 7 days date.
I tried multiple ways using NOW function , but could not get it correct .
modifying the Query so that I can fetch DATA for last 7 days
SELECT NON EMPTY { [Measures].[X] } ON COLUMNS, NON EMPTY { ([PRODUCT].[PRODUCT].[PRODUCT].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [COLOR].[COLORName].&[BLACK], [COLOR].[COLORName].&[BLUE] } ) ON COLUMNS FROM ( SELECT ( [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[3].&[7].&[20150706] : [Date].[Calendar].[Calendar Year].&[2015].&[2015]&[2].&[6].&[20150629] ) ON COLUMNS FROM [MYCUBE]))
I want to replace Date Hard code value , I have used Calendar Hierarchy of date dimension. to find Last 7 days Data.
I need to find the percentile using cube so i am using the below formula :
((n-1) * p /100) -1
n= count of number of array records p= percentile
I am using below MDX query:
WITH MEMBER [Measures].[PV] AS 25 Member [Measures].[CntCT] as Count(NonEmpty([Tb City].[City Name].&[DC], [Measures].[CPT1])) Member [Measures].[PVInt25] as Int(((([Measures].[CntCT] - 1)* [Measures].[PV])/100) - 1) Member [Measures].[PVC] as ([Measures].[CPT1],Order(NonEmpty([Tb City].[City Name].&[DC],[Measures].[CPT1]), [Measures].[CPT1],ASC).Item([Measures].[PVInt25]))
Select [Measures].[PVC] on columns, {[Tb City].[City Name]} on rows from test;
The line 2: Member [Measures].[CntCT]
In that i need to find the n count of rows where city is DC (City is my Dimension) in Measures CPT1
But currently it is giving the result 1 instead in actual in my test cube there is the city DC exists with 23 CPT1 rows count.
I tried the below query:
SELECT NON EMPTY {[Tb City 1].[City Name].[City Name].&[DC] } ON COLUMNS, NON EMPTY { ( [Measures].[Tb Main Count] ) } ON ROWS FROM [test]
Above query gives me the correct count i.e. 23 but i need to get the result of above query in line 2 of MDX query:
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 am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
Background: I have a Huge fact dimension table(table has both measures and dimensions) that i am using to build a SSAS Cube.
The table didn't have a unique identifier, so the database team added ROW NUM as a column to the table which i am using as a PrimaryKey in my Cube build. I was able to create a cube successfully with it without any issues.
Problem: Now customers are asking for a 'Claim Count calculation' which shows the Distinct Claim Count.
Its defined as below :
Count(Distinct Claim_Number || Claim Year || Claim Month)
. All the 3 columns are available in table, but when i am trying to create this Count Distinct Object in the DSV the Cube processing time increased by 5 times, as now i have to use a GroupBy function in my SQL.(There are around 30 columns to group by).
Is there a better way to achieve this Count(Distinct Claim_Number || Claim Year || Claim Month) without using groupBy in DSV SQL logic? I cant seem to find any Count(Distinct) function in the Cube Calculation functions?
SO I have a need to to limit the members of a dimension that get included with a query to just a few. So that means specifying which ones. How do I limit the main set based on a defined list of members in one of the dimensions im using.
In the query below, I need to limit the number of dimension members being included for CommType and MetricType. I get errors if I use a list of specific members in a where clause that includes these same dimensions.
WITH MEMBER [Measures].[YTD Actual] AS SUM({[DimCalendar].[Month Year].&[2015]&[1] : STRTOMEMBER('[DimCalendar].[Month Year].&[' + CSTR(YEAR(NOW())) + ']&[5]') } , [Measures].[Actual]
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic. Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
I am writing a query to display the count of products when a month is the primary month (Month where large sales happened)..Please consider the following query..
With Member ProductRank as Rank([Date Due].[Calendar].currentmember,TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount])) Member PrimaryProd as Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1)) Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,bdesc) on 1 from [Sales]
Output:
It runs for 13 seconds on my laptop. I have modified the query to get the results fast like With
Set MySet as TopCount([Date Due].[Calendar].[Month],1,[Measures].[Sales Amount]) Member ProductRank as Rank([Date Due].[Calendar].currentmember,MySet) Member PrimaryProd as Count(Filter([Product].[Product].[Product],[Measures].ProductRank=1)) Select PrimaryProd on 0, Order([Date Due].[Calendar].[Month],PrimaryProd,Bdesc) on 1 from [Sales]
Output:
This query runs instantly and result is not the same. Am I missing something here?
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects') Other measure which returns no errors, but included for completeness: List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID) 2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I have, a SSAS 2012 tabular instance with SP2, there is a database on the instance with a read role with everyone assigned permissions. When configuring the Power BI analysis services connector, at the point where you enter Friendly Name, Description and Friendly error message, when you click next I receive the error "The remote server returned an error (403)." I've tested connecting to the database from Excel on a desktop and connect fine.I don't use a "onmicrosoft" account so don't have that problem to deal with.
We use Power BI Pro with our Office 365. As far as I can tell that part is working ok as I pass that stage of the configuration with a message saying connected to Power BI.The connector is installed on the same server as tabular services, its a Win2012 Standard server. The tabular instance is running a domain account that is the admin account for the instance (this is a dev environment) that account is what I've used in the connector configuration. It's also a local admin account. There is no gateway installed on the server.
I have a cube that we are processing nightly via an Analysis Service Processing Task in SSIS. In order to increase the performance of the processing time, we elected to use a lot of rigid dimension attributes, and do a full process of everything in the SSIS task. The issue that I am having is that after that task completes, I need to go into Visual Studio to deploy the cube becuase we are unable to browse or use the cube. This issue seemed to start once we changed the SSIS Analysis Service Processing Task to do a full process on the dimensions, rather than an incremental.
I would expect that once development is done, and it is processed and deployed, that is it. My thinking is that the SSIS task should just update the already deployed cube,
