I’m not sure if it’s my lack of SSRS knowledge or if it’s really an unbeatable problem, but here is what I ran into.
Imagine that you have two entities. Let those two be Fiscal Years and Orders. Apparently, in any fiscal year you can have multiple orders. Let’s say every order is attributed to a fiscal year based on its “Created On” date.. So, here they are, the entities:
Now what if we wanted to build a (FetchXml) SSRS report that would display something like this:
Basically, there would be two datasets:
- FiscalYears
- Orders
And, once we had those, we could do some grouping on the client side.. could be something like this:
=Sum(IIF(Fields!new_ordertype.Value = 100000000, 1, 0), “Orders”)
This would give us the total number of all service orders (assuming 10000000 means “service”). However, we would still have to do the same by fiscal year, and that’s where the problem shows up.
We cannot add a parameter from a different scope to the aggregate function, so we can’t do this:
=Sum(IIF(Fields!new_ordertype.Value = 100000000 AND Fields!new_fiscalyearid.Value = ?????, 1, 0), “Orders”)
There, we can’t really reference FiscalYears dataset. We can’t reference group variables. We can’t reference a text box.. We can’t reference anything from a different scope. The thread thread below is a little old, but, it seems, that’s still how it works:
IF it were a SQL report, we could use a stored procedure to prepare all the data on the server, but we can’t use SQL reports with Dynamics online.
Yes, it’s possible to use subreports to calculate the numbers per cell.. However, every subreport will have to open its own dataset, and that may end up being a rather slow report then.
It’s also possible to create rollup fields in the fiscal year entity to calculate those numbers.. However, we can’t create a rollup per every possible number we’ll need to display on the report – it may work for this particular report, but there can be more reports.. and, after all, that’s what reports are for – to do that kind of weird calculations.
I may be missing something, but, it seems, it’s one of those scenarios I’ll need to add to the the list of Things you Can’t Do with Dynamics
Any objections?:)