I have contacted many customers. Before there was no data system, many statements were drawn by Excel. Most of these Excel tables are drawn directly by business people for the convenience of recording data. In such drawn tables, grouping and merging cells are quite free and unconstrained. And when the business people give these Excel to the programmer to use the report tool to make, it is natural to also want the report to be able to merge the cells like Excel. However, this is a problem for programmers, because the group reports produced by the commonly used report development tools are very conventional, and it is difficult to merge cells arbitrarily according to the template provided by the business people. But this can make business people feel rigid, inflexible, or even unable to embody specific data relationships.
Now, master the dry report of the program ape brother is about to break the convention, the challenge is impossible! In other words, the effect of grouping cells should be arbitrarily merged horizontally and vertically according to the same data. The following figure shows the assessment indicators of each branch and subordinate structure displayed by XX Company at different levels, and the merged display is based on the granularity of different levels:
Seeing these renderings, our hearts can not help beating a small drum, really can do it, if you can not need to spend a high price to buy expensive equipment? Here, xiaobian secretly tell you (the general people I will not say), embellish dry statements can not only make such a free and cool effect, but also report tools affordable! (Don’t believe you can go to run dry official website to see Austria.)
Let’s take the first picture as an example to see how the dry report is done…
Firstly, prepare data: here we use the built-in data set of the run and dry report to construct three data sets, among which DS1 is the hierarchical information of XX company’s network and subordinate structure, DS2 is the assessment indicator of network and subordinate structure, and DS3 is the corresponding name of network and subordinate structure, as shown below:
Data set DS1:
Data set DS2:
Data set DS3:
Note: The above built-in dataset field types are strings.
Next, draw a table and fill in the cell expression, as shown below:
Where A4 cell expression: =ds1.group(province; Ds3. select(name,id==value()); ds3.select(name,id==value());
B4 cell expression: =ds1.group(city; Ds3. Select (name,id==value()); ds3. Select (name,id==value());
If (ds1. Group (ds1; Ds3. select(name,id==value());
D4 cell expression: =ds1.group(sales department; Ds3. select(name,id==value()); ds3.select(name,id==value());
E4 cell expression: =ds1.group(dot; Ds3. select(name,id==value()), extend – > null value merge into: left merge;
Ds1. group(schema 1; Ds3. select(name,id==value());
Select (ds1; select(ds1; Ds3. select(name,id==value());
H4 cell expression: ds2.select@1(kpi1,id==G4), display value expression: if(value()==null,0,value()), display format: #0.00;
I4, J4, K4 cell expression: =ds2. Field name, display value expression: if(value()==null,0,value()), display format: #0.00
And then… . A perfect dynamic merged lattice report is complete.
review
Review the problems encountered and tips for solving them:
1. Dynamically merge cells
【 Technique 】 Use same value merge or null value merge
2. Cell expression to achieve multi-data set association
Ds.select ()
3. Cell display value control
Use the data set function ds.select() and non-data set functions if() and value().
Finally, we found that, in fact, we only use the same value merger and null value merger report here, is not careful, and double 叒 to get new skills? Don’t hesitate to search “Raqsoft” on wechat public account to add attention, come to join the big family of Rungan, your program ape brother has been waiting for you for a long time.
All functions involved in the production of statements can be consulted from here, and the same value merge and null value merge is here to consult the details, this secret xiaobian only tell you the Austrian.