I'm having trouble with needing a data warehouse to create such a simple report.   That kind of report just doesn't need a DW to exist.  In fact if you parameterize...

Is a single-product solution a necessity?   It's often a lot less expensive to combine functionality from multiple products and achieve the same result.

A little difficult to know what target we're aiming for here, as you talk about DW design best practice, but then talk about a report for which we have zero...

I don't think there's a way to do this in SSRS without gathering previous values in your data set.   With that in mind, using LAG in your query could handle...

That wide a pivot is going to fail because SQL Server simply can not handle a row that wide.  This is the kind of thing much better handled by using...

Frankly, if a report item is supposed to be a hyperlink, why would you feel the need to italicize any portion of that link?   I simply cannot imagine that being...

We don't have quite enough of your query to know exactly what's going on.   However, I can see one potential problem in the Alias for your second instance of the...

While true, you may simply have hit upon a less than ideal report design.   Ideally, there's no reason to return data to the report that is just going to get...