Hello,
Since my previous post, we've been evaluating the functionalities of the designer.
One of our main requirement is to display data as crosstabs.
We intend to use a WebServiceDataSource to fetch the data as JSON so that the report will only have to display them, without having to make any calculations or containing business rules.
I've included the JSON sample that I'm using below. As you can see, we have a school, which contains classes, which contains a list of students and a list of subjects. Each student has evaluations : a grade in one of the subject of the class. Each subject has a display order and a grade average of the class.
Each report page must display one class with a crosstab. The crosstab must display the grades of each students for each subjects.
Now, I struggle to show the following information (in yellow in the screenshot below) :
- The room number of the class should be located in the header, but I can't get to it from the header since the class is part of the list and not the header.
- The subjects should be on the Y-axis with :
- The subject label on the crosstable header, ordered by it's display order
- For each student line, the grade corresponding to the subject should be displayed
For each subject, the average class (from the JSON) should be displayed at the bottom of the crossbar
Some information :
- For the purpose of this report, I've used a JSON inline datasource.
- I succeeded to show the grade for each student by flattening the data into rows of student-subject-grade inside the class and grouping inside the crosstab. But this solution doesn't suits us because it duplicates data for each student (name, surname,..) and each subject.
- Though, I still couldn't show the class average from the JSON for each subject.
- We don't want to use a subreport.
I'd be glad if you could advise me on the best way to solve those issues.
Also, I've two additional questions :
- In a crosstab cell which contains a textbox, is it possible, under some conditions (Conditional formatting ?), to show something else instead of the textbox ? For example, sometimes a student doesn't take a subject. In this case, for this student, we'd like to include a diagonal lines through the whole cell.
- The Data Explorer doesn't show the drilled information such as the students values and their evaluations. Since those are a great help at design time, isn't there a way to display those ?
Thanks a lot for your help and I can provide more information if needed or if something is unclear.
Here's the JSON which is the main Datasource. Also, I'm attaching the .trdx (in the .zip) to this post.
{
"schoolName": "Muggle School",
"schoolImage": "iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNk+A8AAQUBAScY42YAAAAASUVORK5CYII=",
"classes": [
{
"name": "East123",
"roomNumber": 10,
"studentAmount": 3,
"students": [
{
"id": 12345,
"name": "Snow",
"surname": "John",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 6
},
{
"subjectCode": "FREN",
"grade": 3
},
{
"subjectCode": "MATH",
"grade": 5.5
}
]
},
{
"id": 12346,
"name": "Potter",
"surname": "Harry",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 4
},
{
"subjectCode": "FREN",
"grade": 4.5
},
{
"subjectCode": "MATH",
"grade": 4.5
}
]
},
{
"id": 12347,
"name": "Parker",
"surname": "Peter",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 5
},
{
"subjectCode": "FREN",
"grade": 5.5
},
{
"subjectCode": "MATH",
"grade": 6
}
]
}
],
"subjects": [
{
"code": "ENGL",
"label": "English",
"displayOrder": 2,
"classAverage": 5
},
{
"code": "FREN",
"label": "French",
"displayOrder": 3,
"classAverage": 4.5
},
{
"code": "MATH",
"label": "Mathematics",
"displayOrder": 1,
"classAverage": 5.5
}
]
},
{
"name": "South123",
"roomNumber": 20,
"studentAmount": 3,
"students": [
{
"id": 12348,
"name": "Sparrow",
"surname": "Jack",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 6
},
{
"subjectCode": "FREN",
"grade": 3
},
{
"subjectCode": "MATH",
"grade": 5.5
}
]
},
{
"id": 12349,
"name": "Black",
"surname": "Sirius",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 4
},
{
"subjectCode": "FREN",
"grade": 4.5
},
{
"subjectCode": "MATH",
"grade": 4.5
}
]
},
{
"id": 12350,
"name": "Baggins",
"surname": "Frodo",
"evaluations": [
{
"subjectCode": "ENGL",
"grade": 5
},
{
"subjectCode": "FREN",
"grade": 5.5
},
{
"subjectCode": "MATH",
"grade": 6
}
]
}
],
"subjects": [
{
"code": "ENGL",
"label": "English",
"displayOrder": 2,
"classAverage": 5
},
{
"code": "FREN",
"label": "French",
"displayOrder": 3,
"classAverage": 4.5
},
{
"code": "MATH",
"label": "Mathematics",
"displayOrder": 1,
"classAverage": 5.5
}
]
}
]
}1 Answer, 1 is accepted
Hello,
Thank you for the runnable report and the detailed description of the requirements and what you have already tried.
Indeed, you may achieve what you want more easily with flattened data or subreports.
Since these are not options in your scenario, here is what I suggest:
1. Displaying Room Number:
If I understood correctly, you need to display the correct Room Number in the corresponding page header. I suggest the PageExec page function:
= PageExec('textBox6', Fields.roomNumber)The idea is to show the Room Number corresponding to the Class Name, hence the scope is 'textBox6' which displays the name.
2. Displaying Subjects and Students:
You may use nested Lists in the main List to display the subjects, students, and evaluations.
In the attached sample, I placed the 'subjects' list in the main list 'class', and it lets you display the list of subject labels ordered by the 'displayOrder', which is a field from the same data source/array. The DataSource is bound to the field 'subjects' of the 'class' DataSource.
Under the label, you may add another List nested in the 'subjects' list - 'students'. Its DataSource is bound to the field 'students' of the 'class' DataSource. Note that you may use the following Expression in the Binding, letting you access the field from the grandparent of the list:
= ReportItem.Parent.Parent.DataObject.students
This lets you list the names of the students.
The 'evaluations' may be shown in another nested list, 'evaluations'. It is bound to its parent data and filtered to display only the data for the corresponding subject. Note again the reference to the grandparent data:
= Fields.subjectCode = = ReportItem.Parent.Parent.DataObject.code
The 'Classe Average' is a field from the 'subjects' and may be displayed in the list 'subjects' below the 'students' list.
3. Additional questions:
I confirm that you may use Bindings or Conditional Formatting to switch between report items. For example, you may set conditionally their Visible properties. The items may overlap since only one of them will be visible at the end. I have demonstrated this in the sample.
For a better design-time experience, I created dedicated JsonDataSourcs for all Lists. I copied and pasted the corresponding arrays from the main JSON to the smaller data sources. It may be a bit annoying but is useful when the nested lists/tables have lots of columns.
I hope the suggestions will fit your scenario. If you have other questions do not hesitate to ask.
Regards,
Todor
Progress Telerik
Rank 1
Thank you for your detailed answer, explanations and for the updated report.
For the record and for anyone who read this post, here is what the test-basic-crosstab-02.zip you sent back looks like:
1. Displaying Room Number
The PageExec page function is exactly what we need for the display of the roomNumber. Thank you.
2. Displaying Subjects and Students
Thank you for the useful examples of the use of the ReportItem.Parent.Parent reference.
Unfortunately, the solution provided does not fit in our scenario. I realize I should have included a more detailed screenshot of what the result should look like. Here is a mockup of what it should look like:
As you can see, each student appear only once on the X-axis and each subject appear only once on the Y-axis. At the crossing of both axis, you get the evaluation of the student for the subject.
As I have said, we have achieved this with a Crosstab by using flattened data. Unfortunately, our data source JSON is therefore much bigger in this scenario because you have to repeat the same information multiple times.
Is that result possible with a Crosstab instead of using nested Lists? A Crosstab is much more user friendly and maintainable, especially with the use of groups and conditional expressions.
3. Additional questions
I understand you can overlap items and make them visible under certain conditions. However, it that also possible inside a Crosstab ? I've only been able to have one item inside a Crosstab cell.
Thanks to the dedicated JsonDataSources, you can indeed see the fields at design time, very useful. If I understand correctly, the designer of the report will have to change back the Datasources of each List to the main one before pushing the report to production, is that right ?
Again, thank you for your help.
Regards
Let me start with the dedicated JsonDataSources. You don't need to remove them and assign other DataSources to the smaller tables/lists. The default DataSource values are automatically overridden by the Bindings to the main DataSource. The dedicated JsonDataSources are only for design purposes, to let you see the fields for the corresponding data item. They are not respected in run time when there is a DataSource Binding.
Regarding the Displaying of Subjects and Students, indeed, you may use Crosstab if you flatted the data to achieve the desired layout. The data items cannot resolve inner collections, hence the need to use flattened data. With the provided data schema, the nested collections may be displayed only through nested data items.
Generally, the List, Table, and Crosstab are different visual representations of the same data item. You may add Row and Column Groups and Static row/column Headers/Footers to all of them as explained in the article Adding Groups to Table and Crosstab Items.
Changing the appearance with Bindings and Conditional Formatting applies to all Report Items, including Crosstab and the items in its rows and columns cells. Note that the Expressions depend on the corresponding data scope.
By default, the Report Designers add a TextBox to each Crosstab/Table cell. You may delete it and add any other item, including another data item. You may use a Panel if you want to place more items in a single cell. For example, the List default content is an empty Panel.
Rank 1
Thank you for your answers that helped me a lot.
With your last piece of info about the TextBox in a Crosstab that can be replaced, I've managed to use a Crosstab by tweaking a little bit the data without having to flatten them.
The only part that I'm still missing, and I fear it's not possible, would be to be able to Sort the Column Group by the subject displayOrder... but since I've had to use a filtered List inside a Crosstab cell, I'm not sure I can get this info at the Crosstab level.
With this new version of the report (that I'm attaching to this answer), I've a few questions :
- To display the Subject label at the top and the Subject classAverage at the bottom, I've had to create two similar Lists as you can see below. Is there any way to only have one "listSubject" that'd contain both panel3 and panel5, even though they are in different Crosstab cells ?
- Is there by any chance a similar function as PageExec that I could use in the Crosstab Column Group Sorting ? For example : = PageExec('tbxSubjectsLabel', Fields.displayOrder)
Indeed, with the current data structure, the Crosstab cannot access the subject > displayOrder, as it is a field in a child data item. For this requirement, you need to restructure the data.
As a workaround, you may introduce a dummy Multivalue Report Parameter with AvailableValues > DataSource - the subjects. The parameter ValueMember should be '= Fields.code' as it is available also in the Crosstab DataSource, and its DataSource should be sorted on '= Fields.displayOrder'. The Value of the parameter must be '= AllDistinctValues(Fields.code)', which will represent the subject codes sorted by the displayOrder. This will let you use the following Expression for sorting on the Crosstab:
= IndexOfSubstr(Fields.subjectCode, Join(',', Parameters.Parameter1.Value))Note the usage of Text Functions to concatenate the sorted parameter values and then find the index of the corresponding subject and sort on it.
I have attached the modified report definition.
Regarding your particular questions:
1. A report item cannot encompass different Crosstab Areas. The cells of the different Crosstab Areas cannot be merged.
2. The Page Functions are valid only in the context of a Page Section. The latter cannot contain data items, including Crosstabs. That's why it is impossible using PageExec in a Crosstab.