Is it Possible to Join 2 Queries Using MDX?

Hi,

First, my knowledge of MDX is very limited :o

I am wondering if it is possible to return the result of 2 different queries as one. Similarly, like using UNION in SQL. I looked into the MDX UNION but it works on sets.

Basically, I have 2 queries; one with territory sales and one with region sales. I would like to return the results of all the territories followed by the sales of the region in one go.

Thanks in advance.HUH?

Want to post some sample code?

Read the sticky at the top of the board|||Hi Brett,

I am working with Reporting Services 2005 and Cubes. I create my dataset by picking my measures, dimensions and default parameters in design mode. I face the scenario now where I have two datasets but that I would like to combine them into one so that I can just output them as a list.

I can go into MDX mode and see the MDX query generated by RS, but it is very messy and complex, and I don't think it is worth posting.

So back to the problem, I have two MDX queries (whatever they are), that return the same columns but different rows. What I am hoping for is that there is a way to combine the MDX queries into one big query. Just like in SQL where you can use UNION to join the results of two SELECTS.|||I really need to play with Reporting Services an Analysis Services...

I suspect I do all that stuff manually in the first place|||you might be able to use UNION in sql if you use calls to OPENROWSET to fetch the rows from AS. basically union the two OPENROWSET results together.

Like this:

select * from OPENROWSET('MSOLAP', 'connstr', 'query1') union
select * from OPENROWSET('MSOLAP', 'connstr', 'query2')|||Hi jezemine,

That's big hack ;)

The thing is that, due to security reasons, OPENROWSET is blocked on our servers.

Still, thanks for your input.

Is it possible

Is It Possible to use Inner Join -- to retrieve data from a different Database? Or is it limited to the same Database? The Example below throws me an error. Thank You.
--------------------------------------------------------------
Function GetNames(ByVal uid As Integer) As System.Data.DataSet Dim connectionString As String = (ConfigurationSettings.AppSettings("ConnectionString"))
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test.dbo].[uid], [Test.dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test.dbo] ON [Manufacturers].[uid] = [Test.dbo].[uid]"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_uid As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_uid.ParameterName = "@.uid"
dbParam_uid.Value = uid
dbParam_uid.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_uid)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End FunctionYour syntax is incorrect. You are missing ] and [ when referencing the other database.
Dim queryString As String = "SELECT [Products].[uid], [Products].[ManufacturerId], [Products].[Name], [Manufacturers].[uid], [Manufacturers].[AddressID], [Test].[dbo].[uid], [Test].[dbo].[Company] FROM [Products] INNER JOIN [Manufacturers] ON [Products].[uid] = [Manufacturers].[uid] INNER JOIN [Test].[dbo] ON [Manufacturers].[uid] = [Test].[dbo].[uid]"
|||Thank You Douglas,

I did try it that way, and it threw me this error:

Invalid object name 'Test.dbo'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Invalid object name 'Test.dbo'.
Source Error:

Line 74: dataAdapter.SelectCommand = dbCommandLine 75: Dim dataSet As System.Data.DataSet = New System.Data.DataSetLine 76: dataAdapter.Fill(dataSet)Line 77: Line 78: Return dataSet

|||asp.netcat, you don't have a fully qualified table name there. You should be using this format when referring to your table:

database.owner.tablename

and this format when referring to a column in your table

database.owner.tablename.columnname

|||

Thank you Terri & Douglas,

Yes Indeed I overlooked & forgot all about adding the Table Name. I do have it working now. Big Thanks!! As always... You Guys are the Best!!!