Nested Select - Help

I dont have a clue what i'm doing wrong.

SELECT Tbl_Region.REGION, [NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS], [NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS], [TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS], [TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM (((Tbl_Region LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg, Count(qry_New_Members_HMO_All_Regions_1.CONTRACT_N UM) AS [# of New Members]
FROM (SELECT tbl_hmo.Reg, tbl_hmo.CONTRACT_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo.reg, tbl_hmo.CONTRACT_NUM

) AS qry_New_Members_HMO_All_Regions_1

GROUP BY qry_New_Members_HMO_All_Regions_1.reg
) AS NEW_HMO_CONTRACTS ON Tbl_Region.REGION = [NEW_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_New_Members_HMO_All_Regions_1.reg, Count(qry_New_Members_HMO_All_Regions_1.MEMBER_NUM ) AS [# of New Members]
FROM (SELECT tbl_hmo.reg, tbl_hmo.MEMBER_NUM
FROM tbl_hmo LEFT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo_History.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo.Aff_Area, tbl_hmo.MEMBER_NUM

) AS qry_New_Members_HMO_All_Regions_1
GROUP BY qry_New_Members_HMO_All_Regions_1.reg) AS 4_NEW_HMO_MEMBERS ON Tbl_Region.REGION = [4_NEW_HMO_MEMBERS].reg) LEFT JOIN (SELECT qry_Termed_Contracts_HMO_All_Regions_1.reg, Count(qry_Termed_Contracts_HMO_All_Regions_1.CONTR ACT_NUM) AS [# of Termed Contracts]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.CONTRACT_NUM = tbl_hmo_History.CONTRACT_NUM
WHERE (((tbl_hmo.CONTRACT_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.CONTRACT_NUM
) AS qry_Termed_Contracts_HMO_All_Regions_1
GROUP BY qry_Termed_Contracts_HMO_All_Regions_1.reg) AS TERMED_HMO_CONTRACTS ON Tbl_Region.REGION = [TERMED_HMO_CONTRACTS].reg) LEFT JOIN (SELECT qry_Termed_Members_HMO_All_Regions_1.reg, Count(qry_Termed_Members_HMO_All_Regions_1.MEMBER_ NUM) AS [# of Termed Members]
FROM (SELECT tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
FROM tbl_hmo RIGHT JOIN tbl_hmo_History ON tbl_hmo.MEMBER_NUM = tbl_hmo_History.MEMBER_NUM
WHERE (((tbl_hmo.MEMBER_NUM) Is Null))
GROUP BY tbl_hmo_History.reg, tbl_hmo_History.MEMBER_NUM
) AS qry_Termed_Members_HMO_All_Regions_1
GROUP BY qry_Termed_Members_HMO_All_Regions_1.reg)
AS TERMED_HMO_MEMBERS ON Tbl_Region.REGION = [TERMED_HMO_MEMBERS].reg;

error:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Server: Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.I haven't a clue what you are doing right. Are you getting paid by the parenthesis?

"WHERE (((tbl_hmo_History.CONTRACT_NUM) Is Null))"?

Isn't this:

"WHERE tbl_hmo_History.CONTRACT_NUM Is Null"

...simpler and easier to read?

And I suspect this may be throwing your first error:

"LEFT JOIN [SELECT qry_New_Members_HMO_All_Regions_1.Reg,..."

The square brackets denote a database object. SELECT is a statement, not an object.

Clean up your code, format it well with indents, and try running the individual components separately before putting them all together. That is the best way to debug.|||it was working in access then i brought it over to sql and then MESS...

Are you getting paid by the parenthesis? -- haha i wish.

i will work through it again tomorrow.

thanks for looking at it.|||THAT was working in ACCESS?!

...but that explains the square brackets. I've had Access throw those into free SQL querys before, and then the query won't work until you take them out again. A bug, for sure.

Was it a single free SQL statement, or were the subqueries manifested as independent views?|||I got it to work.
PLEASE let me know if you see anything wrong with what i did.

SELECT dbo.Tbl_Region.REGION_NAME, [4_NEW_HMO_CONTRACTS].[# of New Members] AS [HMO NEW CONTRACTS],
[4_NEW_HMO_MEMBERS].[# of New Members] AS [HMO NEW MEMBERS],
[5_TERMED_HMO_CONTRACTS].[# of Termed Contracts] AS [HMO TERMED CONTRACTS],
[5_TERMED_HMO_MEMBERS].[# of Termed Members] AS [HMO TERMED MEMBERS]
FROM dbo.Tbl_Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.CONTR ACT_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.CONTRACT_NUM
FROM tbl_HMO LEFT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.CONTRACT_NUM = tbl_HMO_History.CONTRACT_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.CONTRACT_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_CONTRACTS].Region LEFT OUTER JOIN
(SELECT qry_New_Members_HMO_All_Region_Names_1.Region, COUNT(qry_New_Members_HMO_All_Region_Names_1.MEMBE R_NUM)
AS [# of New Members]
FROM (SELECT tbl_HMO.Region, tbl_HMO.MEMBER_NUM
FROM tbl_HMO Left JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO_History.CONTRACT_NUM IS NULL
GROUP BY tbl_HMO.Region, tbl_HMO.MEMBER_NUM) AS qry_New_Members_HMO_All_Region_Names_1
GROUP BY qry_New_Members_HMO_All_Region_Names_1.Region) [4_NEW_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [4_NEW_HMO_MEMBERS].Region Left OUTER JOIN
(SELECT qry_Termed_Contracts_HMO_All_Region_Names_1.Region ,
COUNT(qry_Termed_Contracts_HMO_All_Region_Names_1. CONTRACT_NUM) AS [# of Termed Contracts]
FROM (SELECT dbo.tbl_HMO_History.Region, dbo.tbl_HMO_History.Contract_Num
FROM dbo.tbl_HMO RIGHT OUTER JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
dbo.tbl_HMO.CONTRACT_NUM = dbo.tbl_HMO_History.Contract_Num
GROUP BY dbo.tbl_HMO_History.Region, dbo.tbl_HMO.CONTRACT_NUM, dbo.tbl_HMO_History.Contract_Num
HAVING (dbo.tbl_HMO.CONTRACT_NUM IS NULL)) AS qry_Termed_Contracts_HMO_All_Region_Names_1
GROUP BY qry_Termed_Contracts_HMO_All_Region_Names_1.Region ) [5_TERMED_HMO_CONTRACTS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_CONTRACTS].Region Left OUTER JOIN
(SELECT qry_Termed_Members_HMO_All_Region_Names_1.Region, COUNT(qry_Termed_Members_HMO_All_Region_Names_1.ME MBER_NUM)
AS [# of Termed Members]
FROM (SELECT tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM
FROM tbl_HMO RIGHT JOIN
dbo.tbl_HMO_History ON dbo.tbl_HMO.Reg = dbo.tbl_HMO_History.Reg AND
tbl_HMO.MEMBER_NUM = tbl_HMO_History.MEMBER_NUM
WHERE tbl_HMO.MEMBER_NUM IS NULL
GROUP BY tbl_HMO_History.Region, tbl_HMO_History.MEMBER_NUM) AS qry_Termed_Members_HMO_All_Region_Names_1
GROUP BY qry_Termed_Members_HMO_All_Region_Names_1.Region) [5_TERMED_HMO_MEMBERS] ON
dbo.Tbl_Region.REGION_NAME = [5_TERMED_HMO_MEMBERS].Region

negative values when calculating percentages

I get negative values for percentage calculation in a MDX query. The MDX query has a crossjoin between two sets containing calculated members from the same dimension, one of the calculated members being a percentage value. I'm not sure why some of the percentage values are negative.

Another problem I'm facing is that the percentage value is not being displayed as per the FORMAT_STRING property, in my Reports in Reporting Services 2005 that use the data generated by the MDX query.

Any help or suggestion is appreciated.

Hi. Can we see the MDX query you're using and a small data sample which provides the negative percentage?

PGoldy

|||

Hope this will give you an idea:

WITH
MEMBER [ITEMA].[ITEMA].itemmember
as

' (ITEMA.ITEMA.&[itemmember], [Measures].[NUMBER]) '

MEMBER [ITEMA].[ITEMA].TOTAL
as

' SUM(ITEMA.ITEMA.Members, [Measures].[NUMBERS]) '

MEMBER [ITEMA].[ITEMA].ITEMPERC
as

' Iif(IsEmpty([ITEMA].[ITEMA].TOTAL),0,([ITEMA].[ITEMA].itemmember / [ITEMA].[ITEMA].TOTAL)) ', FORMAT_STRING = '#.#%'

select [Measures].[NUMBER] on columns,

non empty crossjoin({[ITEMA].[ITEMA].MEMBERS,[ITEMA].[ITEMA].OTHERS,[ITEMA].[ITEMA].itemmember,[ITEMA].[ITEMA].TOTAL,[ITEMA].[ITEMA].ITEMPERC},
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]})) on rows
FROM [MyCube]

Data Snapshot:

ITEMA_1 ITEMA_2 ........ ITEMA_itemmember ITEMA_TOTAL ITEMA_ITEMPERC

- ITEMB_1
ITEMC_1 10 20 10 100 -0.1
ITEMC_2 5 6 0 150 0.0
ITEMC_3 0 1 2 4 0.5

- ITEMB_2
ITEMC_1 ...................................................................
ITEMC_2 ...................................................................
ITEMC_3 ...................................................................
+ ITEMB_3
+ ITEMB_4
.
.
.

|||

Hi. Thanks for the detailed query and example.

I don't see why you get a negative percentage, but I see you're using the calculated members to get values which are normally available in the cube without the use of a calculated member when you construct the right query. I think you should reconstruct your query to use the WHERE clause and re-define, and eliminate, some calculated members to get the correct results Here are my recommendations:

(1) Use the WHERE cluase to slice your qeury by the desired measure: WHERE (Measures.Number)

(2) Reference ITEMA dimension on the columns.

(3) Eliminate the following calculated members because they are not needed and we can derive the dsired values from normal intersections in the cube: (a) MEMBER [ITEMA].[ITEMA].itemmember, (b) MEMBER [ITEMA].[ITEMA].TOTAL

(4) Change the definition of MEMBER [ITEMA].[ITEMA].ITEMPERC to reference the correct cube intersections.

Assumption: ITEMA hierarchy has an "all" member, aggregation type for Measures.Number is SUM.

Here's the new query with the recommended changes:

WITH
MEMBER [ITEMA].[ITEMA].ITEMPERC AS
' Iif(IsEmpty([ITEMA].[ITEMA].[All ITEMA]),0,([ITEMA].[ITEMA].CurrentMember / [ITEMA].[ITEMA].[All ITEMA])) ', FORMAT_STRING = '0.0%'

NON EMPTY {[ITEMA].[ITEMA].MEMBERS, [ITEMA].[ITEMA].[All ITEMA], [ITEMA].[ITEMA].ITEMPERC} ON COLUMNS,
crossjoin({ITEMB.ITEMB.children},{ITEMC.ITEMC.children,[ITEMC].[ITEMC].[others]}) on rows
FROM [MyCube]
WHERE ([Measures].[NUMBER])

Hoe this helps.

PGoldy

Need urgent help on Aggregate Function

Hi,

I need to do something similar to Proclarity in SSAS 2005. In Proclarity i can create a named member by selecting a few members from a dimension. The underlying mdx looks like this

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })

Once i select the named member all the calculated measures reflect the result based on the members selected in the aggregate function.

How can I do something similar in SSAS 2005. I dont want a named set and calculated member does not work if i use the plain mdx as above. But i also tried using

Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }, [Measures].Orders Received Local)

just to try to to see teh behaviour but to no avail. after processing the cube and selecting this meausre shows up empty cells.

How can I solve this problem.

thanks in adavance for helping

Could you explain why "calculated member does not work if i use the plain mdx as above" - what dimension/hiererchy did you create the member on, and what results did you get?|||

Thanks for asking this question. I think i made a mistake by leaving the parent hierarchy as default "Measure" and Parent Member as empty. I have modifed the Parent hirerachy to Channel.Channel and Parent Member to Channel.All Channels. I am processing the cube now and would get back to you soon with the results.

Thanks once again

|||

after i put the correct hierarchy and parent member the mdx works just fine.

Thanks a million for your help.