Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Append Data Sources 1
- Thread starter btodag
- Start date
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
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
SkipVought
Programmer
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 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,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]
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 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,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]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
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
SkipVought
Programmer
okay, then look for a system data source (Oracle, DB2, etc)
Skip,
[sub]
Just traded in my OLD subtlety...for a NUance!
[/sub]-
1
- #6
PeterDimitrov
IS-IT--Management
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.
Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.
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.
Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.
- Thread starter
- #7
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
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
- Status
Similar threads
- Locked
- Question
- Locked
- Question