VBA Code to double click

Post Reply
Faizal
Posts: 7
Joined: Mon Oct 27, 2008 3:06 am

VBA Code to double click

Post by Faizal » Fri Nov 07, 2008 12:47 am

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
Report.png (18.65 KiB) Viewed 2891 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 :?

User avatar
Steve Vincent
Site Admin
Posts: 1050
Joined: Mon May 12, 2008 8:33 am
OLAP Product: TM1
Version: 10.2.2 FP1
Excel Version: 2010
Location: UK

Re: VBA Code to double click

Post by Steve Vincent » Mon Nov 10, 2008 9:24 am

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...
If this were a dictatorship, it would be a heck of a lot easier, just so long as I'm the dictator.
Production: TM1 64 bit 10.2.2, Windows 2008/2012 Server. Excel 2010, IE11 for t'internet

User avatar
Steve Rowe
Site Admin
Posts: 1965
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: VBA Code to double click

Post by Steve Rowe » Mon Nov 10, 2008 3:27 pm

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

User avatar
paulsimon
MVP
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: VBA Code to double click

Post by paulsimon » Mon Nov 10, 2008 11:56 pm

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
Attachments
Basic Drill.xls
Basic Drill Down and Up using VBA
(47.5 KiB) Downloaded 120 times

User avatar
Alan Kirk
Site Admin
Posts: 6002
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: VBA Code to double click

Post by Alan Kirk » Tue Nov 11, 2008 12:47 am

PaulSimon wrote: 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
paulsimon
MVP
Posts: 718
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: VBA Code to double click

Post by paulsimon » Tue Nov 11, 2008 11:02 pm

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

User avatar
Alan Kirk
Site Admin
Posts: 6002
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: VBA Code to double click

Post by Alan Kirk » Wed Nov 12, 2008 4:54 am

PaulSimon wrote: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.
PaulSimon wrote: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?!? :o ;)
PaulSimon wrote: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...
PaulSimon wrote: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. :(
PaulSimon wrote: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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Post Reply