How to get a summary report in Crystal Reports for Visual Studio?

Question

I rank noob in any reporting, but horizontal lines with sub-totals. I have a data set that includes the time spent by the cars in the parking lot. I have to create a table with a column for each hour and a row for each day, showing the number of records at a specific time on a particular day.

I would find it easier in the RDLC designer, with its column groups, but I can't even find such a thing in Crystal.

Reference.

Answer 1

Assuming the following fields: {table.car_id} and {table.census_time}

• Choose Paste | Cross...; add cross tab to report title section

Right-click the cross tab and select "Cross Tab Expert":

• Add {table.census_time] to the column list; group per hour
• Add {table.census_time} to the list of rows; group by day
• Add {table.car_id} to the list of summary fields; number

** edit **

You do not need to create a special formula to extract the hour from the date / time field; the cross tab will do this for you.

Select the Cross Tab tab, add the {table.census_time} field, then click the Group Options ... button.

Select "for every hour." from the selection list.

Answer 2

You need to create a table with a record for each hour:

DayHour table

 Period StartHour EndHour 00:00 - 01:00 0 1 01:00 - 02:00 1 2 02:00 - 03:00 2 3 03:00 - 04:00 3 4 ... 23:00 - 00:00 23 24 

Then, on the left, attach your data to this table.

 SELECT h.Period, DATEPART(dd,d.EntryTime) as Day, 1 as Value FROM DayHour h LEFT JOIN <YourData> d ON h.StartHour <=DATEPART(hh,d.EntryTime) and DATEPART(hh,d.EntryTime)<h.EndHour 

This will return the entire record with a period of time and even return the record if no vehicles entered the parking for a certain period of time. Drag the Period column into the column section of the Crossroads and Day tabs in the Rows section. Drag the value to the summary section.