Hi Folks
I'm trying to update a current birt report by adding a new column called Terminals. I did modify my existing stored procedure in SQL Server 2005 by adding this column called Ternimals and i tested the stored procedure in SQL Server 2005 it works fine and display the Terminals column, but when i use the same stored procedure in BIRT 2.5.1 the Terminals column doesn't display on the output columns. PLEASE HELP IS URGENT!!!. You can have a look on my stored procedure below: My Terminals column in the sub-query.
CREATE PROCEDURE [dbo].count_terminals
@from_date DATETIME = null,
@to_date DATETIME = null
AS
SET NOCOUNT ON
IF (
@from_date IS null OR
@to_date IS null OR
@from_date >
@to_date)
BEGIN
select '1900/01/01' as 'date',
null as 'cpfs_val', null as 'cpfs_qty',
null as 'dpfs_val', null as 'dpfs_qty',
null as 'dbfs_qty',
null as 'crfs_val', null as 'crfs_qty'
END
ELSE
BEGIN
SELECT
-- savings refund success
SUM(CASE WHEN pos.code = 'CPFS' THEN pos.value ELSE 0 END) AS "cpfs_val",
SUM(CASE WHEN pos.code = 'CPFS' THEN pos.Quantity ELSE 0 END) AS "cpfs_qty",
SUM(CASE WHEN pos.code = 'DPFS' THEN pos.value ELSE 0 END) AS "dpfs_val",
SUM(CASE WHEN pos.code = 'DPFS' THEN pos.Quantity ELSE 0 END) AS "dpfs_qty",
SUM(CASE WHEN pos.code = 'DBFS' THEN pos.Quantity ELSE 0 END) AS "dbfs_qty",
SUM(CASE WHEN pos.code = 'CRFS' THEN pos.value ELSE 0 END) AS "crfs_val",
SUM(CASE WHEN pos.code = 'CRFS' THEN pos.Quantity ELSE 0 END) AS "crfs_qty",
(SELECT COUNT(t.ID)
FROM ta_term_cfg ttc INNER JOIN term t
ON (ttc.id = t.id AND t.term_active = 1)
WHERE ttc.card_acceptor = tca.card_acceptor
) AS [Terminals]
FROM np_mis_pos pos,ta_card_acceptor tca
WHERE pos.[date] >=
@from_date
AND pos.[date] < DATEADD(dd,1,
@to_date)
AND pos.card_acceptor NOT IN ('000000000718544', '000000001109834')
GROUP BY pos.[date],tca.card_acceptor
ORDER BY pos.[date] desc
END
