December 7, 2009 at 7:59 am
Hi,
I'm creating the report using Cube.can anybody help me to write the MDX query to arrive the below mentioned Report Format
Tables Involed in creating reports is
Dimension Tables :
LDepartment - Department name
LChain - ChainName is filter
DimProductHierarchy - Product Code (Should be a count)
Measure table :
FactStockOnHand - Quantaty on hand , Value on hand
by using these tables i need to compare the values on a weekly basis
Eg: i need to show the today's value , minus seven days values as well as minus 14 days values in a seperate columns this calculation should be happen base on the current date.
Report columns : [Department name ,Product code ,units SOH , Value SOH]
E Lines - by Department
Report Format:
07/12/2009 31/11/2009 24/11/2009
Department Product Units Value Product Units Value Product Units Value
Name Code SOH SOH Code SOH SOH Code SOH SOH
Bedding 10 200 200 7 150 150 100 100 100
Bedroom
Carpet
can anybody send the MDX query for this how to acheive this format by using the dimension and fact tables in cubes .
Note : i created cube and i got auto generated MDX query but i'm trying to modify the MDX query according to the report format.but am bit struggling to acheive the result.
MDX Query for your reference :
SELECT NON EMPTY { [Measures].[Quantity On Hand], [Measures].[Value On Hand] } ON COLUMNS, NON EMPTY { ([L Department].[L Department Name].[L Department Name].ALLMEMBERS * [Dim Product Hierarchy].[Current Product Code].[Current Product Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [L Chain].[L Chain Code].&[Barnetts] } ) ON COLUMNS FROM ( SELECT ( { [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True] } ) ON COLUMNS FROM [CUBE_JDGJEDataMart])) WHERE ( [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True], [L Chain].[L Chain Code].&[Barnetts] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
December 7, 2009 at 1:49 pm
Just to point you into the right direction,
WITH
Member [Measures].[oneweek ago StockOnHand] AS
( ParallelPeriod ([Date].[Calendar].[DATE], 7, [Date].[Calendar].Currentmember)
, [Measures].[FactStockOnHand] )
Select
{[Measures].[FactStockOnHand] , [Measures].[oneweek ago StockOnHand] } ON COLUMNS
{} ON ROWS
FROM ( )
WHERE ( STRTOMEMBER ("[DATE].[CALENDAR].[DATE].&[" + VBAMDX!format( VBAMDX!now(), "formatstring") + "]", CONSTRAINED) )
Normally I create a hidden report parameter and assign it a expression value, instead of the string, the hidden paramter is injected*/
VBAMDX!format( now(), "formatstring") tells you that you manually need to format the current date into the valid member value of today. if your data members are in the form [DATE].[CALENDAR].[DATE].&[12/08/2009] the format string = "MM/dd/yyyy"
Trick is PARALLELPERIOD, if you do not apply somewhere in the slicer or dicer the CURRENTMEMBER of the date function default date member is used...
Regards Kees
December 8, 2009 at 1:53 am
Hi kees,
First thanks for your response
as per u guided i created a calculated member to calculate the measure values according to the current date and formed the MDX query but i got some red line below the second 'With Member calculation part' can u tell why this error message coming. i have pasted the MDX query below for your reference.
another thing i like to know in calculated member you used "([Date].[Calendar].[DATE]" time dimension table .but in my scenario am not using any time Dimension table to calculate the Date. so can u tell any other alternative solution to calculate the (-7 days and -14 days) values.
WITH MEMBER [Measures].[ProductCode count] AS
COUNT(
EXISTING [Dim Product Hierarchy].[Current Product Code].[Current Product Code].MEMBERS
)
WITH
Member [Measures].[oneweek ago QuantityOnHand] AS
( ParallelPeriod ([Date].[Calendar].[DATE], 7, [Date].[Calendar].Currentmember)
, [Measures].[FactStockOnHand] )
WITH
Member [Measures].[oneweek ago ValueOnHand] AS
( ParallelPeriod ([Date].[Calendar].[DATE], 7 , [Date].[Calendar].Currentmember)
, [Measures].[FactStockOnHand] )
WITH
Member [Measures].[TwoWeek ago QuantityOnHand] AS
( ParallelPeriod ([Date].[Calendar].[DATE], 14 , [Date].[Calendar].Currentmember)
, [Measures].[FactStockOnHand] )
WITH
Member [Measures].[TwoWeek ago ValueOnHand] AS
( ParallelPeriod ([Date].[Calendar].[DATE], 14 , [Date].[Calendar].Currentmember)
, [Measures].[FactStockOnHand] )
SELECT NON EMPTY { [Measures].[Quantity On Hand], [Measures].[Value On Hand], [Measures].[ProductCode count] , [Measures].[oneweek ago QuantityOnHand] ,[Measures].[oneweek ago ValueOnHand],[Measures].[TwoWeek ago QuantityOnHand] ,[Measures].[TwoWeek ago ValueOnHand] } ON COLUMNS, NON EMPTY { ([L Department].[L Department Name].[L Department Name].ALLMEMBERS * [Dim Product Hierarchy].[Current Product Code].[Current Product Code].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [L Chain].[L Chain Code].&[Barnetts] } ) ON COLUMNS FROM ( SELECT ( { [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True] } ) ON COLUMNS FROM [CUBE_JDGJEDataMart])) WHERE ( [Dim Fact Stock On Hand].[Is SOH More Than Zero].&[True], [L Chain].[L Chain Code].&[Barnetts],( STRTOMEMBER ("[DATE].[CALENDAR].[DATE].&[" + VBAMDX!format( VBAMDX!now(), "formatstring") + "]", CONSTRAINED) )
) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Thanks in Advance,
Sabarinathan.C
December 8, 2009 at 2:24 pm
Hi
I guess you need a fasttrack course MDX. Copy pasting does not do job. I used a 'standarized' way to express myself to make things clear to you.
I guess there is a date/time dimension somewhere in your cube. Your requirements stated -7 days -14 days so there must time involved.. The measures need a relationship to some dates I presume.....how else to solve this..
Second copy paste the second term in the measures does not do the job either :-). It would be nice if you scoped it with the related measures, and learn the BASICS of MDX (WITH SELECT FROM WHERE).
WITH
MEMBER 1 AS 'member formula' ,
MEMBER 2 AS 'member formula' ,
SET 1 AS 'set formula'
SELECT {some set} ON 0, {som set } ON 1 , {some set} ON 2, {some set} ON 3
FROM [SOMECUBE]
WHERE ( SOME TUPLE )
My advise to you is. Find the time dimension in your cube, find the date attribute or level and start play around with the example I gave you.
I am wondering if you use me to do work, you are supposed to do.
Regards.
December 8, 2009 at 2:37 pm
@ Sabarinathan:
Tables Involed in creating reports is
Dimension Tables :
LDepartment - Department name
LChain - ChainName is filter
DimProductHierarchy - Product Code (Should be a count)
Measure table :
FactStockOnHand - Quantaty on hand , Value on hand
Um, somewhat critical to have a Time dimension in your cube, otherwise, how do you expect to be able to reference 'today' or any other date in history or the future?
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply