I am developing several SSRS reports (fed by the BAQ Report method) that require tablix objects to return 10 rows only - and no more.
I have seen several examples from the Interwebz™, such as “Top N”… “Ceiling”… and “Hide/Show” filtering based on specific criteria. None of these approaches seem to work. I realize that they may work, if I engineer them correctly.
With respect to the “Top N” method, the reason this does not seem to be an option is because it appears to behave this way: If there are distinct “Top N” values that fall within the “Top N” rule, the tablix will restrict returned rows to “N”. If there are not distinct values, the tablix will return all similar records that meet your criteria irrespective of total number of rows specified in “Top N”, up to the point where it recognizes that all of those similar records have extended beyond “Top N”.
To explain further:
If I am returning “Top 5 DMRs that have remained open the longest” and I have the following data to draw from —
– the “Top N” method will correctly return ALL of the 9 records because it recognizes only 4 “top values” (18, 11, 7, and 1) that fall within the stated “Top 5” filter.
What I’m truly looking for is a hard 5-row limit of the “Top 5” returned values (or simply, a hard 5-row limit of all descending values), which might appear like so:
DMR Days Open
------ ---------
147238 18
149236 11
142789 7
141928 1
144212 1
Would anyone have any suggestions for such a requirement?
You may use in the BAQ a field indicating the RowNumber of your result?
And in the baq SubQuery criteria indicate to only see rows with rownumber <=5
I have used this before.to retreive the rownumber = 0
…let me dig how I used it…it’s been awhile…
EDIT: sorry it was RANK()
I used a subquery and in the main query where I joined the sub query I had a table contraint on the sub to only see Rank = 0
in your case it could just be Rank <= 5
ex:
RANK() OVER (PARTITION BY JobOpDtl.Company, JobHead.JobNum, JobOper.OpCode, JobOpDtl.OpDtlSeq, JobOpDtl.ResourceID ORDER BY JobOpDtl.OprSeq )
I am also using RANK() a lot when I want to find the most recent revision of a part, because we had multiple approved revs…
Thanks for your input on this. So - sounds like I’ll need to add that calculated field to my existing BAQ, then resync it to the dataset/datadef and work the results filtering back in the SSRS framework. I will definitely give this a go and will let both of you know the progress.
One question though… since I’m keeping the BAQ “open” without filtering so that I can dynamically allow for that in the BAQ Report options instead, will the rank value always understand that “row 1 is rank 1” and “row 2 is rank 2”, etc?
It will go by whatever you are ordering by. (see the second part of the calculated field) So you can order by DAYSOPEN desc and the most days open will show on the top of the list. You can also order by more than one thing to break ties. Check out this link. It will explain it better than I can.
Does that make sense? I don’t really understand what you mean when you say "row 1 is rank 1 ” etc. If you need them in that order, you should put an order in your query then the first row with always be Row number 1 etc.
Here’s the SQL if that makes more sense.
select
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[RequiredQty] as [JobMtl_RequiredQty],
(Row_Number( ) over (partition by JobMtl.Company order by JobMtl.RequiredQty desc)) as [Calculated_RowNumber]
from Erp.JobMtl as JobMtl
where (JobMtl.JobNum = '403413-1-1')
order by RowNumber
Yes - I was thinking in terms of, say, record #43 in my BAQ has now been filtered to exist as returned record #1, therefore, it is “rank #1” instead of “rank #43”.
I think I am relying on SSRS a bit too much to perform a lot of the filtering I require. Again, this decision was made because I wanted to allow the BAQ Report options to do that for the client on-the-fly (a person can set up the parameters of their own report, then run it). For this task, I might need to calculate DAYSOPEN directly in the BAQ (instead of doing it directly in the framework of the SSRS rdl file), then the SSRS filtering would be much easier because the DAYSOPEN values are already passed along.
Thanks Brandon - you’re definitely making things clearer ( ) for me.
Well, I’m biased because I know how to do BAQ’s but not SSRS, but, that being said, I still thing it’s going to be simpler and faster, if you limit the size of the BAQ as much as possible before it gets to SSRS. I’m pretty sure you can still set up the filters/options in the BAQ report before getting to SSRS to limit it dynamically. But I have extremely limited experience there. (since I push almost everything to dashboards instead of paper)
Yeah - we’ve changed our philosophy a bit at my company in an attempt to limit the amount of “dashboard dumping” to Excel so that our employees avoid spending hours and hours and hours creating their own silo reports from these Excel files (that can easily be manipulated to favor whatever needs to be presented) that nobody has control over (in terms of corporate document control), that always seem to get used in some internal process that evolves into SOP by which nobody seems to want to document, or support [all performed outside of the purview of IT/IS, of course] – .
I’ll get to work and see how for I can run with this. Thanks again for lending a hand, @Banderson !
I was able to arrive at a solution concerning “hard limiting” the amount of rows returned in a tablix object.
For those who might be following this, recall that my requirement was to leave the returned results in the BAQ “wide open”, or unfiltered - allowing that functionality to be handled by the BAQ Report options, which get passed directly to the SSRS parameters within the dataset.
Most of the online materials that I had read on this topic focused on creating a filter on the native row group on the tablix. It tried that and it didn’t work at all, as described above.
What I eventually uncovered, that I have not found anywhere in my research into the matter, is that the row count restriction “parameter” must be set as an expression on the Visibility group property. Essentially, what you’re doing is showing, or hiding a row based on that expression parameter. In my case, I was interested in showing only the “Top 10” rows returned (and remember, this must be done as an expression directly pertaining to the row count).
The expression that seems to work is: =IIF(RunningValue(COUNT(1), Count, Nothing) <= 10, false, true)
… which is merely stating, “if the running value of the row count is less than, or equal to 10, then unhide the row - otherwise, hide the row”.
Again, this strategy seems to work well for BAQs that need to be left unfiltered so that a user can select report options when the BAQ Report is launched. If you are creating a BAQ Report that seeks to return rows based on very specific query criteria/parameters, then you should consider following @Hogardy’s or @Banderson’s example of crafting the BAQ/SQL to construct the row rankings directly in the query statement.
) for me.
.