How to use multi-level JSON in a report

Despite the fact that FastReport.Net provides us with a plugin for the report designer to use as a data source for the JSON file, it is not always suitable. This plugin works well with single-level data representing a separate table. In cases where the data has multiple levels of nesting, you will have to try another solution.

 For example, using the Newtonsoft.Json library - with its help you can get data from JSON, make a list from them and register it in a report from code.

 Let's look at everything with an example. Create a WinForms application. With the help of NuGet Manager, install the Newtonsoft.Json package. In Reference, add a link to the library FastReport.dll.

Suppose we have sales data for managers in stores. All these data are in one file, but in fact they are three different entities: a store, a manager, a sale. This will be our json file for this example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

{
 "Shops": [{
 "Name": "First shop",
 "Managers": [{
 "Name": "John",
 "Phone": "45443446343",
 "Sales": [{
 "GoodId": "1",
 "Amount": "3"
 },
 {
 "GoodId": "2",
 "Amount": "5"
 },
 {
 "GoodId": "3",
 "Amount": "2"
 }
 ]
 },
 {
 "Name": "Boris",
 "Phone": "8787964387",
 "Sales": [{
 "GoodId": "15",
 "Amount": "8"
 },
 {
 "GoodId": "12",
 "Amount": "2"
 },
 {
 "GoodId": "13",
 "Amount": "2"
 }
 ]
 }
 ]
 },
 {
 "Name": "Second shop",
 "Managers": [{
 "Name": "Julia",
 "Phone": "5555555555",
 "Sales": [{
 "GoodId": "1",
 "Amount": "30"
 }]
 },
 {
 "Name": "Helen",
 "Phone": "8787964387",
 "Sales": [{
 "GoodId": "2",
 "Amount": "8"
 },
 {
 "GoodId": "3",
 "Amount": "26"
 },
 {
 "GoodId": "1",
 "Amount": "2"
 }
 ]
 }
 ]
 }
 ]
}

 To deserialize this data, we need objects with properties corresponding to the fields in JSON.

Of course, if the JSON document has a really deep nesting, it can be quite tiring. Therefore, there is an excellent web service http://json2csharp.com/. You simply place your valid json in the input field, press the Generate button, and get ready-made C # classes. For our json document, the following set of classes will be generated:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

public class Sale
 {
 public string GoodId { get; set; }
 public string Amount { get; set; }
 }
 
 public class Manager
 {
 public string Name { get; set; }
 public string Phone { get; set; }
 public List<Sale> Sales { get; set; }
 }
 
 public class Shop
 {
 public string Name { get; set; }
 public List<Manager> Managers { get; set; }
 }
 
 public class RootObject
 {
 public List<Shop> Shops { get; set; }
 }

 Let's create a separate Sales.cs file for them.

Add a button to the form. For the click event, we will create a handler:

1
2
3
4
5
6
7

private void RunBtn_Click(object sender, EventArgs e)
 {
 var json = JsonConvert.DeserializeObject<RootObject>(File.ReadAllText(@"Sales.json"));
 Report report = new Report();
 report.RegisterData(json.Shops,"Shops");
 report.Design(); 
 }

 As you can see, here we use the Newtonsoft.json library to deserialize json into a RootObject, which has the property Shops, which is a list of stores. We register this list in the report as a data source. And call the report designer. Run our application with a single button and click on it. The report designer appears with a blank report. Select the report data source:

Based on this data, create a report template:

As you can see, the Data band has a child band, and the band in turn has one more child Data band.

The data on these bands is arranged according to the hierarchy in the data source. Run the report:

Thus, we obtained related data in a Master-Detail report. And we do not need to create special relationships between tables to get dependencies. In the case of large nesting of data in JSON, it is better to use the approach considered by us.