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
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
Rank 1
Iron
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.
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
Rank 1
Iron
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.
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
Rank 1
Iron
<?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="{"@BatchList":"410"}" ConnectionString="MESMKZ" SelectCommand="EXEC [Report].[PackingListJapanExtrasByProducts]
@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("{0:dd.MM.yyyy}", Fields.Created)">
<DataType>System.String</DataType>
</CalculatedField>
</CalculatedFields>
</SqlDataSource>
<SqlDataSource QueryDesignerState="null" ParameterValues="{"@DateFrom":"2023-06-01","@DateTo":"2023-07-20"}" ConnectionString="MESMKZ" SelectCommand="SELECT Id, Batch, Created
FROM Seven.ProdBatch
WHERE PatternId IN (12, 7) AND Created BETWEEN @DateFrom AND @DateTo
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("groupBatch")" 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="= "Strona " + PageNumber + " z " + 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 + ", quantity: " + CountDistinct(Fields.Code) + ", weight netto: " + Format("{0:0.00}", 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>
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.
Nikolay
Progress Telerik
Rank 1
Iron
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
Rank 1
Iron