November 16, 2011 at 12:17 pm
Currently I am working on a project where I have to calculate a measure that is based off two facts. The client sells packages which are a group of services. I have to calculate the consumption of the package within a date range. Using TSQL, I can calculate the value using two dates, but I cannot figure out how to do this using SSAS/MDX.
An example of my TSQL query is below:
SELECT t.ilocation, sum(t.quantity*t.price)
FROM PackageDetails pd
INNER JOIN Transactions t ON t.ipackageid = pd.iid AND t.ilocationid = pd.ilocationid
INNER JOIN PackageInvoices p ON pd.iparentpkgid = p.iid AND pd.ilocationid = p.ILOCATIONID
WHERE
p.TransactionDateKey>= 20111001 AND p.TransactionDateKey <= 20111031
AND t.TransactionDateKey>= 20111001 AND t.TransactionDateKey <= 20111031
group by t.ilocation
The Transactions and PackageInvoices are facts and the PackageDetails provides the information that relate the Package sale to the Transactions. The Transaction table does not contain the sale of the packages.
I use the PackageInvoices to calculate Package Sales, and the Transactions to calculate Service sales.
How can I accomplish the same using SSAS/MDX?
November 17, 2011 at 2:31 am
Please do not crosspost. It wastes peoples time and fragments replies.
Original thread can be found here:
http://www.sqlservercentral.com/Forums/Topic1207038-17-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply