I have a dataset that is made up of two sets of data.
- Old records - which are the historical values of current active records
- New records - the current values of active records
The old records are the primary set of data, but not all old records will have new records. In this instance I still want to show the old record, but with a null value for the new record columns.
Both elements of the data can have multiple options which have an individual price associated with them. I am trying to produce a report which shows the old records and the price options alongside the new options and their price options.
I've include an example output below, this only contains two of the relevant non-matrix fields (Record Id and Record Type), but in the final data there are around 10 data items that are not part of the matrix element, while the remaining items being matrix items (Option1, Option2, Option3). There are only two options for the matrix element (price and option description).
The first 5 columns of the below table are the old data, while the next five are the new data.
Example Output
| Matrix Element | Matrix Element | Matrix Element | Matrix Element | Matrix Element | Matrix Element | ||||
|---|---|---|---|---|---|---|---|---|---|
| Record Id | Record Type | Option1 | Option2 | Option3 | Record ID | Record Type | Option1 | Option2 | Option 3 |
| 001 | Type1 | 100 | 200 | 50000 | 001 | Type1 | 200 | 300 | 100 |
| 002 | Type1 | 10 | 20000 | 50000 | |||||
| 003 | Type2 | 50000 | 25 | 10 | 003 | Type2 | 10 | 50000 | 10 |
I have returned the two sets of data into one dataset. Some options in the Old data, do not have linked records in the new data (Record 002 in the example output). The matrix elements (options) are variable by both old and new data (i.e. a old record could have 4 options, but a new record could have 7 records). The new options are also variable where one record could only have 1 new option, but the next could have 6 options.
I am using a matrix as there are around 70 combinations of options, so I don't want to hardcode this into the report.
When pulling out the data I have tired the following options:
- A basic Tablix - This then creates duplicate lines due to the multiple option values in both the old and new data.
- I then separated the old and new data into separate datasets and then used the lookup option to pull the new data across against the old values. This does return information but is extremely slow (several minutes to return 100 records), and as far as I can see its not possible to use a lookup in the data element of a matrix.
I am trying to find an display option that shows the old and new data on a single line with no duplication of record id, with a matrix type display of the price and options data. For options that don't have a value for a specific record, I would expect this just to be blank.
The raw data for both old and new is currently represented in the following way, the only difference between the two data sets is the representation of the old/new data. The record id is a unique id that can be used to match the two datasets together.
| Record Id | Record Type | ... | Option | Price |
|---|---|---|---|---|
| 001 | Type1 | Option 1 | 100 | |
| 001 | Type1 | Option 2 | 200 | |
| 001 | Type1 | Option 3 | 50000 | |
| 002 | Type1 | Option 1 | 10 | |
| 002 | Type1 | Option 2 | 20000 | |
| 002 | Type1 | Option 3 | 50000 | |
| 003 | Type2 | Option 1 | 50000 | |
| 003 | Type2 | Option 2 | 25 | |
| 003 | Type2 | option 3 | 10 |