A comprehensive example of Reportingservies--sqlserver report development

If we install sqlserver2008 R2, a report development tool will be installed automatically

Do not think that this report development tool is only suitable for sqlserver2008, in fact, in sqlserver2012 is also supported, in fact, I am now in the project is the use of sqlserver2012.

Using this tool for report development will be very fast and convenient. Here I use a comprehensive example to demonstrate its skills, it is no exaggeration to say that if you can use this tool to make this demo , then the work encountered in the 80% report, you can use this tool for development.

Let's look at the effect we're going to achieve:

Tables and fields used in the report:

[SQL]View Plaincopy

2. --table Water Quantity: Hx_t_waterusedamount
4. --Field account opening information: Hx_fcustomerandmeterrelaid primary key: Hx_t_waterusedamountid water meter: Hx_fmeterid section: Hx_fzone Water consumption: hx_famount
6. --Water Assessment reason: Hx_festimateamountreason settlement water: Hx_fpayamount
8. --meter type Hx_frecordtype (estimate water:, 000,001 not copied see: 100,000,002 Normal: 100,000,000 prepaid card table estimate water: 100,000,003)
10. --Water State: Hx_fstate (Plan: 100,000,000, has entered the amount of water: 100,000,001, waiting for input amounts: 100,000,002, has been generated receivable: 100,000,006, awaiting approval: 100,000,004)
12. <span style="font-family: the song Body;" >--</span> This time reading stop: hx_freading
14. --Water year: hx_fyear water Month: Hx_fmonth times: Hx_ftimes
16. --This time: hx_frecorddate estimated water charges: hx_fprepayment estimated generation Fee: Hx_fpreproxypayment
20. --table Water Category Hx_t_watercategory
22. --field Statistics class: Hx_fstatcategoryoneid primary key: Hx_t_watercategoryid name: Hx_fname
26. Water quality of--table Hx_t_waterproperty
28. --field primary key: Hx_t_waterpropertyid water Category: Hx_fwatercategoryid
32. --table-meter-reading team attribution hx_t_teamattribution
34. --Field PRIMARY key: Hx_t_teamattributionid meter Reading team information: Hx_fteamid Levy information: Hx_flevyinstituteid

Okay, now it's officially started.

As can be seen from the above, we use two tables to show this report, and here we use two datasets, but their query conditions are consistent.

Let's start with the show on the table above.

Let's look at SQL:

[SQL]View Plaincopy

2. DECLARE @begin_date datetime
4. DECLARE @end_date datetime
6. Select @begin_date = getdate ()
10. Declare @BusinessunitId uniqueidentifier --Levy Institute
12. --set @BusinessunitId = ' 6a2537dd-b87a-e411-93fe-002590622897 '----the 15th fee
14. DECLARE @TeamId uniqueidentifier --meter-reading team
16. --set @TeamId = ' 18acbd81-5b45-e411-9402-6cae8b22702d '--15th toll collection (Scattered check list group)
18. DECLARE @StarDate datetime
20. DECLARE @EndDate datetime
22. SET @StarDate = convert (datetime,' 2015-01-15 00:00:00 ', +)
24. SET @EndDate = convert (datetime,' 2015-01-25 23:59:59 ', +)
26. DECLARE @sHMS nvarchar (10)
28. DECLARE @eHMS nvarchar (10)
30. SET @sHMS =' 00:00:00 ';
32. SET @eHMS =' 23:59:59 ';
34. DECLARE @ZoneNO varchar (+)
38. -----------------The SQL body----------------------------------------------------------execution time:
40. DECLARE @strSDate nvarchar, @strEDate nvarchar (50)
42. DECLARE @StartDateTime datetime, @EndDateTime datetime
44. SET @strSDate =CONVERT (varchar, @StarDate, +) +"[email protected]
46. SET @strEDate =CONVERT (varchar, @EndDate, +) +"[email protected]
48. SET @StartDateTime =convert (datetime, @strSDate,.)
50. SET @EndDateTime =convert (datetime, @strEDate,.)
52. -------------------------------------the table above-------------------------------------------------
54. Select
56. 1 Watermetercount, --Number of tables
58. W.hx_fpayamount, --Water
60. W.hx_fprepayment,--Estimated water charges
62. W.hx_fpreproxypayment,--estimated generation charges
64. C.hx_fname --Water category
66. from
68. Hx_t_waterusedamount W --Water
70. INNER JOIN hx_t_teamattribution n --meter-reading team ownership
72. On W.owningteam=n.hx_fteamid
74. and IsNull (W.hx_fzone,") = IsNull (isnull (@ZoneNO, W.hx_fzone),")
76. and IsNull (W.owningteam,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@TeamId, W. Owningteam),' 00000000-0000-0000-0000-000000000000 ')
78. and w.hx_fpayamount>0 --clearing water
80. and W.hx_frecorddate between @StartDateTime and @EndDateTime
82. and IsNull (N.hx_flevyinstituteid,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@ Businessunitid,n.hx_flevyinstituteid),
84. ' 00000000-0000-0000-0000-000000000000 ')
86. Left JOIN hx_t_waterproperty p -water Nature
88. On W.hx_fwaterpropertyid=p.hx_t_waterpropertyid
90. Left JOIN hx_t_watercategory C --water category
92. On P.hx_fwatercategoryid=c.hx_t_watercategoryid
96. -----------------END----------------------------------------------------------------------------
98. Select @end_date = getdate ()
100. Select DateDiff (MS, @begin_date, @end_date) as ' spents/milliseconds '

The results of the implementation are as follows:

It can be seen that this is a very common list of vertical table, if the use of pure SQL to achieve the conversion effect, you have to write a series of complex SQL statements, but by using this report development tool, we can easily change its portrait to horizontal display results.

The procedure is as follows:

1. Open this Report development tool, file--New--Project

2.Right-click the report directory in the solution on the right, and then continue to the next step,

However, given that the interface configuration of the matrix is very locked, let's Select the table, and then click Next until you finish

1, delete the table above the interface, and then drag a matrix from the left sidebar,

Select the data cell, right--insert row--Inside group--below, add 4 rows, then right--insert column--Outside the group (because you want to count totals)

Column this place, choose Hx_fname This field, from SQL can see hx_fname This field is we need to convert to horizontal table display of the field, then the data here, select the field to be aggregated.

Right report data,

A number of parameters are automatically generated here because the SQL statement we just added is used, note that the name is the same as the SQL statement we just declared .

Select Properties, right-click, to view configuration information for parameters

These things everyone to a bit more, naturally familiar with, involved in too many things, I can not say all of a sudden.

Here are two parameters that have cascading relationships.

Let's look at the configuration of these two parameters, both of which are shown in the form of a drop-down box, and the values are taken from the database.

First look at the parameters:businessunitid

Where does this data set come from? We can add them ourselves

Take a second look at the parameter:teamid

Right-click an empty space--Create a placeholder

Batch modification can be modified directly here,

Otherwise, only one text box can be modified with a text box property. # represents the kilobits display, followed by a numeric code that retains the number of decimal digits, and0 represents an integer

Quantity / water Volume:#,0

Price:0.00

Amount:#,0.00

The following assembly in the report is similar to the operation, there is not much to say, there are too many things.

SQL is as follows:

[SQL]View Plaincopy

2. ----------------The SQL body----------------------------------------------------------execution time:
4. DECLARE @strSDate nvarchar, @strEDate nvarchar (50)
6. DECLARE @StartDateTime datetime, @EndDateTime datetime
8. SET @strSDate =CONVERT (varchar, @StarDate, +) +"[email protected]
10. SET @strEDate =CONVERT (varchar, @EndDate, +) +"[email protected]
12. SET @StartDateTime =convert (datetime, @strSDate,.)
14. SET @EndDateTime =convert (datetime, @strEDate,.)
16. ----------------------------------The following table------------------------------------------------------
18. If object_id (' tempdb.. #t_estimateamountreason ') is not a null drop table #t_estimateamountreason
22. SELECT * into #t_estimateamountreason from
24. (select distinct t1. Label,t2. Value from metadataschema.localizedlabel T1
26. Inner join Metadataschema.attributepicklistvalue T2
28. On t1. Objectid=t2. Attributepicklistvalueid inner join metadataschema.optionset T3 on T2. Optionsetid=t3. Optionsetid
30. where T3. name=' hx_estimateamountreason_values ' and t1. Objectcolumnname=' DisplayName ' and t1. languageid=2052) T
32. --select * from #t_estimateamountreason
36. Select
38. 1 metercounts, --water meter block number
40. IsNull (E.label,' other ') reasonname --Name of reason for water assessment
42. From Hx_t_waterusedamount w --Water
44. INNER JOIN hx_t_teamattribution n --meter-reading team ownership
46. On W.owningteam=n.hx_fteamid
48. and IsNull (W.hx_fzone,") = IsNull (isnull (@ZoneNO, W.hx_fzone),")
50. and IsNull (W.owningteam,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@TeamId, W. Owningteam),' 00000000-0000-0000-0000-000000000000 ')
52. and w.hx_fpayamount=0 --clearing water
54. and W.hx_frecorddate between @StartDateTime and @EndDateTime
56. and IsNull (N.hx_flevyinstituteid,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@ Businessunitid,n.hx_flevyinstituteid),
58. ' 00000000-0000-0000-0000-000000000000 ')
60. Left JOIN #t_estimateamountreason e – Reason for water assessment
62. On E.value=w.hx_festimateamountreason
66. UNION All
68. Select 1 Metercounts,e.label reasonname from #t_estimateamountreason e
72. If object_id (' tempdb.. #t_estimateamountreason ') is not a null drop table #t_estimateamountreason
74. ----------------END----------------------------------------------------------------------------
    

A comprehensive example of Reportingservies--sqlserver report development