Crystal Reports - Search Dependencies

Question

Is there a tool that will let you search a number of different crystal reports to see where a specific Table/View/SP is being used?

Scenario is this: We have over 200 reports, so when making a change to a View or Stored Procedure it is not easy to find which reports will be affected without opening each one and checking the "Database expert" or "Datasource location".

I have tried Agent Ransack on them and it doesn't pick any table or view names up.

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

See the question here: Any way to search inside a Crystal Report

Another option is to roll-your-own piece of software to do it, but that might be more time consuming than you're looking for. Or, find someone who already has done this :) If you find something that works, let the rest of us know because we're all in the same boat. Good luck!

edited May 23 '17 at 11:56

Community

1
1

answered May 25 '12 at 15:15

Ryan

7,212
1
17
30

score 1 · Accepted Answer · answered Jun 28 '12 at 20:45

I never found a tool to do this, so rolled my own in C# .Net 4.0.

If the crystal report uses a 'SQL Command' instead of dragging in tables, it becomes a bit tricky. I suggest only searching for the TableName rather than the fully qualified Database.dbo.TableName - since this may have been omitted in the pasted SQL Command.

usage:

var reports = CrystalExtensions.FindUsages("C:/Reports", "table_name");

code:

namespace Crystal.Helpers
{
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Reflection;
    using CrystalDecisions.CrystalReports.Engine;
    using Microsoft.CSharp.RuntimeBinder;

    public static class CrystalExtensions
    {
        public static List<string> FindUsages(string directory, string tableName)
        {
            var result = new List<string>();

            foreach (var file in Directory.EnumerateFiles(directory, "*.rpt", SearchOption.AllDirectories))
            {
                using (var report = new ReportClass { FileName = file })
                {
                    if (report.Database == null) continue;

                    var tables = report.Database.Tables.ToList();
                    var hasTable = tables.Any(x => x.Name == tableName || x.GetCommandText().Contains(tableName));

                    if (hasTable)
                        result.Add(file);
                }
            }

            return result;
        }

        public static List<Table> ToList(this Tables tables)
        {
            var list = new List<Table>();
            var enumerator = tables.GetEnumerator();

            while (enumerator.MoveNext())
                list.Add((Table)enumerator.Current);

            return list;
        }

        public static string GetCommandText(this Table table)
        {
            var propertyInfo = table.GetType().GetProperty("RasTable", BindingFlags.NonPublic | BindingFlags.Instance);

            try
            {
                return ((dynamic)propertyInfo.GetValue(table, propertyInfo.GetIndexParameters())).CommandText;
            }
            catch (RuntimeBinderException)
            {
                return ""; // for simplicity of code above, really should return null
            }
        }
    }
}

Hope that helps!

Incredibly useful... Thank you. I used your knowledge to write a simple console tool for myself and I am going to look into writing a Visual Studio custom extension or something that can validate references to SQL database projects from the Crystal reports. We have so many of those reports..... *shudder* — tuespetre, Mar 07 '14 at 06:44

Crystal Reports - Search Dependencies

2 Answers2