A comprehensive example of Reportingservies--sqlserver report development

Source: Internet
Author: User

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

A comprehensive example of Reportingservies--sqlserver report development

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.