Retrive member using _businessKey/attribute value

[JulioSarmiento]

Goal:
The user is to select a date from a Date Prompt and a Crosstab report will display the data for the date selected.  The Crosstab will also show a Year to Date calculation using the PeriodsToDate function - which should be from Jan 1, 2007 until the date selected by the user.

Process:
When creating the Date Prompt and the parameterized filter to the Day level, I receive an error stating that the Date Prompt is not compatible with this type of query item.  The Date Prompt works only when I attach it to the DayId attribute.  Assigning the Date Prompt to the DayId attribute of the Day level returns the desired daily results.

Main Issue:
The problem happens when I try to use PeriodsToDate function.  This function requires that the second property be a type 'Member'.  Thus my question:  If I have the attribute/_businessKey of a level, how can I use it to retrieve the member?

If anyone has a solution or a better method to produce the desired goal, I would truly appreciate the help.

Thank you in advance,
Julio

[MFGF]

Hi,

Try the following approach:

Delete the filter you have created and instead add a query calculation to your crosstab with the syntax:

periodsToDate([your year level], [your day level] -> ?param?)

This will probably generate a value prompt by default, but you could then try adding your own date prompt to fulfil the ?param? parameter value.

Failing this, you could always try using the currentMember() function to isolate your selected member from the filter - ie

periodsToDate([your year level], currentMember([your time hierarchy]))

Regards,

MF.

[JulioSarmiento]

Thanks for you response.

The original attempt at this report was created just as you suggested first - by creating a query calculation in the columns section of the Crosstab.

The second option you wrote about is returning blank values.  As a matter of fact, the column itself doesn't even appear on the report.  I am looking onto this further but if you or anyone has other suggestions, they are welcomed.

Thanks again,
Julio

[JulioSarmiento]

As per Cognos;

"The Date Prompts are not compatible with cube data and this is why you are getting the error seen when using the date prompt"

I was adviced to use a tree prompt.  Thanks for you assistance.