Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


group by clasue in mdx


group by clasue in mdx

Author
Message
asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
Hi Friends i have small doubt in mdx query.
table having data like below
table name : patieninformation


pn prcode dos Expected Payment
MS0000003 PT001 1/2/2013 29.72 0
MS0000003 PT001 1/21/2013 57.1 0
MS0000003 PT002 6/7/2013 26.69 0
MS0000003 PT002 6/7/2013 89.16 0
MS0000003 PT002 6/6/2013 57.1 0
MS0000003 PT002 6/7/2013 12.28 0
MS0000003 PT002 6/7/2013 26.69 26.69
MS0000003 PT002 6/7/2013 89.16 77.16
MS0000003 PT002 6/17/2013 57.1 57.1

Based on this table data i need to output like below




pn prcode maxdos list of servicedays(max of dos-min of dos) Expected Payment
MS0000003 PT001 1/21/2013 19 86.82 0
MS0000003 PT002 6/17/2013 11 358.18 160.98



maxdos means highest date based on prcode wise
list of service days : highet date from dos-least date from dos based on prcode wise.


i tried in sql query
select pn,prcode ,max(dos) as maxdos, max(dos)-min(dos) as list of servicedays frOM [patientinformation] group by pn,prcode

same way how to implement in mdx query.please tell me how to write mdx query to solve this issue
stevefromOZ
stevefromOZ
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1929 Visits: 3754
asranantha,

MDX is used against OLAP sources (ie 'cubes'). You've provided no details of your cube at all, only a DB table.

Have you built your cube yet? If so, maybe if you outline the fact and dimension tables (ie the source) and then anything you may have done with the creation of the dims and measure groups, including what your measures are, *then* you might have more luck getting a response form someone who simply writes the mdx for you.

Steve.
a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 406
Is this what you are looking for? I didn't find any cube related information in your question, so I did this in straight SQL..

DECLARE @Input TABLE
(
pn VARCHAR(10),
prcode VARCHAR(10),
dos DATE,
Expected DECIMAL(10,2),
Payment DECIMAL(10,2)
)

INSERT INTO @Input VALUES('MS0000003', 'PT001', '1/2/2013', 29.72, 0), ('MS0000003', 'PT001', '1/21/2013', 57.1, 0)
,('MS0000003', 'PT002', '6/7/2013', 26.69, 0),
('MS0000003', 'PT002', '6/7/2013', 89.16, 0),
('MS0000003', 'PT002', '6/6/2013', 57.1, 0),
('MS0000003', 'PT002', '6/7/2013', 12.28, 0),
('MS0000003', 'PT002', '6/7/2013', 26.69, 26.69),
('MS0000003', 'PT002', '6/7/2013', 89.16, 77.16),
('MS0000003', 'PT002', '6/17/2013', 57.1, 57.1)

;WITH CTE AS
(
SELECT pn, prcode, MAX(dos) AS Max_dos, MIN(dos) AS min_dos, SUM(expected) as expected, SUM(payment) AS payment
FROM @Input
GROUP BY pn, prcode
)
SELECT a.pn, a.prcode, Max_dos, DATEDIFF(d, min_dos, max_dos) AS differnce, a.Expected, a.Payment
FROM CTE a



Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
stevefromOZ
stevefromOZ
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: Moderators
Points: 1929 Visits: 3754
a4apple - i think he had provided his own tsql, but is looking for an 'equivalent' in MDX. But as stated previously, he's given no details on the dims/measures that he has implemented (assuming he has implemented them) so is asking us how long is that piece of string :-D

Steve.
asranantha
asranantha
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 4717
Hi all, applogy to given some wrong information. Here Cube Name :Testcube....> its having measures expected,payment.
dimensions: dimpatient: its having attribute is pn
Dimprcode: its having attribute is prcode
Dimdate: its having attribute is dos
based on this attributes and measures for one pn having sample data like below
pn prcode dos Expected Payment
MS0000003 PT001 1/2/2013 29.72 0
MS0000003 PT001 1/21/2013 57.1 0
MS0000003 PT002 6/7/2013 26.69 0
MS0000003 PT002 6/7/2013 89.16 0
MS0000003 PT002 6/6/2013 57.1 0
MS0000003 PT002 6/7/2013 12.28 0
MS0000003 PT002 6/7/2013 26.69 26.69
MS0000003 PT002 6/7/2013 89.16 77.16
MS0000003 PT002 6/17/2013 57.1 57.1
Based on this data i required output like below
pn prcode maxdos list of servicedays(max of dos-min of dos) Expected Payment
MS0000003 PT001 1/21/2013 19 86.82 0
MS0000003 PT002 6/17/2013 11 358.18 160.98

Please tell me how to write mdx query based on this cube .
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search