pass tree prompt parameter values to relational model dataitem

[blom0344]

Any chance that a tree prompt would work passing parameter values to a relational dataitem? As soon as I add a filter to the relational data query that references the tree prompt parameter the tree prompt itself no longer shows value (save the top most level of the hierarchy..

[Arsenal]

is it not that the tree would pass along a set when the filter on the main query is set against a dimensional item? If the filter is set against a relational item, which would expect comma separated values, then I guess Cognos recognizes this fact and so the top most node only is shown.

Sort of like if you have a prompt set as multi select allowed but if the filter itself is set to  = instead of In then Cognos will recognize this and the prompt will not allow multiple selections

[blom0344]

Well, I sort of expected that  ;)

As a Square SQL kinda guy I was hoping to combine my relational model with a tree prompt, which is indeed silly.
The alternative is to use 6 different value prompts to navigate through the coststructure, which is going to create really awkward data queries..

[Arsenal]

you could try using cascading prompts for those 6 prompts. Mimics the tree a little bit but can be annoying with the constant refreshes

another alternative would be go ahead and have the tree prompt but to have a JS loop through this tree and add the selections from the different nodes to a hidden textbox. JS will have to ensure that the values are separated by a comma when being added to the textbox. You'll have to change delete the in built finish button and replace it with a JS button which when clicked will call the function which scans through the tree and populates the text boxes.

your main report query's filter would then be set against the param of the textbox so your relational data item ends up getting the comma separated values it wants

[blom0344]

Hmm, there's a thought worth investigating. Need to read up on some JS then!   ;)

[Ras_MA]

Any chance that a tree prompt would work passing parameter values to a relational dataitem?

Yes, you can get a tree prompt to pass parameter values to a relational data item, it's just a matter of identifying (or controlling) the dimensional metadata being passed and then extracting components that will match values returned by the relational query.

The easiest way is using drill through as it has great controls over which metatadata is passed.

As soon as I add a filter to the relational data query that references the tree prompt parameter the tree prompt itself no longer shows value (save the top most level of the hierarchy..

--> this is because the filter is expecting relational metadata.  The metadata from the treeprompt changes in an effor to be valid for the context of the parameter it is referencing. Be aware that you should not try to use dimensional and relational sources in the one query.

you could try using cascading prompts for those 6 prompts. ...another alternative would be go ahead and have the tree prompt but to have a JS loop ...

--> no need for this level of complexity, stick with outofthe box functionality.  Use the built-in functionality of the tree prompt will pass an array of values combined with controlling metadata and macros for deconstructing at the other end.  I've blogged a good example of this so won't repost here.  Let me know if you'd like me to post the link.

Cheers,
-MA

[blom0344]

Thanks for the reaction. Very much interested in the link!!

I am not trying to use relational and dimensional components in one query, just trying to pass parameter values.  Unfortunately I have also never been able to get it to work using drill throughs, so I am also very interested in your approach on this..

Your last remark would be valid for a total dimensional model. We build our reports against relational models due to the particular database design we have , which suits SQL much better than MDX, but tree prompt is such an elegant tool..

addendum:

I took a look at your blog, especially the item on using a DMR report to drill through to a relational target. That is very interesting especially if it were possible to select multiple member values to pass through. It looks as if that is just step too far   :(

[bonniehsueh]

Hi All,
I am trying to pass multiple values from a tree prompt to a relational source as well. Has anyone found the example Ras_MA created?

--> no need for this level of complexity, stick with outofthe box functionality.  Use the built-in functionality of the tree prompt will pass an array of values combined with controlling metadata and macros for deconstructing at the other end.  I've blogged a good example of this so won't repost here.  Let me know if you'd like me to post the link.

[CognosPaul]

Wow, this was more difficult than I first guessed.

Code Select Expand

#
join(
  ' OR ',
substitute(']A','''',
/* Replace the opening bracket with an apostraphe*/
substitute(' \[','''',
/* Replace middle with = */
  substitute('->:\[PC\]\.\[@MEMBER\]\.'
    , ' = '
    /* Replace start with ref to table */
    ,  substitute(
        '\[sales_and_marketing\]\.\[Products\]\.\[Products\]'
      , '[Namespace].[Table]'
    /*split it into an array*/
      , split(';',join('A;',split(';'
        , promptmany('Products','mun','','','[sales_and_marketing].[Products].[Products]')
      ))+'A')
    )
  )
)
)
)
#

It will convert:

Code Select Expand

[sales_and_marketing].[Products].[Products].[Product line]->:[PC].[@MEMBER].[991],[sales_and_marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[971],[sales_and_marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[968],[sales_and_marketing].[Products].[Products].[Product]->:[PC].[@MEMBER].[96]

to

Code Select Expand

[Namespace].[Table].[Product line] ='991' OR [Namespace].[Table].[Product type]='971' OR [Namespace].[Table].[Product type]='968' OR [Namespace].[Table].[Product]='96'

which will work in a filter.

[vegeveeru]

Trying to pass multiple parameters from Tree prompt(dimensional model) values to relational model data item.
Its working only for single select, when selecting multiple values, no data is coming.
Below is the code used for the calculated data item
#csv(array(join(',',substitute(']','',grep(']',split('||',join('||',grep('all',split('->',join('||',split(')',join('||',grep(']',substitute(';','||',substitute('\[','||',substitute(']\.','||',split('].',promptmany('Parameter1', 'memberuniquename','rootMembers([Secured Dimensions].[PRMPT_DEPARTMENT_TREE_DIM].[Department Tree])', 'EMPTYset(', '', ')'))))))))))))))))))#

and the filter used is
([LEVEL_1_DEPT]) in ([TREEDEPT]) or
([LEVEL_2_DEPT]) in ([TREEDEPT]) or
([LEVEL_3_DEPT]) in ([TREEDEPT]) or
([LEVEL_4_DEPT]) in ([TREEDEPT]) or
([LEVEL_5_DEPT]) in ([TREEDEPT])

([LEVEL_1_DEPT]) 2,3,4,5 ---are from relational model

pls help me if you know how to get this done.

thanks