I have a BAQ that pulls in all sales for every customer. I need to build an SSRS report that will display only the top 100 according to their sales dollars, so sales dollars in descending order. I can do this in SSRS by creating a column that has the following expression: RunningValue(Fields!Customer_CustID.Value,CountDistinct,Nothing). Then in the row visibility I have the following expression: Iif(RunningValue(Fields!Customer_CustID.Value,CountDistinct,Nothing) <= 100,false,true). The report prints fine in descending sales values like I want it to. The problem is I cannot get a correct total on that column. I tried: RunningValue(Fields!Calculated_SalesCurrent.Value,Sum,nothing) but the total printed includes the sales totals of the customers that are not displaying on the report. How can I just get a running total value of the 100 that are displayed???
I see where you’re going with this. Just a thought, can you apply the top 100 criteria, and the sum function to the BAQ? That would certainly simplify the report.
you could also add a calculated field to your BAQ to identify the records you want to sum. Then use a sum(iif expression in your report. For example, here I use the OpRow flag to either add up the EstSetHours, or nothing, depending on if I flagged the row in my BAQ. I have a calculation to determine if the row is flagged, you could probably do something similar.
=Sum(iif(Fields!Calculated_OpRow.Value=1,Fields!JobOper_EstSetHours.Value,Nothing))
How would I apply the top 100 criteria in the BAQ
It could be as easy as locating your top-level query (if you have subqueries), then go to Query Builder > SubQuery Options > Change Result Set Rows to Top, and then in Rows Number enter 100.
Of course, this assumes your BAQ is calculating the total sales, and then sorting the results by highest sales.
Thanks, I will give that a try!
putting the limit in the BAQ did not work for me. The BAQ is pulling in multiple years of sales to display but it needs to be sorted by the current fiscal year and then by the sales in that current fiscal year.
Ohh Tricky! I think you can still get this done mostly in the BAQ. You are on the right track. I assume you have tried applying those sorts to the BAQ Top Level? Go to Query Builder > Display Fields > Sort Order. Add your sorting fields there.
yes the top 100 is on the top level query and I have added the sorts on the top level sort order area too. I’m still trying things out but no success so far.
Post up your BAQ, if you can. I will take a look.
Top100DomSlssummary.baq (34.0 KB)
When run like this I get only 3 sales for the current fiscal year:
When I remove the top 100 and run it again I get way more for the current fiscal year which is correct:
ooff! Those dang custom tables and Union queries always throw me for a loop. I am not quite sure about this one. Sorry!
No problem, thanks for all of your help so far, I appreciate any help I can get.