I have developed a crystal report which has the following command.
It runs fine in crystal reports but not enterprise web viewer. Im looking for an alternate solution to achieve the same result or if possible allow the webviewer to work.
In simple terms, it askes the user for the date parameter prior to running the query. (This is important)
it then pulls out the sum of some values, the opening values and closing values.
DECLARE @EndDate AS DATETIME
SET @BeginDate={?BeginDate};
SET @EndDate={?EndDate};
-- sum values
WITH CTE0 AS (
SELECT comptype.comptype,
comptype.compdesc,
'' EFFECTIVEDTTM, '' ADDDTTM, 1 row_num,
0 CURRENTCOSTPS,
0 INITCOSTCP,
0 CAPTCOSTCP,
Sum(assvalmovement.disposalscd) DISPOSALSCD,
Sum(assvalmovement.impvalpriortorevalpe) IMPVALPRIORTOREVALPE,
Sum(assvalmovement.revalincdec) REVALINCDEC,
0 CURRENTCOSTPE,
0 ACCUMDEPRECPS,
Sum(assvalmovement.depreccp) DEPRECCP,
Sum(assvalmovement.dispdeprecwrtnbckcp) DISPDEPRECWRTNBCKCP,
Sum(assvalmovement.revalaccumdeprecwrtnbckgrosscp) REVALACCUMDEPRECWRTNBCKGROSSCP,
Sum(assvalmovement.revalaccumdeprecwrtnbcknetcp) REVALACCUMDEPRECWRTNBCKNETCP,
Sum(assvalmovement.revalaccumdeprecbghttoacctcp) REVALACCUMDEPRECBGHTTOACCTCP,
0 ACCUMDEPRECPE,
0 WDVPS,
0 WDVPE,
Sum(assvalmovement.procondispcp) PROCONDISPCP,
Sum(assvalmovement.plonsalecp) PLONSALECP,
assvalmovement.assvalkey,
assvalue.compkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
FROM assvalue ASSVALUE
inner join comp on assvalue.compkey = comp.compkey
inner join comptype on assvalue.comptype = comptype.comptype
inner join assvalmovement ASSVALMOVEMENT on assvalmovement.assvalkey = assvalue.assvalkey
WHERE
comp.COMPTYPE >1
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)>= CONVERT(DATETIME,@BeginDate,101)
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)<= CONVERT(DATETIME,@EndDate,101)
GROUP BY
comptype.comptype,
assvalue.compkey,
comptype.compdesc,
assvalmovement.assvalkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
),
CTE1
AS (
SELECT comptype.comptype,
comptype.compdesc,
--ASSVALMOVEMENT.EFFECTIVEDTTM, ASSVALMOVEMENT.ADDDTTM,
'' EFFECTIVEDTTM, '' ADDDTTM,
ROW_NUMBER() OVER (
PARTITION BY comptype.comptype
ORDER BY ASSVALMOVEMENT.EFFECTIVEDTTM ASC,
assvalmovement.assvalkey,
assvalue.compkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
) row_num,
assvalmovement.currentcostps CURRENTCOSTPS,
assvalmovement.initcostcp INITCOSTCP,
assvalmovement.captcostcp CAPTCOSTCP,
0 DISPOSALSCD,
0 IMPVALPRIORTOREVALPE,
0 REVALINCDEC,
--assvalmovement.currentcostpe CURRENTCOSTPE,
0 CURRENTCOSTPE,
assvalmovement.accumdeprecps ACCUMDEPRECPS,
0 DEPRECCP,
0 DISPDEPRECWRTNBCKCP,
0 REVALACCUMDEPRECWRTNBCKGROSSCP,
0 REVALACCUMDEPRECWRTNBCKNETCP,
0 REVALACCUMDEPRECBGHTTOACCTCP,
--assvalue.accdeprec ACCUMDEPRECPE,
0 ACCUMDEPRECPE,
assvalmovement.wdvps WDVPS,
--assvalmovement.wdvpe WDVPE,
0 WDVPE,
0 PROCONDISPCP,
0 PLONSALECP,
assvalmovement.assvalkey,
assvalue.compkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
FROM assvalue ASSVALUE
inner join comp on assvalue.compkey = comp.compkey
inner join comptype on assvalue.comptype = comptype.comptype
inner join assvalmovement ASSVALMOVEMENT on assvalmovement.assvalkey = assvalue.assvalkey
WHERE
comp.COMPTYPE >1
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)>= CONVERT(DATETIME,@BeginDate,101)
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)<= CONVERT(DATETIME,@EndDate,101)
),
CTE2
AS (
SELECT comptype.comptype,
comptype.compdesc,
--ASSVALMOVEMENT.EFFECTIVEDTTM, ASSVALMOVEMENT.ADDDTTM,
'' EFFECTIVEDTTM, '' ADDDTTM,
ROW_NUMBER() OVER (
PARTITION BY comptype.comptype
ORDER BY ASSVALMOVEMENT.EFFECTIVEDTTM DESC,
assvalmovement.assvalkey,
assvalue.compkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
) row_num,
0 CURRENTCOSTPS,
0 INITCOSTCP,
0 CAPTCOSTCP,
0 DISPOSALSCD,
0 IMPVALPRIORTOREVALPE,
0 REVALINCDEC,
assvalmovement.currentcostpe CURRENTCOSTPE,
0 ACCUMDEPRECPS,
0 DEPRECCP,
0 DISPDEPRECWRTNBCKCP,
0 REVALACCUMDEPRECWRTNBCKGROSSCP,
0 REVALACCUMDEPRECWRTNBCKNETCP,
0 REVALACCUMDEPRECBGHTTOACCTCP,
assvalue.accdeprec ACCUMDEPRECPE,
0 WDVPS,
assvalmovement.wdvpe WDVPE,
0 PROCONDISPCP,
0 PLONSALECP,
assvalmovement.assvalkey,
assvalue.compkey,
assvalue.dispdate,
assvalue.dispdesc,
comp.unittype,
comp.unitdesc,
assvalue.acqdate,
assvalue.acqtype
FROM assvalue ASSVALUE
inner join comp on assvalue.compkey = comp.compkey
inner join comptype on assvalue.comptype = comptype.comptype
inner join assvalmovement ASSVALMOVEMENT on assvalmovement.assvalkey = assvalue.assvalkey
WHERE
comp.COMPTYPE >1
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)>= CONVERT(DATETIME,@BeginDate,101)
AND CONVERT(DATETIME,EFFECTIVEDTTM,101)<= CONVERT(DATETIME,@EndDate,101)
)
--SELECT * FROM CTE0 ORDER BY COMPTYPE;
SELECT
comptype,
compdesc,
SUM(CURRENTCOSTPS) CURRENTCOSTPS,
SUM(INITCOSTCP) INITCOSTCP,
SUM(CAPTCOSTCP) CAPTCOSTCP,
SUM(DISPOSALSCD) DISPOSALSCD,
SUM(IMPVALPRIORTOREVALPE) IMPVALPRIORTOREVALPE,
SUM(REVALINCDEC) REVALINCDEC,
SUM(CURRENTCOSTPE) CURRENTCOSTPE,
SUM(ACCUMDEPRECPS) ACCUMDEPRECPS,
SUM(DEPRECCP) DEPRECCP,
SUM(DISPDEPRECWRTNBCKCP) DISPDEPRECWRTNBCKCP,
SUM(REVALACCUMDEPRECWRTNBCKGROSSCP) REVALACCUMDEPRECWRTNBCKGROSSCP,
SUM(REVALACCUMDEPRECWRTNBCKNETCP) REVALACCUMDEPRECWRTNBCKNETCP,
SUM(REVALACCUMDEPRECBGHTTOACCTCP) REVALACCUMDEPRECBGHTTOACCTCP,
SUM(ACCUMDEPRECPE) ACCUMDEPRECPE,
SUM(WDVPS) WDVPS,
SUM(WDVPE) WDVPE,
SUM(PROCONDISPCP) PROCONDISPCP,
SUM(PLONSALECP) PLONSALECP,
assvalkey,
compkey,
dispdate,
dispdesc,
unittype,
unitdesc,
acqdate,
acqtype
FROM(
SELECT * FROM CTE0
UNION ALL
SELECT * FROM CTE1 WHERE row_num=1
UNION ALL
SELECT * FROM CTE2 WHERE row_num=1
) A
GROUP BY comptype, compdesc,
assvalkey,
compkey,
dispdate,
dispdesc,
unittype,
unitdesc,
acqdate,
acqtype
ORDER BY comptype```