Didn't load all report data once we use manual relationship

Stimulsoft Reports.JS discussion
chandi
Posts: 10
Joined: Thu Apr 01, 2021 4:33 pm

Didn't load all report data once we use manual relationship

Post by chandi »

Hi,

when I use " report.dictionary.synchronize(); "to automatically create the relationship it's coming records correctly as below.

Correct Report preview
correct.png
correct.png (37.42 KiB) Viewed 3101 times
but once I create the relationship manually it's not showing all the data correctly. please refer to the below image.
we used ["Employee Code"] as parent and child.

Table structure
table.png
table.png (95.99 KiB) Viewed 3100 times
Code:
code.png
code.png (66.99 KiB) Viewed 3100 times
Incorrect Report preview
Issue.png
Issue.png (56.64 KiB) Viewed 3100 times

Please refer to the below image for JSON Data response
JSON Response.png
JSON Response.png (29.58 KiB) Viewed 3100 times

Looking forward to your response.

Thank you.
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: Didn't load all report data once we use manual relationship

Post by Lech Kulikowski »

Hello,

Please send us a sample project that reproduces the issue for analysis.

Also, please try to remove spaces from relations, columns names.

Thank you.
DanielHewett
Posts: 18
Joined: Wed Jun 30, 2021 4:52 am

Re: Didn't load all report data once we use manual relationship

Post by DanielHewett »

Hi,
So what we are wanting to try to achieve is self service reporting. This means that what we would like to present to the user is something that is easy to read. This involves displaying datasources and fields which have descriptive labels in Pascal case but with spaces between the words. For the below examples I have removed the spaces from the fields and datasource names, however, this is something we would like to be able to achieve through whatever means you suggest.

An example of the type of data we would be working with is as follows:

Code: Select all

{
	"employeeDetails": [
		{
			"id": 1,
			"firstName": "Bill",
			"surname": "Smith",
			"employeeDepartments": [
				{
					"id": 10,
					"employeeId": 1,
					"code": "SAL",
					"name": "Sales"
				},
				{
					"id": 11,
					"employeeId": 1,
					"code": "IT",
					"name": "Information Technology"
				}
			]
		},
		{
			"id": 2,
			"firstName": "Mary",
			"surname": "Jane",
			"employeeDepartments": [
				{
					"id": 12,
					"employeeId": 2,
					"code": "SAL",
					"name": "Sales"
				},
				{
					"id": 13,
					"employeeId": 2,
					"code": "Admin",
					"name": "Administration"
				}
			]
		}
	]
}
We can quite easily use "report.regData" and "report.dictionary.synchronize()" to automatically link everything up and make all the fields and the relationships available in the designer. From a functional standpoint this works without issue, however, the result is not great from a self service reporting perspective. There are underscores in the table names of child tables and there is an added relationId which I believe is used as part of the automatic linking, which we would like to avoid. See code snippet and screenshot below:

Code: Select all

	var options = new Stimulsoft.Designer.StiDesignerOptions();
	var designer = new Stimulsoft.Designer.StiDesigner(options, "StiDesigner", false);
	options.appearance.fullScreenMode = false;

	var report = new Stimulsoft.Report.StiReport();
	var dataSet = new Stimulsoft.System.Data.DataSet("DataSet");
	var data = <SEE JSON LISTED ABOVE>

	dataSet.readJson(data);

	report.dictionary.databases.clear();
	report.dictionary.dataSources.clear()

	report.regData(dataSet.dataSetName, "", dataSet);
	report.dictionary.synchronize();
	
	designer.report = report;
	designer.renderHtml("content");
The attachment Automatic DS Sync.png is no longer available
We have tried to manually synchronize the report dictionary in an attempt to remove the relationId and underscores. The has the correct appearance in the designer, but, when the data is previewed, there is no data in the "EmployeeDepartments" table of the datasource (if we don not attempt to establish relationships). If we do attempt manually set relationships, we are presented with an error in preview mode. Even if we do not attempt to remove the relationId and underscores but just manually syncronize (the table is then named "EmployeeDetails_EmployeeDepartments"), we get the same issues. See code snippet (the commented out lines make the desired modifications) and screenshots below:

Code: Select all

                           var options = new Stimulsoft.Designer.StiDesignerOptions();
                           var designer = new Stimulsoft.Designer.StiDesigner(options, "StiDesigner", false);
                           options.appearance.fullScreenMode = false;

                            var report = new Stimulsoft.Report.StiReport();
                            var dataSet = new Stimulsoft.System.Data.DataSet("DataSet");
                            var data = <SEE JSON LISTED ABOVE>

                            dataSet.readJson(data);

                            report.dictionary.databases.clear();
                            report.dictionary.dataSources.clear()

                            report.regData("EmployeeDetails", "EmployeeDetails", dataSet);

                            for (var index in dataSet.tables.list) {
                                    var data = dataSet.tables.getByIndex(index);        
                                    var tableNames = data.tableName;

                                    //if (tableNames.includes("_")) {
                                    //    var arr = tableNames.split("_");
                                    //    tableNames = arr[1];
                                    //}  

                                var dataSource = new Stimulsoft.Report.Dictionary.StiDataTableSource( "EmployeeDetails",tableNames);
                                    for (var index in data.columns.list) {
                                        var dataColumn = data.columns.list[index];
										//if (dataColumn.columnName != "relationId") {
                                            dataSource.columns.add(new Stimulsoft.Report.Dictionary.StiDataColumn(dataColumn.columnName, dataColumn.columnName, dataColumn.columnName));
										//}
                                    }
                                    report.dictionary.dataSources.add(dataSource);
                              }

                            var dataRelation = new Stimulsoft.Report.Dictionary.StiDataRelation("MyRelation", "MyRelation", "MyRelation", report.dictionary.dataSources.getByName("EmployeeDetails"), 
                           report.dictionary.dataSources.getByName("EmployeeDetails_EmployeeDepartments"), ["id"], ["employeeId"]);
                            report.dictionary.relations.add(dataRelation);

                            designer.report = report;
                            designer.renderHtml("content");
Automatic DS Sync.png
Automatic DS Sync.png (17.06 KiB) Viewed 2987 times
The attachment Manual DS Error.png is no longer available

I don't know if the issue is with the method we are using or if there is a bug with the control, however, it would be great if you could provide some example code that is able to correctly manually set up the datasource such that we don't see the "relationId" fields and parent table prefix on child tables. Also, could you please give some guidance on displaying the tables and fields with spaces in the interface. Is it a matter of not using spaces in the name and then setting separate display text for the item?
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: Didn't load all report data once we use manual relationship

Post by Lech Kulikowski »

Hello,

Please send us a sample project that reproduces the issue for analysis.

Also, please try to remove spaces from relations, columns names.

Thank you.
DanielHewett
Posts: 18
Joined: Wed Jun 30, 2021 4:52 am

Re: Didn't load all report data once we use manual relationship

Post by DanielHewett »

Hi,

I have put the code snippets above into a full working demo below.

As in the code snippets above, all fields have no spaces in their names but could you advise on whether it is possible to somehow display the field in the control with a space in the displayed name text for the field.

Code: Select all

<!DOCTYPE html>
<html>
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
    <title>Create report designer and show it immediately</title>

    <!-- Report designer Office2013 White Blue style -->
    <link href="https://raw.githack.com/stimulsoft/Samples-JS/master/JavaScript/css/stimulsoft.designer.office2013.whiteblue.css" rel="stylesheet">
    <link href="https://raw.githack.com/stimulsoft/Samples-JS/master/JavaScript/css/stimulsoft.viewer.office2013.whiteblue.css" rel="stylesheet">

    <!-- Stimusloft Reports.JS -->
    <script src="https://raw.githack.com/stimulsoft/Samples-JS/master/JavaScript/scripts/stimulsoft.reports.js" type="text/javascript"></script>
    <script src="https://raw.githack.com/stimulsoft/Samples-JS/master/JavaScript/scripts/stimulsoft.viewer.js" type="text/javascript"></script>
    <script src="https://raw.githack.com/stimulsoft/Samples-JS/master/JavaScript/scripts/stimulsoft.designer.js" type="text/javascript"></script>
</head>
<body>
<div id="order-confirmation"></div>

<script type="text/javascript">
 
	var options = new Stimulsoft.Designer.StiDesignerOptions();
	var designer = new Stimulsoft.Designer.StiDesigner(options, "StiDesigner", false);
	options.appearance.fullScreenMode = false;

	var report = new Stimulsoft.Report.StiReport();
	var dataSet = new Stimulsoft.System.Data.DataSet("DataSet");
	var data = 
	
			{
				"employeeDetails": [
					{
						"id": 1,
						"firstName": "Bill",
						"surname": "Smith",
						"employeeDepartments": [
							{
								"id": 10,
								"employeeId": 1,
								"code": "SAL",
								"name": "Sales"
							},
							{
								"id": 11,
								"employeeId": 1,
								"code": "IT",
								"name": "Information Technology"
							}
						]
					},
					{
						"id": 2,
						"firstName": "Mary",
						"surname": "Jane",
						"employeeDepartments": [
							{
								"id": 12,
								"employeeId": 2,
								"code": "SAL",
								"name": "Sales"
							},
							{
								"id": 13,
								"employeeId": 2,
								"code": "Admin",
								"name": "Administration"
							}
						]
					}
				]
			}	

	

	dataSet.readJson(data);

	report.dictionary.databases.clear();
	report.dictionary.dataSources.clear()

	report.regData("EmployeeDetails", "EmployeeDetails", dataSet);

/////////////////////////////////////////////////////////////

	//Automatic dataset setup using synchronise
	//report.dictionary.synchronize();


/////////////////////////////////////////////////////////////

	//Manual dataset setup
	for (var index in dataSet.tables.list) {
			var data = dataSet.tables.getByIndex(index);        
			var tableNames = data.tableName;

			//if (tableNames.includes("_")) {
			//    var arr = tableNames.split("_");
			//    tableNames = arr[1];
			//}  

		var dataSource = new Stimulsoft.Report.Dictionary.StiDataTableSource( "EmployeeDetails",tableNames);
			for (var index in data.columns.list) {
				var dataColumn = data.columns.list[index];
				//if (dataColumn.columnName != "relationId") {
					dataSource.columns.add(new Stimulsoft.Report.Dictionary.StiDataColumn(dataColumn.columnName, dataColumn.columnName, dataColumn.columnName));
				//}
			}
			report.dictionary.dataSources.add(dataSource);
	  }

	var dataRelation = new Stimulsoft.Report.Dictionary.StiDataRelation("MyRelation", "MyRelation", "MyRelation", report.dictionary.dataSources.getByName("employeeDetails"), report.dictionary.dataSources.getByName("employeeDetails_employeeDepartments"), ["id"], ["employeeId"]);
	report.dictionary.relations.add(dataRelation);


/////////////////////////////////////////////////////////////


	designer.report = report;
	designer.renderHtml("content");
 
 
</script>
</body>
</html>
DanielHewett
Posts: 18
Joined: Wed Jun 30, 2021 4:52 am

Re: Didn't load all report data once we use manual relationship

Post by DanielHewett »

Hi,

We have recorded a short demo video which demonstrates the issues we are trying to solve.

Link: https://www.dropbox.com/s/ajrgufev7w95a ... X.mp4?dl=0

Thanks
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: Didn't load all report data once we use manual relationship

Post by Lech Kulikowski »

Hello,

We couldn't reproduce the issue.
Please send us a sample project that reproduces the issue for analysis.

Thank you.
DanielHewett
Posts: 18
Joined: Wed Jun 30, 2021 4:52 am

Re: Didn't load all report data once we use manual relationship

Post by DanielHewett »

Hi,

I'm not sure how you were unable to reproduce the issue as in the above 2 posts we have provided a sample project in a code snippet and a demo video showing us using that code snippet to reproduce the issue.

I have attached the code snippet in the form of a html file if that helps.

Thanks
Attachments
StimulsoftDemo.zip
(1.22 KiB) Downloaded 150 times
Lech Kulikowski
Posts: 6197
Joined: Tue Mar 20, 2018 5:34 am

Re: Didn't load all report data once we use manual relationship

Post by Lech Kulikowski »

Hello,

In your code for "//Manual dataset setup" the NameInSource property is not correct. You can check how it set after the synchronize() method.

Thank you.
DanielHewett
Posts: 18
Joined: Wed Jun 30, 2021 4:52 am

Re: Didn't load all report data once we use manual relationship

Post by DanielHewett »

Hi,

Thanks for that, we have now solved the problem with manually building the Data Dictionary and the relationships.

What we are still trying to solve is displaying the names of Datasources, Tables, Fields and Relationships with spaces and other characters to the user. From my research, it seems that the best way to do this is by using an Alias in the Data Dictionary. However, I have been unable to force the designer to only display the Alias, it will always display <Name>[<Alias>] as displayed in the screenshot below.
Alias.png
Alias.png (16.52 KiB) Viewed 2892 times
I found a forum post where a poster discovered that there are settings to force the Alias to be used see below, however, this did not have the desired effect.

Code: Select all

    options.dictionary.showOnlyAliasForComponents = true;
    options.dictionary.showOnlyAliasForData = true;
    options.dictionary.showOnlyAliasForDataColumn = true;
    options.dictionary.showOnlyAliasForDataRelation = true;
    options.dictionary.showOnlyAliasForDataSource = true;
    options.dictionary.showOnlyAliasForDatabase = true;
    options.dictionary.showOnlyAliasForResource = true;
I would appreciate it if you could help us with this and I have attached a working demo project that reproduces the scenario.

Thanks
Attachments
Alias_Example.zip
(1.4 KiB) Downloaded 157 times
Post Reply