A comprehensive example of Reportingservies--sqlserver report development

Previous:reportingservies Report Development Example Tutorial

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:

--table Water: hx_t_waterusedamount--field opening information: Hx_fcustomerandmeterrelaid primary key: Hx_t_waterusedamountid  meter: hx_ Fmeterid sector: Hx_fzone Real water consumption: hx_famount-the reason: Hx_festimateamountreason  settlement water: hx_fpayamount--meter type Hx_frecordtype ( Estimate water:, 000,001 not copy see: 100,000,002 Normal: 100,000,000 Prepaid card Table Assessment water: 100,000,003)--Water Status: Hx_fstate (plan: 100,000,000, Water input: 100,000,001, Wait input amount: 100,000,002, has generated receivable: 100,000,006, waiting for Audit: 100,000,004) <span style= "font-family: Song Body;" >--</span> This time reading stop: hx_freading--water year: hx_fyear  water Month: Hx_fmonth times: Hx_ftimes-the time to meter reading: Hx_frecorddate  Estimated water charges: hx_fprepayment estimated generation Fee: Hx_fpreproxypayment--table Hx_t_watercategory--field statistics Category: Hx_fstatcategoryoneid Primary key: Hx_t_watercategoryid name: hx_fname--table water Nature 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:

DECLARE @begin_date datetimedeclare @end_date datetimeselect @begin_date = GETDATE () declare @BusinessunitId uniqueidentifier-Levy--set @BusinessunitId = ' 6a2537dd-b87a-e411-93fe-002590622897 '----the 15th charge declare @TeamId uniqueidentifier--meter-reading team--set @TeamId = ' 18acbd81-5b45-e411-9402-6cae8b22702d '--the 15th Charge Institute (scattered check group) DECLARE @StarDate Datetimedeclare @EndDate Datetimeset @StarDate = Convert (DateTime, ' 2015-01-15 00:00:00 ', ') SET @EndDate = CONVERT ( DateTime, ' 2015-01-25 23:59:59 ', DECLARE @sHMS nvarchar (ten) DECLARE @eHMS nvarchar (ten) SET @sHMS = ' 00:00:00 '; SET @eHMS = ' 23:59:59 ';D eclare @ZoneNO varchar----------------- SQL body----------------------------------------------------------Execution time: DECLARE @strSDate nvarchar, @strEDate nvarchar (DECLARE) @StartDateTime datetime, @EndDateTime datetimeset @strSDate =convert (varchar, @StarDate, 23) + "[ 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 w.hx_fpreproxypayment,--expected charges c.hx_fname-use category from Hx_t_waterusedamount W-water inner JOIN Hx_t_ Teamattribution N--meter-reading team attribution on W.owningteam=n.hx_fteamidand 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_waterpropertyidleft 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 you use pure SQL to achieve the conversion effect, you have to write a series of responsible SQL statements, but by using this report development tools, We can easily turn its portrait display results 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 show of the field, then data here, Select the fields that you want to aggregate.

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 Body----------------------------------------------------------execution time: DECLARE @strSDate nvarchar ( @strEDate nvarchar (DECLARE) @StartDateTime datetime, @EndDateTime datetimeset @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_estimateamountreasonselect * into #t_estimateamountreason from (Sele CT distinct t1. Label,t2. Value from Metadataschema.localizedlabel t1 inner joins 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 select1 metercounts,--Water meter block IsNull (E.label, ' other ') reasonname--Assessment of Suwon Because of the name from Hx_t_waterusedamount W--Water INNER JOIN hx_t_teamattribution N--meter shift attribution 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- -Water reason on E.value=w.hx_festimateamountreason UNION all select 1 Metercounts,e.label reasonname from #t_estimatea Mountreason e if object_id (' tempdb.. #t_estimateamountreason ') is a NOT NULL drop table #t_estimateamountreason----------------END---------------------------------------------------------------------------- 

Reportingservies--sqlserver Report Development Consolidated instance