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
- --table Water Quantity: Hx_t_waterusedamount
- --Field account opening information: Hx_fcustomerandmeterrelaid primary key: Hx_t_waterusedamountid water meter: Hx_fmeterid section: Hx_fzone Water consumption: hx_famount
- --Water Assessment reason: Hx_festimateamountreason settlement water: Hx_fpayamount
- --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)
- --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)
- <span style="font-family: the song Body;" >--</span> This time reading stop: hx_freading
- --Water year: hx_fyear water Month: Hx_fmonth times: Hx_ftimes
- --This time: hx_frecorddate estimated water charges: hx_fprepayment estimated generation Fee: Hx_fpreproxypayment
- --table Water Category Hx_t_watercategory
- --field Statistics class: Hx_fstatcategoryoneid primary key: Hx_t_watercategoryid name: Hx_fname
- Water quality of--table Hx_t_waterproperty
- --field primary key: Hx_t_waterpropertyid water Category: Hx_fwatercategoryid
- --table-meter-reading team attribution hx_t_teamattribution
- --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
- DECLARE @begin_date datetime
- DECLARE @end_date datetime
- Select @begin_date = getdate ()
- Declare @BusinessunitId uniqueidentifier --Levy Institute
- --set @BusinessunitId = ' 6a2537dd-b87a-e411-93fe-002590622897 '----the 15th fee
- DECLARE @TeamId uniqueidentifier --meter-reading team
- --set @TeamId = ' 18acbd81-5b45-e411-9402-6cae8b22702d '--15th toll collection (Scattered check list group)
- DECLARE @StarDate datetime
- DECLARE @EndDate datetime
- SET @StarDate = convert (datetime,' 2015-01-15 00:00:00 ', +)
- SET @EndDate = convert (datetime,' 2015-01-25 23:59:59 ', +)
- DECLARE @sHMS nvarchar (10)
- DECLARE @eHMS nvarchar (10)
- SET @sHMS =' 00:00:00 ';
- SET @eHMS =' 23:59:59 ';
- DECLARE @ZoneNO varchar (+)
- -----------------The SQL body----------------------------------------------------------execution time:
- DECLARE @strSDate nvarchar, @strEDate nvarchar (50)
- DECLARE @StartDateTime datetime, @EndDateTime datetime
- SET @strSDate =CONVERT (varchar, @StarDate, +) +"[email protected]
- SET @strEDate =CONVERT (varchar, @EndDate, +) +"[email protected]
- SET @StartDateTime =convert (datetime, @strSDate,.)
- SET @EndDateTime =convert (datetime, @strEDate,.)
- -------------------------------------the table above-------------------------------------------------
- Select
- 1 Watermetercount, --Number of tables
- W.hx_fpayamount, --Water
- W.hx_fprepayment,--Estimated water charges
- W.hx_fpreproxypayment,--estimated generation charges
- C.hx_fname --Water category
- from
- Hx_t_waterusedamount W --Water
- INNER JOIN hx_t_teamattribution n --meter-reading team ownership
- On W.owningteam=n.hx_fteamid
- and IsNull (W.hx_fzone,") = IsNull (isnull (@ZoneNO, W.hx_fzone),")
- and IsNull (W.owningteam,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@TeamId, W. Owningteam),' 00000000-0000-0000-0000-000000000000 ')
- and w.hx_fpayamount>0 --clearing water
- and W.hx_frecorddate between @StartDateTime and @EndDateTime
- and IsNull (N.hx_flevyinstituteid,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@ Businessunitid,n.hx_flevyinstituteid),
- ' 00000000-0000-0000-0000-000000000000 ')
- Left JOIN hx_t_waterproperty p -water Nature
- On W.hx_fwaterpropertyid=p.hx_t_waterpropertyid
- Left JOIN hx_t_watercategory C --water category
- On P.hx_fwatercategoryid=c.hx_t_watercategoryid
- -----------------END----------------------------------------------------------------------------
- Select @end_date = getdate ()
- 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
- ----------------The SQL body----------------------------------------------------------execution time:
- DECLARE @strSDate nvarchar, @strEDate nvarchar (50)
- DECLARE @StartDateTime datetime, @EndDateTime datetime
- SET @strSDate =CONVERT (varchar, @StarDate, +) +"[email protected]
- SET @strEDate =CONVERT (varchar, @EndDate, +) +"[email protected]
- SET @StartDateTime =convert (datetime, @strSDate,.)
- SET @EndDateTime =convert (datetime, @strEDate,.)
- ----------------------------------The following table------------------------------------------------------
- If object_id (' tempdb.. #t_estimateamountreason ') is not a null drop table #t_estimateamountreason
- SELECT * into #t_estimateamountreason from
- (select distinct t1. Label,t2. Value from metadataschema.localizedlabel T1
- Inner join Metadataschema.attributepicklistvalue T2
- On t1. Objectid=t2. Attributepicklistvalueid inner join metadataschema.optionset T3 on T2. Optionsetid=t3. Optionsetid
- where T3. name=' hx_estimateamountreason_values ' and t1. Objectcolumnname=' DisplayName ' and t1. languageid=2052) T
- --select * from #t_estimateamountreason
- Select
- 1 metercounts, --water meter block number
- IsNull (E.label,' other ') reasonname --Name of reason for water assessment
- From Hx_t_waterusedamount w --Water
- INNER JOIN hx_t_teamattribution n --meter-reading team ownership
- On W.owningteam=n.hx_fteamid
- and IsNull (W.hx_fzone,") = IsNull (isnull (@ZoneNO, W.hx_fzone),")
- and IsNull (W.owningteam,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@TeamId, W. Owningteam),' 00000000-0000-0000-0000-000000000000 ')
- and w.hx_fpayamount=0 --clearing water
- and W.hx_frecorddate between @StartDateTime and @EndDateTime
- and IsNull (N.hx_flevyinstituteid,' 00000000-0000-0000-0000-000000000000 ') = IsNull (isnull (@ Businessunitid,n.hx_flevyinstituteid),
- ' 00000000-0000-0000-0000-000000000000 ')
- Left JOIN #t_estimateamountreason e – Reason for water assessment
- On E.value=w.hx_festimateamountreason
- UNION All
- Select 1 Metercounts,e.label reasonname from #t_estimateamountreason e
- If object_id (' tempdb.. #t_estimateamountreason ') is not a null drop table #t_estimateamountreason
- ----------------END----------------------------------------------------------------------------
A comprehensive example of Reportingservies--sqlserver report development