Assigning a Query value to a field in dimensional reporting

[Manu0521]

Hi I am new to dimensional reporting and facing an issue.

I am using two prompts. One for say country and other for state and showing the sales value based on the selection. I wanted to add an 'All' value in the country prompt so I added in  static choice of country prompt and removed the cascading property with the state prompt and wrote an expression on the second state prompt as (if (?prmCountry? = 'ALL') then (1=1) Else ([YYYYY].[Country]=?prmCountry?))

Now both of my prompts works fine for all and other selections.Now I have to set this filter value to my main query.
1) For Country - (Case WHEN ?prmCountry? ='ALL' Then (1=1) Else ( [Country]=?prmDistrib?) END)
2) For State - I should use the value from the query that i used for my state prompt
[State]=[qrySalesRep].[State] - but this gives me an error  -XQE-PLN-0020 The V5 Query could not be planned by the query service. The 'V5ComparisonExpression' node is invalid for the OLAP Transformation Library and the Query Service planner is currently not able to resolve it.

Please let me know how to handle this .

Thanks,

[CognosPaul]

When working with a dimensional source, you automatically have an "All" option you can use.

To begin, I see that you're using a filter. That's not the best practice, and some people* on this forum may start lecturing you for that. Are country and state coming from the same hierarchy? Are they included in the report, or are you only filtering by them?

In the prompt, instead of adding a static choice with 'All', set the static choice to the member unique name from the all level. That way you can use the macro prompt: #prompt('prmCountry','memberuniquename')#

If you're not showing the country in the report, then put that macro into the slicer. If you are showing the country, just use that in the data item. The nice thing about dimensional reporting is you can directly call whichever member you want.

The next sticking point is the state. If that's coming from the same hierarchy, you won't be able to have two slicers. In that case, you can have Cognos first attempt to slice by the state, and if that's empty, slice by the country:

#prompt(
  'prmState'
  , 'mun'
  , prompt('prmCountry','memberuniquename')
)#

In that macro, we're prompting for the State member, and if it doesn't find it, it defaults to the country member. We can be sure that this works since no state can exist in two countries.

*Not me, I would never ever dream of lecturing people.

[Manu0521]

Hi ,

I have the country and state in the same hierarchy.

To make it clear.

1) I will remove all my filters
2) Add two prompts , one for country and one for state with values country and state respectively.
3) Add the MUN Name for all for the country in the static choices. what should be the display value here.
I was able to take the mun value for each item in the country , do I need to add an all after the member for all values ? ---- [Cubename].[Dimensionname].[Hierarchyname].[Levelname]->:[TM].[DimensionName].[Hiereachyname].[@MEMBER].[ALL]
The macro prompt should go in to the slicer of the main query or the country prompt query? ('promptname',mun);

And my state is coming from same hierarchy,
#prompt(
  'prmState'
  , 'mun'
  , prompt('prmCountry','memberuniquename')
)#
where should i use my above code..in slicer of the main query?
Thanks,
Manu

[bdbits]

*Not me, I would never ever dream of lecturing people.

HAHAHAHAHA... actually I love your illuminating posts. I already have that first link saved. Never stop lecturing people, including me.  8)

[CognosPaul]

I'll never stop lecturing! I am a know-it-all busy-body after all (although a bit on the busy side, which is why I haven't been responding as much lately).

Manu, since you need to slice your query from a single hierarchy, I would actually recommend using a tree prompt. This way you avoid the refresh needed for a cascade, and you naturally have the "all" member in place.

By using a single tree prompt, you are then using a single parameter and your slicer would be
#prompt('location','mun')#

If you do need the two separate prompt controls, you could put whatever you want into the display name. The state query feeding the prompt control would then look like: descendants(#prompt('prmCountry','mun')#,[Cube].[Dim].[Hier].[State Level])

Again, if you're not using the location in the crosstab, you would place it in the slicer.

Consider the following (simplified) mdx:

select
  cube.dim.time.years.members on 0
  , cube.dim.location.countries.members on 1
from cube
  where (measures.revenue
  , cube.dim.location.countries.&canada)

That would create a crosstab of all the years in the columns, all the countries in the rows, showing revenue in the intersections, but slicing on Canada. In this case it's an inefficient query; it would be better to do:
select
  cube.dim.time.years.members on 0
  , cube.dim.location.countries.&canada on 1
from cube
  where measures.revenue