Hi there,

I'm currently in the process of creating an invoice document and seem to have hit a bit of a stumbling block.

My report (a sales invoice) is based on a view which is looking at data derived from 2 tables: 

   InvoiceHead - a single record per job listed on the invoice. 
   InvoiceLines - a record per line on the invoice (each job COULD have multiple lines)

The issue I've run into is that carriage is only recorded at the Head level... which means I can't simply sum it up.

To get around this in Crystal (which I've been using for pushing 20 years now), I'd either:

   create a running total which would add the carriage value in when the InvoiceHeadID changes
   or
   create a numberVar and manually add the value when required.

... but I can't see any way to do either of these in the designer and Google doesn't seem to have any apparent examples.

Any help will be gratefully appreciated.

Cheers in advance,

Mat

Hi Mat,

If I understood correctly, there would be two data sources in the report, each one feeding a different table (or other data item). The requirement would be to add a field from the InvoiceHead table (Carriage) to the total sum in the InvoiceLines table.

I would recommend two approaches. Which one is applicable or better would depend on the exact scenario. The approaches are demonstrated in the attached reports.

First approach (see attached Report1)
The data source for IvoiceHead could be set as DataSource of the Report item. This would force Report Processor to create a separate details section for each data record, i.e. for each job. The Head data could be displayed in regular report items as TextBoxes. If the Head data should be displayed in a data item like Table (see the Data Items collection of articles for information of Telerik Reporting data items), it might be necessary to filter the data source of the data item (the same as the one for the Report) based on the job Id or other relevant field to avoid data duplication. Both cases are demonstrated side by side in Report1.
The InvoiceLines could be a Table (or other data item) with a separate data source that might be filtered based on a relevant field (job Id) of the Report data source. The Carriage field from the Report DataSource could be accessed in the InvoiceLines table with the expression '=ReportItem.Parent.DataObject.Carriage'. This expression takes the data source of the parent (Report1) of the parent (InvoiceLines) of the TextBox it is applied to. Note that the two data sources are assigned to data items that are on different levels of the report structure, which allows the child item to access the parent item data source. If both sources were assigned to data items that were at the same level this wouldn't be possible with the current approach.

Second approach (see attached Report2)
If the data sources should be assigned to data items that were on the same level of the report structure (i.e. two tables in the details section of the Report, see Report2) one could pass values from first data source to the second data item (the one with second data source) using Report Parameters. I would refer you to this collection of articles for more information on report parameters.
In the example there are two report parameters set to the Report item. The JobId parameter is used to select and display only the relevant data record in both data items, and the second parameter is used to pass the Carriage field from the first data source to the second data item (InvoiceLines). The Carriage parameter is not visible and its value is set depending on the chosen JobId by filtering the Report Parameter->AvailableValues->DataSource. Note that as currently set up, Report2 would display data only for a single job depending on JobId parameter.

If neither of the approaches is applicable, I would ask you to provide more information on the scenario. You could also consider opening a ticket and attaching a sample report so that we could try to provide more specific advises.

Regards,
Todor
Progress Telerik