Convery MUN to string

[cognostechie]

I have a report that is working from a package which has a cube as well as 2 query subjects from Framework Manager. The prompt is pointed to a particular level of a dimension so it is getting data in the form of MUN (I presume). There is a query in the report that is pointed to a query subject from Fm so that is character data type.

There is a column in the query that has exactly the same data as in the level of the dimension. So the query subject has a column that has the same data values as in the cube's dimension's level. The purpose is to filter the query based on whct is selected from the prompt. So I tried using the caption command but that doesn't work.

Is there a way to convert MUN to string data type so that it can be used in the filter? 

[blom0344]

Am currently facing the same type of challenge.

First attempt was with a piece of Java code provided by another Cognoise member. (Populating a hidden text box with concatenating string of values; as I need to pass multiple values) Can'get it to work either.

For a single value the following works for me:

1. Populate a list with one dataitem [DI]
as :  members ([Namespace].[Dimension].[Hierarchy]) with a filter on the query like:   [DI1] in ?MemberTree?

2. MemberTree is the parameter associated with the prompt.
3. Define a drillthrough from the list passing :

Property to pass: businesskey
Value:                   DI1
Method:                Pass data item value

This works perfectly for a single value, but I am still looking for a way to pass multiple values

[CognosPaul]

Let me summarize the problems in my own words to see if I understand them.

Problem 1:
User selects a set of members from a value prompt. The prompt returns MUNs, while the relational DB expects to receive string.

Prompt returns, for example:

Code Select Expand

[cube].[dim].[hier].[level]->[PC].[whatever];[cube].[dim].[hier].[level]->[PC].[whatever2];[cube].[dim].[hier].[level]->[PC].[whatever3]

Filter needs to be:

Code Select Expand

[NS].[Table].[Field] in ('whatever','whatever2','whatever3')

Problem 2:
User selects a set of members from a tree prompt. The prompt returns a set of members from any level. Each level has a corresponding field in the relational DB which needs to be filtered by the levels and members selected in the tree prompt.

Prompt returns, for example:

Code Select Expand

[cube].[dim].[hier].[level]->[PC].[whatever];[cube].[dim].[hier].[level]->[PC].[whatever2];[cube].[dim].[hier].[level2]->[PC].[whatever3]

Filter needs to be:

Code Select Expand

[NS].[Table].[Field] in ('whatever','whatever2') and [NS].[Table].[Field2] in ('whatever3') Where field is level and Field2 is level2.

Are these two statements correct?

Number 1 is fairly easy to accomplish. Macro functions can be used to extract and transform the parameter values.

Code Select Expand

#
csv(
   substitute
   (
        ']'
      , ''
      , substitute
      (
           '\[cube\]\.\[dim\]\.\[hier\]\.\[level\]->\[PC\]\.\['
         , ''
         , split
         (
              ';'
            , promptmany('Param1','mun')
         )
      )
   )
)
#

That extracts the set of members from the param splits it into an array, replaces [cube].[dim].[hier].[level]->[PC].[ with nothing, replaces the final ] with nothing, then joins it again into comma separated strings (the csv macro function will automatically wrap each element of the array with single quotes. Notice the \ escaping certain characters in the substitute function. I don't have Cognos open in front of me so you may need to play with that a bit.

The second problem should have a similar solution. I just need to think on it a bit. The problem with problem 2 is that you don't know in advance which levels are being selected, so the macro expression needs to account for all of them. That one will be a bit difficult, so I need to think on it a bit.

[blom0344]

Yep,Yep and Yep.  And I have every bit of trust in your thinking  ;)

[blom0344]

In my particular case the problem 2 issue is just a tad easier since I need to pass values to just 1 dataitem in the relational part which would yield:

Code Select Expand


//
[NS].[Table].[Field] in ('whatever','whatever2','whatever3')\\


I am at a loss though how to pass the array..

[cognostechie]

Blom - Won't that drill thru require a click of the mouse ? or you use Javascript to force the 'click' ?

I tried to implement Paul's solution using substitute function but I don't have the same level of geniosity as Paul so I got 15978 syntax errors  :(  No documentation of that command but I found a bunch of sites showing some solutions using that command, still no luck ! Paul - Can you share some sample report using that command ?

[cognostechie]

Ok. I was able to achive it using a different technique.

I inserted the data from the Query subject into the 'Short Name' peoperty of the level in the cube. It's a Transformer cube. So basically, I am not using the Query subjects anymore and the 'string' data is coming out of the Short Name of the level itself. The Short Name, Long Name etc are data type 'String' while the data in the level is MUN.

[CognosPaul]

Below is a sample report that allows the user to select a list of members from a value prompt. The set of MUNs created are transformed from [cube].[dim].[hier].[level]->:[pc].[@member].[8] to '8', thus permitting the codes to be used on a relational datasource.

This is a Cognos 10.1.1 report xml based on the Sales and Marketing cube.

Code Select Expand

[code]<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2008-07-25T15:28:38.072Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><selectValue parameter="Products" refQuery="Products" required="false" multiSelect="true" selectValueUI="listBox"><useItem refDataItem="Product"/></selectValue><promptButton type="finish">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton><list horizontalPagination="true" name="List1" refQuery="Query1">



<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Products"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Products"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></list><list horizontalPagination="true" name="List2" refQuery="Query2">



<noDataHandler>
<contents>
<block>
<contents>
<textItem>
<dataSource>
<staticValue>No Data Available</staticValue>
</dataSource>
<style>
<CSS value="padding:10px 18px;"/>
</style>
</textItem>
</contents>
</block>
</contents>
</noDataHandler>
<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="ls"/>
</defaultStyles>
</style>
<listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product - Category Code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product - Category Code"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><conditionalRender refVariable="productsNotNull"><renderFor refVariableValue="1"/></conditionalRender></list></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-07-25T15:28:38.133Z" output="no"/></XMLAttributes><queries><query name="Products"><source><model/></source><selection><dataItem name="Product" aggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product]</expression></dataItem></selection></query><query name="Query1"><source><model/></source><selection><dataItem name="Products"><expression>#
sq(
'[NS].[table][tr][td].[Field] in (' +

csv(
substitute(']','',
   substitute(
      '\[sales_and_marketing]\.\[Products]\.\[Products]\.\[Product]-&gt;\:\[PC]\.\[@MEMBER]\.\['
      ,''
      , split(
              ';'
            , promptmany('Products','mun',' ')
         )
)
)
) +')')
#</expression></dataItem></selection></query><query name="Query2"><source><model/></source><selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product]</expression></dataItem><dataItem name="Product - Category Code" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product].[Product - Category Code]</expression></dataItem></selection><detailFilters><detailFilter use="required" postAutoAggregation="false"><filterExpression>[sales_and_marketing].[Products].[Products].[Product].[Product - Category Code] in (#

csv(
substitute(']','',
   substitute(
      '\[sales_and_marketing]\.\[Products]\.\[Products]\.\[Product]-&gt;\:\[PC]\.\[@MEMBER]\.\['
      ,''
      , split(
              ';'
            , promptmany('Products','mun',' ')
         )
)
)
)
#)</filterExpression></detailFilter></detailFilters></query></queries><reportVariables><reportVariable type="boolean" name="productsNotNull">
<reportExpression>ParamDisplayValue('Products') is not null</reportExpression>
<variableValues>http://cognoise.com/community/Themes/default/images/bbc/code.gif
<variableValue value="1"/>
</variableValues>
</reportVariable></reportVariables><reportName>Split MUN prompt to filter</reportName></report>[/td][/tr][/table][/code]

[cognostechie]

Thanks Paul but I could not make it work. I tried taking out the 1st letters '

Code Select Expand

' but it still gives an error.

[CognosPaul]

This forum really really needs a spoiler tag, or something similar to prevent report XMLs from making it so wide.

Can you copy the entire expression? I'm at a loss as to why you'd be getting any syntax errors.

[cognostechie]

First it gave an error because the word 'code' with the sqauer brackets was part of the xml which I removed later. Now
it gives ar error for this part saying text is not allowed. It is reffering to the site URL

<variableValues>http://cognoise.com/community/Themes/default/images/bbc/code.gif
   <variableValue value="1"/>
</variableValues>

This is in the last part of the xml. I tried taking out the URL and left the variable value intact but it doesn't
like that. Can you send the xml to my e-mail. I am not posting it here but you can probably see my e-mail
since you are a moderator. 


I see what it is doing. When you write the word code with the square brackets, it considers it as a quote. So
I am removing the square brackets from the post.

[cognostechie]

Can't even attach the error file. It says 'The attachment directory is not writable'.

The error says -

Text is not allowed in this context according to DTD/Schema
Expecting: variableValue, variableValuaGroup

Line: 7045
Character: 20
Source:

<variableValues>http://cognoise.com/community

[cognostechie]

Posting on this again because wanted to thank Paul for this !!

Paul - It worked perfectly. I changed it to use my package and for some reason I had to wrap
the filter in TRIM function, otherwise it didn't like my category code data. Here is the filter condition -

trim([EMCPOC].[Product Dimension].[Product Dimension].[Dimension_Name].[Dimension_Name - Category Code]) in (#

csv(
substitute(']','',
   substitute(
      '\[EMCPOC]\.\[Product Dimension]\.\[Product Dimension]\.\[Dimension_Name]->\:\[PC]\.\[@MEMBER]\.\['
      ,''
      , split(
              ';'
            , promptmany('Products','mun',' ')
               )
)
)
)
#)

[anandcognos]

Hi

Thanks to both of you for pointing me to right direction. I am dealing with the exactly same thing. I need help in inserting regular expression in code -

(#
csv(
   substitute
   (
        ']'
      , ''
      , substitute
      (
           '\[Denials\]\.\[program\]\.\[program\]\.\[program1\]->:\[TMR\]\.\[program\]\.\[program\]\.\[25-Programs\^'
         , ''
         , split
         (
              ';'
            , promptmany('New_Denial_Prg_Parameter','mun')
         )
      )
   )
)
#
)

Above code works fine, but 25-Programs is a variable(another parameter) (alphanumeric) and how can I insert parameter value  here ?
Looking for guidance.

Thanks,
Anand

[anandcognos]

Thanks, I have solved this. I did not use regular expression, I found a way to get parameter value. Here is my solution, in case someone in future will face same scenario -

[SQL5].[gp_prg] in (#
csv(
   lstrip(substitute
   (
        ']'
      , ''
,substitute
('\^'
,''
, substitute
      (
           '\[Denials\]\.\[program\]\.\[program\]\.\[program1\]->:\[TMR\]\.\[program\]\.\[program\]\.\['
         , ''
         , split
         (
              ';'
            , promptmany('New_Denial_Prg_Parameter','mun')
         )
      )
)
   ),ParamValue ('prg_caption_Parameter'))
)
#
)