Post reply

Filter by column that is datetime, so only rows that had a ref in time period show up

  • February 11, 2019 at 1:50 pm

    #373758

    Hi,
    My report has to cover 16 months of data.
    Each PK reference has one date in DateInstructed, and may have a value in DateStarted.
    But I only want the references/Rows that were instructed in the same month as started.
    So I have tried to group on year, with a child group of month. This works fine, when showing all data.
    But how to show only those that were instructed and started in that month.

    In the expression for the filter, do I have to define the beginning and end of month and then compare to dateInstructed or is there an easier way?
    Apologies if this if this been asked before.
    What I have doen so far throws an exceptoion saying, can't compare datetime to string.  I think because the Row groups are not times but strings.

  • February 13, 2019 at 12:39 pm

    #2021976

    This may be a matter of understanding the data in your dataset, and possibly modifying that query to bring the needed data forward into the report so that you can filter on it.   I prefer to use the year and month concatenated into a single column for such things as grouping by month, and I sometimes prefer to put an underscore between them as well.   If that were done in the dataset query for each of the dates involved, this would be easy-peasy.   That's why it's so important to understand the data in your dataset, ... so that you know when to expand it.  Such expansions as I'm referring to are far better performed in the dataset query than in the report.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 13, 2019 at 1:17 pm

    #2021992

    I actually prefer using the first of the month to group dates. 

    • There is a fast way to determine the first of the month for any date.
    • You can still do date arithmetic on it.
    • Your presentation layer can format it to only display the year/month.
    • You don't need to do the more expensive conversion to character.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply