.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

Topcount

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
 
Hi, I have requirement where i want to show top 2 promotion under that top 2 Categories and top 2 products under that categories. something like this         Sales Amount No Discount Bikes Mountain-100 Black, 38 $1,232,546.35 No Discount Bikes Mountain-100 Black, 42 $1,198,796.45 No Discount Components Chain $8,986.56 No Discount Components Front Brakes $48,180.60 No Discount Clothing AWC Logo Cap $4,704.16 No Discount Clothing AWC Logo Cap $9,283.84 No Discount Accessories All-Purpose Bike Stand $39,591.00 No Discount Accessories Bike Wash - Dissolver $15,041.40 Volume Discount 11 to 14 Bikes Mountain-100 Black, 38 $69,060.40 Volume Discount 11 to 14 Bikes Mountain-100 Black, 42 $23,020.13 Volume Discount 11 to 14 Clothing AWC Logo Cap $304.63 Volume Discount 11 to 14 Clothing AWC Logo Cap $1,351.17 Volume Discount 11 to 14 Components Chain $391.15 Volume Discount 11 to 14 Components Front Brakes $2,118.71 Volume Discount 11 to 14 Accessories All-Purpose Bike Stand $3,000.65 Volume Discount 11 to 14 Accessories Bike Wash - Dissolver $2,196.13 I have written this query which gives top 2  promotions with product categories but when i add product. it doesnt show top 2 products under those cateogeries. WITH SET TOP2Promotion AS TOPCOUNT({[Promotion].[Promotion].[Promotion].Members},2,[M


View Complete Post


More Related Resource Links

TopCount Weirdness

  
Using 2008r2, I'm wondering why the following queries would return different results, and I gotta think it's a bug: (1) Date as slicer SELECT    {[Reseller Sales Amount]}  ON 0,     TopCount( Product.Subcategory.Subcategory.Members, 5,  [Reseller Sales Amount] )  ON 1 From [Adventure Works] WHERE ([CY 2008]) (2) Date on 0: SELECT    {([CY 2008],[Reseller Sales Amount])}  ON 0,     TopCount( Product.Subcategory.Subcategory.Members, 5,  [Reseller Sales Amount] )  ON 1 From [Adventure Works] Also, query #2 doesn't sort properly.

TopCount and grand totals

  
Hey all, I've written a query to show top 20 product sales. How can I also show the grand total for all product sales? Thanks, Matt

TopCount dynamic named set + "Others" and "Grand Total"

  

After going through all available posts, unfortunately I could not find a solution for my problem. I have to create a dynamic named set of with TopCount in a cube that will be accessed with excel 2007. This how the outcome should look like:

Brand_A        $50.00

Brand_Z        $40.00

Brand_B        $10.00

Others           $200.00

Grand Total   $300.00

 

This is the MDX I used which gives me the top 3 but not the "Others" and the "Grand Total." Just to keep it simple I used top 3 Brands.

CREATE DYNAMIC SET CURRENTCUBE.[Top 3 Brands]
 AS 
  TopCount
  (
    [NDD].[Brand].[Brand].Members,
    3,
    [Measures].[Sales]
    )

, DISPLAY_FOLDER = 'Sets';

Now I am stuck here. After reviewing many posts it seems that Excel 2007 has some issues with handling sets. I found two possible solutions from Raymond-Lee which I think may work.

Solution 1 - Use a named query in dsv

Solution 2 - create a dummy "Others"

Here is the post: http://social.msdn.microsoft.com/Forums/en-US/sqlanalys

Except Topcount with parameter

  

Who can help me to change the mdx below. i want to compare the 2 topcounts with different parameters (locations).

so i have to add an parameter (in my case that would be the dimension 'Location' )

,

STRTOMember(@Location,Constrained)

___________________________________________ 

SELECT
       {([Measures].[Reseller Sales Amount])} ON COLUMNS,
       Except(
           TopCount(
               {[Product].[Product].[Product].Members},
               10,
               ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2004])
               ),
      &n

Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend