Hello community!
The user has a requirement for a report where a specific column needs to be displayed as the row in a report. It is a list of materials (corn, soybean, etc.) that is a vertical column and they would like to use that as the row going across horizontally. They also want this feature to be set as a default so anytime a user opens this report it shows materials as rows.
Please Help. Thanks greatly in advance
MHassan (BOB member since 2013-11-19)
Universe:-
Webi:-
http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html
Mak 1 
(BOB member since 2005-01-06)
If you don’t want it all in one cell, you would also want to look into horizontal tables (a standard table transposed) and crosstab tables.
Lugh (BOB member since 2009-07-16)
Hey Mak!
Thanks for that article, its really helpful!
However, do you think it is possible to have “Sweater Dresses, skirts, evening wear, etc.” as the main rows? So this way, sales revenue can be displayed for each value.
So the rows would look like this:
Lines | Sweater Dresses | Skirts | Evening Wear| …
Thanks for your help!
MHassan (BOB member since 2013-11-19)
Don’t think so.
Yous should look at a database solution to take this further, in the way you have described.
Mak 1 (BOB member since 2005-01-06)
Thanks for your help guys!
MHassan (BOB member since 2013-11-19)
Can be done in the universe using Lua functions, I have an example of this I will post. Alternatively, create a view at the dB and use max (decode), pivot or regex_split_columns_to_rows depending on your dB type…Lua functions are the best option if you are dealing with big data (telco or similar)
Thomas Evans 
(BOB member since 2013-10-17)
Quite interested in seeing this example Thomas.
I would always advise doing this sort of “heavy lifting” in the ETL, however.
Mak 1 
(BOB member since 2005-01-06)
Hi Mak1,
The following done in Netezza 7 (Twin-Fin) - need to have the Lua plugin and do the build in a derived table within the universe. In somecases it’s more feasable to employ this sort of approach, rather than replicate some huge table via ETL - the example below is done on say 40+ million transactions per day.
SELECT DISTINCT SEQ, CALL_ID, DATE_TM, MEASUREMENT_DATA_ID, ECNO, RCSP, CELL_ID
FROM RNC.ETL.MEASUREMENT_DATA as a
INNER JOIN RNC.ETL.MESSAGE_DATA AS b ON b.MESSAGEDATAID = a.MEASUREMENTDATAID
cross join table(inza..nzlua('
local rows={}
function processRow(CALLID, TIMESTAMP,MEASUREMENTDATAID , ECNO1 , ECNO2, ECNO3 ,ECNO4
, EVALUATEDECNO1 ,EVALUATEDECNO2, EVALUATEDECNO3
, RSCP1, RSCP2, RSCP3, RSCP4, EVALUATEDRSCP1, EVALUATEDRSCP2, EVALUATEDRSCP3
, CELLID1, CELLID2, CELLID3, CELLID4, EVALUATEDCELLID1, EVALUATEDCELLID2
, EVALUATEDCELLID3)
rows[1] = {1, CALLID, TIMESTAMP, MEASUREMENTDATAID, ECNO1, RCSP1, CELLID1}
rows[2] = {2, CALLID, TIMESTAMP, MEASUREMENTDATAID, ECNO2, RCSP2, CELLID2}
rows[3] = {3, CALLID, TIMESTAMP, MEASUREMENTDATAID, ECNO3, RCSP3, CELLID3}
rows[4] = {4, CALLID, TIMESTAMP, MEASUREMENTDATAID, ECNO4, RCSP4, CELLID4}
rows[5] = {5, CALLID, TIMESTAMP, MEASUREMENTDATAID, EVALUATEDECNO1, EVALUATEDRSCP1, EVALUATEDCELLID1}
rows[6] = {6, CALLID, TIMESTAMP, MEASUREMENTDATAID, EVALUATEDECNO2, EVALUATEDRSCP2, EVALUATEDCELLID2}
rows[7] = {7, CALLID, TIMESTAMP, MEASUREMENTDATAID, EVALUATEDECNO3, EVALUATEDRSCP3, EVALUATEDCELLID3}
return rows
end
function getShape()
columns={}
columns[1] = { "SEQ", integer }
columns[2] = { "CAll_ID", bigint }
columns[3] = { "DATE_TM", datetime}
columns[4] = { "MEASUREMENT_DATA_ID", bigint }
columns[5] = { "ECNO", integer }
columns[6] = { "RCSP", integer }
columns[7] = { "CELL_ID", bigint }
return columns
end',
b.CALLID, b.TIMESTAMP, a.MEASUREMENTDATAID , a.ECNO1 , a.ECNO2, a.ECNO3 ,a.ECNO4
, a.EVALUATEDECNO1 ,a.EVALUATEDECNO2, a.EVALUATEDECNO3
, a.RSCP1, a.RSCP2, a.RSCP3, a.RSCP4, a.EVALUATEDRSCP1, a.EVALUATEDRSCP2, a.EVALUATEDRSCP3
, b.CELLID1, b.CELLID2, b.CELLID3, b.CELLID4, a.EVALUATEDCELLID1, a.EVALUATEDCELLID2
, a.EVALUATEDCELLID3 ))
WHERE b.CALLID = @Prompt( 'ID' , 'N', , mono, free, persistent )
And also the (little known of) REGEX function I mentioned, also done within Netezza:
SELECT
src.SRC_FILE_NM,
src.GGSN_PGW_CDR_KEY,
src.REC_TYPE,
src.NTWK_INIT,
src.SRVD_IMSI,
src.GGSN_ADDR1,
src.GGSN_ADDR2,
src.GGSN_ADDR3,
src.GGSN_ADDR4,
src.CHRGNG_ID,
src.SGSN_ADDR1,
src.SGSN_ADDR2,
src.SGSN_ADDR3,
src.SGSN_ADDR4,
src.ACCESS_POINT_NM,
src.PDP_TYPE,
src.SRVD_PDP_ADDR1,
src.SRVD_PDP_ADDR2,
src.SRVD_PDP_ADDR3,
src.SRVD_PDP_ADDR4,
src.DYNAMIC_ADDR_FLAG,
src.DATA_VOL_UP,
src.DATA_VOL_DOWN,
src.REC_OPEN_TIME,
src.TOT_DURATION_ALL_RTNG_GRPS,
src.REAS_FOR_CLOSURE,
src.DIAGNOSTICS,
src.REC_SEQ_NUM,
src.NODE_ID,
src.LOCAL_SEQ_NUM,
src.SRVD_MSISDN,
src.CHRGNG_CHARACTERISTICS,
src.SGSN_PLMN_IDENTIFIER,
src.SRVD_PDP_PDN_ADDR5,
src.DIAGNOSTICS_GSM_ERR_VAL,
src.DIAGNOSTICS_ITU_CAUSE,
src.DIAGNOSTICS_ID,
src.DIAGNOSTICS_SIG,
src.DIAGNOSTICS_INFO,
src.DIAGNOSTOCS_MANUFACTURER_SPECIFIC_CAUSE,
src.DIAGNOSTICS_POS_MTHD_FAILR_CAUSE,
src.DIAGNOSTICS_UNATHRZD_CS_CLIENT_CAUSE,
src.APN_SELCTN_MODE,
src.CHCH_SELCTN_MODE,
src.SRVD_IMEI,
src.RAT_TYPE,
src.MS_TM_ZN,
src.USR_LOC_INFO,
DECODE(c1.str,'',Null,c1.str) LST_SVC_DATA_RTNG_GRP,
c2.str CHRGN_RULE_BASE_NM,
DECODE(c3.str,'',Null,c3.str) RESULT_CD,
DECODE(c4.str,'',Null,c4.str) LST_SVC_DATA_LOC_SEQ_NUM,
--CAST(c5.str as TIMESTAMP) DTTM_FRST_USG,
TO_DATE(c5.str,'YYYYMMDDHH24MISS') DTTM_FRST_USG,
TO_TIMESTAMP(c6.str,'YYYYMMDDHH24MISS') DTTM_LAST_USG,
DECODE(c7.str,'',Null,c7.str) TM_USG,
c8.str SVC_COND_CHNG,
DECODE(c9.str,'',Null,c9.str) LST_SVC_DATA_QCI,
DECODE(c10.str,'',Null,c10.str) LST_SVC_DATA_MAX_REQSTD_BNDWDTH_UL,
DECODE(c11.str,'',Null,c11.str) LST_SVC_DATA_MAX_REQSTD_BNDWDTH_DL,
DECODE(c12.str,'',Null,c12.str) LST_SVC_DATA_GUARTD_BITRATE_UL,
DECODE(c13.str,'',Null,c13.str) LST_SVC_DATA_GUARTD_BITRATE_DL,
DECODE(c14.str,'',Null,c14.str) LST_SVC_DATA_ARP,
DECODE(c15.str,'',Null,c15.str) LST_SVC_DATA_APN_AGGR_MAX_BITRATE_UL,
DECODE(c16.str,'',Null,c16.str) LST_SVC_DATA_APN_AGGR_MAX_BITRATE_DL,
c17.str LST_SVC_DATA_ADDR1,
c18.str LST_SVC_DATA_ADDR2,
c19.str LST_SVC_DATA_SGSN_PLMN_IDENTIFIER,
DECODE(c20.str,'',Null,c20.str) DATA_VOL_FBC_UP,
DECODE(c21.str,'',Null,c21.str) DATA_VOL_FBC_DOWN,
TO_DATE(c22.str,'YYYYMMDDHH24MISS') REPORT_DTTM,
DECODE(c23.str,'',Null,c23.str) LST_SVC_DATA_RAT_TYPE,
DECODE(c24.str,'',Null,c24.str) LST_SVC_DATA_FAILR_HNDLNG_CNTNU,
DECODE(c25.str,'',Null,c25.str) LST_SVC_DATA_SVC_ID,
c26.str LST_SVC_DATA_USR_LOC_INFO,
c27.str NUM_EVNTS,
c28.str EVNT_DTTM_TMSTMPS,
DECODE(c29.str,'',Null,c29.str) TM_QUOTA_TYPE,
DECODE(c30.str,'',Null,c30.str) BASE_TM_INTRVL,
src.NODE_ADDR1,
src.SUBS_ID_TYPE,
src.SUBS_ID_DATA,
src.PGW_PLMN_ID,
TO_DATE(src.START_DTTM,'YYYYMMDDHH24MISS') START_DTTM,
TO_DATE(src.STOP_DTTM,'YYYYMMDDHH24MISS') STOP_DTTM,
src.SRVD_3GPP2ME_ID,
src.PDN_CONN_CHRGNG_ID,
src.IMSI_UNAUTHNTCTD_FLG,
src.CSG_ID,
src.CSG_ACCESS_MODE,
src.CSG_MEMBRSHP_IND,
src.THREEGPP_2USR_LOC_INFO,
src.SRVD_PDP_PDN_ADDR_EXT1,
src.SRVD_PDP_PDN_ADDR_EXT2,
src.DYNAMIC_ADDR_FLAG_EXT,
c1.POS CDR_SEQ_NUM
FROM
STAGE..STAGE_GGSN_PGW_CDRS src
CROSS JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_RTNG_GRP,',,,,,,,,,'),',')) split_pivot_col
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_RTNG_GRP,',,,,,,,,,'),',')) c1 on split_pivot_col.POS = c1.POS AND c1.str not in ('')
INNER JOIN TABLE (regexp_split_rows(nvl(src.CHRGN_RULE_BASE_NM,',,,,,,,,,'),',')) c2 on split_pivot_col.POS = c2.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.RESULT_CD,',,,,,,,,,'),',')) c3 on split_pivot_col.POS = c3.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_LOC_SEQ_NUM,',,,,,,,,,'),',')) c4 on split_pivot_col.POS = c4.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.DTTM_FRST_USG,',,,,,,,,,'),',')) c5 on split_pivot_col.POS = c5.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.DTTM_LAST_USG,',,,,,,,,,'),',')) c6 on split_pivot_col.POS = c6.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.TM_USG,',,,,,,,,,'),',')) c7 on split_pivot_col.POS = c7.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.SVC_COND_CHNG,',,,,,,,,,'),',')) c8 on split_pivot_col.POS = c8.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_QCI,',,,,,,,,,'),',')) c9 on split_pivot_col.POS = c9.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_MAX_REQSTD_BNDWDTH_UL,',,,,,,,,,'),',')) c10 on split_pivot_col.POS = c10.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_MAX_REQSTD_BNDWDTH_DL,',,,,,,,,,'),',')) c11 on split_pivot_col.POS = c11.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_GUARTD_BITRATE_UL,',,,,,,,,,'),',')) c12 on split_pivot_col.POS = c12.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_GUARTD_BITRATE_DL,',,,,,,,,,'),',')) c13 on split_pivot_col.POS = c13.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_ARP,',,,,,,,,,'),',')) c14 on split_pivot_col.POS = c14.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_APN_AGGR_MAX_BITRATE_UL,',,,,,,,,,'),',')) c15 on split_pivot_col.POS = c15.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_APN_AGGR_MAX_BITRATE_DL,',,,,,,,,,'),',')) c16 on split_pivot_col.POS = c16.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_ADDR1,',,,,,,,,,'),',')) c17 on split_pivot_col.POS = c17.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_ADDR2,',,,,,,,,,'),',')) c18 on split_pivot_col.POS = c18.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_SGSN_PLMN_IDENTIFIER,',,,,,,,,,'),',')) c19 on split_pivot_col.POS = c19.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.DATA_VOL_FBC_UP,',,,,,,,,,'),',')) c20 on split_pivot_col.POS = c20.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.DATA_VOL_FBC_DOWN,',,,,,,,,,'),',')) c21 on split_pivot_col.POS = c21.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.REPORT_DTTM,',,,,,,,,,'),',')) c22 on split_pivot_col.POS = c22.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_RAT_TYPE,',,,,,,,,,'),',')) c23 on split_pivot_col.POS = c23.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_FAILR_HNDLNG_CNTNU,',,,,,,,,,'),',')) c24 on split_pivot_col.POS = c24.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_SVC_ID,',,,,,,,,,'),',')) c25 on split_pivot_col.POS = c25.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.LST_SVC_DATA_USR_LOC_INFO,',,,,,,,,,'),',')) c26 on split_pivot_col.POS = c26.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.NUM_EVNTS,',,,,,,,,,'),',')) c27 on split_pivot_col.POS = c27.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.EVNT_DTTM_TMSTMPS,',,,,,,,,,'),',')) c28 on split_pivot_col.POS = c28.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.TM_QUOTA_TYPE,',,,,,,,,,'),',')) c29 on split_pivot_col.POS = c29.POS
INNER JOIN TABLE (regexp_split_rows(nvl(src.BASE_TM_INTRVL,',,,,,,,,,'),',')) c30 on split_pivot_col.POS = c30.POS;Mak1 - I see you have some NZSQL experience, so im sure the above would be of use!! Hove you got any experience with ESRI Geospatial plugin?
Thanks,
Thomas
Thomas Evans (BOB member since 2013-10-17)
Hi Thomas,
I worked with Netezza in one of its earliest incarnations.
Just out of interest how does that perform?
The one thing I noticed when working with Netezza was that if good practice was not followed, the platforms performance would degrade over time.
That is why I would always recommend pre-canning this sort of thing, via ETL.
No, I’m afraid not.
Cheers,
Mark.
Mak 1 (BOB member since 2005-01-06)
Oh awesome, it is a great DB appliance!
The Lua function performs exceedingly well, far better than any view to do the operation in a similar manner.
GROOM, GROOM, GROOM - generate stats seems to be the key. I think that performance can be variable, although it’s a massively parallel architecture I have found bottlenecks when you have many users hitting the box - you need to do a little throttling at a group level.
That aside some nasty code I wrote on Oracle took 12 hours to run on 11g and the same code just 15min on Netezza.
Cheers,
Thomas
Thomas Evans (BOB member since 2013-10-17)
Thomas, thanks for sharing this BTW.
I had never heard of this before.
Wow, quite a difference…
Just FYI - A similar query statistic my mate passed me Oracle vs. Hana:-
5.5 minutes Oracle, 0.1 sec on Hana 8) .
Mak 1 (BOB member since 2005-01-06)
No worries, it’s a very cool little known tool for the arsenal. Lua works on most of the major databases, although I haven’t actually heard of anyone else using them in this manner, especially with Business Objects anyway.
Worth a look:
http://www.keplerproject.org/luasql/2.0/
Cheers,
Thomas
Thomas Evans (BOB member since 2013-10-17)