• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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.
  • 4
  • 3
1 Solution
ValentinoVBI ConsultantCommented:
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)
Papa1NZAuthor Commented:
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?
ValentinoVBI ConsultantCommented:
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?
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Papa1NZAuthor Commented:
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?
ValentinoVBI ConsultantCommented:
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.
Papa1NZAuthor Commented:
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.
ValentinoVBI ConsultantCommented:
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)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now