February 3, 2014 at 10:00 am
Hi,
I have a report that shows three lines for a group. The first line contains the first or initial values, the second line contains the most recent values, and the third line shows the difference between the first two. Output should look similar to:
Year TRA Value
Before: 2012 TRA-1 3
After: 11
Change: 8
Before: 2012 TRA-2 5
After: 8
Change: 3
Before: 2012 TRA-3 3
After: 3
Change: 0
I need to produce this from a cube that is built from the following data:
TaxYearTRASeqValue
2012TRA-113
2012TRA-125
2012TRA-139
2012TRA-1411
2012TRA-215
2012TRA-228
2012TRA-313
I have solved this using SQL but MDX is proving a challenge. I can get all initial rows using TopCount() or .FirstChild (from the above data, each of the rows with SEQ=1), but I am unable to get all the most recent value rows. When I use .LastChild, the result only contains the row [2012, TRA-1, 4, 11]. This is the query:
SELECT
{
[Measures].[Num Val]
} ON COLUMNS ,
(
[Trans].[Tax Year].Children, [Trans].[TRA].Children, [Trans].[Seq].LastChild
) ON ROWS
FROM [TransHist]
I suspect either a problem in the query, or in the cube structure.
Any help would be appreciated.
February 4, 2014 at 4:32 am
This'll do it:
WITH MEMBER Aft AS
MAX({([Trans].[TRA].CURRENTMEMBER, [Trans].[Seq].[Seq].MEMBERS)},[Measures].[Num Val])
MEMBER Change AS
Aft - [Measures].[Num Val]
SELECT
{
[Measures].[Num Val], Aft, change
} ON COLUMNS ,
(
[Trans].[Tax Year].[Tax Year].members, [Trans].[TRA].[TRA].members
) ON ROWS
from [YourCube]
WHERE [Trans].[Seq].&[1]
It's a wee bit hacky but you should be able to build your report from that dataset.
February 4, 2014 at 7:36 am
This did the trick - much appreciated.
I still wonder why .LastChild and .FirstChild have different behavior.
Thanks.
February 4, 2014 at 9:14 am
FirstChild and LastChild are used for hierarchy navigation so wouldn't be that relevant in your query.
Have a read of the following:
http://mdxpert.com/Functions/MDXFunction.aspx?f=40
http://mdxpert.com/Functions/MDXFunction.aspx?f=36
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply