CODEWITHSUNDEEP
sqlcrystal-reportsunion-all
Filipe Costa
Filipe Costa

Reputation: 664

Crystal Reports - side by side details from actual and last year, using UNION ALL

I'm creating a report that shows in the details section the current and last year of sales.

The rows of the report have a structure like this:

Article |||| LastYearQty | LastYearPrice |||| ActualYearQty | ActualYearPrice

There are two parameters at the report, the StartDate and EndDate.

I created a view view_RowsDocs, that will replace the original table RowsDocs, with the aditional boolean field IsLastYear to filter each line, but i'm having some problems to filter the date, classifying the rows at the last year or at the actual year.

For example, with StartDate 1-1-2014 EndDate 28-4-2014:

IsLastYear || Article || Qty || Price || Date
   1           A0001     12     13,12    12-2-2013
   0           A0001     13     13,11    12-7-2013

What can be the best way to update the IsLastYear field?

Upvotes: 0

Views: 320

Answers (1)

sion_corn
sion_corn

Reputation: 3141

I think the fact that you are using a View to get this relation with the IsLastYear attribute might be screwing you up. This is because by using a view, you are actually hitting the base relation. Your logic that determines the IsLastYear attribute is doing something screwy.

Instead, consider using a temp table to instantiate the relation with the IsLastYear attribute:

select   
    ...
    ,[Date]
    ,CASE 
        WHEN YEAR([Date]) = YEAR(GETDATE()) - 1
        THEN 1
        ELSE 0
    END as [IsLastYear]
into #myTempTbl
from RowsDocs

If you are producing this dataset with a stored procedure, include a DROP TABLE #myTempTbl at the end of the sproc.

Upvotes: 1

Related Questions