ReportingServies -- SQL Server Report Development Comprehensive instance, SQL Server trigger instance

ReportingServies -- SQL Server Report Development Comprehensive instance, SQL Server trigger instance

Document directory

ReportingServies report development instance tutorial

ReportingServies -- SQL Server Report Development Comprehensive instance

If sqlserver2008 R2 is installed, a report development tool is automatically installed.

Do not use this report development tool only for sqlserver2008. In fact, it is also supported in sqlserver2012. In fact, I am using sqlserver2012 in my project.

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

Let's take a look at the effect we want to achieve:

Tables and fields used in the report:

-- Table water usage: hx_t_waterusedamount -- field account information: primary key: primary water meter: hx_fmeterid section: hx_fzone practical water usage: hx_famount -- Estimation reason: hx_festimateamountreason settlement water usage: hx_fpayamount -- Meter Reading type hx_frecordtype (estimated water:, 000,001 not copied see: 100,000,002 normal: 100,000,000 pre-payment card table estimated water: 100,000,003) -- water volume status: hx_fstate (Plan: 100,000,000, already entered water volume: 100,000,001, waiting for input amount: 100,000,002, generated receivables: 100,000,006, pending review: 100,000,004) <span style = "font-family:; "> -- </span> Current Meter Reading stop: hx_freading -- Water Year: hx_fyear water month: hx_fmonth: hx_FTimes -- Current Meter Reading Time: hx_frecorddate estimated water fee: hx_fprepayment estimated fee: hx_fpreproxypayment -- table water class hx_t_watercategory -- field statistics class: hx_fstatcategoryoneid primary key: hx_t_watercategoryid name: hx_fname -- table water type hx_t_waterproperty -- field primary key: hx_t_waterpropertyid: hx_fwatercategoryid -- hx_t_teamattribution of table Meter Reading team -- field primary key: hx_t_teamattributionid: hx_fteamid fee collection information: hx_flevyinstituteid

Now, it's officially started.

We can see from the above that two tables are used for presentation in this report, and two datasets are used here, but their query conditions are consistent.

Let's first look at the above table.

 

Let's look at SQL:

Declare @ begin_date datetimedeclare @ end_date datetimeselect @ begin_date = getdate () declare @ BusinessunitId uniqueidentifier -- levy -- set @ BusinessunitId = 'your-B87A-E411-93FE-002590622897 '---- 15th toll collection DECLARE @ TeamId uniqueidentifier -- Meter Reading team -- SET @ TeamId = '18acbd81-Shanghai' -- 15th Toll Collection (fragmented table Group) DECLARE @ StarDate datetimeDECLARE @ EndDate datetimeSET @ StarDate = convert (datetime, '2017-01-15 00:00:00 ', 20) SET @ EndDate = convert (datetime, '2017-01-25 23:59:59 ', 20) DECLARE @ sHMS nvarchar (10) DECLARE @ eHMS nvarchar (10) SET @ sHMS = '00: 00: 00 '; SET @ eHMS = '23: 59: 59'; DECLARE @ ZoneNO varchar (30) --------------- SQL body explain execution time: DECLARE @ strSDate nvarchar (50), @ strEDate nvarchar (50) DECLARE @ StartDateTime, @ EndDateTime datetimeSET @ strSDate = CONVERT (varchar, @ StarDate, 23) + ''+ @ sHMSSET @ strEDate = CONVERT (varchar, @ EndDate, 23) + ''+ @ eHMSSET @ StartDateTime = convert (datetime, @ strSDate, 20) SET @ EndDateTime = convert (datetime, @ strEDate, 20) ------------------------------------- the preceding table explain select 1 WaterMeterCount, -- the number of tables is w. hx_fpayamount, -- water volume w. hx_fprepayment, -- estimated water fee w. hx_fpreproxypayment, -- estimated fee c. hx_fname -- water type from hx_t_waterusedamount w -- Water inner join hx_t_teamattribution n -- the meter reading team belongs to ON w. owningTeam = n. hx_fteamidAND isnull (w. hx_fzone, '') = isnull (@ ZoneNO, w. hx_fzone), '') AND isnull (w. owningTeam, '1970-0000-0000-0000-000000000000') = isnull (@ TeamId, w. owningTeam), '2017-0000-0000-0000-000000000000') AND w. hx_fpayamount> 0 -- settlement amount AND w. hx_frecorddate BETWEEN @ StartDateTime AND @ EndDateTime AND isnull (n. hx_flevyinstituteid, '1970-0000-0000-000000000000') = isnull (@ BusinessunitId, n. hx_flevyinstituteid), '1970-0000-0000-000000000000') left join hx_t_waterproperty p -- water type on w. hx_FWaterPropertyID = p. hx_t_WaterPropertyIdLEFT JOIN hx_t_watercategory c -- water type on p. hx_FWaterCategoryID = c. hx_t_WaterCategoryId --------------- END when select @ end_date = getdate () select datediff (MS, @ begin_date, @ end_date) as '/millisecond'

The execution result is as follows:

It can be seen that this is a very common vertical table to change the horizontal table. If you use pure SQL to achieve the conversion effect, you have to write a series of SQL statements in charge, however, by using this report development tool, we can easily change the vertical display result to a horizontal display result.

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 continue the next step,

However, considering that the matrix interface configuration is quite reversed, we should select a table first, and then click Next until the process is completed.

1. Delete the table above the interface and drag a matrix from the left-side toolbar,

Select the "data" cell, right-click the cell, and choose insert row> group> bottom to add four rows. Then, right-click the cell and choose insert column> group external (because the total count is required)

Select the hx_fname field in the column. From the SQL statement, we can see that the hx_fname field is the field to be converted to be displayed in the horizontal table. Then, select the field to be aggregated.

Report data on the right,

Many parameters are automatically generated here because they are used in the SQL statement we just added. Note that the name is the same as the SQL statement we just declared.

Select properties and right-click to view the parameter configuration information.

If you click more of these things, you will naturally be familiar with it. There are too many things involved, and I can't finish it all at once.

There are two parameters that have cascading relationships. Let me first talk about them.

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

First look at the parameter: BusinessunitId

Where does this dataset come from? You can add

Let's take a look at the parameter TeamId.

Right-click a blank space-create a placeholder

Batch modification can be directly modified here,

Otherwise, only one text box can be modified. # Indicates the display of thousands of sub-places. The subsequent numeric code retains the number of decimal places. 0 indicates an integer.

Quantity/volume of water: #, 0

Price: 0.00

Amount: #, 0.00.

The operation of the Assembly below in the report is similar. I will not talk about it here, but there are too many things.

The SQL statement is as follows:

---------------- SQL body explain execution time: DECLARE @ strSDate nvarchar (50), @ strEDate nvarchar (50) DECLARE @ StartDateTime, @ EndDateTime datetimeSET @ strSDate = CONVERT (varchar, @ StarDate, 23) + ''+ @ sHMSSET @ strEDate = CONVERT (varchar, @ EndDate, 23) +'' + @ eHMSSET @ StartDateTime = convert (datetime, @ strSDate, 20) SET @ EndDateTime = convert (datetime, @ strEDate, 20) ------------------------------------ The following table ------------------------------------------------------ if object_id ('tempdb .. # t_estimateamountreason ') is not null drop table # t_estimateamountreasonselect * 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 _ Region' and t1.ObjectColumnName = 'displayname' and t1.LanguageId = 2052) t -- select * from # effecselect1 MeterCounts, -- number of water meter blocks isnull (e. label, 'others') ReasonName -- Evaluation reason name from hx_t_waterusedamount w -- water volume inner join hx_t_teamattribution n -- the meter reading team belongs to ON w. owningTeam = n. hx_fteamid AND isnull (w. hx_fzone, '') = isnull (@ ZoneNO, w. hx_fzone), '') AND isnull (w. owningTeam, '1970-0000-0000-0000-000000000000') = isnull (@ TeamId, w. owningTeam), '2017-0000-0000-0000-000000000000') AND w. hx_fpayamount = 0 -- settlement amount AND w. hx_frecorddate BETWEEN @ StartDateTime AND @ EndDateTime AND isnull (n. hx_flevyinstituteid, '1970-0000-0000-000000000000') = isnull (@ BusinessunitId, n. hx_flevyinstituteid), '1970-0000-0000-0000000000000000') left join # t_estimateamountreason e -- water cause estimation 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 null drop table # t_estimateamountreason ---------------- END ----------------------------------------------------------------------------