Solved

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

Posted on 2014-01-15
7
354 Views
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.
Thanks
0
Comment
Question by:Papa1NZ
  • 4
  • 3
7 Comments
 
LVL 37

Expert Comment

by:ValentinoV
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)
0
 

Author Comment

by:Papa1NZ
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?
0
 
LVL 37

Expert Comment

by:ValentinoV
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Papa1NZ
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.

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?
0
 
LVL 37

Accepted Solution

by:
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:

<DataSets>
	<DataSet Name="DataSet1">
	  <Query>
		<DataSourceName>DataSource1</DataSourceName>
		<CommandText>select some stuff from some table</CommandText>
	  </Query>
	  <Fields>
		<Field Name="AnID">
		  <DataField>AnID</DataField>
		  <rd:TypeName>System.Int32</rd:TypeName>
		</Field>
		<Field Name="Oranges">
		  <DataField>Oranges</DataField>
		  <rd:TypeName>System.String</rd:TypeName>
		</Field>
	  </Fields>
	</DataSet>
</DataSets>

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.
0
 

Author Comment

by:Papa1NZ
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.
0
 
LVL 37

Expert Comment

by:ValentinoV
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)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

I don't know if many of you have made the great mistake of using the Cisco Thin Client model with the management software VXC. If you have then you are probably more then familiar with the incredibly clunky interface, the numerous work arounds, and …
What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
In this Micro Tutorial viewers will learn how to use Windows Server Backup to create full image of their system. Tutorial shows how to install Windows Server Backup Feature on Windows 2012R2 and how to configure scheduled Bare Metal Recovery backup.…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now