Derived table @prompt

[former_member539471]

Hello,

I am trying to use @prompt in my derived table its not parsing even though it works as a univ filter. So I created a parameter 'new_param(select disticnt rpt_year_month). The object in Universe for rpt_year_month is 'Numeric'

error in derived table:

Conversion failed when converting the varchar value 'answer' to data type int.

Declare @rpt_year_month as int

set @rpt_year_month =@Prompt(new_param)

SELECT

a.group_name, a.primed_status as curr_prim_status, b.Primed_Status as prev_prim_status, a.EHR as curr_EHR, b.EHR as prev_EHR

from (select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where RPT_YEAR_MONTH = @rpt_year_month) a inner join

(select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH

from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE.RPT_YEAR_MONTH = convert(varchar(6), dateadd(MM,-3, convert(datetime, substring(cast(@rpt_year_month as varchar), 5,2) + '/' + '30'

+ '/'+ substring(cast(@rpt_year_month as varchar), 1,4))), 112)) b

on a.Group_Name = b.Group_Name

[former_member4998]

Hi

I am suspecting issue with below WHERE condition. So first check below where condition syntax is working or not ..then you can use in the derive table.

where EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE.RPT_YEAR_MONTH = convert(varchar(6), dateadd(MM,-3, convert(datetime, substring(cast(@rpt_year_month as varchar), 5,2) + '/' + '30'

+ '/'+ substring(cast(@rpt_year_month as varchar), 1,4))), 112)) b

[former_member539471]

The Where clause works ...

I got correct data in SQL server for below...

Declare @rpt_year_month as int

set @rpt_year_month ='201406'

SELECT

a.group_name, a.primed_status as curr_prim_status, b.Primed_Status as prev_prim_status, a.EHR as curr_EHR, b.EHR as prev_EHR

from (select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where RPT_YEAR_MONTH = @rpt_year_month) a inner join

(select practice_id, group_name, primed_status, ehr, RPT_YEAR_MONTH

from EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE

where EMR_REPORTING.dbo.vMH_MEASURE_PRACTICE.RPT_YEAR_MONTH = convert(varchar(6), dateadd(MM,-3, convert(datetime, substring(cast(@rpt_year_month as varchar), 5,2) + '/' + '30'

+ '/'+ substring(cast(@rpt_year_month as varchar), 1,4))), 112)) b

on a.Group_Name = b.Group_Name

[former_member539471]

For some reason @prompt is not working in derived table.It works in Universe filter. Originally i gave @prompt (@Prompt('Enter values for Rpt Year Month:','N','Folder\MeasurePractice\Rpt Year Month',Mono,Constrained) )  with all parameters...but according to SAP note 1888841.

i removed folder/objectname from parameter and used 

@Prompt('Enter values for Rpt Year Month:','N',' ',Mono,Constrained)

I also tried using parameters and used @prompt(new _param).

I am getting conversion error....how do i resolve this and use @prompt in my derived table.Please see screenshot.

[former_member193452]

Have you tried chaning the 'N' value to 'A'

Since it is converting a substring, it may think the value should be alpha?

[former_member539471]

Thanks Jacqueline,

I tried with different data types , but no its not working.