仪表值的最大值怎么算呀?

0
学习SSRS 2012 Metro UI 报表视频中案例28中仪表值的最大值怎么算呀?怎么取两个分组后的最大值呀?请老师指教!
 
我只懂得SQL的写法:
select top 1 count(distinct t.salesordernumber) as maxcntsalesordernumber
from (SELECT FIS.SalesOrderNumber,
  FIS.SalesOrderLineNumber,
  FIS.UnitPrice,
  FIS.SalesAmount AS InternetSales,
  ABS(FIS.SalesAmount * RAND(100) - 1000) AS ResellerSales,
  (FIS.SalesAmount + ABS(FIS.SalesAmount * RAND(100) - 1000)) /2 AS AvgSales,
  FIS.OrderDate,
  YEAR(FIS.OrderDate)*100 + MONTH(FIS.OrderDate) AS Datekey,
  YEAR(FIS.OrderDate) AS YearKey,
  FIS.ShipDate,
  FIS.DueDate,
  DC.FirstName + ' ' + DC.LastName AS CustomerName,
  DC.EnglishEducation,
  DC.EnglishOccupation,
  DG.EnglishCountryRegionName,
  DG.CountryRegionCode,
  DG.StateProvinceName,
  DG.StateProvinceCode,
  DG.City, 
  DG.PostalCode,
  DPC.EnglishProductCategoryName,
  DPS.EnglishProductSubcategoryName,
  DP.EnglishProductName
FROM dbo.FactInternetSales AS FIS
INNER JOIN dbo.DimCustomer AS DC
ON FIS.CustomerKey = DC.CustomerKey
INNER JOIN dbo.DimGeography AS DG
ON DC.GeographyKey = DG.GeographyKey
INNER JOIN dbo.DimProduct AS DP
ON DP.ProductKey = FIS.ProductKey
INNER JOIN dbo.DimProductSubcategory AS DPS
ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey
INNER JOIN dbo.DimProductCategory  AS DPC
ON DPS.ProductCategoryKey = DPC.ProductCategoryKey) t
group by t.YearKey,t.EnglishCountryRegionName
order by count(distinct t.salesordernumber) desc
已邀请:
0

zengxinwen 2015-09-18 回答

T-SQ太长了,如果是小数据,在控件中使用了分组,你可以按数值进行排序从大到小排序,然后一个函数=First(分组) 可以取得最大值。

要回复问题请先登录注册