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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Custom Group Element Display
- Thread starter JRO061601
- Start date
entaroadun
Programmer
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.
- Thread starter
- #4
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
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
- Thread starter
- #6
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.
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
Programmer
- Jan 14, 2003
- 143
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.
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.
- Status
Similar threads
- Locked
- Question
- Locked
- Question