October 31, 2014 at 11:59 pm
Hi Everyone,
I am taking Top 10 of Sales Volume grouped by Product categories in SSRS 2012. I need the total of these top 10 but it shows the complete total.
For this I have used running value inside group and Written hidden expression below. This absolutely working for top 10 rows and rows less than 10.. In my report searching based on TaxID..So there may be chance of total sum ties..! so that case this condition is failing
Note: This is condition working for top 10 rows and also rows less than 10...but only failing rows more than 10 rows means top sales ties.( If 10th and 11th category sales are same those are pulling up into report..but sum is making problem for me 🙁
=IIf(RunningValue(Fields!grp.Value, CountDistinct, Nothing) = 10
or (RunningValue(Fields!grp.Value, CountDistinct, Nothing) = CountDistinct(Fields!grp.Value, "DataSet1") and CountDistinct(Fields!grp.Value, "DataSet1") < 10)
, false
, true)
I cant do it on dataset level as I need the complete dataset for other parts in the report. Thanks in advance.
any help on this..greatly appreciated ..
November 4, 2014 at 11:19 am
You CAN do it at the dataset level. What you need to do is identify the RANGE of values that your Top X (10 in this case) encompasses. Return an additional bit field, say, "InTheTopX", and use that in the RDL to filter for the report element where you need your Top 10. This is fairly simple to do if you're using a stored procedure to return your dataset, if it's a single Query, then it gets a lot more difficult.
Here's an example of how you can do it.
/* Pseudo-code*/
Isolate your distinct ranking values.
Get your Top X distinct ranking values.
Identify the MINIMUM of those Top X values, this is your cutoff value.
Tag everything in the full dataset with a ranking value equal or greater than your cutoff value as InTheTopX.
IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp
--SELECT top 10000 * INTO #MyDataset from [dbo].[Whatever]
;with cteTop10 as
( SELECT MIN(TopRankValue) as CutoffValue
from
(select top 10 RankingValue as TopRankValue
from
(SELECT DISTINCT MyRankingField AS RankingValue FROM #MyDataset) dq
order by RankingValue DESC
) trv
)
select *
, CASE WHEN MyRankingField >= (SELECT CutoffValue FROM cteTop10) THEN 1 ELSE 0 END as InTheTopX
into #Temp
FROM #MyDataset
order by InTheTopX DESC
November 9, 2014 at 1:49 am
Hi Thanks for reply..!! It works..!! but I'm not interested to use dataset , as I have got 12 summaries in my report. For this i have to create 12 datasets, report performance will go down.! Everything should be handle in report..any help greatly appreciated . Thanks.
November 9, 2014 at 11:40 am
If you're already bringing back 12 datasets, then this will add minimal overhead, even if you do it 12 times. With the exception of string manipulation, almost anything you do to your dataset will be faster on the SQL side than doing it in the report.
To do this on the report side, you'd need custom code and calculated fields, using essentially the same logic as the SQL. Since you can't do set based operations on the report side (AFAIK), that means it will be RBAR. :w00t:
If you're using the same dataset 12 times, just calculated off different fields, it will be extremely ugly, because you won't even have the luxury of pre-sorting the dataset before SQL hands it off to the RDL. You'll have to sort it in the report object and calculate your values there... (For performance sake, you can make assumptions about how deep you have to go before you're out of the possible TopX window, if you want.... but they will be assumptions.)
Things to remember on the SQL side:
You can have multiple CTEs....
You can create your temp table with indexes...
Good luck with it. I'd like to see what you come up with on the RDL side, because the more ways we have of doing things, the better.
June 16, 2015 at 10:18 pm
1. Add the following custom code to the report
2. Add an empty column to your table and set the expression as =Code.SetTotalSaleAmount(RowNumber(), Fields!SaleAmount.Value). This will accummulate total and save the temporary result in the global variable TotalSaleAmount
3. Use this expression to get the total =Code.GetTotalSaleAmount()
Public Shared TotalSaleAmount As Decimal
Public Function SetTotalSaleAmount(ByVal rowNumber As Integer, ByVal saleAmount As Decimal)
if (rowNumber = 1)
TotalSaleAmount = saleAmount
Else
TotalSaleAmount = TotalSaleAmount + saleAmount
End If
End Function
Public Function GetTotalSaleAmount() As Decimal
Return TotalSaleAmount
End Function
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply