topic Re: Hide specific measure from specific dimension value in pivot in Reporting Service & Alerting

Hide specific measure from specific dimension value in pivot

Rohan_W — Fri, 15 Nov 2024 21:24:06 GMT

Hi Team,

We have below requirement where the structure of report should look like this,

There are 3 measures in total wherein 2 measures(Recovery and Slippages) fall under other dimension (for ex - Since YesterDay) and remaining 1 falls in quarter end dimension(for ex - 30-Jun-2022).

I have created a sample final table attached and I'm able to achieve the structure as shown below,

I have a challenge to hide Slippage and OS measure in dimension quarter (for ex-30-Jun-2022) and need to hide sum([Sheet2.Amt]) in dimension quarter (for ex -During Q2 of 2023-2024).

Please suggest if this is feasible.

Re: Hide specific measure from specific dimension value in pivot

Or — Mon, 04 Sep 2023 13:27:42 GMT

This is not feasible. Measures cannot be displayed selectively within some dimensions.

In some use cases, you can work around this to some degree by avoiding the use of a pivot dimension and instead using a large number of measures. This would require giving up the "grouping" aspect, though.

You can look into the option of using a calculated dimension instead of using measures for the section(s) that don't match the "standard" measures and don't necessarily require a pivoted dimension. This will likely have a negative impact on performance and may be tricky to write, though.

Re: Hide specific measure from specific dimension value in pivot

Or — Mon, 04 Sep 2023 13:28:38 GMT

Looks like the solution was accepted while I was editing, so just posting again to mention I expanded on my original reply.

Re: Hide specific measure from specific dimension value in pivot

marcus_sommer — Mon, 04 Sep 2023 13:32:34 GMT

That the object-layout couldn't be replicated 1:1 doesn't mean that the information couldn't be displayed.

The nearest approaches might be to skip the horizontal dimensions and applying 12 expressions or using a single dimension which includes all types with a single expression which queries the dimension-values and branched into different calculations.