SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to compare data from 2 different data sources and show only the differences in a report?


How to compare data from 2 different data sources and show only the differences in a report?

Author
Message
flbotts
flbotts
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 52
I'm trying to create a report that compares data from a SQL Server 2008 R2 Database to data in a IBM DB2 database and only shows rows where the data is not the same and that is able to run in real time as opposed to one that loads the data at some other time than when it is run. I think this may be possible if the data set for the DB2 database is called from a stored procedure which first imports the data into a staging table in SQL Server (SSIS?) and then produces a dataset between the staging table and the main SQL Server table using a SQL Union Query.

I know how to display the data from my two different data sources in the same table using the lookup feature, but can't figure out how to do this where I only want to show rows where certain fields do not match. This is used to verify data entries between our accounting databases (JD Edwards DBS on an AS/400) and our project management software (Prolog running on SQL Server 2008 R2).

If all rows from both data sources are a match, then the report would not show any data, indicating that there were no issues with that job. It should only display a report when there are inconsistancies in the two data sources indicating something that needs fixing.

I'm still new at creating reports with SSRS and would appreciate any advice, suggestions, or help anyone could give me.
Thanks
geoff5
geoff5
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 543
My suggestion is to use the Visibility property of the entire row and hide the row when the corresponding values between the two datasets are identical. I have a sample line of code below, based on the following assumptions (adapt for your actual environment):

a) Dataset 1 is "SQLServerData" and Dataset 2 is "IBMData"
b) The foreign key relationship is a between fields called KeySQLServerColumn and KeyIBMColumn, respectively.
c) The data you want to compare are in columns called TargetSQLServerData and TargetIBMData, respectively.
d) The Tablix in which the data are displayed has SQLServerData as its dataset.

If those were the settings, the following expression could be used in the Visiblity property of the row to hide values when they match:

= Fields!TargetSQLData.Value = Lookup(Fields!KeySQLServerColumn.Value, Fields!KeyIBMColumn.Value, Fields!TargetIBMData.Value, "IBMData")


flbotts
flbotts
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 52
I'm not sure I get how to do this completely.
I have 5 fields from dataset A that I am comparing to the same fields in dataset B. Dataset A is a sql server 2008 r2 database. the fields in order are: Prolog bdgt allocation(A), JDE Bdgt Allocation(B), Prolog Bdgt Amount(A), JDE Bdgt Amount(B), Prolog Cost Allocation(A), JDE Cost Allocation(B), Prolog Cost Amount(A), JDE Cost Amount(B), Prolog Bdgt Code(A), JDE Bdgt Code(B).
All the 'B' fields are from lookups into the db2 database.
The data looks like this for a row (without the 'A' and 'B' above the fields):
A B A B A B A
Appr Rev Appr Rev ($1,625.00) ($1,625.00) Apprv Commt Apprv Commt ($1,625.00)
B A B
($1,625.00) X128602.1A019200.2450 X128602.1A019200.2450

When each of the 5 fields are an exact match, I do not want to display anything. If any do not match, I want that line to display. This way I can see at a glance if there are any issues to be resolved between the two databases. When I run this report, if there aren't any discrepancies, I want it to display the no rows message. I checked "show or hide based on an expression" in the row and the expression I tried in the row visability property needs to look something like this but I don't know how to refer to the row:
=IIf(Fields!BdgtAllocation.Value <> ReportItems!Target_JDE_BdgtAlloc or Fields!BdgtAppliedAmount.Value <> ReportItems!Target_JDE_BdgtAppliedAmount or Fields!CostAllocation.Value<>ReportItems!Target_JDE_CostAlloc or Fields!CostAppliedAmount.Value<> ReportItems!Target_JDE_CostAppliedAmount or Fields!BdgtCode.Value <> ReportItems!Target_JDE_BdgtCode, (display the row), (hide the row))

I am also not sure if I should refer to the DB2 fields that I populated with a lookup should be referred to by the ReportItems collection.
In your example you had '= Fields!TargetSQLData.Value = Lookup(Fields!KeySQLServerColumn.Value, Fields!KeyIBMColumn.Value, Fields!TargetIBMData.Value, "IBMData")' Does '= Fields!TargetSQLData.Value ' equate to the Row? And if so, how? I was expecting something like 'Tablix!Row' or such. If the first value can reference the row, I think I could follow your example with multiple lookups for each of the 5 fields I need to check, basically having '= Fields!TargetSQLData.Value = Lookup(fields 1) or lookup(fields2) or lookup(fields3) or lookup(fields4) or lookup(fields5)
Your code makes sense, I just can't figure out the name to use for the first field ('= Fields!TargetSQLData.Value' in your example) that I want to be the row or how to tell it to hide or show the row.
Please help as I think I've started bursting blood cells in my brain from too much pondering over this...lol
Thanks
geoff5
geoff5
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 543
I believe the missing piece is the foreign key relationship between the two sets of results. What column or columns need to correspond with identical values between the two sets of rows so that you can then compare the five columns of the corresponding rows?
flbotts
flbotts
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 52
I have a primary key in each dataset that I match them up with. I am displaying all data correctly now. What I'm having trouble with is getting it to only show rows where there is a different value in either field from each of the five sets.
I can't figure out how to imbed a screenshot here, but if you want to shoot me an email at fred.botts@weitz.com I can send you a screenshot showing what I'm doing.
Thanks
geoff5
geoff5
SSChasing Mays
SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)SSChasing Mays (612 reputation)

Group: General Forum Members
Points: 612 Visits: 543
Keep in mind that the "Visibility" expression determines the value of the "Hide" property of the row. So to show only the rows where something is different, the expression should return "true" when the values are all the same.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search