chart in report builder

  • jman76

    Hall of Fame

    Points: 3388

    Hello,

    I have a  query with the following fields. The fields are:

    Date of visit   Team    Hour Slot   Time of visit    count of visits
    2018-01-02    South      2                09:02                3
    2018-01-04    North       5                14:22                5

    I am trying to display the number of visits per hour on any given day in a month.

    I would like to create a chart in report builder. On my y axis i would like the hour slots going from 0 to 23 and then on the x axis i would like the days going from day 1 to day 30. How can i create those numbers regardless of the query on both axises.

    Thanks

  • Thom A

    SSC Guru

    Points: 98653

    I'm a little confused by your data, as it stands. For example, for South you have the hour slot as 2, the count of visits as 3, but the time as 09:02. Is this saying there were 3 visits at 09:02? What about between 09:03 and 10:00?

    Date of visit   Team    Hour Slot   Time of visit    count of visits
    2018-01-02      South   2           09:02            3
    2018-01-04      North   5           14:22            5

    Can you provide some move sample data here, and show what you're expecting to get? Please format your sample data, like I have done above, as well. Simply pasting your data without putting it in IF Markup makes it very difficult/impossible to read.

    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • jman76

    Hall of Fame

    Points: 3388

    Hello sorry for being vague. I have a better snapshot of the data.
    I am trying to create a chart to show where the number of visits by the hour of the day.

    My hourslot field is the actual hour 

    I would like to have the y axis of the chart to show the hour and then on the x axis I would like date of the visit in a month.

    On any given date the hour slot should be summed.

    DateOfvisit  Team   HourSlot TimeOfVisit   Visits
    2018-05-11  South      0        00:00        1
    2018-05-15  South      0        00:00        1
    2018-05-18  South      0        00:00        1
    2018-05-27  South      0        00:00        1
    2018-05-28  South      0        00:00        1
    2018-05-19  South      0        00:00        1
    2018-05-18  South      0        00:10        1
    2018-05-28  South      1        01:10        1
    2018-05-15  South      1        01:15        1
    2018-05-07  South      1        01:20        1
    2018-05-10  South      1        01:20        1
    2018-05-28  South      1        01:21        1
    2018-05-23  South      3        03:10      2
    2018-05-24  South      3        03:10      1
    2018-05-14  South      3        03:20      1
    2018-05-29  South      3        03:20      1
    2018-05-10  South       3        03:26      1

  • Thom A

    SSC Guru

    Points: 98653

    Please, please, format your sample data, it's impossible to read like that. I've done it for you again to help other volunteers here.
    DateOfvisit  Team   HourSlot TimeOfVisit   Visits
    2018-05-11   South  0        00:00         1
    2018-05-15   South  0        00:00         1
    2018-05-18   South  0        00:00         1
    2018-05-27   South  0        00:00         1
    2018-05-28   South  0        00:00         1
    2018-05-19   South  0        00:00         1
    2018-05-18   South  0        00:10         1
    2018-05-28   South  1        01:10         1
    2018-05-15   South  1        01:15         1
    2018-05-07   South  1        01:20         1
    2018-05-10   South  1        01:20         1
    2018-05-28   South  1        01:21         1
    2018-05-23   South  3        03:10         2
    2018-05-24   South  3        03:10         1
    2018-05-14   South  3        03:20         1
    2018-05-29   South  3        03:20         1
    2018-05-10   South  3        03:26         1

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Thom A

    SSC Guru

    Points: 98653

    Personally, I would aggregate your data using T-SQL. Then, you can build a chart of that. I've created an example for you below on how to get the aggregation for your data. You should be able to create a chart from this, as that data is already aggregated. If you're struggling, show you you've tried. A chart for data like this is one of the basics of SSRS, so you'll do better by doing and researching yourself, rather than having someone do the work for you:

    /* White space formatting removal, as always, courtesy of SSC's text editor... */
    CREATE TABLE #sample (DateOfVisit date,
            Team varchar(5),
            HourSlot tinyint,
            TimeOfVisit time(0),
            Visits tinyint);
    INSERT INTO #sample (DateOfVisit,
           Team,
           HourSlot,
           TimeOfVisit,
           Visits)
    VALUES ('20180511','South',0,'00:00',1),
       ('20180515','South',0,'00:00',1),
       ('20180518','South',0,'00:00',1),
       ('20180527','South',0,'00:00',1),
       ('20180528','South',0,'00:00',1),
       ('20180519','South',0,'00:00',1),
       ('20180518','South',0,'00:10',1),
       ('20180528','South',1,'01:10',1),
       ('20180515','South',1,'01:15',1),
       ('20180507','South',1,'01:20',1),
       ('20180510','South',1,'01:20',1),
       ('20180528','South',1,'01:21',1),
       ('20180523','South',3,'03:10',2),
       ('20180524','South',3,'03:10',1),
       ('20180514','South',3,'03:20',1),
       ('20180529','South',3,'03:20',1),
       ('20180510','South',3,'03:26',1);
    GO
    SELECT *
    FROM #sample;

    SELECT --Team, --not sure this is needed, so commented out
       DateOfVisit,
       CONVERT(time(0),DATEADD(HOUR, DATEDIFF(HOUR,'00:00',TimeOfVisit),'00:00')) AS HourOfVisit,
       SUM(Visits) AS TotalVisits
    FROM #sample
    GROUP BY --Team, --not sure this is needed, so commented out,
       DateOfVisit,
       DATEADD(HOUR, DATEDIFF(HOUR,'00:00',TimeOfVisit),'00:00')
    ORDER BY DateOfVisit,
       HourOfVisit;

    GO

    DROP TABLE #sample;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

Viewing 5 posts - 1 through 5 (of 5 total)

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