For the project I am working on we are reconciling attributes accross three different data-base schemas. There is a "Source" schema, a "Target" schema, and a "Machine" schema. The data is being converted from Source to Target, and then loaded from the Target to Machine. Inbetween each schema there are transformations on the data. For example, in the SRC (source) schema, account categories are stored as the attribute "SRC_CAT" and can be any of the values A, B, or C. This same attribute is converted into the target (TGT) attribute "TGT_CAT" which can have any of the values 1, 2, or 3. These target values are then loaded into the Machine (MCN) schema with minor transformation. Inbetween TGT and MCN the attributes and values will stay the same but some records will be dropped. To reconcile that the SRC attributes were converted to the intended TGT and MCN attributes we compare counts of accounts for each combination of values accross the schemas. We do this by joining the source, target, and machine schemas over a Cross Reference. Hopefully the figure below will make this clear: SRC_CAT COUNT SRC IDs TGT_CAT COUNT TGT IDs COUNT_MCN_IDs A 10 1 10 00 B 11 2 11 08 C 06 3 06 05 If All of the 'A's were successfully converted to '1's then the count from source to target should match. If any of those records were dropped however they would not exist in the MCN schema and the count in this column should be lower. This is the problem I am facing: When I will build the above report, but only compare SRC to MCN, I get a difference in counts for some records. This leads me to believe that some may have been dropped. To be sure of this I add the TGT count (the step inbetween the two). If the record was dropped between TGT and MCN, the TGT count should match SRC. (report before adding TGT counts) SRC_CAT COUNT SRC IDs TGT_CAT COUNT_MCN_IDs A 10 1 00 B 11 2 08 C 06 3 05 Instead of seeing what would be expected, the MCN counts suddenly "fix" themselves to match the SRC counts. The difference that existed before TGT was added suddenly no longer exists. (After adding TGT schema counts) SRC_CAT COUNT SRC IDs TGT_CAT COUNT TGT IDs COUNT_MCN_IDs A 10 1 10 10 B 11 2 11 11 C 06 3 06 06 (The expected/desired answer should resemble the first chart expressed above) Has anyone faced a similar problem? I could go into more detail about how the cross-reference is set up and compares values but I'm not sure which info would be valuable. All joins are outer except between the X-ref and src - this drives the accounts based on the source data. If there is more info that could help resolve why this is happening please let me know. If you have any advice please share. Also, if anyone has experienced similar problems, please express them. I hope someone can make sense of this... Thanks, Max

Please post the SQL used by your report datasetCraig Bryden - Please mark correct answers

Hi Max, This is the forum of Reporting Services, are you sure your issue is relevant to Reporting Services? Could you please post your issue at the forum which is more relevant to your issue to get a specific support. If I misunderstand you ,please give the SQL query for getting the report dataset, and desired report layout you want to get. Thanks, Challen FuPlease remember to mark the replies as answers if they help and unmark them if they provide no help.