Drop Down Calendar Selection

overflow
Posts: 22
Joined: Wed Jan 21, 2009 1:24 am
OLAP Product: TM1
Version: 10.2 9.4 10.1
Excel Version: 2013 2010
Location: Melbourne Australia

Drop Down Calendar Selection

Post by overflow »

Hi there,

Wondering if anyone can help me finish off an idea to select a date from a drop down calendar in a cube view, either through Cube View or from a Performance Modeler application?

I am figuring that the initial part needs to be a drill through to a calendar, with the parameters from the selected cube including year / month, in the standard format. The calendar would be a cube built for this purpose, dimensions = Month, Year, Day and Week.
eg:
January 2014

MON TUE WED THU FRI SAT SUN
Wk1: 0 0 1 2 3 4 5
Wk2: 6 7 8 9 10 11 12
Wk3: 13 14 15 16 17 18 19
Wk4: 20 21 22 23 24 25 26
Wk5: 27 28 29 30 31 0 0
Wk6: 0 0 0 0 0 0 0

The last part is how I can automate a selection of the date as a passed parameter back to the original cell. Wanting to clarify my thoughts, and wondering if this is the best way around it?

Overflow.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Drop Down Calendar Selection

Post by declanr »

Rather than having a "calendar cube" I would just set up a dimension with dates as the n-level elements and then consolidations for weeks, months and years.

If you have the option for using a websheet you could select it through a subnm and have a DBSS on that, if it needs to be in the cube viewer - have a picklist based on the dimension.
Declan Rodger
overflow
Posts: 22
Joined: Wed Jan 21, 2009 1:24 am
OLAP Product: TM1
Version: 10.2 9.4 10.1
Excel Version: 2013 2010
Location: Melbourne Australia

Re: Drop Down Calendar Selection

Post by overflow »

Thanks Declan,

I see the point of your suggestion; what I have users suggesting is the need to identify Mondays as per a particular month, or similar concepts, from a drop down box or similar arrangement of a calendar month, similar to a web portal type form. If I have this set up as a drill through, this will give me the format I would like, but not necessarily the selection of items from that.

While I know this could be done with an alias on the date (EG MON Feb 17 2014), I am intrigued by the idea of replicating a calendar as a drop down for selection in the web portal, and guessing at solutions.

Rgds'
Overflow.
dan.kelleher
Community Contributor
Posts: 127
Joined: Wed Oct 14, 2009 7:46 am
OLAP Product: TM1
Version: 9.4
Excel Version: 11
Location: London

Re: Drop Down Calendar Selection

Post by dan.kelleher »

Would dynamic pick lists be an option?
overflow
Posts: 22
Joined: Wed Jan 21, 2009 1:24 am
OLAP Product: TM1
Version: 10.2 9.4 10.1
Excel Version: 2013 2010
Location: Melbourne Australia

Re: Drop Down Calendar Selection

Post by overflow »

Not sure I could get the dynamic pick list to work as a drop down calendar month?

How could I set that up?
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Drop Down Calendar Selection

Post by tomok »

overflow wrote:Not sure I could get the dynamic pick list to work as a drop down calendar month?

How could I set that up?
A picklist in TM1 is really nothing more than a single column drop-down. That's all you can do in TM1. There is no calendar widget like you would have if you were creating a web or VB app. To the extent you could somehow create a dynamic drop-down list of dates for use as a picklist, it would still be just a single list of dates. I wouldn't waste your time on this as you'll never get payback on the amount of time you'd spend creating it. These are the types of things you'll start seeing in future versions of TM1 because they are cute and they help sell software, but for now you're out of luck.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
overflow
Posts: 22
Joined: Wed Jan 21, 2009 1:24 am
OLAP Product: TM1
Version: 10.2 9.4 10.1
Excel Version: 2013 2010
Location: Melbourne Australia

Re: Drop Down Calendar Selection

Post by overflow »

Thanks Tomok;

I was already using a picklist; the users wanted to be able to see the day (so that they could select the first Monday in a month, for example).

What I will do is modify the subset used for that picklist to pick up the alias, including the day with the date.

However, if new developments allow for this functionality, that would be a very useful function to have available!

Rgds
overflow
PlanningDev
Community Contributor
Posts: 349
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: Planning Analytics
Version: 2.0.5
Excel Version: 2016

Re: Drop Down Calendar Selection

Post by PlanningDev »

If you are using web sheets I would think you could open up an excel file that looked like calendar through the url api. And then using the hyperlink function in the calendar area you could pass the clicked value back to the original page. Kind of a Janky way to do it but it would be interesting to see implemented. A TM1 version of a calendar popup.
User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Drop Down Calendar Selection

Post by Harvey »

If your users are really demanding this, I'd imagine your only option would be to make the form entirely custom html. With that freedom you could use the jquery ui calendar widget, which is very cool and probably the kind of thing you are after.

This would require decent web development skills and a knowledge of the TM1 API, and might be hard to integrate into a Contributor application.

Personally, I build a lot of stuff in asp.net using the C API. If you're looking to get into that area, I've published a blog post about using the C API in .NET, which might help.
Take your TM1 experience to the next level - TM1Innovators.net
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Drop Down Calendar Selection

Post by tomok »

Harvey wrote:With that freedom you could use the jquery ui calendar widget, which is very cool and probably the kind of thing you are after.
I've done a good bit of web development in my day and the whole jquery development platform is awesome. What I would love to see (even though it will never happen) would be for IBM to open up the TM1 server to the web development community. The first thing I would like to see is a module that turns an MDX query into a JSON object. With that you could execute AJAX queries on your web pages to pull back data. That would open everything to the possibility of creating our own web-based and smart phone apps. It would be read-only but that's probably the lion's share of use anyway. If anyone wants to volunteer to write an MDX to JSON module in the API I will help wherever I can. Since I don't program in the API I don't know how much help I would be but the offer is out there.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
PlanningDev
Community Contributor
Posts: 349
Joined: Tue Aug 17, 2010 6:31 am
OLAP Product: Planning Analytics
Version: 2.0.5
Excel Version: 2016

Re: Drop Down Calendar Selection

Post by PlanningDev »

tomok wrote:
Harvey wrote:With that freedom you could use the jquery ui calendar widget, which is very cool and probably the kind of thing you are after.
I've done a good bit of web development in my day and the whole jquery development platform is awesome. What I would love to see (even though it will never happen) would be for IBM to open up the TM1 server to the web development community. The first thing I would like to see is a module that turns an MDX query into a JSON object. With that you could execute AJAX queries on your web pages to pull back data. That would open everything to the possibility of creating our own web-based and smart phone apps. It would be read-only but that's probably the lion's share of use anyway. If anyone wants to volunteer to write an MDX to JSON module in the API I will help wherever I can. Since I don't program in the API I don't know how much help I would be but the offer is out there.

Have you looked at Enterprise Services from Carpe Datum? They have a publishing tool that allows you to publish TM1 Objects as Web Services. Then you can do whatever you want with the data (JSON). They recommend Kendo UI but DOJO or any development platform would work. I played around with java development of passing Views as JSON etc. but Im not really a web developer and it quickly got beyond my ability to spend time on it.
User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Drop Down Calendar Selection

Post by Harvey »

tomok wrote:The first thing I would like to see is a module that turns an MDX query into a JSON object. With that you could execute AJAX queries on your web pages to pull back data.
Tomok, that's the kind of thing Flow was created for -- to fill in gaps in the IBM Cognos offering.

I'm certainly in a position to do this, but the way I would envisage the architecture, it would involve installing a web application on the TM1Web server (or another web server with access to TM1).

I would also probably develop it in .NET, as it has much richer tools and libraries for creating web services and has built in JSON support.

I've actually already developed a couple of web services with similar functionality, so the approach is pretty easy.


Aside:

Ironically, I actually built the first version of the current TM1Web, and I had the OP's request covered.

When Applix "borrowed" the source code from me, one of the features they removed to get to a quick release was "plug-in" support for Excel Websheets.

This was very short-sighted, as it would have allowed developers to extend the Websheet functionality by creating a plug-in in Excel and then an associated TM1Web plug-in on the server. A drop-down calendar would have been very possible under such a scenario.

Oh well not to be... I doubt IBM will ever reinstate the functionality, so we're left with the work-arounds being discussed in this thread.
Take your TM1 experience to the next level - TM1Innovators.net
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Drop Down Calendar Selection

Post by tomok »

Harvey wrote:I'm certainly in a position to do this, but the way I would envisage the architecture, it would involve installing a web application on the TM1Web server (or another web server with access to TM1).

I would also probably develop it in .NET, as it has much richer tools and libraries for creating web services and has built in JSON support.

I've actually already developed a couple of web services with similar functionality, so the approach is pretty easy.
If you do decide to develop this and need someone to server as a tester I would be happy to do so. I have done a good bit of development with jQuery and jQuery Mobile. What I would really like to do is take a stab at creating a dashboard app using jQuery Mobile. With all the widgets and stuff available to you in jQuery Mobile there are some really interesting possibilities out there.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
User avatar
Harvey
Community Contributor
Posts: 236
Joined: Mon Aug 04, 2008 4:43 am
OLAP Product: PA, TM1, CX, Palo
Version: TM1 8.3 onwards
Excel Version: 2003 onwards
Contact:

Re: Drop Down Calendar Selection

Post by Harvey »

Tomok, agreed, the opportunities are pretty exciting! I'll have a chat with my colleagues and PM you if we decide to pursue it.
Take your TM1 experience to the next level - TM1Innovators.net
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

Re: Drop Down Calendar Selection

Post by Ptec »

Hey ,

Our customer has the same Request! He wants a calendar (month view) like in the Cube Viewer but in an web application or in TM1 Web.

Sorry that i haven't any good web dev skills but any hints how I can archive this would be very helpful!

Thanks!
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Drop Down Calendar Selection

Post by Elessar »

As far as I know, the calendar will be by default in TM1Web in TM1 10.3.
Just wait until august.
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
Ptec
Posts: 15
Joined: Mon Mar 14, 2016 9:14 am
OLAP Product: TM1
Version: 10.2.2.0
Excel Version: 2013

Re: Drop Down Calendar Selection

Post by Ptec »

Thanks for the information!
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Drop Down Calendar Selection

Post by Elessar »

UPD: The calendar picker works by default (for "date" number format) in PA 2.0, but only in PAW :(
In TM1web, you still need to use picklists
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Drop Down Calendar Selection

Post by jim wood »

BTW A pick list isn't just a single list. If you do create a pick list cube (or not) you can link the lists via rules for say Day, Month and year to only show the values as they apply, so you can set month to null if no year is selected, then set day to null if no month and then change the values displayed depending on the month selected. You can also link it to a look for leap year,

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Drop Down Calendar Selection

Post by declanr »

Elessar wrote: Mon Jul 16, 2018 9:57 am UPD: The calendar picker works by default (for "date" number format) in PA 2.0, but only in PAW :(
In TM1web, you still need to use picklists
Hi Elessar, haven’t had a chance to check this but I know I’ve had it working in tm1 websheets - do you mean it doesn’t work in the cube viewer in TM1 web or do you mean websheets? Or both? If so what fp are you on.
Declan Rodger
Post Reply