Using data item with crosstab in dimensional model

[rohitsant87]

Hi all,
I am using dimensional model that contain data of two years only as 2005 and 2006. I have created a data item as
IF([Dimension view].[YEAR].[YEAR].[YEAR].[YEAR]=?year?)
THEN([VALUE])
ELSE(0)
using this, if I input year as 2006, it show 0 data but if I input year 2005 then it shows agregated data of 2005+2006. Please help me out in this issue.

[CognosPaul]

Can you share the full form of your crosstab? It looks like you're trying limit the data for only the year selected.

There are a few easier ways of doing this. The easiest would be to put a prompt into the slicer of the query:

Code Select Expand

#prompt('Year','memberuniquename','','','[Dimension view].[YEAR].[YEAR].[YEAR]')#
This will slice all of the measures in your crosstab for the selected year. You can just use the measure instead of your IF statement.

If you want to slice only a single measure you can use the same prompt inside a tuple:

Code Select Expand

tuple(#prompt('Year','memberuniquename','','','[Dimension view].[YEAR].[YEAR].[YEAR]')#,[VALUE]) Place that data item into your crosstab instead of your IF statement.

The prompt macro will automatically prompt the user for a member inside [Dimension view].[YEAR].[YEAR].[YEAR]. (In your example you it looks like you're calling an attribute of the member, the prompt is calling the member directly).

[rohitsant87]

Hi PaulM,
I have product line as rows of crosstab and value/quantity as measure. My requirement is that after I input specific year, I should get data of only that perticular year. For this I am using a data item. but it is not giving the expected results as I stated earlier.

[CognosPaul]

So your crosstab should look like this:



            | Value | Quantity |
------------+-------+----------+
Product Line|  123  |   123    |


Create the crosstab exactly like it looks. The level for Product Line in rows, and the Measures Value and Quantity as peers in the columns.

Your requirement to limit the data is called slicing (when you're working with dimensional data). Go to the query and drag in "Slicer Member Set" into the slicer on the lower right side. The expression editor will open automatically. Paste the prompt macro into it:
#prompt('Year','memberuniquename','','','[Dimension view].[YEAR].[YEAR].[YEAR]')#

This will create a value prompt that will pull data from the level Year. When the user selects the desired member (Year), the member will be placed in the slicer. All of the measures in the crosstab will then be sliced (or filtered by) the year selected.

You can search the forums here for a more indepth discussion on prompt macros.

[rohitsant87]

Thanx a lot PaulM