Problem with Connection String on subreport XML Markup

2 Answers 155 Views
DataSource SQL Report Designer (standalone) Report Parameters SubReport
Wojciech
Top achievements
Rank 1
Iron
Wojciech asked on 26 Jul 2023, 07:07 AM

Hi, I have master report with subreport (Report Source set as XML Markup). Everything works fine when I preview it on Report Designer, but after import file to our app I have data only from master report. In subreport section I have error:

An error has occurred while processing SubReport 'subReportDetails':
Unable to establish a connection to the database. Please verify that your connection string is valid. In case you use a named connection string from
the application configuration file, make sure the name is correct and the connection string settings are present in the configuration file of your
application.
------------- InnerException -------------
Initialization string format is out of specification starting at index 0.

It works when I save login data for report but I don't want to save passwords in XML expression.

I wanted to try Assign connection string dynamically using report parameter and bindings - Telerik Reporting, but I don't know how to pass connection string from master report to subreport.

2 Answers, 1 is accepted

Sort by
0
Nikolay
Telerik team
answered on 28 Jul 2023, 01:42 PM

Hi Wojciech,

This error occurred because the Datasource was set only to the main report and the Subreport does not have access to it. Please note that Subreports are individual reports and do not inherit the properties of the main report.

The passing of the ConnectionString as a parameter to the Subreport is the recommended solution. To do that you need to add a new parameter to the Subreport and set its value to the connection string. 

After that, you can bind this parameter to each item that needs to use a data source, as described in the article you shared. Please note that you can not set the connection string parameter directly to the data source in the subreport.

Another approach is to assign the SQL Data Source from the main report to the subreport's DataSource property.

If you do that, you might need to modify the subreport. That is, to bind each item that needs to use the data source to the data source of the report itself. This is done by binding the DataSource property to "= ReportItem.DataObject". See Binding to data from the parent data item.

Regards, Nikolay Progress Telerik

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Wojciech
Top achievements
Rank 1
Iron
commented on 31 Jul 2023, 10:30 AM

Hi, but can I pass data dynamically from main report tu sub without exposing user id and password in to the parameter? For example DataSource.ConnectionString (MainReport) -> Parameter?
0
Nikolay
Telerik team
answered on 01 Aug 2023, 11:18 AM

Hi, Wojciech

The parameters send values in plain text, so if you don't want to do this there are a few possibilities you can try to not have the value of the connection string in the report. If you have an application that uses the report.

You can try using a Shared connection string, you can do this by configuring the data source in the SqlDataSource Wizard to use a shared connection. That can be done by checking the Use as Shared Connection checkbox and providing its name.

That way you can store the connection string inside the appsettings.json of the application and reuse it for all the reports. When the connections string is changed all the report Datasources will use the new one.

You can take a look at the configuration code snippet below for an idea of how to configure it:
  "ConnectionStrings": {
    //This connection string will use System.Data.SqlClient as data provider invariant name.
    //"Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString": "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI"
    //This connection string explicitly states the data provider invariant name - mandatory for databases other than MSSQL Server.
    "Telerik.Reporting.Examples.CSharp.Properties.Settings.TelerikConnectionString": {
      "connectionString": "Data Source=(local)\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=SSPI",
      "providerName": "System.Data.SqlClient"
    }
  },

Another way to do this is to create a SharedDataSource that will be used by all the reports. You can take a look at Shared Data Sources with Telerik Reporting video guide on the Progress Telerik Youtube channel to see how to implement it. It requires using the Web Report Designer.

Also did my second suggestion of setting the data source directly to the subreport not work, it does not send the connection string as plain text, but instead set the entire data source. 

Can look at the suggestions and let me know if they do the trick, looking forward to hearing back from you.

Regards, Nikolay Progress Telerik

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Wojciech
Top achievements
Rank 1
Iron
commented on 01 Aug 2023, 11:29 AM | edited

I always use Shared Connection String. It works only directly in Preview from Standalone Report Designer. When I export it to app I have error like in the first post/question.

Both Main Report and Subreport have the same Shared Connection String set.

Nikolay
Telerik team
commented on 01 Aug 2023, 02:07 PM

Hi, Wojciech

So you use the same a named connection string in the main and subreport.  It works in the Standalone Report Designer, but throw error in the app only for the subreport. They both should resolve the same so this is behavior is unexpected. 

Can you send me the report definitions so I can take a look at it and see what may cause this behavior?

Regards, Nikolay Progress Telerik

Stay tuned by visiting our roadmap and feedback portal pages, enjoy a smooth take-off with our Getting Started resources, or visit the free self-paced technical training at https://learn.telerik.com/.
Wojciech
Top achievements
Rank 1
Iron
commented on 02 Aug 2023, 06:40 AM | edited

SubReport definition:
<?xml version="1.0" encoding="utf-8"?>
<Report DataSourceName="Data" Width="19cm" Name="Packing List Subreport" Zoom="3" SnapGridSize="0.1cm" xmlns="http://schemas.telerik.com/reporting/2022/3.1">
  <DataSources>
    <SqlDataSource QueryDesignerState="null" ParameterValues="{&quot;@BatchList&quot;:&quot;410&quot;}" ConnectionString="MESMKZ" SelectCommand="EXEC [Report].[PackingListJapanExtrasByProducts]&#xD;&#xA;@BatchList" Name="Data">
      <Parameters>
        <SqlDataSourceParameter DbType="String" Name="@BatchList">
          <Value>
            <String>= IIf(Parameters.BatchId.Value Is Null, Null, Join(',', Parameters.BatchId.Value))</String>
          </Value>
        </SqlDataSourceParameter>
      </Parameters>
      <CalculatedFields>
        <CalculatedField Name="FormattedDate" Expression="= Format(&quot;{0:dd.MM.yyyy}&quot;, Fields.Created)">
          <DataType>System.String</DataType>
        </CalculatedField>
      </CalculatedFields>
    </SqlDataSource>
    <SqlDataSource QueryDesignerState="null" ParameterValues="{&quot;@DateFrom&quot;:&quot;2023-06-01&quot;,&quot;@DateTo&quot;:&quot;2023-07-20&quot;}" ConnectionString="MESMKZ" SelectCommand="SELECT Id, Batch, Created&#xD;&#xA;FROM Seven.ProdBatch&#xD;&#xA;WHERE PatternId IN (12, 7) AND Created BETWEEN @DateFrom AND @DateTo&#xD;&#xA;ORDER BY Created DESC" Name="BatchList">
      <Parameters>
        <SqlDataSourceParameter DbType="DateTime" Name="@DateFrom">
          <Value>
            <String>= Parameters.DateFrom.Value</String>
          </Value>
        </SqlDataSourceParameter>
        <SqlDataSourceParameter DbType="DateTime" Name="@DateTo">
          <Value>
            <String>= Parameters.DateTo.Value</String>
          </Value>
        </SqlDataSourceParameter>
      </Parameters>
    </SqlDataSource>
  </DataSources>
  <Items>
    <DetailSection Height="0.6cm" Name="detail">
      <Style Visible="True" />
      <Items>
        <TextBox Width="0.9cm" Height="0.6cm" Left="0cm" Top="0cm" Value="= RowNumber(&quot;groupBatch&quot;)" CanGrow="False" Name="textBox22">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="8cm" Top="0cm" Value="= Fields.NameItalian" CanGrow="False" Name="textBox23">
          <Style VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="11.1cm" Top="0cm" Value="= Fields.NameEnglish" CanGrow="False" Name="textBox24">
          <Style VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="1.6cm" Height="0.6cm" Left="1cm" Top="0cm" Value="= Fields.ArticleCode" CanGrow="False" Name="textBox25">
          <Style VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="1.7cm" Height="0.6cm" Left="17.3cm" Top="0cm" Value="= Sum(Fields.Quantity)" Format="{0:0.00}" CanGrow="False" Name="textBox27">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="3.5cm" Height="0.6cm" Left="4.4cm" Top="0cm" Value="= Fields.Code" CanGrow="False" Name="textBox28">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="1.6cm" Height="0.6cm" Left="2.7cm" Top="0cm" Value="= Fields.FormattedDate" CanGrow="False" Name="textBox4">
          <Style VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="14.2cm" Top="0cm" Value="= Fields.ArticleName" CanGrow="False" Name="textBox6">
          <Style VerticalAlign="Middle">
            <Font Size="8pt" />
          </Style>
        </TextBox>
      </Items>
    </DetailSection>
    <ReportHeaderSection Height="0.6cm" Name="reportHeaderSection1">
      <Items>
        <TextBox Width="19cm" Height="0.5cm" Left="0cm" Top="0cm" Value="EXTRA PARTS - PACKS DETAILS" Name="textBox1">
          <Style VerticalAlign="Middle">
            <Font Bold="True" />
            <BorderStyle Bottom="Solid" />
          </Style>
        </TextBox>
      </Items>
    </ReportHeaderSection>
    <PageFooterSection Height="0.7cm" Name="pageFooterSection1">
      <Items>
        <TextBox Width="3.7cm" Height="0.6cm" Left="15.3cm" Top="0.1cm" Value="= &quot;Strona &quot; + PageNumber + &quot; z &quot; + PageCount" Name="textBox21">
          <Style TextAlign="Right" VerticalAlign="Middle" />
        </TextBox>
      </Items>
    </PageFooterSection>
  </Items>
  <PageSettings PaperKind="A4" Landscape="False" ContinuousPaper="False">
    <Margins>
      <MarginsU Left="1cm" Right="1cm" Top="1cm" Bottom="1cm" />
    </Margins>
  </PageSettings>
  <StyleSheet>
    <StyleRule>
      <Style>
        <Padding Left="2pt" Right="2pt" />
      </Style>
      <Selectors>
        <TypeSelector Type="TextItemBase" />
        <TypeSelector Type="HtmlTextBox" />
      </Selectors>
    </StyleRule>
  </StyleSheet>
  <Groups>
    <Group Name="groupBatch">
      <GroupHeader>
        <GroupHeaderSection Height="1.2cm" Name="groupHeaderSection1">
          <Items>
            <TextBox Width="19cm" Height="0.6cm" Left="0cm" Top="0cm" Value="= Fields.Batch + &quot;, quantity: &quot; + CountDistinct(Fields.Code) + &quot;, weight netto: &quot; + Format(&quot;{0:0.00}&quot;, Sum(Fields.Quantity))" Name="textBox8">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="0.9cm" Height="0.6cm" Left="0cm" Top="0.6cm" Value="No." Name="textBox10">
              <Style TextAlign="Right" VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="1.6cm" Height="0.6cm" Left="1cm" Top="0.6cm" Value="Code" Name="textBox11">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="1.7cm" Height="0.6cm" Left="17.3cm" Top="0.6cm" Value="Weight" Name="textBox12">
              <Style TextAlign="Right" VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="3.5cm" Height="0.6cm" Left="4.4cm" Top="0.6cm" Value="Box ID" Name="textBox14">
              <Style TextAlign="Right" VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="3cm" Height="0.6cm" Left="11.1cm" Top="0.6cm" Value="English name" Name="textBox15">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="3cm" Height="0.6cm" Left="8cm" Top="0.6cm" Value="Italian name" Name="textBox16">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="1.6cm" Height="0.6cm" Left="2.7cm" Top="0.6cm" Value="Date" Name="textBox2">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
            <TextBox Width="3cm" Height="0.6cm" Left="14.2cm" Top="0.6cm" Value="Polish name" Name="textBox5">
              <Style VerticalAlign="Middle">
                <Font Bold="True" />
              </Style>
            </TextBox>
          </Items>
        </GroupHeaderSection>
      </GroupHeader>
      <GroupFooter>
        <GroupFooterSection Height="0.6cm" Name="groupBatchFooter">
          <Style Visible="True" />
          <Items>
            <TextBox Width="19cm" Height="0.6cm" Left="0cm" Top="0cm" Value="= Sum(Fields.Quantity)" Format="{0:0.00}" Name="textBox18">
              <Style TextAlign="Right" VerticalAlign="Middle">
                <Font Bold="True" />
                <BorderStyle Bottom="Solid" />
              </Style>
            </TextBox>
          </Items>
        </GroupFooterSection>
      </GroupFooter>
      <Groupings>
        <Grouping Expression="= Fields.Batch" />
      </Groupings>
      <Sortings>
        <Sorting Expression="= Fields.Batch" Direction="Asc" />
      </Sortings>
    </Group>
    <Group Name="groupArticleCode">
      <GroupHeader>
        <GroupHeaderSection Height="0.2cm" Name="groupHeaderSection">
          <Style Visible="False" />
        </GroupHeaderSection>
      </GroupHeader>
      <GroupFooter>
        <GroupFooterSection Height="0.6cm" Name="groupArticleCodeFooter">
          <Style Visible="True" />
          <Items>
            <TextBox Width="19cm" Height="0.6cm" Left="0cm" Top="0cm" Value="= Sum(Fields.Quantity)" Format="{0:0.00}" Name="textBox3">
              <Style TextAlign="Right" VerticalAlign="Middle">
                <Font Bold="True" />
                <BorderStyle Bottom="Dotted" />
              </Style>
            </TextBox>
          </Items>
        </GroupFooterSection>
      </GroupFooter>
      <Groupings>
        <Grouping Expression="= Fields.ArticleCode" />
      </Groupings>
      <Sortings>
        <Sorting Expression="= Fields.ArticleCode" Direction="Asc" />
      </Sortings>
    </Group>
  </Groups>
  <ReportParameters>
    <ReportParameter Name="DateFrom" Type="DateTime" Text="Partia od" Visible="True" AllowNull="True" AutoRefresh="True">
      <Value>
        <String>2023-01-01</String>
      </Value>
    </ReportParameter>
    <ReportParameter Name="DateTo" Type="DateTime" Text="Partia do" Visible="True" AllowNull="True" AutoRefresh="True">
      <Value>
        <String>2023-07-20</String>
      </Value>
    </ReportParameter>
    <ReportParameter Name="BatchId" Type="Integer" Text="Lista partii" Visible="True" MultiValue="True" AllowNull="True" AutoRefresh="True">
      <AvailableValues DataSourceName="BatchList" DisplayMember="= Fields.Batch" ValueMember="= Fields.Id" />
      <Value>
        <String></String>
      </Value>
    </ReportParameter>
    <ReportParameter Name="ConnectionStringParameter" />
  </ReportParameters>
</Report>

Nikolay
Telerik team
commented on 04 Aug 2023, 01:16 PM

Hi, Wojciech

The sub-report definition looks correct. Can you send the main report definitions, so we can compare both reports and see if there are any differences in the configuration of the connection string?

Also, can you also send us the appsettings.json/web.config with redacted connectionString?

Additionally, can you also attach a Trace log - This will log the errors thrown on the server, where the reports are resolved: How to: Create and Initialize Trace Listeners- .NET Framework | Microsoft Docs. For an ASP.NET Core project, see How to troubleshoot errors in ASP.NET Core projects - Telerik Reporting instead.

Regards,
Nikolay
Progress Telerik

 

Wojciech
Top achievements
Rank 1
Iron
commented on 07 Aug 2023, 07:28 AM | edited

Zipped report in attachment.
Nikolay
Telerik team
commented on 09 Aug 2023, 01:30 PM

Hi, Wojciech

Looking at the reports a couple of things stood out to me that, may cause this issue.
  • The sub-report definition, that you sent and the one in the main report are different, the second one has one additional binding on the DataSource.ConnectionString property of the Report.
  • You are binding only the Data data source's connection string, so if the BatchString data source's Connection string is incorrect it will throw the same error, even if everything is correct with the first Data Source.

To elaborate further on the second point, if you pass a new connection through the ConnectionStringParameter report parameter, it will update the subreport's DataSource but not the data source used in its BatchId report parameter and it may fail due to that. To test this locally, you can try to temporarily remove this report parameter. If the subreport now loads as expected, that was the problem.

Still, in your scenario, it is not actually necessary to pass the connectionString as a parameter. The subreport can read it from the appsetting.json file. So if you do not plan to change connections, just use the same connection alias everywhere and remove the connection string bindings.

With that being said I don't see any problem with the reports themself, so the problem may be in the config file. Would it be possible to provide the appsetting.json file?

A solution you can try is to set the connection string of all data items in the report dynamically via code, you can check Changing the connection string dynamically according to runtime data for more info on how to do it.

Regards,
Nikolay
Progress Telerik
Wojciech
Top achievements
Rank 1
Iron
commented on 11 Aug 2023, 07:25 AM

I added DataSource.ConnectionString and binding for tests only. I forgot to remove that, sorry. I need to consult that with programmer, maybe there is something wrong in our app.
Tags
DataSource SQL Report Designer (standalone) Report Parameters SubReport
Asked by
Wojciech
Top achievements
Rank 1
Iron
Answers by
Nikolay
Telerik team
Share this question
or