VBA Code to double click

[Faizal]

Hi All

I am creating a report in Excel and used a Dynamic slice so that the user can drill down. The formating of the row dimension is not ideal and had for the user to follow as they drill down since we have a fragment Heirarchy. So what I did was add another column and used excel formula to insert space infront of the element in the row dimention so the dimention steps everytime we drill (see attached image "Report"). Column A has the Excel formula and column B has the element from the TM1 Dimentions called "Account"

Report.png (18.65 KiB) Viewed 7302 times

The problem i am now having is that I want to be able to double click on the Excel formated line (Column A) to drill down but TM1 does not allow it.

what I then tried is using VBA. See code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Worksheets("Sheet2").Activate
ActiveCell.Offset(0, 1).Activate
Application.DoubleClick
ActiveCell.Offset(0, -1).Activate
Cancel = True


End Sub


Please help....anyone..... please lol

[Steve Vincent]

I understand your frustration but i'm afraid what you are doing is not going to be possible. As soon as you edit a TM1 formula in Excel, you will break its functionality. Even adding a cell reference to a SUBNM will break the double click ability. Horrible as it is, your users will have to either live with it or use the cube browser directly.

Another word of warning, not sure what version of TM1 you are using but i know in 9.0 the dynamic slices have a habit of getting corrupted. There is no solution to that other than to delete it and start again. Unless you are on 9.4 where a lot of work has gone in to them (and renamed active forms) i'd steer clear of them...

[Steve Rowe]

You can set the formats of the dynamic element list.

To set the formats, from the subset editor (with the relevant elements visible) select Edit Element Formats (icon looks like the properties icon with a pencil. When you slice the dynamic slice select the format you created from the drop down next to the icon.

All the formats are stored in a cube and you could put rules in here to generalise the format.

Like Steve says though make sure your happy with the stability of dynamc slice before you commit yourself to this approach.

The manual section on dynamic slices should give you more detail on how to set up the formats. (chapter 7 of the user guide)

HTH

[paulsimon]

Hi

My experience of Dynamic Slices is that they do not work reliably no matter what version you use. My understanding of Dynamic Slices is that they have been dropped in 9.4. The Active Forms in 9.4 is completely different functionality. Therefore I would not use them, as any application that uses them will have a very short shelf life, even if it does work.

Don't try the In-Spreadsheet Browser either as they are discontinuing that in 9.5.

If you really need this functionality (and I can see why you would) then I would recommend upgrading to 9.4 for Active Forms. I haven't used 9.4 in anger but so far I haven't noticed that it is any less stable than 9.1.

Unfortunately many people prefer to wait until at least the first Service Pack has come out before upgrading. Even if you would like to upgrade, yout company's IT policies may prevent regular upgrades.

The attached example shows how you can easily make a basic drill down/up, with a little VBA, probably something along the lines of what you were trying. Let me know if it helps.

Regards


Paul Simon

[Alan Kirk]

If you really need this functionality (and I can see why you would) then I would recommend upgrading to 9.4 for Active Forms. I haven't used 9.4 in anger but so far I haven't noticed that it is any less stable than 9.1.

Unfortunately many people prefer to wait until at least the first Service Pack has come out before upgrading. Even if you would like to upgrade, yout company's IT policies may prevent regular upgrades.

I think that in this case the reluctance is more to do with the move to Unicode; go forward, and there's no going back since the database files are no longer backward-compatible.

Also my understanding is that the Unicode format will be more demanding on system resources, so it's best not to move (or upgrade the hardware first) if you're currently bumping the ceiling.

[paulsimon]

Hi Alan

I think that in this case the reluctance is more to do with the move to Unicode; go forward, and there's no going back since the database files are no longer backward-compatible.

Also my understanding is that the Unicode format will be more demanding on system resources, so it's best not to move (or upgrade the hardware first) if you're currently bumping the ceiling.

You can go back provided that you save your 9.1 Cubes before you open them in 9.4. Of course, if you have carried on developing in 9.4 for a month or two and then hit a problem which means that you need to go back, then you may have problems remembering all the changes since you took your cut of 9.1 cubes. However, if all else fails, it should still be possible to export the data, re-create the cubes in 9.1 and re-import it. I have an application that generates a series of T_CREATE statements for the cubes on a server so that you can create an empty shell in which to import data. I have TIs that export dims to files, and re-create them, etc. However, although there are often problems in new releases things have never been so bad that I have had to uninstall after a roll out. The bad problems are usually caught while testing the new release before roll out.

From what I have heard from David Usherwood, 9.4 is a better release than 9.1. We are having a few problems with 9.1 at the moment, but then I have had problems in every version of TM1, Essbase, MicroStrategy and SQL Server that I have used.

As for the performance side of things, that is something that you need to check out before upgrading. As I generally do testing on a desktop any performance issues tend to be quite obvious. I can't believe that it will be as bad as the memory hike in 9.1 if you didn't get the config settings right. After all most TM1 data is still numeric floating point data, and therefore it won't be affected by the unicode issue.

Regards


Paul Simon

[Alan Kirk]

Hi Alan

I think that in this case the reluctance is more to do with the move to Unicode; go forward, and there's no going back since the database files are no longer backward-compatible.

Also my understanding is that the Unicode format will be more demanding on system resources, so it's best not to move (or upgrade the hardware first) if you're currently bumping the ceiling.

You can go back provided that you save your 9.1 Cubes before you open them in 9.4. Of course, if you have carried on developing in 9.4 for a month or two and then hit a problem which means that you need to go back, then you may have problems remembering all the changes since you took your cut of 9.1 cubes. However, if all else fails, it should still be possible to export the data, re-create the cubes in 9.1 and re-import it.

That's a good suggestion. If the cubes are huge you could be looking at quite a bit of down time while you do it, but it's certainly viable.

I have an application that generates a series of T_CREATE statements for the cubes on a server so that you can create an empty shell in which to import data.

What, you aren't using the API to do this?!?

I have TIs that export dims to files, and re-create them, etc. However, although there are often problems in new releases things have never been so bad that I have had to uninstall after a roll out. The bad problems are usually caught while testing the new release before roll out.

Usually, yes. Alas (and this is why I can understand some reluctance to go to 9.4 by some users), every so often there's an obscure bug which you won't find no matter how assiduously you test. I recall the one that throttled us in either 8.2.10 or .11 (can't remember which); it would crash the server if you had a person entering to one cube while another person was reading a second cube (related to the first, and a bunch of others, by rules). It's what bugs me when Iboglix's "solution" to a problem is "upgrade". Even if people have the time to run a sufficient battery of tests, there's always that one bug out there that's got somebody's name on it...

From what I have heard from David Usherwood, 9.4 is a better release than 9.1. We are having a few problems with 9.1 at the moment, but then I have had problems in every version of TM1, Essbase, MicroStrategy and SQL Server that I have used.

That's interesting. I haven't noticed a huge difference between them stability-wise (assuming that you sidestep the known landmines in 9.1, though I think that most of them are fixed in SP 4 anyway), though admittedly I only have 9.4 on a notebook since I don't have any spare servers to do an evaluation at the moment.

(Or I DID have it on a notebook until my grrrr-ing notebook decided that it would only talk to its monitor for 30 seconds at a time last weekend, then go black...)

As for having problems in every version... I hear ya'. As long as you know what to expect, though, you can navigate them. This is why everyone wants everyone else to go first. Unfortunately SOMEONE has to do it.

As for the performance side of things, that is something that you need to check out before upgrading. As I generally do testing on a desktop any performance issues tend to be quite obvious. I can't believe that it will be as bad as the memory hike in 9.1 if you didn't get the config settings right. After all most TM1 data is still numeric floating point data, and therefore it won't be affected by the unicode issue.

Yes, that's a good point; I think that 9.1 was the one I was mixing up with 9.4 there for some reason. The Unicode should only affect the string entries, though I'd expect that it will also make more demands in respect of metadata.