Monthly attendance report in Crystal Report

Question

I am doing a student attendance project. I record attendance in a table in the database.

I want to create a monthly attendance report. I found a stored procedure to do that.

Table

CREATE TABLE Attend
(
   NAME VARCHAR(50),
   DATE DATETIME ,
   PRESENT_STATUS VARCHAR(10)
)

Stored procedure:

CREATE PROCEDURE GET_ATTENDANCEREPORT
    @STARTDATE DATETIME,
    @ENDDATE DATETIME
AS 
BEGIN
    WITH DATERANGE AS
    (
         SELECT DT = DATEADD(DD,0, @STARTDATE)
         WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE
         UNION ALL
         SELECT DATEADD(DD, 1, DT)
         FROM DATERANGE
         WHERE DATEADD(DD, 1, DT) <= @ENDDATE
    )
    SELECT * 
    INTO #TMP_DATES
    FROM DATERANGE

    DECLARE @COLUMN VARCHAR(MAX)

    SELECT @COLUMN = ISNULL(@COLUMN + ',', '') + '[' + CAST(CONVERT(DATE, T.DT) AS VARCHAR) + ']' 
    FROM #TMP_DATES T

    DECLARE @Columns2 VARCHAR(MAX)
    SET @Columns2 = SUBSTRING((SELECT DISTINCT ', ISNULL(['+ CAST(CONVERT(DATE, DT) as varchar) + '],''N/A'') AS ['+CAST(CONVERT(DATE , DT) as varchar )+']' 
                               FROM #TMP_DATES 
                               GROUP BY dt 
                               FOR XML PATH('')), 2, 8000)

    DECLARE @QUERY VARCHAR(MAX)

    SET @QUERY = 'SELECT NAME, ' + @Columns2 +
                 ' FROM (SELECT A.NAME, B.DT AS DATE, A.PRESENT_STATUS FROM Attend A RIGHT OUTER JOIN #TMP_DATES B ON A.DATE = B.DT) X
                   PIVOT
                      (MIN([PRESENT_STATUS])
                       FOR [DATE] IN (' + @COLUMN + ')) P
                   WHERE ISNULL(NAME,'''')<>''''
    '

    EXEC (@QUERY)

    DROP TABLE #TMP_DATES
END

But I want to create the report in Crystal Report.

I want it to appear like this:

Because when I use the stored procedure in Crystal Report, it does not work because to create the columns it requires me to put the start and end dates, which is a variable that cannot be fixed.

I want the report in Crystal Report

Answer 1

Create a DAYS table with columns for Year, Month, Date, DayLetter and populate it with one record for each day. If you Google the topic "script for date dimension table" you should find samples.

Do an outer join to the attendance table using the Date column.

In Crystal, insert a CrossTab. Use the Display String expression to show the desired data.

Answer 2

Because when I use the stored procedure in Crystal Report, it does not work because to create the columns it requires me to put the start and end dates, which is a variable that cannot be fixed. Does this mean you think the values you assign during selecting the procedure is fixed and cant be changed after design? values to stored procedure can be changed dynamically even after designing.

I add Stored procedure like this. select Database expert > select the correct connection > select Db > select schema > select stored procedure > select required procedure > click right single arrow > it'll ask values for stored procedure variable. The values assigned here is used only to show the columns for designing purpose. You can give different values for this parameters even after designing. Can give different values in each Run.