C1FlexReport SQL Server query with 2 tables from 2 databases

C1FlexReport SQL Server query with 2 tables from 2 databases

Posted by: saidnai on 9 May 2018, 12:57 am EST

• 

  saidnai

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 9 May 2018, 12:57 am EST

  Dear All,

  I met a Problem with C1Flexreport as follow:
  
  I have one query running well on the SQL Server. The query get informations from 2 databases on the same Server:
  
  Sample:
  
  SELECT a.field1, a.field2, e.field1, e.field2, e.filed3
  
  FROM dbo.QRY1 a LEFT OUTER JOIN
  
  OPENROWSET(‘SQLOLEDB’, ‘111.11.1.111\Sessionname’; ‘xxxxxx’; ‘’, DB2.dbo.QRY2) e ON a.field1 = e.field1

  Unlikely the C1Flexreport does not recognize the query. Is there a solution for this issue?

  Best regards
  
  Said

• 

  esha.dhir

  

  • Post Options:
  • Link
    Copy

  Posted 9 May 2018, 8:44 pm EST

  Hello Said,

  We are investigating on the issue and will let you know once it is done.

  Best Regards,
  
  Esha

• 

  saidnai

  • Post Options:
  • Link
    Copy

  Posted 10 May 2018, 5:23 pm EST

  Hello Esha,

  Thanks a lot. waiting and for your Feedback. Please inform me for the solution per email.

  Best regards
  
  Said

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 17 May 2018, 12:03 am EST

  Hi, Said
  
  FlexReport uses standard ADO.NET mechanics to connect to database DbConnection, DbCommand etc. So if your query can be execucted by code like this:

  
              string dsPath= Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments).ToString();
              OleDbConnection con = new OleDbConnection();
              con.ConnectionString = @"....";
              con.Open();
  
              var com = con.CreateCommand();
              com.CommandText = @"...";
  
              OleDbDataAdapter da = new OleDbDataAdapter(com);
              DataSet dataset = new DataSet();
              DataTable table = new DataTable();
              dataset.Tables.Add(table);
              da.Fill(table);
  
  

  then it can be used in FlexReport.
  
  I have tried to create simple report using my local SQL server and NORTHWND database, query:

  
  select * from
  OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;',  
       'SELECT *
        FROM NORTHWND.dbo.Categories')
  
  

  works OK.
  
  I have attached this report

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 17 May 2018, 4:10 pm EST

  Forgot to attach a reportrep.flxr.zip

• 

  saidnai

  • Post Options:
  • Link
    Copy

  Posted 30 May 2018, 7:37 pm EST

  Dear Manuzin,

  Thanks for the reply.
  
  Can you please provide a sample executable code in VB please not in C#. And to do what?
  
  I explain the procedure as follow:

  1. I have one SQL-Server.
  2. I have 2 databases DB1 and DB2.
  3. In DB1 I have a query for the production orders.
  4. In DB2 I have the usage of the raw material used for the each PO.
  5. In DB1 I have another query where the query of the PO is bind to the query in DB2. This
     
     is working fine.
  6. The Flexreport does not accept this.
  7. You send to me a solution and you mean I should place it in the code. But where?

  8. Following is my code to run the flexreport:
     
      SqlStr = "SELECT * From DB1.QueryCost WHERE YYYYMM = '201805'"
      Cursor = Cursors.WaitCursor
      FV.StatusText = "Loading " + Glb_RptName2
      FR.Load("c:\" & Glb_RptName1, Glb_RptName2)
      FR.DataSource.ConnectionString = "Driver={SQL Server};Server=999.99.9.999\Session       
      name;DataBase=DB1;Uid=XXXX;Pwd=xxxx;"
      FR.DataSource.RecordSource = SqlStr
      FV.StatusText = "Rendering " + Glb_RptName2
      FV.DocumentSource = Nothing
      FV.DocumentSource = FR
      Me.Cursor = Cursors.Default
      FV.Focus()
     

  Best regards
  
  Said

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 4 June 2018, 12:56 am EST

  I have attached a sample, sample renders a report using SQL query:

  
  select p.ProductID, p.ProductName, p.CategoryID, c.CategoryName from Products p left outer join (select * from OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'SELECT * FROM NORTHWND2.dbo.Categories')) as c on p.CategoryID = c.CategoryID
  

  I.e. this query joins two tables Products (NORTHWND database) and Categories (NORTHWND2 database) and all works as expected.

• 

  alexander.manuzin

  • Post Options:
  • Link
    Copy

  Posted 4 June 2018, 12:57 am EST

  Sample attachedWindowsApp1.zip