Dynamically Set DataSource as XML and the RecordSource a sql query

Dynamically Set DataSource as XML and the RecordSource a sql query

Posted by: ziane.djebbari on 1 April 2020, 5:19 am EST

• 

  ziane.djebbari

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 1 April 2020, 5:19 am EST

  Hello All,
  
  anyone has experience to set the properties of the DataSource of an C1FlexReport as dataSource from a structured XML file and keep using the RecordSource as a SQL Select Statement.
  
  the issue is when at design time of the Flex Report, we make use of a snapshot of the data as OLEDB DataProvide with all the data available. we set the RecordSource as a SQL Query.
  
  at RunTime, we expect to supply the data as an XML file and we would like to keep the RecodSource inchanged.
  
  Is it possible

  
  DataSource>
          <Name>Main</Name>
          <DataProvider>OLEDB</DataProvider>
          <ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\DataSources\Data_01.accdb;Persist Security Info=False;</ConnectionString>
          <RecordSource>SELECT a.*, b.* 
  FROM a 	INNER JOIN b ON a.a_Id = b.a_Id	
  </RecordSource>
        </DataSource>
  
  

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 5 April 2020, 9:05 pm EST

  Hello,

  You can directly bind the XML datasource at the design as Flexreport support the XML datasource as shown in the following link:
  
  https://www.grapecity.com/componentone/docs/win/online-flexreport/DataSourcesinFlexReport.html

  Also, you can change the connection string and record set at runtime without chaning the recordset in the report:
  
  https://www.grapecity.com/componentone/docs/win/online-flexreport/RetrievingDatafromaDatabase.html

  Hope it helps.

  Thanks,
  
  Mohit

• 

  ash3650

  • Post Options:
  • Link
    Copy

  Posted 6 April 2020, 4:37 am EST

  Hi Mohit,
  
  How about if you want to keep the SQL string (RecordSource Property) and at runtime you want to supply a DataSet that does have all the Tables/Columns that where expected by the SQL Query? is this possible
  
  Regards

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 6 April 2020, 5:34 pm EST

  Hello,

  Yes, you can change the record set at runtime using the following line of code:

  
  // load DataTable from cache or from a secure/custom provider
  DataTable dt = GetMyDataTable();
  
  // load report definition (before setting the data source)
  c1FlexReport1.Load(@"reportFile", "reportName");
  
  // use DataTable as the data source
  c1FlexReport1.DataSource.Recordset = dt
  
  

  Hope it helps.
  
  Thanks,
  
  Mohit

• 

  ash3650

  • Post Options:
  • Link
    Copy

  Posted 12 April 2020, 11:11 pm EST

  Hello Again,
  
  could you please advise on the best approach to make this relationship between a parent and a sub-report working if you do have only XML data to work with, not a back-end database that let you do query.
  
  the idea to have 2 or more table joined using SQL, but used for XML data source.

  
  SELECT MyParent.ParentName, MyParent.MyParent_Id, MyChild.StartDateTime, MyChild.MyChild_Id
  FROM MyParent LEFT JOIN MyChild ON MyParent.MyParent_Id = MyChild.MyParent_Id;
  
  

  so instead to work with SubReport field, make use of a single query that will avoid to do the OnFormat

  
  Report.ParentReport.Fields("ChildStartDateTime").Value = StartDateTime
  
  

  Please take a look at the attached files.
  
  thanks in advance

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 14 April 2020, 7:22 pm EST

  Hello,

  It seems that files are not attached properly. Could you please attached your files again.

  Please note that it only accept zip and image format file.

  Thanks,
  
  Mohit

• 

  ash3650

  • Post Options:
  • Link
    Copy

  Posted 20 April 2020, 11:05 pm EST

  Hello Mohit,
  
  Please find the attached zipped files
  
  thanks in advance for your guidance and help.
  
  Regards
  
  DataSourceAsXML.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 21 April 2020, 10:27 pm EST

  Hello,

  In my understanding, you have a parent report and SubReport which bound with XML datasource. Now, you want to set the xml datasource at runtime and filter the subreport datasource as per parent datasource parentid. To Filter the data, you need to use OnFormat event to set the subreport as per parentid.

  I think you are using the same approach at your end.Request to please confirm if I am missing something.

  Thanks,
  
  Mohit