May 30, 2013 at 2:23 pm
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
May 30, 2013 at 8:15 pm
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")
June 10, 2013 at 8:17 am
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
June 10, 2013 at 9:31 am
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?
June 10, 2013 at 11:59 am
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
June 10, 2013 at 12:18 pm
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply