Search code examples
c#crystal-reportsreport

Crystal report c#


what I want to do is insert data in crystal report, the challenge is on how can I achieve that.

my data is

Jan 1, Number, Name
Jan 2, Number, Name
Jan 4, Number, Name
Jan 5, Number, Name
Jan 7, Number, Name

what I want to do is regardless of my data, I want to display in my crystal report the whole month.

so the expected output is

Jan 1, Number, Name
Jan 2, Number, Name
Jan 2, Null, Null
Jan 4, Number, Name
Jan 5, Number, Name
Jan 2, Null, Null
Jan 7, Number, Name
Jan 8, Null , Null
...
Jan 31, Null , Null 

,is it possible to do this on crystal report?

I can generate it using select * from my queries, but i dont know how to fill in the missing values.


Solution

  • I think you need to create a date table once for the dates you need, then you can join to your data.

    Ex. select * from [DateTable] left outer join [your queries] on [DateTable].date=[your queries].date

    Here is an example how to create date table once for 10 years period starting from the current year:

    DECLARE @StartDate  date = '20210101';
    
    DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 10, @StartDate));
    
    ;WITH seq(n) AS 
    (
      SELECT 0 UNION ALL SELECT n + 1 FROM seq
      WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
    ),
    d(d) AS 
    (
      SELECT DATEADD(DAY, n, @StartDate) FROM seq
    ),
    src AS
    (
      SELECT
        TheDate         = CONVERT(date, d),
        TheDay          = DATEPART(DAY,       d),
        TheDayName      = DATENAME(WEEKDAY,   d),
        TheWeek         = DATEPART(WEEK,      d),
        TheISOWeek      = DATEPART(ISO_WEEK,  d),
        TheDayOfWeek    = DATEPART(WEEKDAY,   d),
        TheMonth        = DATEPART(MONTH,     d),
        TheMonthName    = DATENAME(MONTH,     d),
        TheQuarter      = DATEPART(Quarter,   d),
        TheYear         = DATEPART(YEAR,      d),
        TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
        TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
        TheDayOfYear    = DATEPART(DAYOFYEAR, d)
      FROM d
    )
    SELECT * into dbo.DateTable
     FROM src
      ORDER BY TheDate
      OPTION (MAXRECURSION 0);