Are there any metasyntactic keywords for controlling the flattening
functionality when doing linked queries through SQL Server? I'm having
a problem where if my linked MDX query doesn't return any results then
it also doesn't return all of the columns! See the query at the bottom
of this message for my example.

The general flow is thus:
1. Get the data in the MDX query. Use NON EMPTY rows because the
[SoldTo] dimension on the ROWS axis has 14000+ records.
2. Flatten the MDX recordset and rename the columns to something
workable. The consumer (ColdFusion) doesn't handle the native flattened
column names very well.
3. Join the flattened query with our address book to provide more detail.

The problem is that when the WHERE clause in the MDX query is too
restrictive and no records are returned, the resultant query doesn't
contain the "KeyID" column ([ShipTo].[Addr].[Addr].[MEMBER_CAPTION]).
This, of course, throws the rest of the query for a loop. I could
probably just throw all of this into a stored procedure to check for the
existence of the column before it returns the result set, but with the
number of filter dimensions that I'm looking at (10+) that seems like a
bit of a kludge.

For now I've got the whole thing wrapped in a CFTRY/CFCATCH block in the
ColdFusion code that returns "No results found" instead of an error, but
that's also a bit of a kludge.

Thoughts, ideas?
-R

Example query:
------
SELECT
RTRIM(LTRIM(c.alpha_name)) as Name,
LTRIM(RTRIM(c.Address_Book_Number)) AS ID,
b.*
FROM address_book AS c INNER JOIN (
SELECT
CONVERT(int,CONVERT(varchar,"[When].[Invoice].[All
When].[2005].[3].[Measures].[ExtendedPrice]")) AS P0,
CONVERT(int,CONVERT(varchar,"[When].[Invoice].[All
When].[2005].[3].[Measures].[OrderedQty]")) AS Q0,
/* More date/value columns here */
CONVERT(varchar,"[ShipTo].[Addr].[Addr].[MEMBER_CAPTION]") AS KeyID
FROM
OPENQUERY(local,'
SELECT
CROSSJOIN(
(When.Invoice.&[2005].&[3]:When.Invoice.&[2005].&[6]),
{ Measures.ExtendedPrice, Measures.OrderedQty }
) ON COLUMNS,
NON EMPTY {ShipTo.Addr.Children} ON ROWS
FROM Forecast
WHERE (
ShipTo.Salesperson.Salesperson.&[900],
SKU.Segment.Segment.&[090],
SKU.Brand.Brand.&[DIX]
)
') AS a
) AS b ON (b.KeyID = c.Address_Book_Number)
WHERE (c.salesperson = 900)
ORDER BY Name
------

--
Rick Osborne

Only idea that comes to mind is to add a dummy calculated member to
rows, which always appears in results:

With Member ShipTo.Addr.[Ignore] as ''0''
SELECT
CROSSJOIN(
(When.Invoice.&[2005].&[3]:When.Invoice.&[2005].&[6]),
{ Measures.ExtendedPrice, Measures.OrderedQty }
) ON COLUMNS,
NON EMPTY {ShipTo.Addr.[Ignore],
ShipTo.Addr.Children} ON ROWS
FROM Forecast
WHERE (
ShipTo.Salesperson.Salesperson.&[900],
SKU.Segment.Segment.&[090],
SKU.Brand.Brand.&[DIX]
)
') AS a

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***