none
changing datasource of a report RRS feed

  • Question

  • I need help in filling the report with data from a query as apposed to a dataset/data adapter

     

    right now i have multiple reports and one reportViewer... based on what report needs to be generated i do the following:

    add/set the datasource

    Code Block

    ReportDataSource.Name = "ReportsDataSet_Schedule"

    ReportDataSource.Value = Me.ScheduleBindingSource

    Me.ReportViewer.LocalReport.DataSources.Add(ReportDataSource)

    Me.ReportViewer.LocalReport.ReportEmbeddedResource = "ReportSchedule.rdlc"

     

     

    fill in the report by calling one of these dataadapter fill query:

    Code Block

    Me.ScheduleTableAdapter.FillDate(Me.ReportsDataSet.Schedule, Today.Date)

     

    Me.ScheduleTableAdapter.FillDateRange(Me.ReportsDataSet.Schedule, DateFrom, DateTo)

     

    Me.ScheduleTableAdapter.FillSpecificDateRange(Me.ReportsDataSet.Schedule, DateFrom, DateTo, UserID)

     

     

    what i want to be able to do is... instead of calling a pre configured fill query.. i want to make the select query in code (which i am already doing) and then fill in the report based on the query i create. how can i do this? can i add a data adapter to the dataset and then call the query (like i do above?) or can i just pass the query to the report?

     

    hope this all makes sense!!

    really appreciate all the help!! 

     

    thanks!

    Tuesday, December 4, 2007 9:43 PM

Answers

All replies

  • You can do any and all of those things <s>.

     

    You could actually pass the whole query to the report as a string if you want, and if you are already pre-fabricating it, or you could fabricate it within the report itself. Please see this walkthrough for a very complete explanation:

     

    http://spacefold.com/lisa/post/Writing-Dynamic-SQL-in-and-for-RDLs.aspx

     

    If you would prefer to create the dataset dynamically and then bind the dataset to the report at runtime, you can do that as well.  Please see here:

     

    http://spacefold.com/lisa/post/Dynamically-loading-reports-in-ReportViewers.aspx

     

    ... covers more than you need, because the reports are being loaded dynamically as well as what data is going into the reports... but you will find what you need.

     

    >L<

     

     

     

     

    Wednesday, December 5, 2007 6:03 PM
  •  

     

    Hi Lisa,

     

    I read through your posts…

    I know this must be simple but I just can’t seem to get it Sad

     

    I have a form where users can select a report from a dropdown list and there are a bunch of filters/criteria that they can set for the report.

     

    Based on their selections I build a query, for example, if they choose Income Summary report where the amount for a charge is greater then $50.00. So the query would look like this:

     

    SELECT     SUM(tblAcctgXref.AcctXref_Amount) AS TotalAmount, tblCharges.Charge_name

    FROM         tblAcctgXref INNER JOIN

    tblAcctgCharges ON tblAcctgXref.AcctXref_ChgId = tblAcctgCharges.AcctChg_ID INNER JOIN

                      tblCharges ON tblAcctgCharges.AcctChg_ChargeID = tblCharges.Charge_Id

    WHERE     (tblAcctgXref.AcctXref_Amount > 50)

    GROUP BY tblCharges.Charge_name

    ORDER BY tblCharges.Charge_name

     

    I have another form, ReportViewer, which is used for viewing the report. On the load event of this form I reset the viewer and set the ReportEmbeddedResource to the specified report, like this:

     

    Me.ReportViewer.Reset()

    Me.ReportViewer.LocalReport.ReportEmbeddedResource = "AAM.ReportIncomeSummary.rdlc"

     

    'Fill in Report Data with query

     

    Me.ReportViewer.RefreshReport()

     

    Now, how do I populate the report with the results from the above query?

    The report has only two columns, Description (tblCharges.Charge_name) and Amount (TotalAmount)

     

    Your help is really appreciated

    Thanks

     

    Thursday, December 6, 2007 7:32 PM
  •  

    Below is how you can create a report data source, attach it the report.  Now if you want to do dynamic reports, then you'll really need to read and understand the links that Lisa sent you.

     

    In regards to how you fill your dataset, I recommend you go do some reading on ADO.net...since a .RDLC file doesn't really care how you filled the datatable, it's just going to read from it and show the fields on the report that match the fields in the datatable.

    DataSet ReportDataSet = new DataSet();

     

    //the code to fill the dataset with your data

     

    Me.ReportViewer1.Reset();

    Me.ReportViewer1.LocalReport.ReportEmbeddedResource = "AAM.ReportIncomeSummary.rdlc";

     

    ReportViewer1.LocalReport.DataSources.Clear();

     

    ReportDataSource reportInfo = new ReportDataSource();

     

    reportInfo.Name = "ReportDataset_ReportData1";//the information for the report

    reportInfo.Value = ReportDataSet.Tables[0];

     

    ReportViewer1.LocalReport.DataSources.Add(reportInfo);

     

    Me.ReportViewer1.RefreshReport();

    Friday, December 7, 2007 8:49 PM