Papa1NZ
asked on
update Report Definition Language report directly with find and replace using SSIS or Talend?
Hi Experts. I want to find the best way to update lots of reports with lots of different data label changes however the reports stays the same.
In a bit more detail I have many different reports all using the same database. However I need the same reports to work with other client dbs. The data will be labelled differently but the reports will be the same. I can map the data changes but I'm not sure about the best way to change all the reports E.g. a label in the first database is called e.g. apples (Fields!apples.value) but in the second database it is labelled as oranges. So replace all the Fields!apples.value with Fields!Oranges.value.
This sounds simple but I don’t want to miss any references in groups, calculations etc. So I am not sure if sometimes the field will be referenced in another way perhaps? I have not used xml for about 10 years.
So what I would like to do to just find and replace all the items in the rdl file with the name apples to oranges. There are probably 100 different labels to change for the reports I am using, so doing a find and replace manually is not what I would like to do.
I have done ETL with flat files where the information lines up in columns but never with xml (RDL). How do I only get the data not the e.g. tag names etc. to update? I'm not looking for a complete solution just the best way to go about this. Useful web links, SSIS/Talend component to use, etc.
Thanks
In a bit more detail I have many different reports all using the same database. However I need the same reports to work with other client dbs. The data will be labelled differently but the reports will be the same. I can map the data changes but I'm not sure about the best way to change all the reports E.g. a label in the first database is called e.g. apples (Fields!apples.value) but in the second database it is labelled as oranges. So replace all the Fields!apples.value with Fields!Oranges.value.
This sounds simple but I don’t want to miss any references in groups, calculations etc. So I am not sure if sometimes the field will be referenced in another way perhaps? I have not used xml for about 10 years.
So what I would like to do to just find and replace all the items in the rdl file with the name apples to oranges. There are probably 100 different labels to change for the reports I am using, so doing a find and replace manually is not what I would like to do.
I have done ETL with flat files where the information lines up in columns but never with xml (RDL). How do I only get the data not the e.g. tag names etc. to update? I'm not looking for a complete solution just the best way to go about this. Useful web links, SSIS/Talend component to use, etc.
Thanks
ASKER
Hi Valentino,
Yes sorry I am referring to the database field name. However I should have mentioned this earlier, I am using an application specific odbc driver to connect to my database. The labels which are configurable in our application are the database fields we use in the report which is why I mixed up my context. So the database fields will change depending on what changes are made to the label names in the application. E.g. Description field might be changed to Comments. So my sql query for the exact same field would change from 'Select table1.Description from Table1' to 'Select table1.Comments from table1'. They will be the same field in the application user interface and refer to the same information but the field name would be different. This is actually a bit of a nightmare to manage and why I need to find a way to automatically change the fields in the reports when labels are changed in the application.
However this means I am unable to connect to a stored procedure as the odbc driver manages the security and information. So I can only access information via the odbc driver.
Any further advice?
Yes sorry I am referring to the database field name. However I should have mentioned this earlier, I am using an application specific odbc driver to connect to my database. The labels which are configurable in our application are the database fields we use in the report which is why I mixed up my context. So the database fields will change depending on what changes are made to the label names in the application. E.g. Description field might be changed to Comments. So my sql query for the exact same field would change from 'Select table1.Description from Table1' to 'Select table1.Comments from table1'. They will be the same field in the application user interface and refer to the same information but the field name would be different. This is actually a bit of a nightmare to manage and why I need to find a way to automatically change the fields in the reports when labels are changed in the application.
However this means I am unable to connect to a stored procedure as the odbc driver manages the security and information. So I can only access information via the odbc driver.
Any further advice?
This is actually a bit of a nightmare to manage...
I surely can imagine that!
Not sure yet if I fully understand what you've explained. As far as I understood, the user "defines" what he wants to see in the report through another application? How does the report take this into account? (Or is that the part you're struggling with?) And what does a query through that ODBC driver look like?
I surely can imagine that!
Not sure yet if I fully understand what you've explained. As far as I understood, the user "defines" what he wants to see in the report through another application? How does the report take this into account? (Or is that the part you're struggling with?) And what does a query through that ODBC driver look like?
ASKER
Hi Valentino,
Actually the labels (database fields) will only change once or twice during the configuration stage. So not as bad as it sounds. But I have lots of standard reports I need to change for each new user configuration. The application structures it in a way that you see the fields in tables and you use junction tables to link the many to many tables. So it basically acts like a normal rational database and you select the information from it the same as you would from a database.
E.g.
C.Id as CatId, C.CatName, C.CatDesciption, C.Ref
D.Id as DogId, D.DogName, D.NumberOfLegs,
from
Cat C inner join DogCat DC inner join D
on C.Id=DC.BaseId
on DC.relatedId = D.Id
where C.CatName="Fred"
A user might reconfigure CatName to be RocketShipName, CatDescription to Rocketdescripton, etc.
So what I wanted to do was to a find and replace in the RDL with Catname to RocketShipName.
That is not the actual script but hopefully explains it enough?
Actually the labels (database fields) will only change once or twice during the configuration stage. So not as bad as it sounds. But I have lots of standard reports I need to change for each new user configuration. The application structures it in a way that you see the fields in tables and you use junction tables to link the many to many tables. So it basically acts like a normal rational database and you select the information from it the same as you would from a database.
E.g.
C.Id as CatId, C.CatName, C.CatDesciption, C.Ref
D.Id as DogId, D.DogName, D.NumberOfLegs,
from
Cat C inner join DogCat DC inner join D
on C.Id=DC.BaseId
on DC.relatedId = D.Id
where C.CatName="Fred"
A user might reconfigure CatName to be RocketShipName, CatDescription to Rocketdescripton, etc.
So what I wanted to do was to a find and replace in the RDL with Catname to RocketShipName.
That is not the actual script but hopefully explains it enough?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks Valentino, I was thinking only those same lines but hoping there where other alternatives. But at least you have confirmed what I need to do!
Many thanks.
Many thanks.
You're welcome. I was hoping I could come up with a nicer solution but alas, not in this case... (yeah, I'm a perfectionist)
I think you need to take a step back here and think about the report design a little more. In ideal circumstances, all that's needed for a report to display data from another DB is to connect it to a different shared data source.
To be able to handle field name differences, you should consider using stored procedures. Each DB will have similar stored procs: SP name, parameters, fields returned are identical, query implementation will differ for each DB, depending on what the fields are called.
In your report, instead of Fields!Apples or Fields!Oranges you could have Fields!Fruit.
In case your report needs to label things differently, depending on DB, you can use an additional dataset that returns the labels to be shown on the report. This dataset could call a stored proc, or just retrieve data from a special ReportLabel table.
This way you can completely avoid that task of manipulating the RDL, which could work eventually but it won't be easy to implement and you'd almost certainly need to write quite some code to manipulate the XML properly...
Food for thought! Let me know if I wasn't clear on something or if you need more detail.
In case you haven't used stored procs in dataset before:
Reporting On Data From Stored Procedures (part 1)
Reporting On Data From Stored Procedures (part 2)