Hi,
I am trying to call a UDF from my report, But it throws an error saying that
Data source adapter error: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.spr_getservername", or the name is ambiguous. - when processing query: SELECT "V_AR_MODALITY"."MOD_MODALITY_DESC" AS "MODALITYNAME", SUM("V_AR_MTD_YTD"."CHARGEAMOUNT") AS "CHARGEAMOUNT" FROM "dbo"."V_AR_MTD_YTD" "V_AR_MTD_YTD" INNER JOIN ( SELECT "DATE_DIMENSION"."ACTUALDATE", "DATE_DIMENSION"."YEAR", "DATE_DIMENSION"."QUARTER", "DATE_DIMENSION"."MONTH", "DATE_DIMENSION"."WEEK", "DATE_DIMENSION"."DAYOFYEAR", "DATE_DIMENSION"."DAYOFMONTH", "DATE_DIMENSION"."DAYOFWEEK", "DATE_DIMENSION"."DAYOFWEEKNAME", "DATE_DIMENSION"."ISWEEKEND", "DATE_DIMENSION"."CALENDARWEEK", "DATE_DIMENSION"."LEAPYEAR", "DATE_DIMENSION"."MONTHNAME" FROM "dbo"."DATE_DIMENSION" "DATE_DIMENSION" ) "V_AR_POST_DATE_DIMENSION" ON "V_AR_MTD_YTD"."POSTDATE" = CAST("V_AR_POST_DATE_DIMENSION"."ACTUALDATE" as DATETIME) LEFT OUTER JOIN ( SELECT "T"."MOD_SUPPORT_ID", "T"."MOD_LEVEL_CODE_1", "T"."MOD_LEVEL_CODE_2", "T"."MOD_LEVEL_CODE_3", "T"."MOD_LEVEL_CODE_4", "T"."MOD_LEVEL_CODE_5", "T"."MOD_CODE", "T"."MOD_MODALITY_DESC", "T"."MOD_ACTIVE_STAT", "T"."ROW_INSERTED", "T"."ROW_UPDATED", "dbo".spr_getservername("T"."MOD_LEVEL_CODE_1") AS "SERVER", "dbo".spr_getsystemname("T"."MOD_LEVEL_CODE_1","T"."MOD_LEVEL_CODE_2") AS "SYSTEM" FROM "dbo"."MODALITY" "T" ) "V_AR_MODALITY" ON "V_AR_MTD_YTD"."SUPPORTID" = "V_AR_MODALITY"."MOD_SUPPORT_ID" AND "V_AR_MTD_YTD"."MODALITY" = "V_AR_MODALITY"."MOD_CODE" WHERE "V_AR_MTD_YTD"."MASTERID" IS NULL AND "V_AR_MTD_YTD"."MASTERID" IS NULL AND "V_AR_POST_DATE_DIMENSION"."MONTHNAME" = Datename(month,CAST(CURRENT_TIMESTAMP AS DATE)) AND "V_AR_POST_DATE_DIMENSION"."YEAR" = year(CAST(CURRENT_TIMESTAMP AS DATE)) AND ("V_AR_MTD_YTD"."RECORDTYPE" = 'C' OR "V_AR_MTD_YTD"."RECORDTYPE" = 'GC') GROUP BY "V_AR_MODALITY"."MOD_MODALITY_DESC"
Can the experts share your thoughts, Am strugling to solve this. Thanks.
Did you import the UDF within the model you are using? You do not specify the type of SQL used from within Report Studio, but if it does not exist in the metadata , then I would expect such a message..
Is the account that Cognos uses for connecting to the database autorized to access this function in the dbo schema?