Checkbox and Dimensional Hierarchy

[gosoccer]

Well, using Dimensional Hierarchy, ::) I started using te CSV file. I created a Datasource that looks like the following
SMALL 20
SMALL 30
SMALL 40
MEDIUM 50
MEDIUM 60
MEDIUM 70
LARGE 80
LARGE 90
LARGE 100
The problem is I need to create the Value Prompt-Checkbox in a way to present the checkbox to the user but show,

SMALL
MEDIUM
LARGE

And once they choose SMALL for example, the filter or Macro can go after the rows from the main Query based on size 20,30, and 40 for Small.

do you know anyway I can tell the Static Selections or the Checkbox created from the CSV file to show the user only one time SMALL but the Query Filter based on 20,30,40?

Anybody can help, I'll appreciate for ever!!

[tjohnson3050]

create a new data item

case when [dataitem] contains 'SMALL' then 'Small'
when [dataitem] contains 'MEDIUM' then 'Medium'
[dataitem] contains 'LARGE' then 'Large'
end

You can use that new data item in the prompt and in your filter. 

When you filter for Small you will get SMALL 20, SMALL 30, SMALL 40.

[gosoccer]

Man,
I am getting this error

Invalid coercion from 'level' to 'string' for '[dsCube-xxx].[Card_Type].[Card_Type]' in 'case when [dsCube-xxx].[Card_Type].[Card_Type] contains '[20]' then 'SMALL' when
[dsCube-XXX].[Card_Type].[Card_Type] contains '[50]' then 'LARGE' end'.

here is the case statement,
case
when [dsCube-XXX].[Card_Type].[Card_Type] contains '[20]' then 'SMALL'
when [dsCube-XXX].[Card_Type].[Card_Type] contains '[50]' then 'LARGE'
end

I really appreciate the help!

[pricter]

The
[dsCube-XXX].[Card_Type].[Card_Type]
is a level
in order to take the string you should use
caption( [dsCube-XXX].[Card_Type].[Card_Type] )

But to be honest I am not sure if a case statement could work with a dimentional model.

Give it a try

[gosoccer]

yes,
Tj and pricter, thx so much.
I changed the items a little bit in my request to clarify things.

caption works and here is where I am now.
Hierarchy: Color_Type
Dimension: Color_Type
Level: Member:
+ TYPEA
------+ Black
------+ Brown
------+ Silver
or TYPEB the same way.

To deal with the prompt selection of TYPEA, TYPEB, TYPEC only showing to the user, here is what I have by creating a data item 1. It works great.

case
when (caption([COLOR_TYPE]) in ('Black','Brown','Silver')) then ('TYPEA')
when (caption([COLOR_TYPE]) in ('Yellow', 'White', 'Blue')) then ('TYPEB')
else 'TYPEC'
end

I used this data item to create the checkbox and it is showing nicely with these selections.
Now, I am stuck with how to establish the filter to go against the query of the Crosstab. The Query - Tabular View shows the following,
COLOR_TYPE Data Item1
--------------------------------------
Black TYPEA
Brown TYPEA
Silver TYPEA
Yellow TYPEB
Blue TYPEB
White TYPEB
Now, all I need to do is to have the code for the filter so the Query Filter can go against this data so the filter would say,

I am using the following Filter based on the Query but I get a prompt asking for Black, Brown, etc, since it has the ?Parameter2?.

[dsCube-XXX].[Card_Type].[Card_Type]
in (?Parameter2?)

Not sure how to handle the filter so it doesn't have a prompt since the Checkbox will be displayed. and what code I can use to pass the different members under TYPEA for example (Black, etc) to the filter.
Thx,

[pricter]

Which parameter have you associated with the prompt?

[gosoccer]

(?Parameter2?) in the filter of the Query which is the same parameter under the checkbox property.

[gosoccer]

If I could get the right code for my filter of the Query, I think I can get this thing done. Here is what I am getting a parsing error.

CASE caption([dsCube-is-rapids-0007-idcardrollup].[Media].[Media].[CRD_TYP_CD])

WHEN 'TYPEA' THEN
caption([dsCube-xxxxx][COLOR_TYPE].[COLOR_TYPE].[COLOR_CD] )in ('Black','Brown',Silver')

WHEN 'TYPEB' THEN
caption([dsCube-xxxx].[COLOR_TYPE].[COLOR_TYPE].[COLOR_CD])in ('White','etc')

ELSE
caption([dsCube-xxxx].[COLOR_TYPE].[COLOR_TYPE].[COLOR_CD])in ('Green','Red')
END

I am getting a parsing error.
Thx in advance for you time.

[pricter]

Lets recap

you create a data item for creating the for the type in the query of the prompt
you create the same data item in query of the crosstab
and you placed the filter
[dsCube-XXX].[Card_Type].[Card_Type]
in (?Parameter2?)
in the crosstab

Right?

[gosoccer]

I created a data item so the prompt/checkbox use it but in the same Query that is related to the Crosstab
'YES'
you create the same data item in query of the crosstab
'NO'
and you placed the filter
' YES, in the same Query of the Crosstab, I created the filter'[dsCube-XXX].[Card_Type].[Card_Type]
in (?Parameter2?)'
in the crosstab
Do I need to separate the Filters between the Query of the Checkbox and the Crosstab?
Thx again

[pricter]

You should place the the data item in the query of the crosstab and apply the filter in this data item.

For example if you call the data item [Color] then in you filter you should put [Color] in ?parameter2?

Since the values of the prompt are TYPE A, TYPE B and TYPE C

[gosoccer]

After I did the [color] in ?Parameter2? in the Crosstab Query, I am getting a very strange error,
Prompt 'Parameter2' has an unknown data type during SQL generation
:o I am looking into it!!

[CognosPaul]

A different possible solutions. Set the data item in the slicer or cube to:

set(#promptmany('colors','token')#)

and set the static choices of the prompt to the muns separated with semicolons.

Remember, on cubes we don't use case statements or detail filters!

[pricter]

@Paul M
Nice approach, only if the TYPE C can be a set of members and it is not all the others.

Remember, on cubes we don't use case statements or detail filters!

Only when we want to suppress a tree prompt  ;)
Nice blog by the way, If I was you I will put in my signature
Keep up the good work

[CognosPaul]

Wow, I missed that bit.

Okay, so here's what we'll do. We'll use a detail filter, but pretend it's not a detail filter for the purpose of this exercise. Because you shouldn't use detail filters, but there are the occasional time that it's the only way to do it.

Create the groups the way you want them inside the query.

TypeA = set(Black,Brown,Silver)
TypeB = set(Yellow,White,Blue)
TypeC = except(colors,set(Black,Brown,Silver,Yellow,White,Blue))  <-- the annoying dynamic one that makes life difficult and annoying.

The use value of the checkbox prompt should be

Code Select Expand

[TypeA],[TypeB],[TypeC]

Now, despite my dire warnings never to use detail filters, create a filter with the expression

Code Select Expand

level in (#promptmany('Group','token')#) 


I've created a similar report based on the sales and marketing cube, with the order method grouped into "telephone" (telephone and fax), "web based" (email and web site) and "others".

Code Select Expand

<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='2010-06-24T18:14:00.360Z']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>

<query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Telephone"><expression>set(
[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[602],[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[601]
)</expression></dataItem><dataItem name="Web Based"><expression>set(
[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[604],[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[605]
)</expression></dataItem><dataItem name="Others"><expression>except([sales_and_marketing].[Order method].[Order method].[Order method type],set(
[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[604],[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[605],[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[602],[sales_and_marketing].[Order method].[Order method].[Order method type]-&gt;:[PC].[@MEMBER].[601]
)
)</expression></dataItem><dataItem name="Revenue"><expression>[sales_and_marketing].[Measures].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem name="Order method type" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Order method].[Order method].[Order method type]</expression></dataItem></selection>
<detailFilters><detailFilter><filterExpression>[Order method type] in (#promptmany('Groups','token','[Others]')#)</filterExpression></detailFilter></detailFilters></query></queries>
<layouts>
<layout>
<reportPages>
<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
<contents>

<selectValue parameter="Groups" selectValueUI="listBox" multiSelect="true" range="false" required="false"><selectOptions><selectOption useValue="[Telephone]"><displayValue>Telephone</displayValue></selectOption><selectOption useValue="[Web Based]"><displayValue>Web Based</displayValue></selectOption><selectOption useValue="[Others]"><displayValue>Others</displayValue></selectOption></selectOptions><defaultSelections><defaultSimpleSelection>[Others]</defaultSimpleSelection></defaultSelections></selectValue><promptButton type="reprompt">
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="bp"/>
</defaultStyles>
</style>
</promptButton><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query2">
<crosstabCorner>
<contents/>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<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="xt"/>
</defaultStyles>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents>
</pageBody>
<pageHeader>
<contents>
<block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
<contents>
<textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
<dataSource>
<staticValue/>
</dataSource>
</textItem>
</contents>
</block>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="ph"/>
</defaultStyles>
<CSS value="padding-bottom:10px"/>
</style>
</pageHeader>
<pageFooter>
<contents>
<table>
<tableRows>
<tableRow>
<tableCells>
<tableCell>
<contents>
<date>
<style>
<dataFormat>
<dateFormat/>
</dataFormat>
</style>
</date>
</contents>
<style>
<CSS value="vertical-align:top;text-align:left;width:25%"/>
</style>
</tableCell>
<tableCell>
<contents>
<pageNumber/>
</contents>
<style>
<CSS value="vertical-align:top;text-align:center;width:50%"/>
</style>
</tableCell>
<tableCell>
<contents>
<time>
<style>
<dataFormat>
<timeFormat/>
</dataFormat>
</style>
</time>
</contents>
<style>
<CSS value="vertical-align:top;text-align:right;width:25%"/>
</style>
</tableCell>
</tableCells>
</tableRow>
</tableRows>
<style>
<defaultStyles>
<defaultStyle refStyle="tb"/>
</defaultStyles>
<CSS value="border-collapse:collapse;width:100%"/>
</style>
</table>
</contents>
<style>
<defaultStyles>
<defaultStyle refStyle="pf"/>
</defaultStyles>
<CSS value="padding-top:10px"/>
</style>
</pageFooter>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2010-06-24T18:14:00.390Z" output="no"/></XMLAttributes></report>

[gosoccer]

Wow,
Great stuff!! Love it!!
Thx so much Paul. Pricter thank you for your time too,
I'm going after this now. :)

[gosoccer]

YES, YES, IT IS WORKING BEAUTIFULLY!! Thx so much!! If there is something I need to provide as a great comment,
please let me know. Paul M, I appreciate the code spec. I put it in my report and certainly did the trick!!
Issue is resolved!!!! Pricter, thanks so much!! Great Site!!