Unconventional use of Microsoft Reporting Services
Historically, MS SSRS was chosen by the management of the companies in which I worked as a means of building corporate reporting. Of course, most often he did not choose, but got bundled with MS SQL Server. And since it is already there, then it should be used, and more often than not for its intended purpose, but as necessary. Therefore, we had to use Reporting Services for not quite trivial tasks. Under the cut several options for such an application.
Implementing SSRS through the Action Property object.
c, depending on the requirements, either to another report (Go to report if necessary, with passing parameters), or to a bookmark in the current report (Go to bookmark), or to a link (Go to URL). Drawing buttons is a separate topic. In my case, they evolved:
Let's add buttons to check in the best traditions of Microsoft products “Are you sure? Right? ”
Since SSRS does not know this by default, we will use javascript. Add a line to the item Properties-> Action-> Go to URL:
What javascript can do in conjunction with SSRS can be found in Google (alert, User Prompt, confirmations, open new window, injections, etc.). Yes, Javascript will not work in Visual Studio, it will be necessary to check the performance in the browser.
There is also a specific solution -
. In essence, this is the same Action -> Go to Repoort -> Current Report. It was used initially when the user pressed the buttons faster than the data was generated. But the user is lazy, so "I do not want to press a button, let it update itself."
Progressbar will serve as a normal Bar Chart, slightly adjusted
Now we set the report-> AutoRefresh property to the minimum possible auto-update value until our progress
And finally, we add from paragraph 1 a button Next, which will be visible (TextBox Properties -> Visibility) only after our progress bar reaches 100%
In this case, the usual linear graph with auto-update every second was used to display on the big screen (TV) statistics of the call center download, the number of current calls, agents, etc.
It is worth noting that the generation of the SSRS report itself should be much less than 1 second, i.e. the report should be easy, otherwise the auto-update will always hang on the icon
')
"I can not build in Excel Waterfall Chart, do something."
In this case, the user found it difficult to build a Waterfall Chart type in Excel 2007. There are many variations on the Internet on how to build this graph using SSRS, if interested, I can provide detailed step-by-step instructions.
The difference of this example was an unlimited number of initial-intermediate-final values that should not participate in the aggregation.
If the topic is interesting, I can add several examples of using the MS SQL Server + SSRS bundle as User driven ETL.
1. SSRS as a GUI or “Make Me Buttons”
Implementing SSRS through the Action Property object.
c, depending on the requirements, either to another report (Go to report if necessary, with passing parameters), or to a bookmark in the current report (Go to bookmark), or to a link (Go to URL). Drawing buttons is a separate topic. In my case, they evolved:
- from a simple textbox
- to schedule
With Properties -> Border -> Emboss and the active link in the title of the chart (Chart Title -> Action -> Go to Report) - and back to textbox
Let's add buttons to check in the best traditions of Microsoft products “Are you sure? Right? ”
Since SSRS does not know this by default, we will use javascript. Add a line to the item Properties-> Action-> Go to URL:
="javascript:var check=confirm('Do you really want to process all the data? \n \nPlease do not close the browser while executing.'); if (check == true) {window.open('http://localhost/Reportserver?%2fTarget_Report&rs:Command=Render','_self')}"What javascript can do in conjunction with SSRS can be found in Google (alert, User Prompt, confirmations, open new window, injections, etc.). Yes, Javascript will not work in Visual Studio, it will be necessary to check the performance in the browser.
There is also a specific solution -
. In essence, this is the same Action -> Go to Repoort -> Current Report. It was used initially when the user pressed the buttons faster than the data was generated. But the user is lazy, so "I do not want to press a button, let it update itself."2. SSRS as Progressbar
Progressbar will serve as a normal Bar Chart, slightly adjusted
- Add a bar chart, select values (Values),
- set the limits of the axis of values (Horizontal Axis Properties Minimum = 0, Maximum = 1), the header value selects the Value value, changes of which will display our progress bar. In my case, this is
=format(Fields!__.Value,"p0") - Delete all unnecessary (axes, titles, legend),
- set the absolute value of the chart area (Chart Area-> Position -> Custom Position change to 0,0,100,100),
- the width of the column (Chart Series-> Custom Attributes-> PointWidth make such that 1 column fill the entire area of the graph, in my case) PointWidth = 2,
- Set ChartArea Border Solid, Black and Chart Border None and get something like this:
Now we set the report-> AutoRefresh property to the minimum possible auto-update value until our progress
=IIF(Fields!__.Value <> 1,1,0) reaches 100% =IIF(Fields!__.Value <> 1,1,0)And finally, we add from paragraph 1 a button Next, which will be visible (TextBox Properties -> Visibility) only after our progress bar reaches 100%
=IIF(Fields!__.Value <> 1,True,False)3. SSRS as Online Monitor
In this case, the usual linear graph with auto-update every second was used to display on the big screen (TV) statistics of the call center download, the number of current calls, agents, etc.
It is worth noting that the generation of the SSRS report itself should be much less than 1 second, i.e. the report should be easy, otherwise the auto-update will always hang on the icon
')
4. SSRS as an interface for building graphs
"I can not build in Excel Waterfall Chart, do something."
In this case, the user found it difficult to build a Waterfall Chart type in Excel 2007. There are many variations on the Internet on how to build this graph using SSRS, if interested, I can provide detailed step-by-step instructions.
The difference of this example was an unlimited number of initial-intermediate-final values that should not participate in the aggregation.
If the topic is interesting, I can add several examples of using the MS SQL Server + SSRS bundle as User driven ETL.
Source: https://habr.com/ru/post/164611/
All Articles