Hi!
We need a one dimention portlet which use a lookup query attribute as dimention. The lookup query attribute name is "osd_tipo_req".
Clarity accepts the query in Query Properties page (NSQL), but doesn't show the portlet in the portlet page. Instead of visualizing the portlet results the word "ERROR" appears (image attached).
We replaced the lookup query attribute with a normal attribute and we check the portlet query it's ok and the problem is the type of attribute (lookup query).
So we would like to know if there is a way to use a lookup query attribute as a dimention in a portlet.
Can anyone help us?
The looup query attribute is:
select
(CASE @WHERE:PARAM:USER_DEF:INTEGER:osde_codigo_req@
WHEN 11 THEN 'Bronce'
WHEN 12 THEN 'Bronce'
WHEN 13 THEN 'Plata'
WHEN 14 THEN 'Plata'
WHEN 15 THEN 'Oro'
end) value
FROM odf_ca_inv
WHERE id = @WHERE:PARAM:USER_DEF:INTEGER:ID@
The portlet query is:
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:TIPI:tipificacion:Tipificacion@,
@SELECT:METRIC:USER_DEF:IMPLIED:cantidad:Cantidad@
From (
Select
ca.osd_tipo_req as tipificacion,
COUNT(*) as cantidad
from
niku.INV_INVESTMENTS as inv
inner join niku.ODF_CA_INV as ca on inv.ID=ca.ID
inner join INV_PROJECTS as proj on proj.PRID=inv.ID
where
inv.IS_ACTIVE=1
and proj.is_template='0'
and osd_plan_semestral=(select plan_admin.osd_plan_actual from niku.ODF_CA_OSD_PLAN_ADMIN plan_admin where plan_admin.id=5000000)
group by ca.osd_tipo_req
) T
WHERE @filter@