这篇随笔介绍一下Reporting Services的参数化报表(Parameterized Report)。 按照报表内容的可变性,报表分为静态报表和参数化报表,数据驱动的参数化报表更符合人们的需求习惯。 首先来看一下使用Reporting Services创建参数化报表的步骤: 1、在项目AWRepor
这篇随笔介绍一下Reporting Services的参数化报表(Parameterized Report)。
按照报表内容的可变性,报表分为静态报表和参数化报表,数据驱动的参数化报表更符合人们的需求习惯。
首先来看一下使用Reporting Services创建参数化报表的步骤:
1、在项目AWReport中使用右键菜单“添加”→“新建项”→“报表”添加一个新的报表rptPR.rdl;
2、在报表rptPR.rdl的“数据”选项卡中,单击数据集下来列表新建数据集DataEmployee(如图1所示),单击“通用查询设计器”切换到查询设计状态,添加表HumanResources.Employee,并选择所有列作为输出。
图1 新建数据集
参数名称
数据类型
提示
pTitle
String
头衔
pMStatus
String
婚姻状况
pGender
String
性别
pSFlag
Boolean
是否带薪
pBDate
DateTime
生日
表1 参数名称、数据类型和提示的设置
4、切换到报表的“数据”选项卡,为相应字段添加图2所示的筛选条件。
图2 为字段添加参数作为筛选条件
5、切换到报表的“布局”选项卡,为报表设计图3所示的布局。
图3 报表布局
6、在预览状态下,在报表中显示的参数项目中为参数指定不同的值,就可以看到图4所示的参数化报表了。
图4 预览状态下的参数化报表
“婚姻状况”的实际取值有两种——M(Married,已婚的)和S(Single或Sole,未婚的),而对于用户来说,该参数的值使用文本框来接受输入显然是不合适的,例如输入单词的全拼或Unmarried的第一个字母U就查不出任何结果,可以考虑在“报表参数”对话框的“可用值”区域进行图5所示的设置,这样“婚姻状况”参数的输入就可以使用下拉列表来进行了。“性别”参数应该进行同样的设置。
图5 为“婚姻状况”设置可用值
如果用户对于公司雇员的头衔不熟悉或者头衔有不同的叫法,同样会带来上面的困扰,而“头衔”字段的值并不是固定的,系统维护时很可能出现增加或减少的情况,这时候可以考虑从查询中为该参数指定“可用值”。需要注意的是,如果使用同一数据集DataEmployee中的Title字段作为可用值的来源,而Title字段又是第一个参数时会出现前向依赖错误,因为此时Title字段的取值是由后面的参数决定的;而如果将Title字段作为最后一个参数时,每次更改其它参数的值都会引起Title参数下拉列表中数据的刷新和变动(这个可用来做如省/市/县等的联动)。此时,应该考虑,通过图1所示的方法另外新建一个数据集DataTitle,并指定其数据来源为:
SELECT Distinct Title FROM HumanResources.Employee
然后按照图6所示的设置指定“头衔”参数的可用值。
图6 为“头衔”设置可用值
另外,可以为每个参数指定“默认值”,减少常见查询时的数据输入。
报表在一个Web项目中的显示如图7所示。
图7 Web项目中显示的参数化报表(点击小图看大图)
Reporting Services在一定程度上满足了用户对参数化报表的需求,但是也存在一些可以改进的地方,如一旦定义参数就不能忽略参数的值进行报表的查看。

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL is suitable for small and large enterprises. 1) Small businesses can use MySQL for basic data management, such as storing customer information. 2) Large enterprises can use MySQL to process massive data and complex business logic to optimize query performance and transaction processing.

InnoDB effectively prevents phantom reading through Next-KeyLocking mechanism. 1) Next-KeyLocking combines row lock and gap lock to lock records and their gaps to prevent new records from being inserted. 2) In practical applications, by optimizing query and adjusting isolation levels, lock competition can be reduced and concurrency performance can be improved.

MySQL is not a programming language, but its query language SQL has the characteristics of a programming language: 1. SQL supports conditional judgment, loops and variable operations; 2. Through stored procedures, triggers and functions, users can perform complex logical operations in the database.

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL is an open source relational database management system suitable for data storage, management, query and security. 1. It supports a variety of operating systems and is widely used in Web applications and other fields. 2. Through the client-server architecture and different storage engines, MySQL processes data efficiently. 3. Basic usage includes creating databases and tables, inserting, querying and updating data. 4. Advanced usage involves complex queries and stored procedures. 5. Common errors can be debugged through the EXPLAIN statement. 6. Performance optimization includes the rational use of indexes and optimized query statements.

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

InnoDB's lock mechanisms include shared locks, exclusive locks, intention locks, record locks, gap locks and next key locks. 1. Shared lock allows transactions to read data without preventing other transactions from reading. 2. Exclusive lock prevents other transactions from reading and modifying data. 3. Intention lock optimizes lock efficiency. 4. Record lock lock index record. 5. Gap lock locks index recording gap. 6. The next key lock is a combination of record lock and gap lock to ensure data consistency.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

WebStorm Mac version
Useful JavaScript development tools