Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Required Each Geography Group wise product maximum sales report (Using mdx) Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2014 6:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:48 AM
Points: 21, Visits: 143
Hi,
My table structure having bellow format :

State ProductID Sales
Ap P1 80
Ap p2 10
Ap p3 10
Tn P1 25
Tn p2 50
Tn p3 25
Mp p1 20
Mp p2 70
Mp p3 10

Required output is :
State ProductID sales
Ap p1 80
Tn p2 50
Mp p3 70

Regards,
Narendra
Post #1599644
Posted Wednesday, August 6, 2014 4:05 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 812, Visits: 5,171
Looks like your example data is wrong. Without an explanation of the rules, this is as close as I could get:


SELECT x.*, y.productID
FROM
(SELECT xState
, MAX(Sales) AS MaxSales
FROM salesData
GROUP BY xState) X
INNER JOIN salesData Y ON X.xState=Y.xState
AND X.MaxSales=Y.Sales

Here's the result I got:
xState MaxSales productID
Ap 80 P1
Mp 70 p2
Tn 50 p2
Post #1600417
Posted Friday, August 8, 2014 4:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:48 AM
Points: 21, Visits: 143
Hi,
I required output in mdx not in sql server . Any how i got the solution FYI


WITH SET Top10Cust AS
[DimGeographyBuyer].[State].[State]

set topP as
Generate( {Top10Cust}, CrossJoin( {[DimGeographyBuyer].[State].CurrentMember},
TopCount([DimProduct].[MaterialId].[MaterialId].Members, 1, ([Measures].[DistributorSalesSum]))))

SELECT
{([Measures].[DistributorSalesSum]) } ON COLUMNS ,

non empty {topP}
ON ROWS

FROM (SELECT ( { [MarketYear].[MarketYearHierarchy].[MARKETYEAR].&[2014] } ) ON COLUMNS

FROM
[MyCube])
Post #1601105
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse