Facing below error for an if condition I wrote. Please help.
XQE-PLN-0264 The argument at position 2 of the function 'if' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.
RSV-SRV-0042 Trace back:
Below are my data items. If I remove, [ReportingPeriod] = 'P13/14' , the statement works fine. I don't how to use two different conditions in and. Please help
[Data Item1] >
if (?pPeriod? = 'Reporting Period' and [ReportingPeriod] = 'P13/14' )
then ([P13Weeks]) else ([Reporting Period])
[ReportingPeriod] > descandants([Reporting Period],1)
Try rewriting it as a case-statement:
CASE
WHEN ?pPeriod? = 'Reporting Period' and [ReportingPeriod] = 'P13/14'
THEN [P13Weeks]
ELSE [Reporting Period]
END
I tried this but I am facing below error
XQE-PLN-0264 The argument at position 1 of the function '=' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.
My bad should be:
CASE
WHEN ?pPeriod? = 'Reporting Period' and caption([ReportingPeriod]) = 'P13/14'
THEN [P13Weeks]
ELSE [Reporting Period]
END
Since you are working with dimensional data it expects a member not a string, so you have to caption [ReportingPeriod] to be able to match it with 'P13/14'
Apologies, It looks like an Invalid statement
XQE-PLN-0264 The argument at position 2 of the function 'WHEN' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.
What is the parameter ?pPeriod? Passing? A string or a member ?
It's a member. How to check the parameter data type
Write the expression like this then:
CASE
WHEN ?pPeriod? = 'Reporting Period' and caption([ReportingPeriod]) = 'P13/14'
THEN caption([P13Weeks])
ELSE caption([Reporting Period])
END
I am able to get past this error but I am not getting any values for my calc member which I have put this case statement in.
This is probable because of my wrong selection of member caption in the when statement. first child of [Reporting Period] is dynamic and changes as P04,P12,P13/14 etc.. May I know the easiest way to get the firstchild hierarcy please? Below is not fetching any data for me as I need to get 4 weeks for each period.
CASE
WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P04') THEN ([P04 (P-P04)])
WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P12') THEN caption([P12Weeks])
WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P13/14') THEN caption([P13Weeks])
ELSE caption([P13 (P-P13)])
END
To get the first child of the an hierarchy you can write firstChild(currentMember([Cube].[Dimension].[Hierarchy]
))
I have a member hierarcy as below..
Cube - Profit & Loss
Dimension - Periods
Hierarcy - Periods
Member I want to get the first child from - [Reporting Period]
Method 1:
CASE
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P04' ) THEN ([P04 (P-P04)])
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P12') THEN caption([P12Weeks])
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P13') = 'P13/14') THEN caption([P13Weeks])
ELSE caption([P13 (P-P13)])
END
Error: error saying [Reporting Period] is wrong
Method 2:
CASE
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P04' ) THEN ([P04 (P-P04)])
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P12' ) THEN caption([P12Weeks])
WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P13' ) = 'P13/14') THEN caption([P13Weeks])
ELSE caption([P13 (P-P13)])
END
Error: XQE-EXE-0044 Data Types "numeric" and "string" cannot be compared