TM1 Forum

Hello all,

i have the following problem:

I have a dynamic cube-view composed of four dynamic subsets. I do a classic slice of this view to excel.
The reference I get in the Excel-slice (cell A1/B1) isn't the view's name but the cube's name. That means, the slice isn't dynamic anymore, because the cube references the dimensions and not my dynamic subsets.

CUBE:
=VIEW("server:Cubename",$B$2,"!","!")

Is there any possibility to reference the dynamic view instead the original cube?

Thank you in advance!

I don't think there is a way of using the View function to do this...
but you could replace your row and column headers with subnm functions that reference your subset name

=SUBNM("server:dimName","subsetname",index number)

Where index number is just a 1 or 2 and so on, you can extend this by replacing the hard coded index number with
=SUBNM("server:dimName","subsetname",row() -x)
=SUBNM("server:dimName","subsetname",column() -y)

where x and y are the 1 less than the starting row and column of your layout. So if your report starts on row 4, x=3 which will give you the 1 you need. If link into the dimension names rather than hardcoding them then you will stop users double clicking on them.


HTH
Cheers,

What you are asking for is the functionality in active forms, as in v9.4. They had previous attempts at this (dynamics slices from 9.0) but i've had mixed luck woth them. The in-spreadsheet browser also has that kind of functionality but that is being removed in the not too distant future...

Thank you guys for your replies.

@Steve Vincent: Do you know, how this active forms can be processed in Excel? I didn't use active forms so far, because i tried to create a Excel-chart which has references on the active form. That worked so far, but when i recalculated the active form (F9), my chart lost it's data. Is there a way to avoid this behavior?

Hi SADELMA

What you are asking for is not within the functionality of a slice direct from the cube viewer. However if you use insert an in spreadsheet browser object and reference it to your view then it will stay dynamic in the way that you want. However with ISB you have very limited options as far as formatting goes so it is more of an analysis tool than a presentation tool. Making an Excel report dynamic can be achieved quite easily with the use of SUBNM, ELCOMP, ELPAR, DBRA, etc. formulas (this is much easier and out of the box with active forms in 9.4 though).

I suggest a very good place to start would be to read the appropriate sections of the TM1 Users Guide!

SADELMA

I think what you mean by a Dynamic View is just a View that has MDX subsets?

The SUBNM method is one method. However, compared to Active Forms, it is certainly not easy, and it falls down if you want to nest one dimension within another.

There is something called Dynamic Slices, which do allow expansion and contracting on rows and columns. However, they never worked properly and have been dropped in 9.4. I would not even bother trying that option.

The In-Spreadsheet Browser ISB is being dropped in the version after 9.4, so it probably isn't a good long term building block. It is more reliable than the Dynamic Slices but has always had its glitches. It is quite possible to link normal DBRWs to the results of the ISB, to get at the page by elements with MID() etc functions.

The Active Forms option in 9.4 is more limited, in that only the Rows are dynamic, and the columns are still static.

I have been experimenting recently with the old VUSLICE & SUBDEFINE Macro functions, with good results. However, to do any formatting you need to link across to another sheet. I am also a little concerned about the warnings about server locking. I wonder if this does actually still apply in 9.1, which should really only lock the cube you are working on, and I can't see why it would need to do even that. So far, it doesn't seem to have been a problem in practice. The VUSLICE method does allow for suppress zeroes. At first it appeared on only work with Public Views, but it does work with Private Views, so each user can have their own Private View with their own Private Subsets for selections, and Public Subsets that are usually MDX ones for the rows and columns.

To get over the Chart issue, you need to make your Chart dynamic. This is a pure Excel technique. If you search on Google you will find the details. Here is one link I found:

http://peltiertech.com/Excel/Charts/Dyn ... hart1.html

If you have problems getting it to work, then let me know. I seem to remember that I had to tweak a few things to get it to work properly. I should be able to find the example.

Regards


Paul

I'd like to show an excel chart in a Websheet based on an active form with zero suppression on rows.
As the active form deletes existing data rows (except the first row) when updating, the chart would loose its data source.

In Excel, I know about two solutions:

1) Select one additional row under the active form as chart source. To prevent the chart from showing an empty item and label, hide this row.
2) Use dynamic names, as proposed by Paul (http://peltiertech.com/Excel/Charts/Dyn ... hart1.html)

But this doesn't work in Websheets.

With 1) it shows the empty item/label anyway
With 2) the chart is empty

Does anybody know how to solve this? Thanks!

Berol