Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-15
7
Medium Priority
?
392 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
[X]
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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 1500 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

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.

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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 …

722 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