Can we define a column/row range in a GetDataCell?

[Nitishkrish16]

Hey folks, 
We have a requirement to define range of rows in a GetDataCell.
For example: GetDataCell(CVR(Row1) + CVR(Row2) + CVR(Row3)..... CVR(Row100))
Instead of defining it manually till Row100, Is there any way to give a range like this GetDataCell(CVR(Row1):CVR(Row100)) ?

Any solution to this will be helpful. Thanks. 

[BenThompson]

Hello @Nitishkrish16 ,

I am interested to hear what others have to say on this.  If this is something they use multiple times, would it be better to just create an alternate hierarchy for the aggregation of those specific accounts?  That way the client can also adjust it in the future easily.  

Just a thought,

Ben

[JackLacava]

This is really the right answer. As soon as one has to manually define 100 rows in a CV, one is in "we're doing it wrong" territory. It's better to create an alternate hierarchy; as well as being easier to reference and maintain, it will likely be faster to display in Cube Views.

[phollander]

Agreed, although I am not sure if the 100 row count in a Cube View was just an example or a real-life case, as that would be quite an extreme amount of rows for one definition. One issue with the use of an alternate (account) hierarchy is when the underlying children are set up as DynamicCalc. In that case, I believe you will need to have a DynamicCalc on the top parent of the hierarchy to sum of the retrieved amounts of its children (might be different ways of dealing with this, but the point is towards DynamicCalc as part of a roll-up).

For most use cases, using an alternate hierarchy is the way to go.

[Nitishkrish16]

Hi @BenThompson , 
This is such an amazing idea!! But, few accounts used here are dynamic. So it would create complexities if the alternate hierarchy is created. Thanks a lot for your input 🙂 

[phollander]

HI @Nitishkrish16,

You could consider writing an XFBR rule for this in different variations, depending what style you use to set up Cube Views. If you would set up the row names as "ROW <digit>" and you simply want to add up the result of those rows, you could work with a start- and end row numbers as parameter in the XFBR. In this example, you can convert the start- and end numbers to an integer and use a For..Next loop to generate the GetDataCell syntax.

The example above is relatively simple and assumes you set up your cube view row headers all in the same way. In case your row name are diverse, you can go as far as passing through the Cube View name as a parameter into an XFBR and use the GetCubeViewItemUsingName function to get a list of the cube view row headers, with which you can generate the GetDataCell syntax. If you want to exclude specific row headers from the sub total, you can also pass those as a parameter in your function and adjust the code accordingly. More complexities will come up when you have a row with a dynamic function and you want to sum the result from multiple members from that row. I suspect this is not what you are after, but please feel free to correct me and I can provide some insights how I have dealt with this.

In short, there are various ways with XFBR to generate a string in a more automated way. I do agree with the comment that @BenThompson made that it probably makes more sense to create a hierarchy to reflect the sub total and just retrieve the top member, if you need the sub total in multiple places. This is also useful if you have users who need the sub total on an ad-hoc basis, when they are building their own quick views.

Hope this helps!

Regards,
Paul

[Nitishkrish16]

Hi Paul, 
Thanks a ton for your input. This is what exactly I have been looking for. I hope XFBR rule can solve this. Also, creating an alternate hierarchy would be much more easier way of dealing with this. Thanks a lot for your insights in this. 🙂