Database Reference
In-Depth Information
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
Tip
For better performance it is recommended to use
FORMAT_STRING
to
specify a value to return if a specific condition is met. For example, the
previous query could be rewritten to return
NULL
if the reseller transaction
count is zero. Next we would use
FORMAT_STRING
to cleverly substitute
N/A
for
NULL
values as follows:
WITHMEMBER [measures].[average unit
price] AS
IIF (measures.[reseller transaction
count]=0, NULL,
[measures].[reseller unit price] /
measures.[reseller transaction count]),
FORMAT_STRING="$#,##0;;;\N\/\A"
SELECT [Geography].[Country].[All
Geographies].ChildrenON 0,
[Measures].[average unit price] ON 1
FROM [Adventure Works]
WHERE [Product].[Product
Categories].[Category].[Components]
For more information on this topic please refer to
ht-
4. Now suppose that you'd like to limit the result set only to those countries
in which the reseller sales amount for the components category exceeded
$300000. You could use the
FILTER
function to check the reseller sales
amount in each country. To verify the output, let's include the reseller sales
amount along with the average unit price on the rows axis:
Search WWH ::
Custom Search