Renaming tables in Crystal Reports


From the May 2010 GP Reports Viewer newsletter comes a tip for renaming the tables (or views or stored procedures) on Crystal Reports. Some may ask why this in needed – I actually use this quite a bit in the following scenario.

I often find that I am using one report as a template for another when working with Dynamics GP tables because the table structure is the same for many work, open and history tables. For example, if I am creating two versions of an SOP invoice – unposted and posted – most of the tables and fields are the same except for the two main tables. In that case, I just need to change SOP10100 to SOP30200 and SOP10200 to SOP30300. This is pretty straight forward to do in Crystal, however the name of the changed tables will still show with the original name almost everywhere, which can be quite confusing, especially when others may be working on the reports after you are done with them. You can change the display name of any table, view or stored procedure on a Crystal Report using the steps below.

Let’s start at the beginning. I have an SOP Unposted Transaction report that pulls from the following tables, shown in Crystal’s Field Explorer:

I update the tables by going to Database > Set Datasouce Location, selecting the table I want to change under Current Data Source, selecting the table to replace it with under Replace with and clicking Update: 

I repeat these steps for every table I need to change, however the list at the top (under Current Data Source) looks unchanged. Only if I expand the Properties for each table will I see the actual table name:

In Field Explorer I still see the old table names. Very confusing, especially when I come back and work on this months or years after the fact. So how do I change this? Here are the steps:

Open the Database Expert (Database > Database Expert) and find the table in the list on the right under Selected Tables:

Right click on the table, choose Rename:

Type in the new table name:

Click OK when done. You will see the updated names in Field Explorer immediately:

If you would like to see more tips like this, take a look at our GP Reports Viewer newsletter archive. We have recently added a section for SSRS tips to the newsletter, so take a peek at those if you are starting to work with SSRS. If you are looking to print Crystal and SSRS reports in Dynamics GP, check out our GP Reports Viewer. We have demo videos and fully functional downloads available.

22 Responses to “Renaming tables in Crystal Reports”

  1. Thanks, It was very useful to me.

    Sundar

    Like

  2. Thank you. I used this method to change the use of a view to use a command. Save a major amount of time.

    Like

  3. Great!!!
    Thank you so much..

    Like

  4. Your explanation helped me a lot.
    Thanks Victoria
    Agustin

    Like

  5. Thank you very much for solution, It really helped me lot.

    Like

  6. Thanks for the solution. It really helped me alot

    Like

  7. When I try this, my new name for the table is reduced to lowercase every time. Is this a bug? Your screenshots would seem to indicate that you found a way around this.

    Like

    • Hi Mark,

      I have never done anything special for this – it just works by following the steps I have outlined. I have definitely done this in Crystal 2008 and Crystal XI with no issue, probably older versions, too, but it’s been a really long time sine I used anything prior to XI. I just tested again with Crystal 2008 SP 2 and whatever I enter (either lower case or upper case) shows up exactly as I enter it. What version of Crystal Reports are you using?

      -Victoria

      Like

  8. Hello from England!

    Changing the table name one at a time is all well and good but is there a way of getting Crystal to always display the actual table name? Something in Options perhaps? I’ve never managed to find it but it would be very handy. Nice site by the way. Regards, Matt

    Like

    • Thanks Matt,

      I am not aware of any way tp have Crystal always display the actual table name. Otherwise, I would have definitely blogged about that instead of changing the tables one at a time. 😉 If you find something like that, please let me know.

      -Victoria

      Like

  9. Thank you!!! I have spend many, many hours experimenting with this or that, trying to rename the table that serves as the datasource. I felt that there had to be a way to do it, but had just about given up. Thank you for taking the time and trouble to post this solution!

    Dave

    Like

  10. Victoria,

    Great post! I’ve done this a number of times myself, especially when using a third-party such as Forms Printer’s template Crystal or SRS reports.

    This post was timely as well, since a client actually was thrown off lately as a view in the Crystal Report she was using appeared to not even exist in the GP database. I’ll foward this post along to her.

    Thanks
    Les

    Like

    • Thanks Les,

      Had exactly the same situation with a customer, that’s what prompted me to remember this trick. Good to know it’s helpful to others.

      -Victoria

      Like

      • Hi Victoria. I have Crystal Reports XI user that is trying to change the name of a materialized view on several reports with formulas that reference the view. I’m assuming that following your directions will also change the formulas? (I am not currently using Crystal, so wanted to check before I sent my user the steps you so wonderfully outlined.) Thanks.

        Like

  11. The scenario that you have mentioned as instance; I have also faced the same thing in my consulting. Normally what I have come across is that writing a UNION on SOP10100/SOP10200 and SOP30200/SOP30300 tables and maintain a single report. But in such cases, the performance is often degraded, if especially it has to go thru’ all records in these tables.

    One reason developers point out (including me till now): Maintaining two reports for same kind of document is very difficult.

    Nevertheless, this tip is quite handy.

    Vaidy

    Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | Renaming tables in Crystal ReportsRenaming tables in Crystal Reports - DynamicAccounting.net - April 28, 2016

    […] Victoria Yudin brings reporting tips on renaming tables in Crystal Reports. […]

    Like

  2. Crystal Reports Viewer 2008 | Home - March 22, 2016

    […] Renaming tables in Crystal Reports | Victoria Yudin – From the May 2010 GP Reports Viewer newsletter comes a tip for renaming the tables (or views or stored procedures) on Crystal Reports. Some may ask why this in … […]

    Like

  3. DynamicAccounting.net - July 21, 2010

    Renaming tables in Crystal Reports…

    Victoria Yudin brings reporting tips on renaming tables in Crystal Reports ….

    Like

  4. Dynamics GP - Learn and Discuss - July 21, 2010

    Rename Tables in Crystal Reports – Victoria…

    Another nice tip from Victoria on How to Rename Tables in Crystal Reports . And this tip is quite handy…

    Like

  5. Microsoft Dynamics GP Learn & Discuss - July 20, 2010

    Rename Tables in Crystal Reports – Victoria…

    Another nice tip from Victoria on How to Rename Tables in Crystal Reports . And this tip is quite handy…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: