Reporting Service [self-learning in Development]

SQL serverreporting services provides a variety of ready-to-use tools and services to help you create, deploy, and manage organization reports. It also provides programming functions that enable you to expand and customize the report functions.

Reporting Services is a server-based report platform that provides comprehensive report functions for various data sources. Reporting Services includes a complete set of tools for creating, managing, and transferring reports, and allows developers to customize applications.ProgramAPIS that integrate or expand data and report processing. The reporting services tool works in the microsoftvisual studio environment and is fully integrated with SQL Server tools and components.

-- From msdn: http://msdn.microsoft.com/zh-cn/library/ms159106.aspx

A few reportingservice reports are required for the recent project.

I have been in touch with it before, but it is developed by other RD teams. It is OK to directly reference the jump link. I have no in-depth understanding of the specific report design. This time I am in a hurry and have not prepared much, they are all problems encountered in development. Find the answer and explore it slowly.

Use Visual Studio to develop Repoting Service

I have to say that Microsoft's Visual Studio integration tool provides great convenience for our developers.

Create a Report Server project in vs to go to the preceding interface. Similar to crystal report, you can add data sources, datasets, define fields, query fields, use SQL statements, or use stored procedures.

The report design window consists of three tabs: [data] [layout] [Preview], which are used to edit the data source, dataset, report style, layout, and content, and preview the report effect.

[Data]

Data sources and datasets can be easily selected through the window:

[Layout]

Layout interface. A wide range of UI Models are provided here, and some common basic models are provided, including [text box] [line] [Table] [matrix] [rectangle] [LIST] [Image] [self-report] [Chart], these models can be combined in multiple ways, solve some complicated report design. For example, add a subreport to the table to display details.

[Preview]

The above is basically nonsense. The following list of things I encountered during development may not be worth mentioning for people I know, but for the people I did this for the first time, is the accumulation of experience.

Datasets, parameters, and filters

When designing a dataset, parameters, queries, filtering, and other actions are often required. Parameters and filters are provided in reportingservic, which is very useful for some simple filtering conditions.

For example, in the report, I preset the [form_no] parameter condition. In the filter, you can select a dataset field [form_id] In some way [=/like /! =/> =/<=...] Corresponds to the parameter.

If the normal direct correspondence cannot meet your needs, you can also use expressions. Here more processing functions and operators are provided.

If this is not the case, write the SQL statement by yourself:

View code

 Select  
Ar. form_no As Form_id,
Ar. sample_des,
Eda. emp_name As Owner,
Eda. org_id As Owner_group,
Ar. pre_cost As Pre_cost,
Ar. create_dt,
Ar. end_dt,
Ar. z_machine As Tool,
Af. fab_name As Website,
 Case Form_status
When   '  AP  '   Then   '  Complete  '  
 Else   '  Incomplete  ' 
 End   As Status,
Ar. report_desc,
Ar. report_path,
Ar. last_approver,
Ar. approver_date
 From Auoform22 ar
 Left   Join Emp_data_all EDA On Eda. emp_no = Ar. form_applicant
 Left   Join Auoform22_fab af On Af. fab_sn = Ar. fab_sn
 Where Ar. create_dt > =  Convert ( Datetime , @ Date_from )
 And Ar. create_dt <=  Convert ( Datetime , @ Date_to )
 And ( @ Form_no   Is   Null   Or Ar. form_no =   Rtrim ( Ltrim ( @ Form_no )))
 And ( @ Fab   Is   Null   Or   Len ( @ Fab ) =  0   Or   @ Fab   =  ' All  '   Or Af. fab_name =   Rtrim ( Ltrim ( @ Fab )))
 And ( @ Machine   Is   Null   Or   Len ( Rtrim ( Ltrim (@ Machine ))) =  0    Or Ar. z_machine Like   '  %  '  +   Rtrim ( Ltrim ( @ Machine )) +  '  %  ' )
 And ( @ Applier_no   Is   Null   Or   Len ( Rtrim ( Ltrim ( @ Applier_no ))) =  0    Or Eda. emp_name Like   '  % '  +  Rtrim ( Ltrim ( @ Applier_no )) +  '  %  ' )
 And Ar. form_status =  '  AP  ' 

 

Layout, control model usage

The table should be a model that is inevitable for report generation. In my needs, I need to click the ID to show more details about a certain document, at the beginning, we directly considered using subreports to display detailed information, but how to display the details is constantly entangled with the presentation effect.

At first, the URL jump form is used. The ID is designed as a hyperlink. After clicking it, The subreport is displayed and the parameter value of the ID is input:

But the actual display is not good:

Click [Form Number] to overwrite the data on this page. The subreport information is displayed:

Later, I thought about using [LIST]

This function is basically implemented, but the exported Excel section will display the sub-report together. the user does not want to display the sub-report information for this.You cannot find a method to control the exported content.

Finally, it is changed to a table and embedded into a subreport to fulfill the requirement. However, when exporting an Excel file, it will still carry a line of the subreport [Is there a solution?]

The report design part also has some small functions, such as project display [visibility can be set], field content format, grouping, sorting, paging, and so on. Let's look at the attributes, just touch it!

I have filled a blank space in the reportingservice section for several small report tasks ~ Haha

Attached several [ugly, don't be surprised]

Table + subreport

Chart + Matrix

Simplest table