Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 Other Topics
 RDL

Author  Topic 

OBINNA_EKE
Posting Yak Master

234 Posts
Posted - 2010-12-30 : 06:35:37
using System;

namespace RdlCorrector
{
class Program
{
static void Main(string[] args)
{
Console.Write("Rdl file to be modified (needs to reside in the execution directory of this console app):");
string fileName = Console.ReadLine();

Console.Write("ReportOutputTemplateId:");
int reportOutputTemplateId = Int32.Parse(Console.ReadLine());


RdlReport report = new RdlReport(fileName, reportOutputTemplateId);
report.Init();

Console.WriteLine("Modifying " + report.FileName);

for (int i = 0; i < report.ColumnCount; i++)
{
if(!report.CheckColumns(i))
continue;
string dbFieldValue = report.GetDBFieldNameFromColumn(i);
string headerFieldValue = report.GetLabelValueFromColumn(i);

report.AddVisibilityTag(i, dbFieldValue);
report.InsertFriendlyHeaderMethodCalls(i, dbFieldValue);
report.WriteOutputTemplateColumnInsertToFile(i, dbFieldValue, headerFieldValue);
}

report.AppendReportParametersTag();
report.AppendRdlMethods();
report.AppendCodeModuleTag();
report.Exit();

if (ErrorLog.HasContent)
{
Console.WriteLine("\n~~~~ Attention, attention! Not everything has been properly modified. For details, see error log at " + ErrorLog.Location);
}

Console.WriteLine("\nFinished!");
Console.ReadKey();

}

}
}
**************************

using System.Linq;
using System.Xml.Linq;
using System.Collections.Generic;

namespace RdlCorrector
{
internal class RdlReport
{
public string FileName { get; private set; }
public int ReportOutputTemplateId { get; private set; }

private IEnumerable<XElement> LabelColumns{ get; set; }
private IEnumerable<XElement> ValueColumns { get; set; }
private IEnumerable<XElement> TablixMembers { get; set; }
private XDocument _xmlDoc;
private SqlFile _sqlFile;

private const string _FormulaPrefix = "=Fields!";
private const string _FormulaSuffix = ".Value";

/// <summary>
/// Constructor
/// </summary>
/// <param name="fileName"></param>
public RdlReport(string fileName, int reportOutputTemplateId)
{
FileName = Utils.GetExecutionPath() + fileName;
ReportOutputTemplateId = reportOutputTemplateId;
}

public void Init()
{
_xmlDoc = XDocument.Load(FileName);
XNamespace ns = GetNamespace();


IEnumerable<XElement> listRows = from c in _xmlDoc
.Descendants(ns + "TablixRow")
select c;

LabelColumns = from c in listRows.ElementAt(0)
.Descendants(ns + "TablixCell")
select c;

ValueColumns = from c in listRows.ElementAt(1)
.Descendants(ns + "TablixCell")
select c;

TablixMembers = from c in _xmlDoc
.Descendants(ns + "TablixColumnHierarchy")
.Descendants(ns + "TablixMembers")
.Descendants(ns + "TablixMember")
select c;

_sqlFile = new SqlFile(FileName + ".sql");
_sqlFile.DeleteExistingReportOutputTemplateEntries(ReportOutputTemplateId.ToString());
}

public int ColumnCount
{
get
{
return LabelColumns.Count();
}
}

public void Exit()
{
Save();
_sqlFile.Save();
}

private XElement GetLabelColumn(int position)
{
return LabelColumns.ElementAt(position)
.Descendants(GetNamespace() + "TextRun")
.Descendants(GetNamespace() + "Value")
.ElementAt(0);
}

private string GetDbFieldValuesForColumn(int position)
{
return ValueColumns.ElementAt(position)
.Descendants(GetNamespace() + "TextRun")
.Descendants(GetNamespace() + "Value")
.ElementAt(0).Value;
}

public string GetLabelValueFromColumn(int position)
{
return GetLabelColumn(position).Value;
}

public string GetDBFieldNameFromColumn(int position)
{
string value = GetDbFieldValuesForColumn(position);
return (value.Trim().Substring(_FormulaPrefix.Length, value.Length - (_FormulaPrefix.Length + _FormulaSuffix.Length)));
}

public bool CheckColumns(int position)
{
if (LabelColumns.ElementAt(position).Descendants(GetNamespace() + "TextRun").Count() == 1 &&
ValueColumns.ElementAt(position).Descendants(GetNamespace() + "TextRun").Count() == 1 &&
GetDbFieldValuesForColumn(position).StartsWith(_FormulaPrefix) && GetDbFieldValuesForColumn(position).EndsWith(_FormulaSuffix)
)
{
return true;
}

string error = getNotificationForIrraticFields(GetDbFieldValuesForColumn(position), position);
ErrorLog.AppendLine(error);
return false;
}

public void AppendReportParametersTag()
{
if (_xmlDoc.Root != null)
{
if (_xmlDoc.Root.Descendants().Any(child => child.Name.LocalName == "ReportParameters"))
{
XElement reportParameters = (
from c in _xmlDoc
.Descendants(GetNamespace() + "ReportParameters")
select c
).Single();
reportParameters.Add(XElement.Parse(GetReportParametersTag()));

}
else
{
_xmlDoc.Root.Add(new XElement(GetNamespace() + "ReportParameters",
XElement.Parse(GetReportParametersTag())));
}
}
}

public void AppendRdlMethods()
{
if (_xmlDoc.Root != null)
{
_xmlDoc.Root.Add(new XElement(GetNamespace() + "Code", GetRdlMethods()));
}
}

public void AppendCodeModuleTag()
{
if (_xmlDoc.Root != null)
{
_xmlDoc.Root.Add(new XElement(GetNamespace() + "CodeModules",
XElement.Parse(GetCodeModuleTag())));
}
}

public void AddVisibilityTag(int position, string dbColumnName)
{

XElement visibilityElement = new XElement(GetNamespace() + "Visibility");
XElement hiddenElement = new XElement(GetNamespace() + "Hidden",
@"=Code.ManageVisibility(""" +
dbColumnName +
@""",Parameters!UserColumnFilterBlock.Value)");
visibilityElement.Add(hiddenElement);

TablixMembers.ElementAt(position).Add(visibilityElement);

}


private XNamespace GetNamespace()
{
return XNamespace.Get("http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition");
}

public void WriteOutputTemplateColumnInsertToFile(int position, string dbColumnName, string labelValue)
{

_sqlFile.WriteOutputTemplateColumnInsertToFile(position + 1,
ReportOutputTemplateId.ToString(),
dbColumnName,
labelValue);
}

public void InsertFriendlyHeaderMethodCalls(int position, string dbColumnName)
{
GetLabelColumn(position).SetValue(@"=Code.GetFriendlyHeader(""" +
dbColumnName +
@""", Parameters!UserColumnFilterBlock.Value)");

}

private void Save()
{
_xmlDoc.Save(FileName);

// clean up some empty namespaces
Utils.ReplaceInFile(FileName, @" xmlns=""""", string.Empty);
}

private string getNotificationForIrraticFields(string value, int fieldPosition)
{
string msg = "\r\n" +
"Column number " + fieldPosition + " has a data value which looks irratic and therefor will not be processed. \r\n" +
"The column data value is:\r\n" +
value + "\r\n" +
"To do: following parts need to be manually adjusted:\r\n" +
" - Visibility xml element in rdl file\r\n" +
" - the actual value of the data field in the rdl file\r\n" +
" - the value for the name field in the generated .sql file needs to be adjusted\r\n" +
"\r\n";
return msg;
}

private string GetRdlMethods()
{
return Utils.LoadTextFromFile(Utils.GetExecutionPath() + "Resources\\RdlMethods.txt");
}

private string GetCodeModuleTag()
{
return Utils.LoadTextFromFile(Utils.GetExecutionPath() + "Resources\\CodeModuleTag.txt");
}

private string GetReportParametersTag()
{
return Utils.LoadTextFromFile(Utils.GetExecutionPath() + "Resources\\ReportParametersTag.txt");
}
}
}
****
using System.IO;

namespace RdlCorrector
{
internal class SqlFile
{
private StreamWriter file;

internal SqlFile(string filePath)
{
file = new StreamWriter(filePath);
}

internal void DeleteExistingReportOutputTemplateEntries(string reportOutputTemplateId)
{
string s = @"DELETE FROM [WorkbenchReporting_NewFeatures_2010Q4].[Application].[ReportOutputTemplateColumns]
WHERE (ReportOutputTemplateId = " + reportOutputTemplateId + ")";
file.WriteLine(s);
}


internal void WriteOutputTemplateColumnInsertToFile(int position, string reportOutputTemplateId, string spFieldName, string displayName)
{
string s = @"INSERT INTO [WorkbenchReporting_NewFeatures_2010Q4].[Application].[ReportOutputTemplateColumns]
([ReportOutputTemplateId]
,[Name]
,[DisplayName]
,[ColumnGroupId]
,[IsSelectedByDefault]
,[IsCompulsory]
,[Position]
,[CreatedBy]
,[CreatedOn]
,[ModifiedBy]
,[ModifiedOn]
,[VersionNo])
VALUES
(" +reportOutputTemplateId + @"
,'" + spFieldName + @"'
,'" + displayName + @"'
,17
,1
,0
," + position + @"
,'script'
,GetDate()
,'script'
,GetDate()
,1)";
file.WriteLine(s);
}

internal void Save()
{
file.WriteLine("GO");
file.Close();
}
}
}
***
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;

namespace RdlCorrector
{
static class Utils
{
internal static string LoadTextFromFile(string filePath)
{
StreamReader streamReader = new StreamReader(filePath);
string text = streamReader.ReadToEnd();
streamReader.Close();
return text;
}

internal static string GetExecutionPath()
{
return System.IO.Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\";
}

internal static void ReplaceInFile(string filePath, string searchText, string replaceText)
{
StreamReader reader = new StreamReader(filePath);
string content = reader.ReadToEnd();
reader.Close();

content = Regex.Replace(content, searchText, replaceText);

StreamWriter writer = new StreamWriter(filePath);
writer.Write(content);
writer.Close();
}
}
}
*****
namespace RdlCorrector
{
internal static class ErrorLog
{
private const string ERRORLOG_FILENAME = "ErrorLog.txt";

private static readonly string _location;
private static bool _hasContent;

internal static string Location
{
get
{
return _location;
}
}

internal static bool HasContent
{
get
{
return _hasContent;
}
}

static ErrorLog()
{
_location = Utils.GetExecutionPath() + ERRORLOG_FILENAME;
}

internal static void AppendLine(string logContent)
{
if (!_hasContent)
{
//delete potentially existing old file first
File.Delete(_location);
}
File.AppendAllText(_location, logContent + "\n");
_hasContent = true;
}
}
}

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2010-12-30 : 08:46:32
Is there a question here?

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site