Limit dimension list in DTSX package

[former_member591760]

I was trying to modify an existing package file so the user would only be prompted for the dimensions relevant to the script.  The original package had the following line which lists all of the dimension members in the model when the package is run.

PROMPT(SELECT,,,"Message",%DIMS%)

I would normally change it to something like below but I can't get the right dimensions to appear.  It only seems to list the time dimension or any of the secure dimensions and ignores every other dimension I've tried.  Is that how it works by design or do I have a syntax issue?

PROMPT(SELECT,,,"Message",%DIM1_DIM%%DIM2_DIM%%DIM3_DIM%)

Regards

[Former Member · Accepted solution]

Hi Pablo,

if you want to ask category, entity and time in a package this is the right sintax

PROMPT(SELECTINPUT,,,"Please select category, entity and time",%CATEGORY_DIM%%ENTITY_DIM%%TIME_DIM%)

Please don't forget to close also old threads that are in between solved, if there isn't the right answer you can add a message that explain the solution and mark this as "correct answer"

Regards

     Roberto

[former_member591760 · Accepted solution]

Hi Robert,

Thanks for the response, but those are the only ones that work and I need a prompt for a different dimension.  I cannot list any of the others without using the "%DIMS%" option which in turn displays all nine dimensions in the model.  Is it possible to prompt for a non-secure dimension without having to prompt for all dimensions?  I need the user to make a selection for time, category, entity and one other dimension and would like for those to be the only options they see.

Regards

[Former Member · Accepted solution]

Hi Pablo,

the only different possibilty for non secured dimension is to add a second prompt (after previous for cat,ent,tim) like this

PROMPT(TEXT,%MYDIM1%,"Select the DIM1:",,"")

where the user can enter the member value (the user must lnow the value, no selection) and you can use %MYDIM% variable in the rest of the script.

Regards

     Roberto

[former_member591760 · Accepted solution]

OK, thanks for the reply Roberto.  I will make note of this alternative solution for future use but I'm not sure I want to use a free form prompt in this situation.  Do you know what happens in the event the users misspells a member or provides an incorrect value?  Would the logic fail altogether or just in those cases?

Thanks

[Former Member · Accepted solution]

Hi Pablo,

if the user put an inexistent member value the scripts or stored procedures that use this parameter will extract nothing and maybe you will receive an "empty file" error.

If other dtsx tasks are connected their behaviour will depend on the conditions you have sets between the tasks in BIDS.

Regards

     Roberto

[JohnL · Accepted solution]

Hi Pablo, what version of BPC are you on ? 7.X or 10.X ?

Thanks

[former_member591760 · Accepted solution]

Hi John,

We are running MS 10.0.

Thanks

[JohnL · Accepted solution]

Hi Pablo,

Try this:

PROMPT(SELECTINPUT,,,,"%CATEGORY_DIM%,%ENTITY_DIM%,%TIME_DIM%,AuditTrail”")

Make sure the quotation marks are not the curly type  i.e. “ ” but rather neutral (vertical) i.e. "

This worked for me so you should be able to get it working as well.