Share
Report
240Hi
I am new to cubes. I am using AX 2012 R3 with CU 13. I tried to modify the MDX query used by the report SalesPerformanceByProducts. I am using the Retail Cube to create a new SSRS report and below is the modified query based on client requirement to include category name to the rows. But I am getting error
WITH
//MEMBER ColumnName as "Amount"
//MEMBER RowCount as 100
//MEMBER Company as "ceu"
//MEMBER standalone as "False"
//MEMBER FromDate as "1/1/1900"
//MEMBER ToDate as "1/1/2154"
//Member ItemGroup as "Lighting Business Unit Sales Hierarchy"
SET [OrderedBy]
AS
(
Case WHEN @ColumnName = "Amount" THEN
TOPCOUNT(ORDER(
NonEmpty([Released products].[Product number].[Product number],
{[Measures].[Customer invoice line amount - accounting currency]}),
[Measures].[Customer invoice line amount - accounting currency], DESC),@RowCount)
WHEN @ColumnName = "Quantity" THEN
TOPCOUNT(ORDER(
NonEmpty([Released products].[Product number].[Product number],
{[Measures].[Customer invoice quantity - sales unit]}),
[Measures].[Customer invoice quantity - sales unit], DESC),@RowCount)
WHEN @ColumnName = "Profit" THEN
TOPCOUNT(ORDER(
NonEmpty([Released products].[Product number].[Product number],
{[Measures].[Gross profit margin]}),
[Measures].[Gross profit margin], DESC),@RowCount)
END
)
SELECT {[Measures].[Customer invoice line amount - accounting currency],
[Measures].[Customer invoice quantity - sales unit],
[Measures].[Gross profit margin],
[Measures].[Gross profit margin percentage]} ON COLUMNS ,
{ [OrderedBy] *
[Released products].[Product name - Released products].[Product name].members
,[Released products].[Item group name - Released products].[Item group name].members
}
on Rows from [Retail cube]
where (
strtomember('[Company].[Company].&[' + @Company + ']'),
IIF(@standAlone = "True", [Retail category].[Parent category].[All],
strtomember(@ItemGroup)
)
,
{STRTOMEMBER("[Date].[Date].[Date].&[" + format(CDate(@FromDate),
"yyyy-MM-dd") + "T00:00:00]"): STRTOMEMBER("[Date].[Date].[Date].&
[" + format(CDate(@ToDate), "yyyy-MM-dd") + "T00:00:00]")})
Please guide me solve this error.
Regards,
Durai Murugan R
*This post is locked for comments
All responses (
Answers (
