The solution below is how I would tackle this for Reporting Services (SSRS). Other report environments may have different approaches, so hopefully this one is suitable to your particular reporting...

Sean, that last solution is an elegant way to collapse the three UNIONed queries together. Kudos on seeing the simpler way.

JB, I'm not sure what the results need to...

The tables your describe in your sample are not hierarchical in the sense of the term usually applied to database tables, which is this: a self-referencing table in which for...

I would base the tablix on the BIPM dataset and then use an expression similar to this one for the value of each detail textbox in the tablix:

= Fields!ItemSales.Value -...

This link was shared in a similar post earlier today. I think it is applicable to your situation as well.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

The link above will show you how to list...

I think the issue that is muddying the waters is that the "examination" table is apparently a self-referencing hierarchical table. Hopefully there are a fixed number of "steps" from top...

Glad I could help out. Although they are needed rarely, non-equi-joins (joins on an expression other than columnA = columnB) can be just the ticket for complex relational problems.

Eugene,

After reading your explanation of the reason why GROUP BY is more efficient than window functions, I can see the logic in your in-line view solution. I would still do...

Sean,

I'm not an expert at execution plans, so I can't speak definitively on which approach is more efficient, but intuitively it would seem to me that referencing the raw data...

My solution uses a "tally table" in a CTE to explode the list of classes into a list where each class is listed multiple times, specifically ClassCapacity + 10 times,...

My solution avoids joining the table to itself by using a couple of window functions in a CTE named "flagged_data." It adds a "StudentID_Problem" column (1 for a problem, 0...

First, let me say that the idea of a "dynamic ranking" table that defines multiple ranking sets is an intriguing idea, and I may use it in other applications of...

In my proposed solution below, I am making a few assumptions about your data and the results, some of which vary from your explicit description, so please reply and correct...

Be aware that sometimes fiscal years can shift. A company may decide to move the first day of the fiscal year from January 1 to June 1. Or they may...

I don't know why not, though I have not tested that particular arrangement. But once the data are in datasets, I think the report would see it all the same.