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!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

grouping and rollup

Status
Not open for further replies.
cristina10

cristina10

Programmer
Dec 21, 2006
9
0
0
FR
I make a rapport but it takes a lot of time and I make a other sql and it take less time
The old sql is
(
SELECT
INFOBLEU.ACTIVI.BANQ_BCF,
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
INFOBLEU.ACTIVI.INTER_INTRA,
sum(INFOBLEU.ACTIVI.MNT_EURO),
sum(INFOBLEU.ACTIVI.NBR_TC),
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
FROM
INFOBLEU.ACTIVI,
INFOBLEU.TYPE_CARTE
WHERE
( INFOBLEU.TYPE_CARTE.IDF_TYPE_CARTE=INFOBLEU.ACTIVI.IDF_TYPE_CARTE
AND INFOBLEU.TYPE_CARTE.IDF_RESEAU in ('1','2') )
AND (
INFOBLEU.ACTIVI.DAT_TRAITEMENT BETWEEN @variable('Date de début?') AND @variable('Date de fin?')
AND INFOBLEU.ACTIVI.SENS_ACTIVITE = @variable('Sens (I/O) ?')
AND INFOBLEU.ACTIVI.USAGE = '1'
AND INFOBLEU.ACTIVI.COD_TRANSACTION IN ('05', '06', '07', '15', '16', '17', '25', '26', '27', '35', '36', '37')
)
GROUP BY
INFOBLEU.ACTIVI.BANQ_BCF,
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
INFOBLEU.ACTIVI.INTER_INTRA,
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
UNION
(
SELECT
INFOBLEU.ACTIVI.BANQ_BCF,
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
'TR',
sum(INFOBLEU.ACTIVI.MNT_EURO),
sum(INFOBLEU.ACTIVI.NBR_TC),
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
FROM
INFOBLEU.ACTIVI,
INFOBLEU.TYPE_CARTE
WHERE
( INFOBLEU.TYPE_CARTE.IDF_TYPE_CARTE=INFOBLEU.ACTIVI.IDF_TYPE_CARTE
AND INFOBLEU.TYPE_CARTE.IDF_RESEAU in ('1','2') )
AND (
INFOBLEU.ACTIVI.DAT_TRAITEMENT BETWEEN @variable('Date de début?') AND @variable('Date de fin?')
AND INFOBLEU.ACTIVI.SENS_ACTIVITE = @variable('Sens (I/O) ?')
AND INFOBLEU.ACTIVI.USAGE = '1'
AND INFOBLEU.ACTIVI.COD_TRANSACTION IN ('05', '06', '07', '15', '16', '17', '25', '26', '27', '35', '36', '37')
)
GROUP BY
INFOBLEU.ACTIVI.BANQ_BCF,
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
'TR',
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
UNION
(
SELECT
'TB',
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
INFOBLEU.ACTIVI.INTER_INTRA,
sum(INFOBLEU.ACTIVI.MNT_EURO),
sum(INFOBLEU.ACTIVI.NBR_TC),
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
FROM
INFOBLEU.ACTIVI,
INFOBLEU.TYPE_CARTE
WHERE
( INFOBLEU.TYPE_CARTE.IDF_TYPE_CARTE=INFOBLEU.ACTIVI.IDF_TYPE_CARTE
AND INFOBLEU.TYPE_CARTE.IDF_RESEAU in ('1','2') )
AND (
INFOBLEU.ACTIVI.DAT_TRAITEMENT BETWEEN @variable('Date de début?') AND @variable('Date de fin?')
AND INFOBLEU.ACTIVI.USAGE = '1'
AND INFOBLEU.ACTIVI.SENS_ACTIVITE = @variable('Sens (I/O) ?')
AND INFOBLEU.ACTIVI.COD_TRANSACTION IN ('05', '06', '07', '15', '16', '17', '25', '26', '27', '35', '36', '37')
)
GROUP BY
'TB',
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
INFOBLEU.ACTIVI.INTER_INTRA,
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
UNION
SELECT
'TB',
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
'TR',
sum(INFOBLEU.ACTIVI.MNT_EURO),
sum(INFOBLEU.ACTIVI.NBR_TC),
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
FROM
INFOBLEU.ACTIVI,
INFOBLEU.TYPE_CARTE
WHERE
( INFOBLEU.TYPE_CARTE.IDF_TYPE_CARTE=INFOBLEU.ACTIVI.IDF_TYPE_CARTE
AND INFOBLEU.TYPE_CARTE.IDF_RESEAU in ('1','2') )
AND (
INFOBLEU.ACTIVI.COD_TRANSACTION IN ('05', '06', '07', '15', '16', '17', '25', '26', '27', '35', '36', '37')
AND INFOBLEU.ACTIVI.DAT_TRAITEMENT BETWEEN @variable('Date de début?') AND @variable('Date de fin?')
AND INFOBLEU.ACTIVI.SENS_ACTIVITE = @variable('Sens (I/O) ?')
AND INFOBLEU.ACTIVI.USAGE = '1'
)
GROUP BY
'TB',
INFOBLEU.ACTIVI.COD_MCC,
INFOBLEU.ACTIVI.IDF_TYPE_CARTE,
INFOBLEU.ACTIVI.SENS_ACTIVITE,
INFOBLEU.ACTIVI.USAGE,
INFOBLEU.ACTIVI.USAGE_ARDEF,
'TR',
INFOBLEU.TYPE_CARTE.LIB_TYPE_CARTE
)
)
)
and the new sql is
SELECT DECODE(GROUPING(infobleu.activi_m.banq_bcf),1,'TTEBQE', infobleu.activi_m.banq_bcf) banq_bcf,
infobleu.activi_m.cod_mcc,
infobleu.activi_m.idf_type_carte, infobleu.activi_m.sens_activite,
infobleu.activi_m.usage, infobleu.activi_m.usage_ardef,
DECODE(GROUPING(infobleu.activi_m.inter_intra),1,'TTEREGION', infobleu.activi_m.inter_intra) inter_intra,
sum(infobleu.activi_m.mnt_euro), sum(
infobleu.activi_m.nbr_tc), infobleu.type_carte.lib_type_carte
FROM infobleu.activi_m, infobleu.type_carte
WHERE infobleu.type_carte.idf_type_carte = infobleu.activi_m.idf_type_carte
AND infobleu.type_carte.idf_reseau IN ('1', '2')
AND infobleu.activi_m.mois_traitement BETWEEN '01/10/2006' AND
'01/12/2006'
AND infobleu.activi_m.sens_activite = 'I'
AND infobleu.activi_m.usage = '1'
AND infobleu.activi_m.cod_transaction IN ('05', '06', '07', '15', '16',
'17', '25', '26', '27', '35', '36', '37')
GROUP BY rollup(infobleu.activi_m.banq_bcf),
infobleu.activi_m.cod_mcc,
infobleu.activi_m.idf_type_carte, infobleu.activi_m.sens_activite,
infobleu.activi_m.usage, infobleu.activi_m.usage_ardef,
rollup(infobleu.activi_m.inter_intra), infobleu.type_carte.lib_type_carte

But I don't now how I can make the grouping and rollup in BO
Help me please
Thanks
 
Sort by date Sort by votes
Grouping and rollup are taken care of by BO. Do not work this into the dataproviders SQL, since this is not the way to go.

Grouping can be set either by section or breaks and BO will then automatically 'roll-up' measures against the dimensions.
Subtotals are automatically generated in blocks and sections.
You can even play with calculations in special contexts (something impossible with pure SQL)

SQL based grouping and rollup are meant for structuring the outcome of SQL datasets 'as is'. Do not try to use them with a reporting tool like BO. There is simply no need..

Ties Blom

 
Upvote 0 Downvote
I don't agree. As much should be done in the universe as possible. Buckets OR groupings should be done in universe (and thus the SQL).

Since you know the code, just build objects that use that code. Build a grouping object that has your Decode or Case statement in it.

Steve Krandel
Symantec
 
Upvote 0 Downvote
Yes, grouping should be done as much as possible at the universe level. But using rollup techniques will add records to the dataset returned!
Totally useless if you have an advanced reporting tool that perform rollups automatically and automatically calculates subtotals from the data set.
Rollup introduces a structure within the dataset (i.e. it is semi-reporting with SQL itself)..

Ties Blom

 
Upvote 0 Downvote
Fair enough. I guess I'm not sure what rollup techniques are.

Steve Krandel
Symantec
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

lahddah
  • Locked
  • Question
Header and Details showing horizontally
Replies
7
Views
101
SkipVought
S
PWD
  • Locked
  • Question
Detail Variable Syntax - If A = "n" And B = "z" "True" 1
Replies
3
Views
66
PWD
PWD
PWD
  • Locked
  • Question
Reporting (Match [WAD] (*a* or *b*) or [WAD] = c AND Match [WAD] (*d* or *e*) or [WAD = f) Then &quo 1
Replies
5
Views
44
PWD
PWD
boboleft
  • Locked
  • Question
BO grouping question
Replies
3
Views
14
hilfy
hilfy
m3gd04
  • Locked
  • Question
Converting integer to separate fields for hours and minutes 1
Replies
4
Views
65
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top