@UNIT Row Restriction not working on side by side multi-unit repor

Discussion:

(too old to reply)

YSM

2010-09-01 17:12:04 UTC

We have a side by side report with different Reporting Units limited in the
column layouts. There is also a "consolidated" column

Some rows in the row layout have conditional formulas depnding on the unit
eg: IF (@UNIT="SALES THEN @760 ELSE @780)

the problem is that the conditional formulas in the ROW all evaluate based
as the Unit "consolidated" across all columns, even when the column Reporting
Unit is restricted to "SALES"

The tree for the report has "consolidated" as the parent, with the other
units as children underneath it.

Any ideas?

Thanks!

rivers1

2010-09-01 18:17:03 UTC

Permalink

Hello,

Since the columns are fixed to a specific unit in the tree, you do not need
to use the @UNIT command.

Say you want this row to come from 760 when the unit is ABC, which is in
column B. And if unit is anything else it should come from 780. Your CAL
formula in that row would be as follows.

B=@760, C TO Z=@780.

The reason you are getting the consolidated unit value accross all columns
is because that is the only unit in the tree that contains all the child
units you have restricted each column to.

Let me know if this makes sense and if you would like any additional
assistance on this or other things. You can reply here in this newsgroup or
directly at ***@MSXGroup.com. Also visit our website
http://MSXGroup.com for information about our services, support options,
training classes and FREE webinars on FRx, Forecaster and Management Reporter.

Thanks

Rob
--
Rob Iversen
MSX Group
www.msxgroup.com

Post by YSM
We have a side by side report with different Reporting Units limited in the
column layouts. There is also a "consolidated" column
Some rows in the row layout have conditional formulas depnding on the unit
the problem is that the conditional formulas in the ROW all evaluate based
as the Unit "consolidated" across all columns, even when the column Reporting
Unit is restricted to "SALES"
The tree for the report has "consolidated" as the parent, with the other
units as children underneath it.
Any ideas?
Thanks!

YSM

2010-09-01 18:56:03 UTC

Permalink

Hi Rob,
Thanks for the speedy response!

We are using the @unit command in the row format because the row format is
also used with other column layouts (where the unit may not be specified by
column), but where the unit is specified by the tree. We also may change the
layout/order of the columns at any time, so going through all the rows to
change column labels can be an issue.

I think this is an oversight in FRX. The @UNIT command in the row format
should check for a column unit restriction first and use that in its
evaluation. If there is not a column restriction, then it it should use the
unit value from the tree.

I'll see if I can figure something else out ....

Post by rivers1
Hello,
Since the columns are fixed to a specific unit in the tree, you do not need
Say you want this row to come from 760 when the unit is ABC, which is in
column B. And if unit is anything else it should come from 780. Your CAL
formula in that row would be as follows.
The reason you are getting the consolidated unit value accross all columns
is because that is the only unit in the tree that contains all the child
units you have restricted each column to.
Let me know if this makes sense and if you would like any additional
assistance on this or other things. You can reply here in this newsgroup or
http://MSXGroup.com for information about our services, support options,
training classes and FREE webinars on FRx, Forecaster and Management Reporter.
Thanks
Rob
--
Rob Iversen
MSX Group
www.msxgroup.com

rivers1

2010-09-01 20:18:03 UTC

Permalink

Yes, unfortunately the @Unit command only looks at what unit of the tree is
being processed, not per column, but for the row format (since that is where
the @unit command resides).

I cannot think of a dynamic formula that would work for both reports with
column restrictions and without, and where columns change around. None of
that is going to be supportable in FRx.

I do not even think Management Reporter supports what you are looking for
but I am checking with the PM. If I find out differently I will let you know
but in the mean time, you may have to create some macros in Excel to create
the formulas you want in rows, after exporting the FRx report to Excel.

If you do come across a solution in FRx for this situation I would love to
hear about it.

thanks

Rob
--
Rob Iversen
MSX Group
www.msxgroup.com

Post by YSM
Hi Rob,
Thanks for the speedy response!
also used with other column layouts (where the unit may not be specified by
column), but where the unit is specified by the tree. We also may change the
layout/order of the columns at any time, so going through all the rows to
change column labels can be an issue.
should check for a column unit restriction first and use that in its
evaluation. If there is not a column restriction, then it it should use the
unit value from the tree.
I'll see if I can figure something else out ....

YSM

2010-09-01 22:23:03 UTC

Permalink

Hi Rob,

No, I can't think of a solution....

It makes more sense to me that the @unit picks up the unit from the column
first, if the column is restricted. Not having this funcionality has really
messed up my reporting plans and now I have to jump through extra hoops in
Excel. Such is report design life!

Unfortunately, Management Reporter won't be a solution for us as we are on
Sage MAS. the SAGE MAS Intelligence product is not available to us yet and we
are unde time constraints so FRX+ Excel it will have to be.

If there are valid reasons for the functionality of @unit is to always go to
the tree first, then maybe there should be a second function @unitC (or ??)
that goes to the column for unit restriction first, then the tree .... Mind
you, there are a bunch of other areas that FRX could use some enhancement on
.....

thanks again!

Post by rivers1
being processed, not per column, but for the row format (since that is where
I cannot think of a dynamic formula that would work for both reports with
column restrictions and without, and where columns change around. None of
that is going to be supportable in FRx.
I do not even think Management Reporter supports what you are looking for
but I am checking with the PM. If I find out differently I will let you know
but in the mean time, you may have to create some macros in Excel to create
the formulas you want in rows, after exporting the FRx report to Excel.
If you do come across a solution in FRx for this situation I would love to
hear about it.
thanks
Rob
--
Rob Iversen
MSX Group
www.msxgroup.com

rivers1

2010-09-01 22:52:03 UTC

Permalink

Yes, it would be nice if FRx was enhanced but it is getting towards its end
of life. Still works great but no more development or enhancements.

The Sage MAS Intelligence product should be more flexible in some ways. I
am working with that company to develop a number of interfaces to different
GL and other modules. If you have any questions on that or anything else,
feel free to holler.

thanks
--
Rob Iversen
MSX Group
www.msxgroup.com

Post by YSM
Hi Rob,
No, I can't think of a solution....
first, if the column is restricted. Not having this funcionality has really
messed up my reporting plans and now I have to jump through extra hoops in
Excel. Such is report design life!
Unfortunately, Management Reporter won't be a solution for us as we are on
Sage MAS. the SAGE MAS Intelligence product is not available to us yet and we
are unde time constraints so FRX+ Excel it will have to be.
that goes to the column for unit restriction first, then the tree .... Mind
you, there are a bunch of other areas that FRX could use some enhancement on
.....
thanks again!