Custom Group Element Display

[JRO061601]

I have a custom group with several elements defined. If one of the filter criteria doesn't return any records, that element isn't displayed on the report grid. I'd like for all the custom group elements to display, and if there are no records, display a zero in the metric column. Is there a way to enable this? Thanks.

 

[chael]

any luck with the VLDB settings to enable full outer join and playing with the null value display?

 

[entaroadun]

To my knowledge, you can't code a custom group that way. However, you may be able to get a consolidation to work that way, if you can rewrite your custom group as a consolidation.

 

[JRO061601]

chael...

no luck with vldb settings or metric join types. null values are displayed as zeros.

entaroadun...

thanks, i don't think i can use a consolidation, but i'll give it a second look.

 

[nlim]

J, I created a CG with 2 elements using tutorial project. One element had year=2000 AND year=2004 (ie. no elements), the other element had country USA. When I used it in a report, the empty element showed up as a row with a null metric.

I then went to report>data options and changed the null display to 0. So there are 2 rows, one with 0.

Is that what you need? I'm on 7.5

 

[JRO061601]

nlim,

thanks for the reply. first, that is almost what i need. my custom group elements have valid filter values from the lookup tables, there is simply no rows in the fact table with those values. so the part with 2004 doesn't apply.

i did what you said and it worked in tutorial. i also added the CG 'Age Groups' across the column headers, to more closely replicate what i have. here was the SQL from tutorial:

select sum(a11.[ORDER_AMT]) AS WJXBFS1
from [ORDER_FACT] a11,
[LU_ORDER] a12,
[LU_CUSTOMER] a13,
[LU_DAY] a14
where a11.[ORDER_ID] = a12.[ORDER_ID] and
a12.[CUSTOMER_ID] = a13.[CUSTOMER_ID] and
a11.[ORDER_DATE] = a14.[DAY_DATE]
and (a14.[YEAR_ID] = 2000
and (YEAR(getdate()) - YEAR(a13.[CUST_BIRTHDATE])) < 25)

this returned a null value, which got changed to 0.

here was the SQL from my project:

select a12.LOAD_DATE LOAD_DATE,
count(distinct a11.PRS_LN_ID) WJXBFS1
from PRS_TBL_LN_PAYMENT a11
join PRS_TBL_LOAD_PERIOD a12
on (a11.TIME_ID = a12.TIME_ID)
where (a11.LN_OTS_DAYS_IN_DELINQUENCY >= 120
and a11.LN_OTS_STATUS_ID in (224))
group by a12.LOAD_DATE

this returned no rows. and the reason why, at least from the SQL, is that my query contained an additional criteria in the select clause and group by (LOAD_DATE). if you remove those two, the query would return a null. (going against SQL server, by the way). i don't know from a database perspective why this occurs.

so now if i go back to the tutorial project, and add the attribute 'Day' underneath the column CG and above the metric, the SQL passes contain a group by, and i am able to similarly break the tutorial example and get no value and hence no row in the report grid.

 

[Z3]

Not sure what you do in SQL Server, but in Oracle, you can do this:

select a12.LOAD_DATE LOAD_DATE,
count(distinct a11.PRS_LN_ID)-1 WJXBFS1
from PRS_TBL_LN_PAYMENT a11
join PRS_TBL_LOAD_PERIOD a12
on (a11.TIME_ID = a12.TIME_ID)
where (a11.LN_OTS_DAYS_IN_DELINQUENCY >= 120
and a11.LN_OTS_STATUS_ID in (224)) or ROWNUM=1
group by a12.LOAD_DATE

this will return 1st row of a12 date, and inflate count by 1. So you will need to decode the rownum=1 to some other date.