How can I use "Previous(scope, expression)" in a CrossTab

[Marvin]

I am trying to create a comparative Profit & Loss with columns comparing the previous column with the current column. The Previous() function doesn't allow more than one scope as the Exec() does, but it appears that that is what I need.

As an example I'm using the following csv. See the attachment as well.

Year,Account,Amount
2000,50000,1000.00
2000,51000,560.00
2001,50000,1000.00
2001,51000,560.00
2002,50000,5000.00
2002,51000,60.00
2000,50000,1500.00
2001,51000,500.00

Note that there are two values for Year "2000" and Account "50000"

How can I get the Previous = Exec('year1','account1', Sum(Fields.Amount))?

[Todor]

Hi Marvin,

I'm afraid that by design, the scope of the Previous data function cannot be a body scope. A body scope is determined from the cross-section of rows and columns axes data scope. Unfortunately, it is not possible to use the Previous function also in the Exec function.

As a workaround, you may implement a custom Aggregate function to achieve the requirement. You need to implement the IAggregateFunction interface.

The other option is to create a custom User Function that fulfills the requirement.

Regards,
Todor
Progress Telerik

[Mirzodaler]

Hi Todor,

We also needs this feature. It will be good to have something like this with Prev function:

- PrevColumnCell(columnsOffset, expression)

- PrevRowCell(rowsOffset, expression)

Basically, being able to access previous column/row cell. Is there any plans to implement this or we need to do a feature request?

And, it will be good if you can provide some example or implementation code of Data Functions, especially 2 forms of Exec and Prev function. This will be helpful if we will try to have some custom function for these. It is difficult to imaging how that custom function will look like and what is input and output.

 

Thanks,

[Todor]

Hello Mirzodaler,

You may log a feature request for the requirement. This will allow other users to vote for it and increase its priority.

Thank you for the feedback. We will consider adding more examples in our Documentation.

Regards,
Todor
Progress Telerik

[Mirzodaler]

Hello Todor,

Thank you. I will assume you will let us know about the Documentation update in this thread. And, when do you expect this will happen?

We are at the point where we need to write more user functions and the documentation is not enough to go ahead with this.

 

Regards,

[Todor]

Hi Mirzodaler,

I have attached a sample report that demonstrates how you may use the built-in data functions:

• Exec(scopeByColumns, scopeByRows, expression) - the cells in green
• Previous(scope, expression) - the cells in yellow

I will discuss with the team whether to add the code of the functions in the documentation as an example or post it in the forum post/KB.

Regards,
Todor
Progress Telerik