Question

NEG1934 on Wed, 03 Aug 2016 15:04:30


I am trying to develop a report with Report Viewer in VS 2013 with a subreport. Now both the main data source and the sub report data source are stored procedures with parameters. I can get my main report to run with getting parameters from the controls on the form. From my testing I can get the parameter that needs passed from my main report to my subreport but it does not filter the results out based on that parameter. I am at a complete lost. 

Here are my two data sources, ObjectDataSource3 is my main data source and ObjectDataSource4 is my sub report data source:

        <asp:ObjectDataSource ID="ObjectDataSource3" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="FS2.FS2_v2DataSetTableAdapters.spselNonCompliantTableAdapter" OnSelecting="ObjectDataSource3_Selecting">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlProjectName" Name="ProjectId" PropertyName="SelectedValue" Type="Int32" />
                <asp:ControlParameter ControlID="txtRoute" Name="Route" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="txtZone" Name="Zone" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="txtNecoSection" Name="NecoSection" PropertyName="Text" Type="String" />
                <asp:ControlParameter ControlID="ListBox1" Name="LogTypeInclude" PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
        </asp:ObjectDataSource>

        <asp:ObjectDataSource ID="ObjectDataSource4" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="GetData" TypeName="FS2.FS2_v2DataSetTableAdapters.spselSubRepNonCompliantEventsTableAdapter">
            <SelectParameters>
                <asp:Parameter DefaultValue="" Name="AccountId" Type="Int32" />
            </SelectParameters>
        </asp:ObjectDataSource>

protected void Button1_Click(object sender, EventArgs e)
        {

ReportViewer1.LocalReport.SubreportProcessing += new SubreportProcessingEventHandler(SetSubDataSource);

            

            ReportViewer1.LocalReport.SetParameters(reportParameterCollection);

        }

        public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {
e.DataSources.Add(new ReportDataSource("FS2DS", "ObjectDataSource4"));

        }

I have my in my main report rdlc the sub report with its name properly spelled with a parameter to be passed. If I give the sub report a default parameter from the form, it will run but will only use that parameter even though if I display the parameter being passed from the main report it is different. 


Sponsored



Replies

NEG1934 on Wed, 03 Aug 2016 17:31:59


Looking more at it. My main problem is how do I tied the AccountId from the Main Report Data Set to my Sub Report Data Set?

CoolDadTx on Wed, 03 Aug 2016 19:45:24


In your subreport you will have defined the parameter it needs. When you add the subreport to the parent report you then need to assign the parameter for the subreport whatever value it should use from the main report. This is done via the designer and can be based upon a value in the tablix you're using, a calculated value or a parameter passed into the main report. The subreport will only reference its parameter, the main report is responsible for setting that parameter.

Michael Taylor
http://www.michaeltaylorp3.net

NEG1934 on Wed, 03 Aug 2016 20:05:16


I currently have that setup already. Except my sub report is not returning results based on the parameter being passed to it from the main report. It is only passing in a parameter from the form that displays the report where the objectdatasource is for the sub report. If I remove that data source then I get the message that it cannot results because no data source is being attached to it. 

To test my theory on this, on the form I gave my sub report data source a default value. Then on the sub report I displayed the parameter passed in or @AccountId along with the query results. For every record on my main report it would show results of my sub report where the parameter was set by the default value, even though displaying its @AccountId showed it was getting the proper value from the main report. But my datasource is not basing its results off of the parameter passed in from the main report, only through the form. Hope that makes sense. 

CoolDadTx on Wed, 03 Aug 2016 20:40:11


In your subreporting processing you aren't ever setting the parameter of the child data source. The parameter value should be passed in as part of the event arguments. You should be able to set the child data source's parameter based upon that value. If that doesn't work then please post the updated handler code.

NEG1934 on Thu, 04 Aug 2016 12:18:03


I am not sure how to do that. Here is my event handler in my form that calls the report. FS2DS is the Dataset from the subreport, and ObjectDataSource4 is the object on the form that points to the stored procedure for the sub report. 

public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {

            e.DataSources.Add(new ReportDataSource("FS2DS", ObjectDataSource4));
            
        }

CoolDadTx on Thu, 04 Aug 2016 13:56:49


I guess I'm not convinced that you really need that subreport event. It's been a while since I've done local RDLCs that have subreports but I don't remember ever having to use that event.  Historically all I've ever done is add all the data sources to the root report's data source list.  Inside the report designer the subreport is generally embedded in the tablix and therefore it has access to the rows returned by the main report's data source. Inside the designer, when setting up the subreport parameters I've set the parameter to the column in the tablix it was nested in and it just works.

So can you exclude the event call, move the subreport data source creation into the main report's data source list and then verify that subreport is using the correct column from the tablix (assuming you nested it)? If that doesn't work then do you have a simple repro that I can look at?

Rich P123 on Thu, 04 Aug 2016 17:51:04


One thing you could try is this -- instead of placing a subreport in your rdlc -- create 2 rdlc's and (assuming you are saving down as .pdf) you can use iTextSharp.dll in your project and merge the pdf results into one .pdf.  You can download iTextSharp.dll for free and there are several tutorials on how to merge .pdfs together.

Here's (sort of) an example (mix of VB.Net and C#)

ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("AttachmentEntity", photos))

photos here is a List<AttachmentEntity>  where AttachmentEntity is a class with some properties (fields).

then I have another procedure -- ViewFile -- where PdfContent is where you convert your rdlc to a pdf (byte array)

ViewFile("attachment; filename=QuadChart.pdf", "application/pdf", PdfContent)

here PdfContent is a byte array where we convert the rdlc to pdf format

Public ReadOnly Property PdfContent As Byte()
  Get
    LoadReport()
    Return ReportViewer1.LocalReport.Render("PDF", Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
   End Get
End Property

LoadReport is where I create the Datasource (ReportViewer1.LocalReport.DataSource.Add(...as above)

and here is the code for ViewFile (in VB.Net land right now)

Public Sub ViewFile(filename As String, mimetype As String, filecontent As Byte())
    Response.Buffer = False
    Response.Clear()
    Response.AddHeader("Content-Disposition", filename)
    Response.ContentType = mimetype
    Response.OutputStream.Write(filecontent, 0, filecontent.Length)
    Response.SuppressContent = True
    HttpContext.Current.ApplicationInstance.CompleteRequest()
End Sub

And in the C# part I merge several of these rdlc reports (pdf) into one pdf -- using iTextSharp;

public static byte[] MergeFiles(List<byte[]> sourceFiles, string fileHeader)
{
    Document document = new Document();
    MemoryStream output = new MemoryStream();
    try
    {
       try
       {
         // Initialize pdf writer
         PdfWriter writer = PdfWriter.GetInstance(document, output);
         writer.PageEvent = new PdfPageEvents(fileHeader);

          // Open document to write
          document.Open();
          PdfContentByte content = writer.DirectContent;

          // Iterate through all pdf documents
          for (int fileCounter = 0; fileCounter < sourceFiles.Count; fileCounter++)
          {
             // Create pdf reader
             PdfReader reader = new PdfReader(sourceFiles[fileCounter]);
             int numberOfPages = reader.NumberOfPages;

             // Iterate through all pages
             for (int currentPageIndex = 1; currentPageIndex <= numberOfPages; currentPageIndex++)
             {
               // Determine page size for the current page
               document.SetPageSize(reader.GetPageSizeWithRotation(currentPageIndex));
               // Create page
               document.NewPage();
               PdfImportedPage importedPage = writer.GetImportedPage(reader, currentPageIndex);
               // Determine page orientation
               int pageOrientation = reader.GetPageRotation(currentPageIndex);
               if ((pageOrientation == 90) || (pageOrientation == 270))
               {
                  content.AddTemplate(importedPage, 0, -1f, 1f, 0, 0, reader.GetPageSizeWithRotation(currentPageIndex).Height);
               }
               else
               {
                   content.AddTemplate(importedPage, 1f, 0, 0, 1f, 0, 0);
                }
              }
            }
         }
         catch (Exception exception)
         {
            throw new Exception("There has an unexpected exception occured during the pdf merging process.", exception);
         }
       }
       finally
       {
          document.Close();
        }
       return output.GetBuffer();
   }

There are tutorials how to set this up -- my project uses a VB.Net frontend with a C# mid section (controller, whatever it is -- another assembly mixed in with my proj).  So for each section of your actual report -- use a separate datasource -- can be a List<yourClasses>, Datasets, ... I use mostly Lists.  Parameters are kind of old school.  I guess VS2013 supports params, but I just use datasources.  note:  open your RDLC using the XML reader to setup your datasources and the designer to setup your textboxes (display controls).  Here is some xml of one of my reports (which my project contains several dozen reports -- web based)

    <DataSet Name="AssignmentEntity">
      <Query>
        <DataSourceName>DummyDataSource</DataSourceName>
        <CommandText />
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
      <Fields>
        <Field Name="AssignmentID">
          <DataField>AssignmentID</DataField>
          <rd:TypeName>System.Int64</rd:TypeName>
        </Field>
        <Field Name="UserID">
          <DataField>UserID</DataField>
          <rd:TypeName>System.Int64</rd:TypeName>
        </Field>
        <Field Name="StartDate">
          <DataField>StartDate</DataField>
          <rd:TypeName>System.Nullable`1[System.DateTime]</rd:TypeName>
        </Field>
        <Field Name="EndDate">
          <DataField>EndDate</DataField>
          <rd:TypeName>System.Nullable`1[System.DateTime]</rd:TypeName>
        </Field>
        <Field Name="Position">
          <DataField>Position</DataField>
          <rd:TypeName>DDTMS.Business.PositionEntity</rd:TypeName>
        </Field>
        <Field Name="Narrative">
          <DataField>Narrative</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="FieldNames">
          <DataField>FieldNames</DataField>
          <rd:TypeName>System.Collections.Generic.Dictionary`2[System.String,Nslc.Framework.Business.BusinessEntity+FieldDetails]</rd:TypeName>
        </Field>
        <Field Name="IsValid">
          <DataField>IsValid</DataField>
          <rd:TypeName>System.Boolean</rd:TypeName>
        </Field>
      </Fields>
      <rd:DataSetInfo>
        <rd:ObjectDataSourceSelectMethod>Get</rd:ObjectDataSourceSelectMethod>
        <rd:ObjectDataSourceSelectMethodSignature>System.Collections.Generic.List`1[DDTMS.Business.AssignmentEntity] Get(System.String, Nslc.Framework.Data.DataAccessParameters, DBHelper)</rd:ObjectDataSourceSelectMethodSignature>
        <rd:ObjectDataSourceType>DDTMS.Business.AssignmentDao, DDTMS.Business, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</rd:ObjectDataSourceType>
      </rd:DataSetInfo>
    </DataSet>
DDTMS is my project name, Business is just an assembly (the C# assembly)

Note:  if my sample here is a little overwhelming -- I'm working on an enterprise level project with a few other coders, and yes, I have left out several chunks of stuff (mostly building the datasource(s) that's more database related stuff).  The idea is to give you a peek on ways to achieve the desired result (don't rely on the wizard -- easier to go into the xml to fine tune your rdlc well, the datasource part -- use the designer for the display stuff).

NEG1934 on Thu, 04 Aug 2016 18:55:33


I am trying to think of a good way to send you what I have. But what you told me above is exactly what I have done or tried. 

1. I tried to exclude the event handler and have everything come from the main rdlc report. But I then get 'Data source could not be retrieved' for my sub report. 

2. If I add the handler back in, I do not get that error. If fact if I set the sub report data source with a default parameter, it will return the results for that default parameter for every record in my parent report. So just to see, I added what value my parameter for the sub report was getting and it in fact was getting the id from the main report's row of data it just was not using it to filter my sub reports data. The sub reports data was still just showing result for the default parameter I initially passed it. I do not understand why Microsoft has made this so difficult. I agree with you it seems it should be as easy as adding the sub report to the tablix of the main report and then putting in that parameter. 

I am at a lose of what to do, I think it is overly complex try and merge the reports. 

CoolDadTx on Thu, 04 Aug 2016 18:59:59


"in fact was getting the id from the main report's row of data it just was not using it to filter my sub reports data"

So then that would seem to indicate that the data source and parameter stuff is working properly and the issue is related to your query. But if you hard coded the parameter value and it was working this would indicate that the query is right and the parameter is actually not being set properly.

Can you post the contents of your GetData method that is being called by the subreport data source? It should have a where clause or equivalent in it that is using the AccountId parameter.

NEG1934 on Thu, 04 Aug 2016 19:15:47


Here is my stored procedure:

ALTER PROCEDURE [dbo].[spselSubRepNonCompliantEvents]
@AccountId int
AS

BEGIN

IF 1=0 BEGIN
    SET FMTONLY OFF
END

SELECT t.Description AS LogType,
l.LogId,
l.LogTypeId
FROM [Log] l 
INNER JOIN LogType t ON t.LogTypeId = l.LogTypeId
WHERE l.AccountId = @AccountId;

END

NEG1934 on Thu, 04 Aug 2016 19:19:01


You see though my query does does filter out by the default value set in the objectdatasource. But its like it does not requery my data source with a new parameter for each row. 

CoolDadTx on Thu, 04 Aug 2016 19:34:31


You know, in your markup you have defined that data source.  You are using that data source in your event. If that event is raised multiple times (say once per row) then you'll end up using the same data source object for each subreport. This means that the parameter would keep getting overwritten so whoever had written to it last (before the source is run) would win.

What I don't know, since I don't normally do subreports this way, is whether the subreport processing event is raised for each row. It would seem to make sense that it would run prior to the report being rendered. This should be fine since each subreport would get the correct data. What I'm really wondering about is whether the query is actually running each time.  Can you profile the call and determine if you're actually making the sproc call each time?  I'm wondering if the query is only running the first time and then it is ignoring subsequent calls because it already has results. ObjectDataSource is configured to cache results but I would assume that if any parameters changed it would run again, but maybe it doesn't. Then again it shouldn't cache anything if EnableCaching is false, which it should be by default.

Out of curiosity, is there any reason why you're doing this report using RDLC rather than using SSRS which can handle the data source stuff automatically?  We used to use RDLCs but we switched to SSRS because: a) the data is already in the SQL database, b) it is faster, c) it is easier and d) it requires far less resources on the server.

NEG1934 on Thu, 04 Aug 2016 19:58:43


It is only running the data source once. I even hard coded the data source to say EnableCaching = false and it still did not work. 

I had all intentions of using SSRS for reports, but our SSRS server is not really ready to be depended on. I may just need to bite the bullet and make it ready because this simple task is taking a lot of time and it does not seem Microsoft makes it easy to figure out how to do something so simple. 

CoolDadTx on Fri, 05 Aug 2016 03:19:30


I created a quick little demo with nested reports and having the child report require a parameter provided by the parent report. Since you seemed to be using ASP.NET I did it using that framework.  Since you were using ObjectDataSource for both reports I did that as well. In order to get the parameter from the parent to the child report you would need to use SubreportProcessing. It gets the list of parameters from the parent report but since you're using ODS you have to hook up the parameters to the SelectParameters so that it will use it in the query.  Here's what I did.

private void LocalReport_SubreportProcessing ( object sender, SubreportProcessingEventArgs e )
{
   //Set the ODS parameter to the current value for the report as defined by the e parameter            
    dsOrders.SelectParameters["CustomerId"].DefaultValue = e.Parameters["CustomerId"].Values.FirstOrDefault();
    e.DataSources.Add(new ReportDataSource("setOrders", dsOrders));
}

But this seems unclean to me, as does ODS in general. So I personally tend to avoid using data sources altogether and prefer to simply select the data directly.

private void LocalReport_SubreportProcessing ( object sender, SubreportProcessingEventArgs e )
{            
    //dsOrders.SelectParameters["CustomerId"].DefaultValue = e.Parameters["CustomerId"].Values.FirstOrDefault();

    var customerId = Int32.Parse(e.Parameters["CustomerId"].Values.FirstOrDefault());
    var db = new Database();
    e.DataSources.Add(new ReportDataSource("setOrders", db.GetOrders(customerId)));
}

Here I'm using a simple in-memory DB to retrieve the data but you can use anything you want. The hardest part is converting the parameter to the correct type that the query actually requires. However this does eliminate the need for an ODS for the child report. You can actually do the same thing for the main report as well. Personally when I used to use RDLCs I created strong wrappers around the actual report and ran queries to populate the data without ever using an ODS. But that was personal preference.

NEG1934 on Tue, 09 Aug 2016 15:44:39


Thank you for that example. The main difference I have is that in my main report it does not have a parameter of AccountId that my sub report needs. The stored procedure of my main report returns the AccountId value, but again it is not a parameter set in the form to run the report. So I am not sure then how you extract the AccountId value from the main report to pass into the sub report. 

CoolDadTx on Tue, 09 Aug 2016 15:56:20


My main report didn't have a parameter either. In my example the parameter to the subreport was set by selecting a column (Id in this case) that was returned for the current row. The subreport was nested in the row so it was called once for each row in the main report. I assume here that the subreport is rendered for each row in your main report.

In the main report where you dropped the subreport, open the properties for the subreport. Go to the Parameters tab and in the parameter list select the parameter in the subreport. Then click the Value (or whatever it is called) column and a dropdown should appear listing the fields that are defined in the dataset of the main report. Set the parameter to the appropriate column. This wires up the subreport to get its parameter from the current row (in the main report).

In the event handler, once you've made this change, the e.Parameter collection contains the parameters to the subreport. Since you've associated the 2 reports in the designer you'll find that the parameter value will be set to the current row's value. Because you're using an ODS the assignment is to force the ODS to update the select parameter that it uses when it runs the query to the value that the main report is passing to it rather than whatever value it currently has.

If you need me to send you my example program then let me know. It is too large to post in the forums.

NEG1934 on Thu, 11 Aug 2016 13:57:44


Thank you very much CoolDadTx. That did work for me. I had to do one other small thing as you suggested and I just did not use the ODS. Instead I called and created a data table for my datasource for my sub report. 

public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
        {
            int accountId = int.Parse(e.Parameters["AccountId"].Values.FirstOrDefault());

            TableAdapter sp = new TableAdapter();
            DataSet.DataTable ds = new DataSet.DataTable();
           sp.Fill(ds, accountId);

            e.DataSources.Add(new ReportDataSource("DataSet1", ds.ToList()));
            //e.Parameters["AccountId"].Values. = accountId;
            
        }