Help with dynamic datasource and record selection

[Former Member]

<span>Hi, I&#39;m having some difficulty with part of an application I&#39;m trying to build, but here&#39;s some background first.<br /> <br /> I am a student at the University of Maryland, and I&#39;m writing an application for one of the departments here at the university. It is sort of a front-end for an access database with a lot of extra functionality. This includes reports. First of all, the user can change the databse that the application uses, so I store that in an appsetting. For this reason, I have to tell the report what database to use at runtime rather than using the database expert. I am building a sort of report wizard in which the user selects certain values, and based upon those values, the SQL statement that the report gets data from changes. So almost everything about the report&#39;s data is dynamic and will be determined at runtime. <br /> <br /> So for example if the user chooses an officer report by chapter names, I do this (chapters is a comma delimited string):<br /> <br /><span style="font-weight: bold"> string sel = "SELECT * FROM [Undergrad Leadership] WHERE [Leadership Position] = &#39;" + txtPosition.Text + "&#39; AND [Chapter Name] IN (" + chapters + ");";</span><br /> <br /> The part I am lost on, is how to actually interface with crystal reports. In the following code, conn is an OleDbConnection to the correct database, rep is the ReportDocument, and view is a CrystalReportsViewer.<br /> <br /><span style="font-weight: bold"> rep.DataSourceConnections[0].SetConnection(conn.DataSource, conn.Database, false); <br /> rep.RecordSelectionFormula = sel;</span><br /><span style="font-weight: bold"> view.ReportSource = rep;</span><br /> <br /> When I try to load the report, I get the following error message:<br /> Error in formula <Record Selection>: a number, currency amount, boolean, date, time, date-time, or string is expected here.<br /> <br /> Also, assuming this formula gets fixed, how do I actually get fields from this formula and datasource onto my report, since I can&#39;t do it at design time? Thanks in advance for the help.<br /> <br /> -Jared<br /> </span>

[robert_horne · Accepted solution]

<p>RCAPI (Report Creation and Modification) calls are only available with Crystal Reports Server RAS SDKs. This means that using the bundled version of Crystal Reports for Visual Studio will not allow you to place fields onto the report. A free copy of Crystal Reports Server comes with a registered copy of Crystal Reports Developer. My guess is that you don&#39;t have either of these products and that you are just using the product that came with Visual Studio.</p><p>All is not lost. The first question is, do you really need to add the fields at runtime? In many cases developers just want to have control of the data that gets sent to the report and they are ok with having the same fields display.</p><p>In your code you are changing the datasource which is fine, and you are assigning a Record selection formula which is also fine. </p><p>The problem with the record selection is that it doesn&#39;t fit the syntax of the Report. I would suggest printing out the value that you programatically get for the formula and insert it into the Crystal Report Designer. You will probably get the same error there and gets some hints as to why it doesn&#39;t work.</p><p>What I would suggest is creating a template report that already has the fields on the report and then change its datasource at runtime and add a RecordSelectionFormula.</p><p>Otherwise you will have to use RAS to be able to add fields at runtime. </p><p>Rob Horne<br /><a href="/blog/10">Rob&#39;s blog - http://diamond.businessobjects.com/blog/10</a></p>;

[Former Member · Accepted solution]

<p>That is actually what I&#39;m doing. I guess what I&#39;m asking is how can you put a database field in the report through the designer when the database and selection formula aren&#39;t added until runtime. How will the field know what sql statement to get the data from. In my current design, I add the database with the database expert and have a sql command called command. Then I can easily add fields like &#39;command.Chapter Name&#39; and command.Type. Thanks for the help.</p><p>&nbsp;</p><p>EDIT: Actually I suppose if the datasource and selection formula that I insert at runtime have the same tables and columns as the one I used at design time, then it would work fine. </p><p> Jared <br /></p>

[Former Member]

<p>How to dynamically set datasource for an rpt file at the runtime in a jsp???</p><p>Thank you.</p>