Skip to Content
author's profile photo Former Member
Former Member

Summarize from two data sources

I am not sure how to summarize data from two data sources (SQL Server and Access). I know how to do it from each data source one at a time (by using grouping and hiding the details), but my internal customer wants it all in one report linked by username. My trick does not extend to two data sources because there is not a one-to-one relationship between the records.

Here's a simplification:

Data source 1: SQL Server

Username, Time Interval, Quantity, Time

Bob, 7/1/2008 12:30pm, 5, 10

Bob, 7/1/2008 1:00pm, 1, 2

Data source 2: Access

Username, Date, Quantity, Time

Bob, 7/1/2008, 500, 5

Bob, 7/1/2008, 400, 4

Bob, 7/2/2008, 100, 1


Username, AVG Time/Unit 1, AVG Time/Unit 2

Bob, 2, 1

I use Crystal Reports Advanced 10.0 to embed reports in [CIC|] 2.3.1.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jul 31, 2008 at 09:41 PM

    I think by using subreports we can calculate the summaries. Create a main report with the SQL table and create a subreport with Access Table and insert the subreport in group header or footer of the field UserName and link the subreport with Username and calculate the summaries in main report and in subreport and pass the summaries from sub report to main report using shared variables.



    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I think I found my solution. For SQL Server 2000, I wrote a pivot table SQL query to put into Crystal as a "command." Then in Access, I added a "group by" query. Now I should be able to link the two queries (considered tables in Crystal) with a one-to-one relationship on the date and person columns.

      I appreciate everyone's help

  • Posted on Jul 31, 2008 at 10:04 PM

    Query different datasources

    Most of the ppl here asking about how could they generates reports from different datasources using businss objects product.

    like someone who has an Oracle database and another SQL Server database and he wants to create a crystal repot getting data from both of the databases.

    actually there two ways to do that.

    1) Business views manager.

    for the business views manager its an easy tool you use to integrate the structure of different datasources even if they are from different vendors to be able to get the data into one report, and this tool is embded into the XI R2, and XI 3.0 as well.

    you just login and create your connection to the data sources you want to get the data from, after the step of creating the connection you create something called data foundation, in this step you can join and arrange the relations between the tables from all the connections you have created before.

    lets say that there is a table T1 in Connection C1, and there is another table T2 in connection C2. and then you want to make some joins from T1 ot T2. like you say C1.T1.ID = C2.T2.ID,, this relationship comes from different datasources , but in the datafoundation they mapped together and you can see them as they are from one datasource, after this step you create business views and business elements which will be used from your crystal reports.

    once you finish the desing and the development stage in business views manager, you export those things to the repository which it will save them as repository objects.

    in the crystal reports, you can now create a report using business views, which in turn will connect to different data sources.

    2) Business Objects Data Federator.

    business objects data federator, is a very usefull product from business objects which allows you to integrate the structure of many datasources in one datasource, only the structure, not the data, the data will be reside on their own datasources, will not be moved anywhere like data integrator does.

    the data federator used a query server which save a complex statements based on how you did your design and development process among the different datasources you have selected before.

    from any other business objects product now you can access this query server as you can access a normal datasource with specific ways of connections described in the manual, you can also create universes ,crystal reports ,,, etc over this query server datasrouce.

    i hope this was helpful

    thank you

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.