Simple SQL Report Format & Filters

[algisbekeris]

Hi, I’m creating a very simple report based on a SQL table, I’m unable to proper format the report and also I do not know how to filter it.

This is the table:

This is the report:

This is how it looks:

and this is the sample data:

My questions:

1.- How do I make the report to properly format date and time? It only shows dates and not time
2.- How do I make the report to fill the null values as empty columns?

3.- How do I make the date-time filter to work?

4.- How do I do to order the table? (like order by in SQL)

Thanks

[algisbekeris]

I have fixed the formatting creating a view of the table in SQL and using the view:

This is the query for the view:

This is the result:

Now I need to know how to filter by time/date and how to sort the report

[Memo]

If you want to use a view, in your where clause you can pass ‘{Start}’ and ‘{End}’ and those will be replaced with the date constraints behind the scenes.

I can’t get REPORT SQL DETAILS to work when formatting dates. So this is what I did:

Create a script:

code

DECLARE @StartDate NVARCHAR(25) = N'{Start}';
DECLARE @EndDate NVARCHAR(25) = N'{End}';

SELECT UserId,
       UserName,
       ISNULL(FORMAT(ClockIn, 'dd/MM/yyyy hh:mm:ss'), 'N/A')  AS ClockIn,
       ISNULL(FORMAT(ClockOut, 'dd/MM/yyyy hh:mm:ss'), 'N/A') AS ClockOut,
       ISNULL(CONVERT(NVARCHAR(28), TotalHours), 'N/A')           AS TotalHours
    FROM dbo.ABR_ClockLog
    WHERE ClockIn
    BETWEEN @StartDate AND @EndDate
    ORDER BY Id;

I just went with constraining the clockin column

and then the report:

[Clock Report:3 ,15, 20, 20, 20]
>Id|Name|In|Out|Total
@@Punches

[Jesse]

To format the dates you need to wrap an expression around the fields {1}|{2} etc.

Example:

[=FD('{0}','h\:ss tt')] That would produce something like 3:42 pm

[Jesse]

@algisbekeris

4.- How do I do to order the table? (like order by in SQL)

You use Order By in the sql. The tag you are using is doing sql.

[algisbekeris]

Whatever I do, {Start} is static to June27 2022 and {End} goes to current date time. Am I doing anything wrong? I copied your code and tested it.

[algisbekeris]

@Jesse

use Order By in the sql. The tag you are using is doing sql.

That works!, I thought there was another way. Thanks

[algisbekeris]

To test the {Start} and {End} parameters alone, I did the following:

Create a script to display those values:

Create a report for it:

Test it:

Every selection I do from this menu default to this dates, no matter what I select:

[Jesse]

are you opening and closing work periods?

[algisbekeris]

I just figured out that those parameters {Start} and {End} are based on the Work Period records, which in my opinion is a wrong approach, this is the reason why it defaults to those dates and times:

My WorkPeriods table:

Those are the dates and times it was defaulting {Start} and {End}, instead of real date/time calculations.

Is there any way I can get, for example, the filter “Today” with {Start} = today at 0:00:00 and {End} = Now()?

Thanks

[algisbekeris]

Not for my tests…I was thinking that those were based on actual dates and not the work periods.

[Memo]

{Start} and {End} should be replaced with what range is specified in the report viewer.

IIRC, one can’t specify a date that is before the earliest work period and defaults back to the current work period or the earliest work period (can’t recall which).

[Jesse]

It will work if you filter with date and time on the right.

The report screen is build around work periods. Work periods exist to designate reporting start and end dates. Many restaurants work past an actual day.

If you are trying to use it for shifts like time clock shifts it may not work how you think and you may need to build a screen that can filter exact date input easier.

However you can type a time after the date filter that’s on the right side of report screen to get exact date time filter.

[algisbekeris]

Those are not perfect so I had to modify them a little bit so it does what I want:

I transform the start date to the beginning of that day and the end date to the beginning of next day

now it does what I want.

Thanks for the ideas.