Filter Data Range Within Data Manager Package – Import

[Eyal_Feiler]

Hi Experts,
I would like to filter by a filed using the Load BW InfoProvider UI (import from a BW infocube) DM package.
Previously, we used the selection option filter as needed.  However this is confusing to the users and we want to automate.
The requirement is to filter out using an InfoObject that is not a dimension, and B) to filter with two ranges.
For example:
Select where FLAG1 from “” to A; and FLAG1 is from D to Z

I tried adding the parameter in the tasks as a limit but the task failed.
Is it possible to add a value and or a range to filter/limit?
Thank you in advance
Eyal Feiler

Below is the modification I made:

[former_member186338]

Hi Eyal,

It will not work this way!

Press "Advanced" button and look on the whole advanced script... %SELECTION%....

Vadim

[Eyal_Feiler]

Hi Vadim,

Thank you.

Can you expand a bit more?

In the advanced tab the script is below ( I previously commented out the default prompts and provided values to bypass and pre-define the prompt screen) –this works fine.

So do you mean that I should add the yellow noted below? 

B) I want to add a range e.g. null to A; and then D-Z is allowed?  What is the syntax?

Thank you

Eyal

'DEBUG(ON)

PROMPT(INFOPROVIDERSELECTION,%InforProvide%,%SELECTION%,"Please select the InfoProvider and set selection (InfoProvider list is restricted by both BW and BPC authority)",,)

'PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation file:",,,Import.xls)

'PROMPT(RADIOBUTTON,%TARGETMODE%,"Handling of records in target",0,{"Append","Overwrite records with match key","Replace data in same data region of Entity, Category, Time and Audit ID"},{"0","1","2"})

'PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select whether to run default logic for stored values after importing",1,{"Yes","No"},{"1","0"})

'PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when importing data.",1,{"Yes,
check for work status settings before importing","No, do not check work status settings"},{"1","0"})

PROMPT(KEYDATE,%KEYDATE%,"Key date",0)

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%ACTNO%,%INCREASENO%)

TASK(/CPMB/INFOPROVIDER_CONVERT,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/INFOPROVIDER_CONVERT,ACT_FILE_NO,%ACTNO%)

INFO(%MAI_TRFILE%,\ROOT\WEBFOLDERS\MAI_CONSO\Consolidation\DATAMANAGER\TRANSFORMATIONFILES\EXAMPLES\MA\T_FILE_BW_IPROVIDER.XLS)

'INFO(%FLAG_A,B)

TASK(/CPMB/INFOPROVIDER_CONVERT, TRANSFORMATIONFILEPATH , %MAI_TRFILE%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SUSER,%USER%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPP,%APP%)

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,%InforProvide%)

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%, GF_FLG01,A)

TASK(/CPMB/INFOPROVIDER_CONVERT,KEYDATE,%KEYDATE%)

TASK(/CPMB/LOAD_IP,PREPROCESSMODE,0)

TASK(/CPMB/LOAD_IP,TARGETMODE,2)

TASK(/CPMB/LOAD_IP,INPUTNO,%TEMPNO1%)

TASK(/CPMB/LOAD_IP,ACT_FILE_NO,%ACTNO%)

TASK(/CPMB/LOAD_IP,RUNLOGIC,1)

TASK(/CPMB/LOAD_IP,CHECKLCK,1)

TASK(/CPMB/LOAD_IP,KEYDATE,%KEYDATE%)

'TASK(/CPMB/LOAD_IP,GF_FLG01,B)

'supress
prompts for work status default script and replace - default as yes and yes replace.  Default transformation file path

[former_member186338]

PROMPT(INFOPROVIDERSELECTION,%InforProvide%,%SELECTION%,"Please select the InfoProvider and set selection (InfoProvider list is restricted by both BW and BPC authority)",,)

%SELECTION% here is xml style string! Somthing like:

<Selections><SelectionType="Selection"><Attribute><ID>YSEMPOSIT</ID><Operator>3</Operator><LowValue>14140</LowValue><HighValue>14141</HighValue></Attribute></Selection><Selection Type="FieldList"><FieldID>0CALMONTH</FieldID><Fiel

dID>0CFM_TR_CUR</FieldID><FieldID>0CHNGID</FieldID><FieldID>...

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%, GF_FLG01,A) - will not work, incorrect format!

Vadim

[Eyal_Feiler]

Ok I see.  so something similar to what I see from the task log

I'll perform some testing trial/error and update.

thanks

Eyal

[former_member186338]

The only thing you can do is to fix the whole %SELECTION% string and only for few parameters use user selection with PROMPT(COMBOBOX,%VAR%,... Insert this %VAR% inside xml text.

Vadim

[Eyal_Feiler]

Hi Vadim,

I replaced the %SELECTION% with 3 COMBOX. and tried to default the infocube.

1 Receiving a failed message – please see attached.

2. How would I select a range e.g. FLAG1 #-A and then D-Z (or <> B or C)? I was able to default
the D-Z.

3 Was this what you meant?

Changes are in bold

Please see below.

Thank you.

Eyal

  'DEBUG(ON)

  'PROMPT(INFOPROVIDERSELECTION,%InforProvide%,%SELECTION%,"Please
select the InfoProvider and set selection (InfoProvider list is restricted by
both BW and BPC authority)",,)

  'PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation
file:",,,Import.xls)

'PROMPT(RADIOBUTTON,%TARGETMODE%,"Handling of records in target",0,{"Append","Overwrite records with match key","Replace data in same data region of Entity, Category, Time and Audit ID"},{"0","1","2"})

'PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select whether to run default logic for stored values after importing",1,{"Yes","No"},{"1","0"})

'PROMPT(RADIOBUTTON,%CHECKLCK%,"Select whether to check work status settings when importing data.",1,{"Yes, check for work status settings before importing","No, do not check
work status settings"},{"1","0"})

PROMPT(KEYDATE,%KEYDATE%,"Key date",0)

PROMPT(COMBOBOX,%ENTITY_1%,"Select the entity",1,,,)

PROMPT(COMBOBOX,%TIME_1%,"Select the time period",1,,,)

PROMPT(COMBOBOX,%CATEGORY_1%,"Select the category",1,,,)

PROMPT(COMBOBOX,%FLAG_01%,"Choose the Flags to
include",0,D-Z,,)

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%ACTNO%,%INCREASENO%)

TASK(/CPMB/INFOPROVIDER_CONVERT,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/INFOPROVIDER_CONVERT,ACT_FILE_NO,%ACTNO%)

INFO(%MAI_TRFILE%,\ROOT\WEBFOLDERS\MAI_CONSO\Consolidation\DATAMANAGER\TRANSFORMATIONFILES\EXAMPLES\MA\T_FILE_BW_IPROVIDER.XLS)

TASK(/CPMB/INFOPROVIDER_CONVERT, TRANSFORMATIONFILEPATH , %MAI_TRFILE%)

  TASK(/CPMB/INFOPROVIDER_CONVERT,SUSER,%USER%)

  TASK(/CPMB/INFOPROVIDER_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPP,%APP%)

  'TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,%InforProvide%)

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,GFC_TB50)

'TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%)

  TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%ENTITY_1%)

  TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%TIME_1%)

  TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%CATEGORY_1%)

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%FLAG_01%)

TASK(/CPMB/INFOPROVIDER_CONVERT,KEYDATE,%KEYDATE%)

TASK(/CPMB/LOAD_IP,PREPROCESSMODE,0)

TASK(/CPMB/LOAD_IP,TARGETMODE,2)

TASK(/CPMB/LOAD_IP,INPUTNO,%TEMPNO1%)

TASK(/CPMB/LOAD_IP,ACT_FILE_NO,%ACTNO%)

TASK(/CPMB/LOAD_IP,RUNLOGIC,1)

TASK(/CPMB/LOAD_IP,CHECKLCK,1)

TASK(/CPMB/LOAD_IP,KEYDATE,%KEYDATE%)

'supress prompts for work status default script and replace - default as yes and yes replace.  Default transformation file path

[Eyal_Feiler]

Couldn't add file:

Here is the error log:

CPMB/MODIFY completed in
0 seconds

/CPMB/INFOPROVIDER_CONVERT
completed in 0 seconds

/CPMB/CLEAR completed in
0 seconds

[Selection]

--------------------------------------------------------------

ENTITY_1 = 1120

TIME_1 = 2015.06

CATEGORY_1 = ACTUAL

FLAG_01 = D-Z

[Message]

--------------------------------------------------------------

Task name CONVERT:

No 1 Round:

  An exception with the
type CX_ST_MATCH_ELEMENT occurred, but was neither handled locally, nor
declared in a RAISING clause

  System expected element
'Selections'

model: Consolidation.
Package status: ERROR

[former_member186338]

Sorry, Eyal

I can't help you ...

You are performing strange experiments without understanding....

First: TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%) - %SELECTION% here has to be in xml format like in the sample from my previous message.

You have to create a full xml text string with all required settings (look on the log of unmodified package).

Then, in this xml string you can change some parameters using variables filled by COMBOBOX prompts (simple text strings)

And at the end:

PROMPT(COMBOBOX,%ENTITY_1%,"Select the entity",1,,,)

will not work, the correct syntax is:

PROMPT(COMBOBOX,%ENTITY_1%,"Select the entity",1,,{ENT1,ENT2,ENT3})

where ENT1,ENT2,ENT3 is the manually created list of entities you want user to select.

Please read:

Vadim

[Eyal_Feiler]

Hi Vadim,

Ok. I went back to the xml method.

1. Inserted the xml parameters copied from the task log for the regular task.
2.  Added the variables for the selections e.g. ENTITY TIME and CATEGORY.
3. Added combo boxes for the 3 variables above.  Thank you for your post on COMBOBOXES, it was helpful.  I created COMBOXES that the user could select since I'm not going to

predefine.
Re ran the DM task.
The result - the DM task just runs as if there are no parameters.  The reason being, I assume, is because it's reading the parameters of %SELECTION% associated with the INFOCUBE

and not the new variables.  Killed the job.
Therefore as next step #4:
4.  Commented out the first PROMPT statement and inserted the name of my cube GFC_TB50 to the statement below, in the hopes of pre-defining the cube.

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,%InforProvide%)

Result error as earlier -
No 1 Round:
An exception with the type CX_ST_MATCH_ELEMENT occurred, but was neither handled locally, nor declared in a RAISING clause
System expected element 'Selections'

Below is the script.
How do you propose I fix it?
Thank you again for all your help, especially to an Accountant with application experience but not a programmer....
Regards,
Eyal

Script for #3 above:

Green highlight represents the 3 inserted variables.

Yellow = defaults I want hard coded

light green - cube reference %SELECTION%

'DEBUG(ON)

PROMPT(INFOPROVIDERSELECTION,%InforProvide%,%SELECTION%,"Please
select the InfoProvider and set selection (InfoProvider list is restricted by
both BW and BPC authority)",,)

SELECTION = <?xml
version="1.0" encoding="utf-16"?><Selections
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"><Selection

   Type="Selection"><Attribute><ID>GF_ENT_S</ID><Operator>1</Operator><LowValue>%ENTITY_X%</LowValue><HighValue

   /></Attribute><Attribute><ID>GF_TIME_S</ID><Operator>1</Operator><LowValue>%TIME_X%</LowValue><HighValue

   /></Attribute><Attribute><ID>GF_CTEG_S</ID><Operator>1</Operator><LowValue>%CATEGORY_X%</LowValue><HighValue
/></Attribute><Attribute><ID>GF_FLG01</ID><Operator>3</Operator><LowValue

/><HighValue>A</HighValue></Attribute><Attribute><ID>GF_FLG01</ID><Operator>3</Operator><LowValue>D</LowValue><HighValue>Z</HighValue></Attribute><Attribute><ID>GF_CUR_S</ID><Oper

ator>1</Operator><LowValue>USD</LowValue><HighValue
/></Attribute><Attribute><ID>GF_SCOP_S</ID><Operator>1</Operator><LowValue>S_NONE</LowValue><HighValue

/></Attribute></Selection><Selection

Type="FieldList"><FieldID>0CALMONTH</FieldID><FieldID>0CHNGID</FieldID><FieldID>0RECORDTP</FieldID><FieldID>0REQUID</FieldID><FieldID>GF_ACC_S</FieldID><FieldID>GF_ACC_S__GF_EX_C1

</FieldID><FieldID>GF_ACTV_S</FieldID><FieldID>GF_AI_S</FieldID><FieldID>GF_AUDT_S</FieldID><FieldID>GF_BNK_S</FieldID><FieldID>GF_CC_S</FieldID><FieldID>GF_CNTR_S</FieldID><Field

ID>GF_CTEG_S</FieldID><FieldID>GF_CUR_S</FieldID><FieldID>GF_ENT_S</FieldID><FieldID>GF_ENT_S__GF_EX_C2</FieldID><FieldID>GF_FLG01</FieldID><FieldID>GF_FLG02</FieldID><FieldID>GF_

FLOW_S</FieldID><FieldID>GF_ICO_S</FieldID><FieldID>GF_LDAT</FieldID><FieldID>GF_LDUSR</FieldID><FieldID>GF_LTIM</FieldID><FieldID>GF_PRD_S</FieldID><FieldID>GF_SCOP_S</FieldID><F

ieldID>GF_SGR_S</FieldID><FieldID>GF_SOURCE</FieldID><FieldID>GF_TC_S</FieldID><FieldID>GF_TIME_S</FieldID></Selection></Selections>

'PROMPT(TRANSFORMATION,%TRANSFORMATION%,"Transformation
file:",,,Import.xls)

'PROMPT(RADIOBUTTON,%TARGETMODE%,"Handling
of records in target",0,{"Append","Overwrite records with
match key","Replace data in same data region of Entity, Category,
Time and Audit ID"},{"0","1","2"})

'PROMPT(RADIOBUTTON,%RUNLOGIC%,"Select
whether to run default logic for stored values after
importing",1,{"Yes","No"},{"1","0"})

'PROMPT(RADIOBUTTON,%CHECKLCK%,"Select
whether to check work status settings when importing data.",1,{"Yes,
check for work status settings before importing","No, do not check
work status settings"},{"1","0"})

PROMPT(KEYDATE,%KEYDATE%,"Key date",0)

PROMPT(COMBOBOX,%ENTITY_X%,Please enter the Entity,1,,)

PROMPT(COMBOBOX,%TIME_X%,Please enter the Time,1,,)

PROMPT(COMBOBOX,%CATEGORY_X%,Please enter the Category,1,,)

INFO(%TEMPNO1%,%INCREASENO%)

INFO(%ACTNO%,%INCREASENO%)

TASK(/CPMB/INFOPROVIDER_CONVERT,OUTPUTNO,%TEMPNO1%)

TASK(/CPMB/INFOPROVIDER_CONVERT,ACT_FILE_NO,%ACTNO%)

INFO(%MAI_TRFILE%,\ROOT\WEBFOLDERS\MAI_CONSO\Consolidation\DATAMANAGER\TRANSFORMATIONFILES\EXAMPLES\MA\T_FILE_BW_IPROVIDER.XLS)

TASK(/CPMB/INFOPROVIDER_CONVERT, TRANSFORMATIONFILEPATH , %MAI_TRFILE%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SUSER,%USER%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPPSET,%APPSET%)

TASK(/CPMB/INFOPROVIDER_CONVERT,SAPP,%APP%)

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,%InforProvide%)

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%)

TASK(/CPMB/INFOPROVIDER_CONVERT,KEYDATE,%KEYDATE%)

TASK(/CPMB/LOAD_IP,PREPROCESSMODE,0)

TASK(/CPMB/LOAD_IP,TARGETMODE,2)

TASK(/CPMB/LOAD_IP,INPUTNO,%TEMPNO1%)

TASK(/CPMB/LOAD_IP,ACT_FILE_NO,%ACTNO%)

TASK(/CPMB/LOAD_IP,RUNLOGIC,1)

TASK(/CPMB/LOAD_IP,CHECKLCK,1)

TASK(/CPMB/LOAD_IP,KEYDATE,%KEYDATE%)

'supress
prompts for work status default script and replace - default as yes and yes
replace.  Default transformation file
path

[former_member186338]

Sorry, you don't understand me!!!!!

"3. Added combo boxes for the 3 variables above.  Thank you for your post on COMBOBOXES, it was helpful.  I created COMBOXES that the user could select since I'm not going to predefine."

You have to predefine if you want to have COMBOBOX with the list.

If you want user to just type the ENTITY ID - the use PROMPT(TEXT,... not COMBOBOX! Read help about PROMPT(TEXT: TEXT Prompt() Command - SAP BusinessObjects Planning and Consolidation - SAP Library

For the rest I simply do not understand what are you trying to achieve with you advanced DM script!

It will NEVER work with the syntax like you have - complete mixture of statements, text lines etc...!

Please, search forum an read help about the allowed syntax!

Start with standard code for import and replace:

PROMPT(INFOPROVIDERSELECTION,%InforProvide%,%SELECTION%,"Please select the InfoProvider and set selection (InfoProvider list is restricted by

both BW and BPC authority)",,)

With

PROMPT(TEXT,%ENTITY_X%,...

PROMPT(TEXT,%TIME_X%,...

...

if you want user to type parameters.

Use fixed %InforProvide% in:

TASK(/CPMB/INFOPROVIDER_CONVERT,FILE,%InforProvide%)

Then replace %SELECTION% in:

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,%SELECTION%)

with xml:

<SelectionType="Selection"><Attribute><ID>GF_ENT_S</ID><Operator>1</Operator><LowValue>%ENTITY_X%</LowValue><HighValue/></Attribute>...

Like:

TASK(/CPMB/INFOPROVIDER_CONVERT,INFOPROV_SELECTION,<SelectionType="Selection"><Attribute><ID>GF_ENT_S</ID><Operator>1</Operator><LowValue>%ENTITY_X%</LowValue><HighValue/></Attribute>... )

Vadim

P.S. No mistakes are allowed in the advanced script

[former_member40152]

Hi Eyal

Have you find the solution for this? I'm having the same requierement, could you share ?

Thanks in advance

Regards

[former_member186338]

Hi Lucio,

The complete solution is provided in my answer...

If you have extra questions - please open a new discussion!

Vadim

[former_member40152]

Thanks Vadim

[Eyal_Feiler]

Hi Lucio

Still in process.

I took the xml generated from a vanilla Data manager package copied into Excel and then compared to my modified xml script to confirm the changes are correct.  It requires some trial and error.

Regards,

Eyal