Error on Previous Month

[joecog]

Hi,

I need to generate a report that show the salesman's sales amount comparison between the user selected month and a month before the selected month.
Eg : User select 2010/Oct
then report will show 2010/Sep and 2010/Oct figure
I had add a column known as PreviousMth with filter as PreviousMth = _add_months(?userSelectionParam?,-1) but system prompt the following error :

RSV_VAL_0044 The member caption can only be used in the scope of crosstab or chart.

Any idea on solving this?

[ksr]

Here we have to consider lot of things...

In these scenarios, we should not apply query level filter, instead we use column filters using case statement.

In your case..

Apply query filter on year as  YEAER=2010

for the user selected month Create a query calculation and enter the following expression..

case
when [month queritem]=?p? then ([measure column])
else null
end

select this calculated column and change aggregate property from automatic to total in the properties pane

for the previous month, Create another query calculation and enter the following expression

case
when [month queritem]=?p?-1 then ([measure column])
else null
end

select this calculated column and change aggregate property from automatic to total in the properties pane

I think this is very help full to you. if you have any doubts reply back...

[joecog]

Hi,
Thanks for the reply.

FYI, currently in the report, I had added CurrentMonth column where I had put the Details filter as [CurrentMonth]=?p1?

I created a new Data Item name as PreviousMth with Expression Definition [Ori].[Period].[Month]

I had created the 2 query calculation as mentioned but I get the following error :


QE-DEF-0459 CCLException
QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[PreviousMth]' that does not exist.

QE-DEF-0459 CCLException
QE-DEF-0030 Expression parsing error.
QE-DEF-0359 The query contains a reference to at least one object '[PreviousMth]' that does not exist.

RSV-VAL-0044 The member caption can only be used in the scope of crosstab or chart.

RSV-VAL-0044 The member caption can only be used in the scope of crosstab or chart.

RSV-VAL-0044 The member caption can only be used in the scope of crosstab or chart.

RSV-VAL-0044 The member caption can only be used in the scope of crosstab or chart.

RSV-VAL-0044 The member caption can only be used in the scope of crosstab or chart.

Is there any steps that I miss out?

[ksr]

let me know which report you are trying to create weather it is cross tab or list first..

[joecog]

Hi,

I'm trying to create a crosstab report.

[ksr]

if it is cross tab, use the following steps..

1) create a detail filter on year to point the report to particular year..
2) drag a query calculation to column of the cross tab and enter the following expression

case
when [month]=?p? then ('current_month')
when [month]=?p?-1 then ('previous_month')
else null
end

drag appropriate measure to the measure area of the cross tab..

[joecog]

Hi,

I had tried on create a new query calculation known as Period in the crosstab and when run the report system prompted error below :

QE-DEF-0478 Invalid coercion from 'memberSet' to 'value' for '[Month]' in 'case when [Month] = ?p1? then 'current_month' when [Month] = ?p1? - 1 then 'previous_month'    else null end'.

Still not working.
Any idea?

[ksr]

replace [month] with your month query item from your package

[joecog]

Hi,

I had replace the [month] with but get the error below:

QE-DEF-0478 Invalid coercion from 'level' to 'value' for '[SalesMarketing].[Period].[Period].[Month]' in 'case when members([SalesMarketing].[Period].[Period].[Month]) = ?p1? then 'current_month' when [SalesMarketing].[Period].[Period].[Month] = ?p1? - 1 then 'previous_month'    else null end'.

[ksr]

r u working on dimensional modal? if so, please expand particular dimension and take a screen shot of that and share with me..

cognosindia@gmail.com

[MFGF]

Hi,

I need to generate a report that show the salesman's sales amount comparison between the user selected month and a month before the selected month.
Eg : User select 2010/Oct
then report will show 2010/Sep and 2010/Oct figure
I had add a column known as PreviousMth with filter as PreviousMth = _add_months(?userSelectionParam?,-1) but system prompt the following error :

RSV_VAL_0044 The member caption can only be used in the scope of crosstab or chart.

Any idea on solving this?

Assuming this is a dimensional package, try the following:

1. Drag a Query Calculation into your crosstab as a column, and call it SelectedMth.  Use the expression

[Month level from your Time Dimension] -> ?userSelectionParam?

2. Drag a second Query Calculation into your crosstab alongside the SelectedMth column, and call it PreviousMth.  Use the expression

lag([Month level from your Time Dimension] -> ?userSelectionParam?,1)

This should hopefully give you what you need (without the need for any query filters or case statements).

Regards,

MF.

[joecog]

Hi,

Finally got it... It's working....
Thanks a lot...

Thanks all for helping...