Analysis :: Top Count With Non Empty Clause

Aug 7, 2015

I have an autogenerated MDX.

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.

View 5 Replies


ADVERTISEMENT

Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?

May 25, 2015

below data,

Countery
parentid
CustomerSkId
sales

A
29097
29097
10

A
29465
29465
30

A
30492
30492
40

[code]....
 
Output

Countery
parentCount

A
8

B
3

c
3

in my count function,my code look like,

 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

it will take 12 to 15 min to execute.

View 3 Replies View Related

Analysis :: Convert A Zero To Null Or Empty?

Sep 10, 2015

I am working on a cube and I want to hide the results of a calculation if it is zero.

It is an inventory problem based on a transaction pattern. (add plus one to the inventory, add minus one to the inventory).

The sum of those two is zero, but the cube get messed but by thousands of zero.

I found some solutions of displaying 0 for NULL but I want to dispaly NULL (or empty) for 0.

View 2 Replies View Related

Field Names Missing In MDX Data Set When Using NON EMPTY Clause

Sep 25, 2007

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?

View 8 Replies View Related

Checking If A Table Is Empty Using Count

Sep 10, 2007

Hi,

I want to use something like select count(*) from table name = 0; to check whether a table is empty,

is this possible?

Thanks for any info.

Al.

View 4 Replies View Related

Analysis :: YTD / MTD Functions Return Empty Values Probably Due To Old Test Data

Jun 17, 2015

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.

View 4 Replies View Related

Analysis :: How To Handle Empty Resultset From OpenQuery Call To Linked Server

Aug 17, 2015

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.

View 2 Replies View Related

Analysis :: Date In Where Clause

Aug 3, 2015

Getting result for this
 
  from [Trans]
  where [Date].[Date].[2015-07-22] 

But when i used like this not getting any result 

With

member measures.A as "2015-07-22"
 from [Trans]
  where [Date].[Date].[measures.A] 

View 3 Replies View Related

Analysis :: MDX Query Parameters In WHERE Clause?

Sep 25, 2015

I need to filter my select statement with 2 parameters, each of which defaults to the "All" member.  Some of the members may have spaces in the name.

So I need to handle something like these:

FROM MySalesCube
WHERE ( ... expression here ...)

The expression above  needs to resolve to a set using both parameters

 STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")

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.

[update]

Here is what I have now:

WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"]  , [Department].[Department].[" + @pDept +  "])")  ) )

If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error

View 2 Replies View Related

Count For One Day - Use Between In Where Clause?

Aug 26, 2013

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.

View 5 Replies View Related

Analysis :: MDX - Get Data For The Time Between Two Dates In Where Clause

Nov 10, 2015

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

View 2 Replies View Related

Analysis :: Where Clause On Measure Group In MDX Query

Nov 11, 2015

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.

View 6 Replies View Related

Count Subquery In Where Clause (3.1)

Apr 18, 2008

I am attempting to do something like this...

select * from applebucket ab where 2 = (select count(id) from apples ap where ap.applebucket_id = ab.id)

SSCE 3.1 throws the following

There was an error parsing the query. [ Token line number = x,Token line offset = y,Token in error = SELECT ]

Any help would be greatly appreciated!

View 3 Replies View Related

Adding A Count Clause To A Query

Jun 5, 2008

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.

View 5 Replies View Related

Analysis :: Count Columns Of A Result - MDX

Jun 26, 2015

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...

View 3 Replies View Related

Analysis :: Distinct Count On Cube

Jun 12, 2006

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.

View 17 Replies View Related

Analysis :: MDX - Count A Set As Calculated Measure

Sep 9, 2015

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

[Code] .....

View 5 Replies View Related

Analysis :: Count Working Days MDX

Nov 23, 2015

 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]
);

View 4 Replies View Related

Analysis :: SSAS 2012 - MDX To Find Last 7 Days Data In Where Clause?

Jul 6, 2015

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.

View 2 Replies View Related

SQL 2012 :: Estimate Row Count - WHERE Clause Of Statement

Oct 16, 2014

How SQL calculates the estimated row count when < or > are used in the WHERE clause of a statement. For example:-

WHERE Created_Datetime_utc > CONVERT(DATETIME,'2014-10-14 10:00:00',102)

I know how the estimated number of rows are calculated when an = is used but I've been googling and cannot find anything about < and >.

View 2 Replies View Related

Analysis :: Count Of Rows In SSAS MDX Query

Sep 17, 2015

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:

Member [Measures].[CntCT] 

View 4 Replies View Related

Analysis :: Distinct Count Using Filter Function

May 27, 2015

My requirement is to count the customer order number for premium order type orders which has some order quantity.I am using below MDX

count

Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[P]),[Measures].[Ordered, pcs]>0 ) ,
)

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

count

Filter
(([Customer Order].[Dim Customer Orderkey].[Dim Customer Orderkey].members,
[Outbound Order Attributes].[Order Type].&[C]),[Measures].[Shipped, pcs]>0 ) ,
EXCLUDEEMPTY)

View 3 Replies View Related

Analysis :: How To Get Cumulative Count Of Business Days

Jul 9, 2015

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:

   COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))

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.

What am I doing wrong?

View 7 Replies View Related

Analysis :: Count Distinct In SSAS Cube?

Sep 8, 2015

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?

Environment: SSAS 2012 Multidimensional Model

View 4 Replies View Related

Analysis :: MDX - Putting A List In Where Clause That Includes A Dimension Already Mention In Crossjoin

Sep 22, 2015

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]

[Code] ....

View 4 Replies View Related

OUTPUT @count =no Of Records Returnd By Dynamic Where Clause

Aug 18, 2005

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."

View 1 Replies View Related

Analysis :: Query To Display Count Of Products When Month Is Primary

Sep 18, 2015

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?

View 2 Replies View Related

Analysis :: Calculating Distinct Count Over A Period Of 3 Consecutive Years

Aug 11, 2015

I have the need to calculate a distinct count over a period of 3 years. I use the MDX  

SUM([Year].[Year].CurrentMember.Lag(2):[Year].[Year].CurrentMember,[Measures].[CNT])

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

A    A1
1 1 2 1
A2 1
0 1 1
A3 0
1 1 1

How can I achieve this?

View 5 Replies View Related

Analysis :: DAX For Distinct Count Of Related Column Without Returning Out Of Memory Error?

Aug 10, 2015

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....

View 3 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

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) 
 

View 1 Replies View Related

Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.

Aug 6, 2006

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

View 5 Replies View Related

Analysis :: Power BI Analysis Services Connector - Remote Server Returned Error

Mar 5, 2015

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.

View 10 Replies View Related

Analysis :: Cube Needs To Be Deployed From VS After SSIS Analysis Services Processing Task Completes?

May 13, 2014

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,

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved