Post reply

How to create SSRS report with 2 diffrent datasources, Oracle 10g and Sql Server 2005?

  • August 25, 2009 at 7:01 am

    #141115

    I am working on a report (in SQL Server Reporting Services 2005) that has two data sources, one is Oracle 10g and one is Sql Server 2005. I can make the connections to both data sources, and even create the datasets for each in the data tab. My problem is that one dataset needs to "feed" the parameters for the second dataset. The first dataset shows all Orders included in a particular sales region. The user must choose the sales code to get all info about the orders. The second dataset has regions and associated sales. Both datasets have a relatable (join-able) field which is the ZipCodes. I cannot figure out how to join the two datasets.

    Is it possible to create a report in report builder (VS 2005) using two different datasets? How to I create the report if it is feasible?

    Also as the database is Oracle 10g, when I choose date, I don’t get calendar control when I view the report. The user needs to enter the date in ‘yyyy-mm-dd’ format, which is not liked by all users. How do I get the calendar control in the reports?

    Does anyone know how achieve these tasks in SSRS 2005? This is urgent.

  • August 26, 2009 at 6:11 am

    #1044785

    Its been a few months since I have worked with SSRS but if I remember correctly, the current version will not allow you to have 2 data sets on the same report. Can you do something with sub-reports?

    Also, I don't think you can control the format that the report calendar does the date in. If SSRS recognizes the field as a date, then it should allow you to use the calendar option. We used to use Business Objects Crystal Reports and they would allow you to format that date output from the calendar but SSRS isn't quite there.

    Maybe both of these would be possible in future releases? i haven't seen SSRS 2008 yet.

    HTH,

    John

  • August 26, 2009 at 7:32 am

    #1044830

    I solved the problem of calender control by using to_char(to_date(:1),'DD-MON-YYYY') which would and when I set the format as datetime in reportparameters it shows the calender control.

    The other problem I have is how do I get to display multi -value parameters along with date parameters ?

    Regarding the first question you say that it is impossible to create a report from 2 different datasources ?

    Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply