Jeje - EXISTS() with COUNT works. Needs to add SET to it.

MS SQL SERVER

    Sponsored Links

    Next

  • 1. Any recommandation Books SSAS AMO ?
    Hello I'm searching Books or Link's to a Documentation for Microsoft.AnalysisServices AMO Namespace. Did not find very much Online. Any recommandation, book hint, link is very appreciated Than you for Help Bye Paolo
  • 2. Data level Security in SSAS
    We are migrating our Analysis Services 2000 to SSAS. Our main concerns are Performance and Data level Security. Will we be able to achieve any performance difference by migrating the OLAP as it is without doing any design change And also we need to restrict by Levels . For example we need to restrick by branch the access rights. So do we need to go one by one all dimensions which has Branch as a Hierachy level & restict? Can any body help on this
  • 3. Measure only for specified member
    Is there an MDX function which will only return a measure for a specified member? For instance, how would I return this measure: [Measures].[CF # Feedbacks] only for member 1: [Feedback Types].[Type ID].&[1] Any help is appreciated. Thanks, CB
  • 4. Processing MSAS 2005 Cubes Using C# (Info for Basic Reference Material)
    Gurus, I am relatively new to MSAS 2005 and have created cubes using BIDS. I have to populate the cubes, partitions within the cubes. I am extremely new to C# and have lot of experience in Oracle OLAP. Therefore, I need some pointers to loading/ processing cubes using C#. Are there some very basic reference books or documents/ pdfs for the same? Thanks,

Jeje - EXISTS() with COUNT works. Needs to add SET to it.

Postby UHJhc2Fk » Thu, 03 Aug 2006 03:16:01 GMT

Hi Jeje,

I am able to get the count working now. See the MDX below. I have one filter 
in my "WHERE" clause - Local Market with "NE - North East" region.

WITH
	MEMBER[Measures].[Count] AS 
	'count(exists([Servicing Provider].[Hierarchy].[TAX_ID_NBR].members,
	         {[Time].[Hierarchy].[Year].&[2006]}, "SALES MSR GRP"))'

SELECT
{[Measures].[Count]}ON COLUMNS FROM [SALES Model]
WHERE
([Local Market].[Hierarchy].[REGION_CD].&[NE])
 
 Now, i want to add one more region "SE - South East" to the 'WHERE CLAUSE". 
I am getting the following error.

Error: The 'Hierarchy' hierarchy appears more than once in the tuple.

WITH
	MEMBER[Measures].[Count] AS 
	'count(exists([Servicing Provider].[Hierarchy].[TAX_ID_NBR].members,
	         {[Time].[Hierarchy].[Year].&[2006]}, "SALES MSR GRP"))'

SELECT
{[Measures].[Count]}ON COLUMNS FROM [SALES Model]
WHERE
([Local Market].[Hierarchy].[REGION_CD].&[NE], [Local 
Market].[Hierarchy].[REGION_CD].&[SE])

Please help with the syntax. I tried creating a set for the Local Market 
with 'SE' & 'NE' and included it in my "WHERE". But the stmt returned 0.

Thanks,

Prasad.



Re: Jeje - EXISTS() with COUNT works. Needs to add SET to it.

Postby ZULFIQAR SYED » Mon, 21 Aug 2006 02:35:58 GMT

Hi Prasad,

Here is a sample query based on adventure works where it adds couple of
promotion member items to the where clause. This might you in adding
extra region in your where clause.

Thanks

WITH
MEMBER MEASURES.X AS
	[Promotion].[Promotion].&[1]
SELECT
(
[Date].[Calendar].[Calendar Semester].&[2003]&[2]
.CHILDREN
)
ON 1,
(
[Measures].[Reseller Order Quantity]
,

[Product].[Product Categories].[Category]
)
ON 0
FROM
[Adventure Works]

WHERE
{
	[Promotion].[Promotion].&[1]
	,	[Promotion].[Promotion].&[2]

}

HTH..

ZULFIQARSYED

 http://www.**--****.com/ 







Re: Jeje - EXISTS() with COUNT works. Needs to add SET to it.

Postby UHJhc2Fk » Wed, 23 Aug 2006 02:13:01 GMT

Hi Syed,

Your suggestion works. Thank You.

I found the problem in my statement. I was referring to multiple members 
(more than one) from the same dimension in the tuple. That is why i got the 
error.

Once i made it a SET, the problem is resolved.








Similar Threads:

1.Adding SBS 2K8 Std to existing set-up that has Sql '08 server

I have a prospect with SBS 2K with Sql 2K and Exchange running with 30
clients (they have only 20 CAL's) They have been completely managed
in house until last Friday when they let their IT employee go. The
employee bought a copy of Std Server 2K8 and Sql Std 2K8, both have 5
CALS. Is there any way to buy SBS 2K8 Std and use the Std Server 2K8
and Sql Std 2K8 they own and end up with SBS 2K8 Premium?

MM


2.Need Help with Adding a Duplicate record count column to Query

I am attempting to create a simple recordset that would return the
number of duplicates that exist in a table with a single column.  For
example if I had a table like the following:

ID   Reference   Amount
1    123456      1.00
2    123456      2.00
3    123         1.00

I would like to get the following result:

ID   Reference   Amount    RecCount
1    123456      1.00      2
2    123456      2.00      2
3    123         1.00      1

Please help!
Thanks,
Shawn

3.Multiple Distinct Counts and Distinct Count where NULL values exist

Hi,

I have an SSAS cube that only has one measure group but I need to
deliver two distinct count measures. Seeing as you can only deliver
one distinct count per measure group, can someone please recommend the
best approach for delivering two distinct counts from one fact table.

Also, one of the columns that will be used for one of the distinct
count measures contains NULL values and I was wondering if there was
an easy way of disregarding the NULL values when generating the
distinct count.

Any help will be greatly appreciated!

Thanks,
Stu

4.Import not working- need SQL statement to add table2 into table1

Ok.  Access03 isn't allowing me to import an XLS file.  So I imported
it into a table.  Now I need to take the contents of table one and add
it to table two.

I'm not very access-literate. So type slowly and enunciate


Thanks

C_kubie

5.adding existing column to an existing replication

Hi all,

Here is my question, hope you can help or shed some light. I have a 
replication setup like following:

ServerA replicating to ServerB with filtered columns, ServerB then 
replicating to ServerC without column filtering. Table1 on ServerA has 
columns: col1, col2,col3, col4,col5.  but only col1 and col2 are being 
replicated. Now we want to replicate col3 and col4 as well. So the data in 
col3 and col4 is available on ServerB and ServerC.  My question, what is the 
best and fastest way to do this? Can I just drop the article and then re-add 
it with all columns we need. What is the snapshot implications? 

Any help or commnets will be much appreciated. 

Brian

6. Add an existing SQL 2000 server to an existing Win 2k3/SQL2k C

7. Add an existing SQL 2000 server to an existing Win 2k3/SQL2k Clust

8. adding information to existing existing data



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 73 guest