dynamically Set DataSource as XML and the RecordSource a sql query

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

  • 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>

  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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
  • Marked as Answer

    Replied 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
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels