INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Append Data Sources

Append Data Sources

(OP)
Is there any way to append to datasources? I have a spreadsheet that is NAME-2013.xlsx and now of course, they have made a NAME-2014.xlsx. I'd like to use both sets of data in an appended manner.

I'm using standalone CR2011.
Thanks!
Brian Garrett

RE: Append Data Sources

This can be difficult when you're working with Excel data sources. If your data were in a database such as MS SQL Server, you could just write a command (SQL Select statement) that would union the two tables together. At that point you could sort the data however you want it.

With Excel, you will probably have to use a subreport in the report footer for the second spreadsheet. In this case, you can only sort with the spreadsheet that's in the main report first and then sort the spreadsheet that's in the subreport. It will also be difficult to do any summary calculations (counts, etc.) if necessary - you would have to use shared variables to get information from the subreport to the main report.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Append Data Sources

brian,

This is a typical problem when you choose a REPORT as a data source. I'd suggest that the first thing that you ought to do, if at all possible, is 1) to find what data source(s) these Excel reports used and 2) to find thoses sources in your BO universe.

Often that is not possible, so the second question I'd ask is, does YOUR report need to be generated in CR or might Excel be possible? It would be much easier to generate your report in Excel, seing that the data sources are Excel workbooks.

If you decide to proceed with Excel, you might post in forum707: VBA Visual Basic for Applications (Microsoft) for help if you need it to programatically query each workbook and join the results. Rememeber that in subsequent years you will likely need to join another year's data from another Excel workbook.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Append Data Sources

Skip,

As Brian states, he's using stand-alone CR 2011, so there's no BO or universe involved in the process.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Append Data Sources



okay, then look for a system data source (Oracle, DB2, etc)

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Append Data Sources

You may consider to use an Access ( or any other) database as a "proxy".

Create an empty Access database and add the excel files as linked tables. Create a query (view) to union the data. The query will look similar to this one:
SELECT a,b,c,d FROM Worksheet1
UNION
SELECT a,b,c,d FROM Worksheet2

Use this query as a datasource for your report. The advantage of this method is that if your datasource changes again you will change the proxy , not the report. For example next year, when you have NAME-2015.xlsx, you will need just to add it as a linked table and update the query.

www.R-Tag.com Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

RE: Append Data Sources

(OP)
Thanks for all of the replies! I've looked at using an access database as a proxy and may go that way.

I've tried similar things before, but also added complexity by the access database being linked to a sharepoint list. It didn't seem to work for me.
Thanks everyone!
Brian

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close