update Report Definition Language report directly with find and replace using SSIS or Talend?

Posted on 2014-01-15
Last Modified: 2016-02-10
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.
Question by:Papa1NZ
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 37

Expert Comment

ID: 39784681
When you say "label" you're actually referring to a database field name, right?  It's rather confusing in a reporting context where the word "label" has another meaning. :)

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)

Author Comment

ID: 39793208
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?
LVL 37

Expert Comment

ID: 39796970
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?
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why


Author Comment

ID: 39799070
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.

C.Id as CatId, C.CatName, C.CatDesciption, C.Ref
D.Id as DogId, D.DogName, D.NumberOfLegs,
 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?
LVL 37

Accepted Solution

ValentinoV earned 500 total points
ID: 39799402
Okay, got it.  Given your situation it indeed looks like XML manipulation is the only way to possibly automate this task.

I'd first test this manually.  Take one of those reports and change the RDL/XML manually.  First make the changes to the dataset, then to the Fields!Oranges.Value references using Find/Replace All functionality.  Make sure to include "Fields!" in the search string so other references to "Oranges" don't get replaced (unless it intended that way).

The datasets are located at the top of the RDL, something like:

	<DataSet Name="DataSet1">
		<CommandText>select some stuff from some table</CommandText>
		<Field Name="AnID">
		<Field Name="Oranges">

Open in new window

Now run the report to verify if it still runs and if the change did what you expected.  If yes, you could try writing a small program (or .NET script in SSIS if you'd prefer) that uses simple string manipulation functions to mimic the manual task.

Author Comment

ID: 39801388
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.
LVL 37

Expert Comment

ID: 39802385
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)

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this Micro Tutorial viewers will learn how to restore their server from Bare Metal Backup image created with Windows Server Backup feature. As an example Windows 2012R2 is used.
In this Micro Tutorial viewers will learn how to restore single file or folder from Bare Metal backup image of their system. Tutorial shows how to restore files and folders from system backup. Often it is not needed to restore entire system when onl…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question