Combine multiple records with same date value into single row

[Bryan]

Hello,

 

For my report I will have multiple records with the same date, but each with different times. I was hoping to be able to show an aggregate for the data for each date, with a drill down to show more info for the different times. So far I have been unable to actually get the dates to combine at all, and instead will show every record. How can I get the records to combine into a single row?

 

Currently showing:

 

Day     Sum(price)

1

[Bryan]

Sorry posted before finished typing. It shows this:

Day    sum(price)

1        5

1        7

 

Instead I want:

day    sum(price

1         12

 

Thanks

[Mads]

Hi, Bryan

I made a sample report that resembles your which I have attached to this reply.

I will explain the steps to create this result:

1. To the datasource, I added a CalulatedField. This field is generated with the expression "= Date(Fields.DateTime.Year, Fields.DateTime.Month, Fields.DateTime.Day)" which creates a new date based only on date, not the time. This will be useful in the next step to group the data.
2. In the detail section of the report, I created a List item, and set its DataSource property to the dataset, then I defined a Grouping based on the CalculatedField we created in the previous step. Add a few textboxes with the information you want to show before drill-down action shows the rest of the details, and an empty textbox we later can use to attach the Show/Hide action for the details.
3. Underneath the text-fields we created in the previous step, but still inside the List-item, I created the table which displays the details for each hour. Use the TableWizard and choose the dataset in the first step. When the wizard is finished and the table is configured the way you want, remove the data-source in the properties, then create a binding which sets the DataSource of the table to ReportItem.DataObject. This will make sure the table only displays the items which are part of the grouping
4. Set the table to visible = false so it is initially hidden and click the textbox added last in step 2. For this textbox we, open up Actions in the property browser, choose Toggle visibility, and find the name of the table in the dropdown-list. Deselect Toggle mark initially expanded, and click OK. Preview the report and hopefully it works like you want it to.

Let me know if this solves you issue, if you run into challenges or have any other questions.

Regards, Mads Progress Telerik