SSRs series of Microsoft bi-report design for combining charts in Excel

One of the questions discussed in the group is that there is a chart similar to this in Excel. The chart is above, and the value on the X axis of the chart is exactly the column header of the table below. How should this be implemented in SSRS? SSRs 2008, 2008rs, and 2012 do not actually have such corresponding controls. We usually think of a separate chart on the top, and a table on the bottom is merged. But there will be some problems, because the value of the chart axis is not fixed and will expand as the aggregate value increases. In addition, there is no way for the column headers in the table to completely correspond to the coordinates on the X axis of the chart in a vertical line, and there must be deviations. In SSRS, we can use some techniques to achieve this effect. Below is the effect similar to that in Excel that I use column chart. Although the implementation is very close, I do not recommend this! Because we can use other methods to make the data more reasonable with reasonable flexibility! The original test data (adventureworksdw2012) is a simple query-

SELECT DPC.EnglishProductCategoryName,    FIS.OrderDate,    FIS.OrderDateKey,    DD.CalendarYear,    FIS.SalesOrderNumber,    FIS.SalesOrderLineNumber,    FIS.SalesAmount AS Internet_SalesFROM FactInternetSales AS FISINNER JOIN DimProduct AS DPON FIS.ProductKey = DP.ProductKeyINNER JOIN DimProductSubcategory AS DPSON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKeyINNER JOIN DimProductCategory AS DPCON DPS.ProductCategoryKey = DPC.ProductCategoryKeyINNER JOIN DimDate AS DDON FIS.OrderDate = DD.FullDateAlternateKey

Create a report and dataset. Set the matrix row group to product category and the column group to calendar year.

Add two new rows-select outside group-above. And delete the first line. Only rows are deleted when the group is deleted. This is what it looks like after deletion. The second row is the year to be displayed in the header, and the first row is used as the column chart in the row. Insert data bar at the position of the first line and select Stacked column. Fill in the aggregated values, groups, and series in the Stacked column. After filling, you can see the approximate effect. Each column is expanded from the matrix column group, the column chart group in each column depends on the Year in the current column group. Do some beautification work on your own, including formatting. There is a problem. Different series come out, but the relationship between different series and product classification cannot be distinguished. The idea is that the color templates in the chart use custom functions to obtain the specified color. These colors must be the same as those in the Custom template.

Private colorPalette As String() = {"SeaGreen", "Orange", "RoyalBlue"}    Private count As Integer = 0    Private mapping As New System.Collections.Hashtable()    Public Function GetColor(ByVal groupingValue As String) As String        If mapping.ContainsKey(groupingValue) Then            Return mapping(groupingValue)        End If        Dim c As String = colorPalette(count Mod colorPalette.Length)        count = count + 1        mapping.Add(groupingValue, c)        Return c    End Function

Custom functions of a report.

Set the Custom template color of the Stacked column chart. Select the English product category text box, set the background color, and call the function. = Code. getcolor (Fields! Englishproductcategoryname. value) Finally, further formatting is required to achieve this effect, and operations such as adjusting the border make the Stacked column look like a whole, not a single individual, these beautification details are not described one by one. For more information, see Case 6, CASE 13, Case 18, Case 24, and improvement of report beautification and design in SSRS report design. Therefore, this article only describes the implementation process, design skills that are too detailed, including some concepts of aggregate grouping and user-defined functions, which are not the focus of this article. The line chart format shown at the beginning of the article can be done or not. You can adjust the plane width of the line chart area to make several independent line areas join into a whole. But how does one implement the Y axis on the leftmost side? Do you need to use the self-contained series in the chart instead of the bottom line? These functions are not implemented by dragging and dropping. You need to master various knowledge points and skill points in the SSRs report and the combination and use skills of different controls. Of course, in this case, if it is me in actual development, I will refuse to design reports like this, obviously, reading and data presentation do not show the effect shown in Excel. Therefore, in the actual report development process, it is very likely that it cannot be imitated to imitate it. Spit it out! In actual projects, it is often accompanied by saliva and fighting in guiding customers to change their existing habits and adapt to new habits, including suffering customers' doubts about their own capabilities, these are normal in project development. For example, many customers often propose that some results that they think are well implemented but are hard to achieve must be achieved. These extreme situations also exist objectively. For me, I may also encounter abnormal and unreasonable demands, but I usually reject them and tell the customer honestly that they cannot implement them, because it violates the characteristics of SSRs. But at the same time, I will try to come up with another alternative solution for the customer to choose, so that the customer is generally easier to accept and compromise. Just like a business trip from Beijing to Shanghai, telling them that the plane is indeed unable to fly because of technical reasons. If the customer wants to prove that the plane is capable of taking off, they can choose high-speed trains, in general, the customer will choose the best solution that he can most accept, because no one can afford the time.

SSRs series of Microsoft bi-report design for combining charts in Excel