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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Other Forums
 Other Topics
 RDL
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 12/30/2010 :  06:35:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 12/30/2010 :  08:46:32  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Is there a question here?

--
Gail Shaw
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000