Search found 215 matches

by Mark RMBC
Wed Feb 19, 2020 2:23 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: SQL Query - ODBC or syntax issue?
Replies: 5
Views: 136

Re: SQL Query - ODBC or syntax issue?

Looking at the error I would check your spacing, for example you have sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT There needs to be a space between Amount and From and ZapisySap and Left There are other spacing issues so I would sort that out first regards, M...
by Mark RMBC
Tue Feb 18, 2020 7:43 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: Drill Through to ORACLE ODBC
Replies: 11
Views: 555

Re: Drill Through to ORACLE ODBC

Just ruling out anything obvious, you haven't applied any case sensitivity to any of your table columns have you?
by Mark RMBC
Thu Jan 30, 2020 1:24 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX FILTER and non-existent elements
Replies: 7
Views: 234

Re: MDX FILTER and non-existent elements

Oh so this stops server crashes for invalid members, in that case a welcome improvement, given I have fallen foul of this when I first started out on TM1!
by Mark RMBC
Wed Jan 29, 2020 9:45 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX FILTER and non-existent elements
Replies: 7
Views: 234

Re: MDX FILTER and non-existent elements

Hi, Yes I see this too. I can't imagine a situation where I would allow a non existent element to be included in the MDX but things like this does give cause for caution when using MDX! The only way I can get the statement to error is if I take off the dimension prefix, so instead of [Time].[2020_17...
by Mark RMBC
Tue Jan 28, 2020 10:54 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX filter according to attribute of current user
Replies: 2
Views: 335

Re: MDX filter according to attribute of current user

Hi,

How about:

Code: Select all

{FILTER({[Organisation].Members}, [Organisation].currentmember.name = StrToMember ( "[}Clients].[" + UserName + "]" ).properties("Department"))}
regards,
Mark
by Mark RMBC
Mon Jan 27, 2020 3:32 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: DBR/DBRW formulas resulting in #VALUE! errors
Replies: 8
Views: 232

Re: DBR/DBRW formulas resulting in #VALUE! errors

One of those occasions where you swear you are going mad!

My money would have been on cell B5, out of interest how exactly was that cell formatted, the cell that held Jan-2010?

regards,

Mark
by Mark RMBC
Tue Jan 14, 2020 11:35 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX Issues with 2 row dimensions in Active form
Replies: 3
Views: 561

Re: MDX Issues with 2 row dimensions in Active form

Hi, I can't think of an mdx workaround. If these forms are NOT being consumed in TM1 web you could insert a column to DBRW to the category lookup cube and apply custom validation (Unfortunately custom validation does not appear to work in TM1web) on whether the DBRW returns Y, so only allow none zer...
by Mark RMBC
Mon Jan 13, 2020 12:48 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX Issues with 2 row dimensions in Active form
Replies: 3
Views: 561

Re: MDX Issues with 2 row dimensions in Active form

Hi, It would be nice to know which dimensions are in your 2 .lookup cubes. Just looking at your category mdx statement (the second one), there is no reference to the category dimension in the mdx? It would appear to be pointing at the KPI dimension? Incidentally: I do not think what you are trying t...
by Mark RMBC
Mon Jan 13, 2020 9:22 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: TM1 MDX in TI Process
Replies: 14
Views: 605

Re: TM1 MDX in TI Process

Hi, I managed to get this work in a fashion, i.e. to asciioutput where the FTE = 0 and the Bonus Rate > 0. The problem comes with the string element, i.e. Action element. Under no circumstances could I get it to ascii where FTE = 0 and Action <>"" Incidentally I found the following to be more robust...
by Mark RMBC
Thu Jan 09, 2020 4:45 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: TM1 MDX in TI Process
Replies: 14
Views: 605

Re: TM1 MDX in TI Process

Hi,

So you have the dimensions category, company, department, division, employee and measure in your cube
and your MDX refers to only the employee and measures dimension? Also your Action measure is a string.

I am surprised the MDX returns the correct values under those circumstances.

regards,
Mark
by Mark RMBC
Thu Jan 09, 2020 10:36 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: PAX Dynamic Form FILTERBYPATTERN MDX
Replies: 0
Views: 265

PAX Dynamic Form FILTERBYPATTERN MDX

Hi Folks, In the grand old days of perspectives I was able to build an active form with the option to do a wildcard search, to do this I ensured the alias was populated in the TM1RPTROW and then the mdx part of the TM1RPTROW would reference a cell. The mdx would be something like: ="{TM1Sort(TM1Filt...
by Mark RMBC
Tue Dec 03, 2019 5:24 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX - dynamic subset to filter by two other dimensions
Replies: 12
Views: 5098

Re: MDX - dynamic subset to filter by two other dimensions

Hi James,

no worries, tbh without planningdev I wouldn't have got there either!

Just for my sanity, what were the tweaks you made?

regards,
mark
by Mark RMBC
Tue Nov 19, 2019 1:49 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX Filtering on StrMember
Replies: 4
Views: 267

Re: MDX Filtering on StrMember

Sorry misread that! You want to get the attribute value for Oct-2019! How about? FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= StrToMember("[Periods-Months].[" +[Global Variables].([Global Values].[Value],[Global Variables].[LACT])+ "]...
by Mark RMBC
Tue Nov 19, 2019 1:02 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX Filtering on StrMember
Replies: 4
Views: 267

Re: MDX Filtering on StrMember

Hi Tomok,

it would be easier if the global value was numeric, but how about:

FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL([Periods-Months])}, 0)}, [Periods-Months].[TM1 Last Day Serial Date] >= strtovalue([Global Variables].([Global Values].[Value],[Global Variables].[LACT])))

regards, mark
by Mark RMBC
Thu Nov 14, 2019 6:06 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX - dynamic subset to filter by two other dimensions
Replies: 12
Views: 5098

Re: MDX - dynamic subset to filter by two other dimensions

Ha! Thanks PlanningDev, I get it. I think you are correct about the and statements, but I tried the following and it seemed to work: StrToSet( IIF(Instr(1, [GLOBAL Level].CurrentMember.Name, 'All Levels', 1) >0 or Instr(1, [GLOBAL Mode].CurrentMember.Name, 'All Modes',1) >0, "{TM1SubsetAll([Course])...
by Mark RMBC
Wed Nov 13, 2019 5:17 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX - dynamic subset to filter by two other dimensions
Replies: 12
Views: 5098

Re: MDX - dynamic subset to filter by two other dimensions

Hi PlanningDev,

Maybe you can explain where in the MDX Mode=PT AND level=LevelA is referenced because it is confusing me! :?
by Mark RMBC
Wed Nov 13, 2019 10:07 am
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: Optimize While
Replies: 20
Views: 853

Re: Optimize While

I don't think gTonkins long hand method will work here given I think this is spreading the same value to multiple cells, so if my understanding is correct the problem looks as follows: Source Cube: 2019 AreaA SectionD 100 2019 AreaA SectionE 200 Destination Cube: 2019 AreaA SectionD SubSectA 100 201...
by Mark RMBC
Thu Nov 07, 2019 2:54 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX - dynamic subset to filter by two other dimensions
Replies: 12
Views: 5098

Re: MDX - dynamic subset to filter by two other dimensions

Hi, I am slightly confused, which may well be me! What does the cube have anything to do with this, that is what I am struggling with. When you say Global Level and Global Mode are both title dimensions in this cube, which cube? And in reality Global Level and Global Mode are both just dimensions ar...
by Mark RMBC
Thu Nov 07, 2019 1:33 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: MDX - dynamic subset to filter by two other dimensions
Replies: 12
Views: 5098

Re: MDX - dynamic subset to filter by two other dimensions

Hi, I haven't quite worked out what Global Level or Mode is, a subset, a single element in a dimension of one element? I don't think the UNION is the issue personally. Have you tried doing something like just to check it returns something: {UNION( StrToSet( IIF( [GLOBAL Level].CurrentMember.Name<>"A...
by Mark RMBC
Fri Oct 25, 2019 1:13 pm
Forum: IBM TM1, Planning Analytics, PAx and PAW
Topic: IF function return undefined type Error
Replies: 9
Views: 6044

Re: IF function return undefined type Error

It would also help if example 1 had the correct number of parenthesis!