Hi there

I'm trying to come up with generic version of stored procedure to
generate reports for financial application.

The company is divided into 4 hierarchical levels, let's say A,B,C and
D.

So we can have an atomic division like 134,253,384,489 (like an IP
number).

Now, based on user input in presentation tier I'd like to be able to
genarate costs report grouped by some subset of those hierarchical
levels, eg. user clicks on B and C, and gets the report of costs
generated in whole company (all atomic units) grouped by B and C values
of each unit's costs. So, provided I have a table with four columns
designating organization unit (A,B,C,D) and a column with a cost
(COST), the query should be equivalent to:

select B,C, SUM(Cost) from Costs
GROUP BY B, C

What I need is a parameterized version which would take four boolean
parameters (@A,@B,@C,@D) and group results only by columns with its
corresponding parameter being true. So to achieve functionality of
previous example, I would make the following call:

GenericReport(FALSE,TRUE,TRUE,FALSE)


I know the trick allowing parameterized WHERE clause
( WHERE (@A IS NULL OR A=@A)

but I have no idea whether any similar functionality is possible with
GROUP BY. As a last resort, maybe some dirty CASE would be possible
listing all 2^4 = 16 possible values combinations?

I'm using sql server 2k standard ed.

thank you very much for any insight!

HP

PS. On mssql ng I've been suggested to use WITH CUBE clause, but it
seems to be an overkill (listing all group by combinations possible
each time) and from what I've seen it doesn't support SUM() aggregate
anyway.

HP wrote:
You could try something like below. The idea is to map columns that you
don't want to a constant (in this case 'Total'):

with T(A,B,C,D,cost) as (
values ('1','1','1','1',100),
('2','1','1','1',100),
('1','2','1','1',20),
('2','2','1','1',30)
) select
case when 1=1 then A else 'Total' end,
case when 1=1 then B else 'Total' end,
sum(cost)
from T
group by
case when 1=1 then A else 'Total' end,
case when 1=1 then B else 'Total' end

1 2 3
----- ----- -----------
1 1 100
1 2 20
2 1 100
2 2 30

--

with T(A,B,C,D,cost) as (
values ('1','1','1','1',100),
('2','1','1','1',100),
('1','2','1','1',20),
('2','2','1','1',30)
) select
case when 1=1 then A else 'Total' end,
case when 1=0 then B else 'Total' end,
sum(cost)
from T
group by
case when 1=1 then A else 'Total' end,
case when 1=0 then B else 'Total' end

1 2 3
----- ----- -----------
1 Total 120
2 Total 130


I don't know sqlserver but If it does not support SUM in CUBE, what
aggregate functions does it support? I would be surprised if it does
not support SUM, but since I don't know sqlserver I really cant tell.

/Lennart

HP wrote:
CUBE does support the SUM aggregate(). You can filter the results in
the HAVING clause so that only the required subset of results gets
returned.

--
David Portas

Thank you guys!
First of all excuse me the confusion with CUBE and SUM, I've misread
the reference - it doesn't support SUM (distinct ...), but of course
SUM alone is supported.

First solution (with constant column values) seems quite elgant and
efficient so I'll check it in a moment and reply here, but for now I'd
like to ask about WITH CUBE performance when used on big tables.

I guess it would return grouped sub-table for each (of 16 possible)
subsets of those 4 columns. does anyody know about the performance
overhead? would it be lineary bigger than a single GROUP BY
proportionally to the number of combinations (ie. 16 x single query
time), or maybe the aggregate calculations are reused and whole thing
isn't as heavy performance-wise as it looks?
(I'll try to benchmark it, but nonetheless any technical insight into
optimazation technique is always valuable).

thanks again
HP

I think that you can get some hints for your procedure from following
samples.
(I borrowed some idea from Lennart.)
First table expression "Costs" simulates Table Costs and it's data.
Second table expression "ReportParm" simulates procedure parameters.

------------------------- Commands Entered -------------------------
WITH
Costs(A,B,C,D,cost) AS (
VALUES ('1','1','1','1',100)
, ('1','2','1','1', 20)
, ('1','2','2','1', 50)
, ('1','2','2','2', 10)
, ('2','1','1','1',100)
, ('2','2','1','1', 30)
, ('2','2','2','1',150)
, ('2','2','2','2', 70)
, ('2','2','2','3', 80)
, ('2','2','3','1', 90)
)
,ReportParm (Set, @A, @B, @C, @D) AS (
VALUES (1, 'FALSE', 'TRUE', 'TRUE', 'FALSE')
, (2, 'TRUE', 'TRUE', 'FALSE', 'FALSE')
)
SELECT Set
, SUBSTR( CASE WHEN MAX(@A) = 'TRUE' THEN ',A' ELSE '' END
, SUBSTR( CASE WHEN MAX(@A) = 'TRUE' THEN ','||MAX(A) ELSE '' END
, SUM(cost) AS Sum_of_Cost
FROM Costs
, ReportParm
GROUP BY
Set
, CASE WHEN @A = 'TRUE' THEN A ELSE '' END
, CASE WHEN @B = 'TRUE' THEN B ELSE '' END
, CASE WHEN @C = 'TRUE' THEN C ELSE '' END
, CASE WHEN @D = 'TRUE' THEN D ELSE '' END
ORDER BY
Set
, CASE WHEN @A = 'TRUE' THEN A ELSE '' END
, CASE WHEN @B = 'TRUE' THEN B ELSE '' END
, CASE WHEN @C = 'TRUE' THEN C ELSE '' END
, CASE WHEN @D = 'TRUE' THEN D ELSE '' END
;
------------------------------------------------------------------------------

SET COLUMNS COLUMN_VALUE SUM_OF_COST
----------- -------- ------------ -----------
1 B,C 1,1 200
1 B,C 2,1 50
1 B,C 2,2 360
1 B,C 2,3 90
2 A,B 1,1 100
2 A,B 1,2 80
2 A,B 2,1 100
2 A,B 2,2 420

8 record(s) selected.


------------------------- Commands Entered -------------------------
WITH
Costs(A,B,C,D,cost) AS (
VALUES ('1','1','1','1',100)
, ('1','2','1','1', 20)
, ('1','2','2','1', 50)
, ('1','2','2','2', 10)
, ('2','1','1','1',100)
, ('2','2','1','1', 30)
, ('2','2','2','1',150)
, ('2','2','2','2', 70)
, ('2','2','2','3', 80)
, ('2','2','3','1', 90)
)
,ReportParm (Set, @A, @B, @C, @D) AS (
VALUES ('1', 'FALSE', 'TRUE', 'TRUE', 'FALSE')
, ('2', 'TRUE', 'TRUE', 'FALSE', 'FALSE')
, ('3', 'TRUE', 'FALSE', 'TRUE', 'TRUE')
, ('4', 'TRUE', 'FALSE', 'FALSE', 'FALSE')
)
SELECT CASE
WHEN LOCATE(SUBSTR(Columns,1,1), 'ABCD') > 0 THEN
Set
ELSE ''
END AS "Set"
, Columns AS "Columns"
, CAST(Sum_of_Cost AS DEC(5,0)) AS "Sum_of_Cost"
FROM
(SELECT Set
, SUBSTR( CASE WHEN @A = 'TRUE' THEN ', A' ELSE '' END
, CAST(NULL AS DEC(5,0)) AS Sum_of_Cost
FROM ReportParm
UNION ALL
SELECT Set
, SUBSTR( CASE WHEN MAX(@A) = 'TRUE' THEN ', '||MAX(A)
ELSE '' END
,3) AS Columns
, SUM(cost) AS Sum_of_Cost
FROM Costs
, ReportParm
GROUP BY
Set
, CASE WHEN @A = 'TRUE' THEN A ELSE '' END
, CASE WHEN @B = 'TRUE' THEN B ELSE '' END
, CASE WHEN @C = 'TRUE' THEN C ELSE '' END
, CASE WHEN @D = 'TRUE' THEN D ELSE '' END
) R
ORDER BY
Set, LOCATE(SUBSTR(Columns,1,1), 'ABCD1234'), Columns
;
------------------------------------------------------------------------------

Set Columns Sum_of_Cost
--- ------------ -----------
1 B, C -
1, 1 200.
2, 1 50.
2, 2 360.
2, 3 90.
2 A, B -
1, 1 100.
1, 2 80.
2, 1 100.
2, 2 420.
3 A, C, D -
1, 1, 1 120.
1, 2, 1 50.
1, 2, 2 10.
2, 1, 1 130.
2, 2, 1 150.
2, 2, 2 70.
2, 2, 3 80.
2, 3, 1 90.
4 A -
1 180.
2 520.

22 record(s) selected.

I wanted to try to solve the problem with an alternate solution, but I
didn't fully understand it.
Do any divisions in the hierarchy have multiple parents?
Are there always exactly and forever 4 levels?
Are costs associated only with the bottom level or at any/every levels?
Do you want to generate a total cost report for all nodes below a user
specified node?
Does the hierarchy look something like below?
(If not can you change it to your requirements)

division_1
division_11
division_111
division_1111, cost 10
division_1112, cost 20
division_12
division_121
division_1211, cost 30
division_122
division_1221, cost 40