How do I achieve a pivot report in Crystal Reports for Visual Studio?

ProfK picture ProfK · Jul 16, 2013 · Viewed 10.9k times · Source

I am a rank noob at any reporting but horizontal lines with sub-totals. I have a dataset that comprises the times spent by cars in a carpark. I must produce a table showing a column for each hour and a row for each day, showing the number of entries at a particular time on a particular day.

I would find this easier in the RDLC designer, with it's columnar groupings, but I can't even find such a thing in Crystal.

Help.

Answer

craig picture craig · Jul 29, 2013

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

  • Choose Insert | Crosstab...; add cross-tab to report-header section

Right click the cross-tab and chose 'cross tab expert':

  • Add {table.census_time] to the column-field list; group by hour
  • Add {table.census_time} to the row-field list; group by day
  • Add {table.car_id} to the summary-field list; count

** edit **

You don't need to create a special formula to extract the hour from the date/time field; the cross-tab will do that for you.

enter image description here Select the 'Cross-Tab' tab, add the {table.census_time} field, then click the 'Group Options...' button.

enter image description here

Choose 'for each hour.' from the picklist.