Customizing it asset reports using SQL2012 Report Builder

This article describes how to customize a report by using SQL Server SP1 Report Builder, in order to get closer to the actual combat, this article will customize an IT asset report.

First, the deployment of IT asset management system

1. This article uses Microsoft SCCM2012R2 (System Center Configuration Manager, R2) as an IT asset management system to collect hardware information from clients. Specific deployment process because this is not the focus of this article, and there is a lot of information on the deployment of SCCM installation, interested in the words can be consulted on their own. It is important to note, however, that the report features component must be installed when you install SQL Server 2012SP1.

2, the deployment environment is as follows:

Computer name	IP Address	Operating system	Role	Installing the Software
Sccm-dc	192.168.10.150	Win2012r2	Domain controllers, DNS servers	DC, DNS
Sccm	192.168.10.151	Win2012r2	Database server (with report functionality), SCCM Server	SQLSERVER2012SP1, SCCM2012R2, IIS, and so on

3. After deploying SCCM, install Client Agent to SCCM-DC and SCCM server.

650) this.width=650; "Width=" 1010 "height=" 586 "title=" 01.PNG "style=" Width:736px;height:420px;float:none; "alt=" Wkiom1grs6hzm5b_aahcrlgd-80583.png "src=" Http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6HzM5B_AAHCrLGd-80583.png "/>

4, enable SCCM hardware inventory collection function, successfully collected the SCCM, SCCM-DC two server hardware information, which is written in the SCCM database, our IT assets report is based on these hardware information data in the database for custom development.

650) this.width=650; "Width=" 1085 "height=" 606 "title=" 02.PNG "style=" Width:738px;height:427px;float:none; "alt=" Wkiol1grs6hicr_taacodqdqgk8955.png "src=" Http://s2.51cto.com/wyfs02/M02/89/62/wKioL1gRs6HiCR_tAACODqdQGk8955.png "/>

650) this.width=650; "Width=" 1081 "height=" 602 "title=" 03.PNG "style=" Width:739px;height:398px;float:none; "alt=" Wkiol1grs6kqyppjaacrv8td3la234.png "src=" Http://s2.51cto.com/wyfs02/M00/89/62/wKioL1gRs6KQYpPJAACRV8Td3lA234.png "/>

Second, debug the IT Assets report SQL query statement

The original source of this article: Jiangjianrong Technical Blog http://jiangjianlong.blog.51cto.com/3735273/1867368

1, SCCM database, each computer as long as the client agent installed will be assigned a ResourceID (that is, the resource ID), different hardware information written to different tables, each table will have a ResourceID field to identify who is the hardware information, Therefore, a multi-table connection query can be made through a resource ID, and all of the fields we want are displayed in a single report. The database for SCCM defines some views by default, and the SQL query statements in this article are actually done through query views. The SQL statement is the information that is queried through the view v_R_System to the resource ID and computer name.

650) this.width=650; "width=" 819 "height=" 544 "title=" 04-1.png "style=" Width:737px;height:480px;float:none; "alt=" Wkiom1grs6lt6du3aafv2ecmyiu925.png "src=" Http://s2.51cto.com/wyfs02/M01/89/65/wKiom1gRs6LT6Du3AAFV2EcmyiU925.png "/>

2, the SQL statement is through the View V_gs_network_adapter_configur query to the resource ID, IP address and MAC address information. The For XML path function is used for merging multiple behavior lines, because some client computers have multiple IP addresses, which are displayed as multiple rows in the table of the SCCM database, which can be combined on a single line, so it also uses the + ', ' just to add a comma between multiple IP addresses.

650) this.width=650; "width=" 1057 "height=" 547 "title=" 05-1.png "style=" Width:737px;height:402px;float:none; "alt=" Wkiom1grs6lhqd4zaagic04yx5k806.png "src=" Http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6LhqD4ZAAGIc04YX5k806.png "/>

3, the above we in two views of the different hardware information, the common point is that all have a resource ID field, then we can use the LEFT JOIN statement, the two views of the outer join query, the effect as shown. Because we are to find the computer name of the table as the left table, the table should be based on, even if some of the hardware information is empty, but also to display white space, so you need to use a left outer connection. The left join makes it easy to modularize and connect SQL statements that query different hardware information.

650) this.width=650; "Width=" 1044 "height=" 608 "title=" 06-1.png "style=" Width:741px;height:425px;float:none; "alt=" Wkiom1grs6obkwwnaagrxjicmlu367.png "src=" Http://s2.51cto.com/wyfs02/M01/89/65/wKiom1gRs6OBkwWNAAGRxjIcmLU367.png "/>

4, that is, I finally completed the SQL statement, query out all the fields to be customized as the basis for the following it asset reports. In fact, the writing of a SQL query statement needs to be constantly modified and debugged, and applied in different deployment environments to verify the bug, such as the For XML path function above, if only in a simple environment where the client has only 1 IP, because there is no need for multi-line merging, Will not use this function, but once a client has more than one IP case, you will find that the bug appeared, need to modify debugging, continuous improvement and improvement.

650) this.width=650; "Width=" 797 "height=" 494 "title=" 07.PNG "style=" Width:738px;height:466px;float:none; "alt=" Wkiol1grs6pav-vxaab0edtgp7w705.png "src=" Http://s2.51cto.com/wyfs02/M01/89/62/wKioL1gRs6PAv-vXAAB0eDTgP7w705.png "/>

650) this.width=650; "title=" 08.PNG "style=" Float:none; "alt=" wkiol1guvfoj2xevaab3vvmbvoa165.png "src=" http:/ S4.51cto.com/wyfs02/m02/89/79/wkiol1guvfoj2xevaab3vvmbvoa165.png "/>

650) this.width=650; "title=" 09.PNG "style=" Float:none; "alt=" wkiom1guvfbictibaaciimlbgny219.png "src=" http:/ S1.51cto.com/wyfs02/m01/89/7b/wkiom1guvfbictibaaciimlbgny219.png "/>

5, because the specific SQL statement writing is not the focus of this article, so skip, but I also put the main hardware information in which views to summarize, for reference only.

Hardware information	Field name	Your view
Computer name	Netbios_name0	v_R_System
IP Address	IPAddress0	V_gs_network_adapter_configur
MAC address	MACaddress0
NIC model	Name0	V_gs_network_adapter Vgna2
cpu type	NAME0	v_gs_processor
Number of cores per CPU	NumberOfCores0
Number of CPUs	NumberOfProcessors0	V_gs_computer_system
System architecture	SystemType0
Hardware Manufacturers	Manufacturer0
Owning domain	Domain0
Product model	Model0
Operating system	Caption0	V_gs_operating_system
Drive letter	DeviceID0	V_gs_logical_disk
Disk format	Filesystem0
Disk size	Size0
HDD model	Caption0	V_gs_disk
Video card model	Name0	V_gs_video_controller
Physical memory	TotalPhysicalMemory0	V_gs_x86_pc_memory
HDD model	Caption0	V_gs_disk

III. Customizing IT Asset reports

The original source of this article: Jiangjianrong Technical Blog http://jiangjianlong.blog.51cto.com/3735273/1867368

1, after the completion of the SQL statement writing debugging, we can create a custom report. Use your browser to enter Url:sccm.long.me/reports for the report server (SQL Server Reporting Services), and then click Report Builder when you log on.

650) this.width=650; "Width=" 758 "height=" 575 "title=" 10.PNG "style=" Width:739px;height:560px;float:none; "alt=" Wkiol1grs6tijbmwaabwy6g9oxc188.png "src=" Http://s2.51cto.com/wyfs02/M02/89/62/wKioL1gRs6TijbMWAABwY6G9Oxc188.png "/>

2, click to run the program. 650) this.width=650; "Width=" 760 "height=" 573 "title=" 11.PNG "style=" Width:736px;height:564px;float:none; "alt=" Wkiom1grs6tczdyxaac86mkhthq037.png "src=" Http://s2.51cto.com/wyfs02/M02/89/65/wKiom1gRs6TCzdyXAAC86mKHtHQ037.png "/>

3. After you start Report Builder, select Create a new blank report.

650) this.width=650; "Width=" 1009 "height=" 607 "title=" 13-1.png "style=" Width:735px;height:483px;float:none; "alt=" Wkiom1grs6xyrzn4aaetegjnxnu327.png "src=" Http://s2.51cto.com/wyfs02/M00/89/65/wKiom1gRs6XyRZn4AAETEGjNxnU327.png "/>

4. Add a data source, select "Use a connection embedded in my report", select the connection type is "Microsoft SQL Server", enter the connection string into data source=.;i Nitial catalog=cm_001, you can also point to generate a connection string via the graphical interface, and finally click Test connection to see that the connection has been successfully created.

650) this.width=650; "Width=" 999 "height=" 606 "title=" 14.PNG "style=" Width:735px;height:435px;float:none; "alt=" Wkiom1grs6wyxgukaadhzxtahc4569.png "src=" Http://s5.51cto.com/wyfs02/M00/89/65/wKiom1gRs6WyxgUkAADhzXTAHc4569.png "/>

5, add the data set, the data source select the data source created above, at the query place we in the previous debugging SQL statements pasted in, immediately can come in handy.

650) this.width=650; "Width=" 1001 "height=" 611 "title=" 15.PNG "style=" Width:741px;height:442px;float:none; "alt=" Wkiol1grs6xjlyp3aad0k-emlte100.png "src=" Http://s5.51cto.com/wyfs02/M00/89/62/wKioL1gRs6XjLYp3AAD0k-EmLTE100.png "/>

6, after adding the data set can see the field is recognized, then we create the table, click Insert-Table-Table wizard.

650) this.width=650; "Width=" 1004 "height=" "title=" 16.PNG "style=" Width:741px;height:507px;float:none; "alt=" Wkiol1grs6big8s9aafkarsr_ns962.png "src=" Http://s4.51cto.com/wyfs02/M00/89/62/wKioL1gRs6biG8S9AAFkArSR_Ns962.png "/>

7. In the Table Wizard, select the dataset by default, as we have created a dataset above and directly next.

650) this.width=650; "Width=" 843 "height=" 602 "title=" 17-1.png "style=" Width:739px;height:552px;float:none; "alt=" Wkiom1grs6atzcamaabzttbonec030.png "src=" Http://s5.51cto.com/wyfs02/M01/89/65/wKiom1gRs6aTzCAmAABzTTBOnEc030.png "/>

8. Drag the resource ID to the row group, and then drag all the remaining fields to the value.

650) this.width=650; "width=" 844 "height=" 604 "title=" 18.PNG "style=" Width:740px;height:530px;float:none; "alt=" Wkiom1grs6bch32qaacqnj_qaf8947.png "src=" Http://s4.51cto.com/wyfs02/M01/89/65/wKiom1gRs6bCh32QAACQnJ_qAf8947.png "/>

9. check boxes for subtotals and grand totals, expand/collapse groups are removed.

650) this.width=650; "width=" 844 "height=" 601 "title=" 19.PNG "style=" Width:739px;height:554px;float:none; "alt=" Wkiol1grs6ebuoozaabxkbzuv2y011.png "src=" Http://s4.51cto.com/wyfs02/M01/89/63/wKioL1gRs6eBUOOzAABXkBzUV2Y011.png "/>

10, choose your favorite style, then click Finish to complete the creation of the table.

650) this.width=650; "Width=" 842 "height=" 601 "title=" 20.PNG "style=" Width:739px;height:557px;float:none; "alt=" Wkiom1grs6et-do_aablpipeuyq548.png "src=" Http://s5.51cto.com/wyfs02/M02/89/65/wKiom1gRs6eT-DO_AABLPiPEUYQ548.png "/>

11, so our IT assets report is basically completed.

650) this.width=650; "Width=" 1005 "height=" 606 "title=" 21-1.png "style=" Width:737px;height:471px;float:none; "alt=" Wkiol1grs6fa0ewaaafxabaveze179.png "src=" Http://s5.51cto.com/wyfs02/M02/89/63/wKioL1gRs6fA0ewaAAFxABavezE179.png "/>

12, of course, we can add a title, insert a picture, adjust the width of the cell, center display, and so on to customize and beautify, the footer in the [&executiontime] (auto-generated time) dragged into the table, you can export the report together.

650) this.width=650; "Width=" "height=" 515 "title=" 22.PNG "style=" width:735px;height:375px; "alt=" Wkiom1gvme-tt6k7aaf5gn8i1qy535.png "src=" Http://s3.51cto.com/wyfs02/M01/89/81/wKiom1gVmE-TT6k7AAF5gN8i1QY535.png "/>

13. Finally click Run in the top left corner and you will see how our Customized IT assets report works.

650) this.width=650; "Width=" "height=" 591 "title=" 23.PNG "style=" Width:724px;height:442px;float:none; "alt=" Wkiol1grs6iqqfn4aagc8bko_ve803.png "src=" Http://s1.51cto.com/wyfs02/M00/89/63/wKioL1gRs6iQqFN4AAGc8bko_vE803.png "/>

14. Do not forget to save at last, save to report server by default.

650) this.width=650; "title=" 24.PNG "style=" Float:none; "alt=" wkiol1grs6jigex0aabejccdwca312.png "src=" http:/ S1.51cto.com/wyfs02/m01/89/63/wkiol1grs6jigex0aabejccdwca312.png "/>

15, log on to the report server, you can see our IT assets report, click on it, you will be able to see the effect of running in the browser, if the page is not displayed properly, you can open the browser's compatible view mode.

650) this.width=650; "Width=" 757 "height=" 405 "title=" 25-1.png "style=" Width:739px;height:406px;float:none; "alt=" Wkiom1grs6nzoh-xaacpooi8e98559.png "src=" Http://s1.51cto.com/wyfs02/M02/89/65/wKiom1gRs6nzOH-XAACPooi8e98559.png "/>

650) this.width=650; "Width=" "height=" 519 "title=" 26.PNG "style=" Width:724px;height:365px;float:none; "alt=" Wkiom1grs6ngzselaafwe-lne3y973.png "src=" Http://s5.51cto.com/wyfs02/M01/89/65/wKiom1gRs6ngZSelAAFwE-Lne3Y973.png "/>

16, we can export the report, such as export to Excel file.

650) this.width=650; "Width=" "height=" 530 "title=" 27.PNG "style=" Width:733px;height:395px;float:none; "alt=" Wkiol1grs6mh_yokaaf0rdfsqsm244.png "src=" Http://s5.51cto.com/wyfs02/M01/89/63/wKioL1gRs6mh_yoKAAF0RDFsQsM244.png "/>

650) this.width=650; "Width=" "height=" 533 "title=" 28.PNG "style=" Width:735px;height:406px;float:none; "alt=" Wkiom1grs6rtrxrpaaga9amlfzc152.png "src=" Http://s5.51cto.com/wyfs02/M02/89/65/wKiom1gRs6rTrXrPAAGA9AmLFzc152.png "/>

17. Open with WPS or Excel after export and the content is displayed in the browser.

650) this.width=650; "Width=" "height=" 521 "title=" 29.PNG "style=" width:735px;height:348px; "alt=" Wkiol1gvms6bedzhaagfws5rzte551.png "src=" Http://s3.51cto.com/wyfs02/M01/89/7E/wKioL1gVmS6BEDZhAAGfwS5RzTE551.png "/>

Now our IT assets report is complete, as long as the database has the corresponding data, write SQL query statements, using SQL Server 2012 Report Builder can customize any report we want, and the report can be accessed in the browser run, can also be exported to a report file, very useful.

This article is from the "Jiangjianrong Technology blog" blog, make sure to keep this source http://jiangjianlong.blog.51cto.com/3735273/1867368

Customizing it asset reports using SQL2012 Report Builder