I have created a SSAS Tabular model cube which this connects too.
The cube contains
Measures: - CountA, SumB, CountC, SumD, CountE
Dimensions:
- TblLabelDefinition(ID,Definition,Type)
- TblLabels(ID,Type,reference,date,sold,leftover,A,B,C,D,E)
- References(ID,reference,date)
Below is a query I am working on:
SELECT
NON EMPTY
{
[Measures].[CountA],
[Measures].[SumB],
[Measures].[CountC],
[Measures].[SumD],
[Measures].[CountE]
}
ON COLUMNS,
NON EMPTY
{
(
[TblLabelDefinition].[Definition].[Definition].ALLMEMBERS *
[TblLabels].[Type].[Type].ALLMEMBERS
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
(
SELECT ( -{ [TblLabels].[sold].&[1] } ) ON COLUMNS
FROM
( SELECT ( { [TblLabels].[leftover].&[0] } ) ON COLUMNS
FROM [Model]
)
)
WHERE
( [TblLabels].[leftover].&[0] )
AND
(
EXISTS([TblLabels].[reference], [References].[reference])
)
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I am trying to check if reference in the TblLabels table exists in References table.
This is the error I get: The And function expects a string or numeric expression for the 2 argument. A tuple set expression was used.
Try the below to get rid of the syntactic error:
SELECT
NON EMPTY
{
[Measures].[CountA],
[Measures].[SumB],
[Measures].[CountC],
[Measures].[SumD],
[Measures].[CountE]
}
ON COLUMNS,
NON EMPTY
{
(
[TblLabelDefinition].[Definition].[Definition].ALLMEMBERS *
[TblLabels].[Type].[Type].ALLMEMBERS
)
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM
(
SELECT ( -{ [TblLabels].[sold].&[1] } ) ON COLUMNS
FROM
( SELECT ( { [TblLabels].[leftover].&[0] } ) ON COLUMNS
FROM [Model]
)
)
WHERE
(
[TblLabels].[leftover].&[0]
,
(
EXISTS
(
[TblLabels].[reference],
[References].[reference]
)
))
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
As a side note:
EXISTS
(
[TblLabels].[reference],
[References].[reference]
)
yields a set of those references that exists in the table TblLabels
for one or more references in the table References
. If these tables are joined on the 'reference' column then the above EXISTS
construct would return only those references that are present in both References
and TblLabels
. Do check if that is what you needed in the first place.