关于已知模式的自定义用户驱动报告
即将开展的项目将满足最终用户能够根据我们的固定/已知模式关系数据库中的数据生成自定义报告的要求。
界面需要非常用户友好，因此将 t-sql 的所有语言概念转换为图形范例对于项目团队和最终用户来说都过于复杂。
围绕满足这一业务需求存在哪些研究或产品（开源或其他）？我知道通用的业务分析工具，但这是更具体的，我试图更好地理解问题领域，而不是试图从供应商营销材料中对其进行逆向工程。
我假设研究将以某种模式编码的形式进行，该模式指定允许哪些连接和表、哪些字段可用，然后是一种允许用户在可能的多个组合中选择一个特定有效组合的方法，生成查询，并显示结果。
头脑风暴 - 按复杂程度排列的功能支持：SELECT、WHERE 过滤器、FULL JOIN、LEFT JOIN、排序、分页、分组、聚合、HAVING 过滤器。
我的备份计划是将其简化为预先编写的 SQL 视图（内置 JOIN），并能够通过自定义按行过滤显示可用列。分页和排序是可行的。就其本身而言，这不允许进行分组、聚合函数、HAVING 过滤器或其他行间分析。
There's an upcoming project at work to fill a requirement that end-users be able to generate custom reports off their data in within our fixed/known-schema relational database.
The interface needs to be very user friendly and so transposing all of t-sql's language concepts into a graphical paradigm is far too complex for both the project team and the end user.
What research or products, open-source or otherwise, exist around satisfying this of business need? I'm aware of general Business Analytic tools but this is more specific and I'm trying to understand the problem domain better rather than trying to reverse engineer it from vendor marketing materials.
I assume the research would be in the form of a some encoding of the schema that specifies which joins and tables are allowed, which fields are available, then then a method for allowing the user to select one particular valid combination among the possible many, generate the query, and display the results.
Brainstorming - feature support in order of complexity: SELECT, WHERE filters, FULL JOIN, LEFT JOIN, sorting, paging, grouping, aggregation, HAVING filter.
My backup plan is to just dumb it down to pre-written SQL Views (with JOINs built-in) with the ability to display available columns with custom row-wise filtering. Paging and sorting is doable. By itself, this doesn't allow for grouping, aggregate functions, HAVING filters, or other inter-row analysis.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
作为@Dems帖子的后续（评论框还不够:)）..
在大多数方面都同意..如果您的数据主要是分析性的，那么您可能需要研究像 PowerPivot。在这种情况下，您可以编写通用查询，然后允许用户根据熟悉的工具（Excel）中的结果集导出报告。
在每个临时报告引擎的核心，您都会发现一些常见主题：
元数据
将有某种描述模式的方法，以便用户可以轻松使用该模型。 Sql Server Reporting Services (SSRS) 要求您构建元数据模型才能使用报表生成器。使用 PowerPivot 时，您可以为列名添加别名以使其更具可读性，但最终，您只是提供一个平面数据集并允许用户构建联接/关系。
查询生成器
一旦用户操作了元数据，就必须有一个中间系统将概念报告转换为实际查询。许多工具都是根据它们生成的 Sql 的复杂性来衡量的，因为这会极大地影响性能。解决这个问题的一种方法是创建报告引擎可以针对其构建查询的视图。我见过的最好的开源示例之一是支持 Hibernate/NHibernate 的引擎（研究构建查询时如何使用各种
方言）。渲染引擎
根据我的经验，构建渲染引擎并不是您想要走的路。有许多特定于设备的问题以及外观和功能。感受到问题（即您计划如何表示级联连接/关系？）。每个渲染引擎都有其自己的怪癖（PowerPivot 使用 Excel，SSRS 具有构建原始结果并将其返回到使用应用程序的服务），因此必须小心选择。
之前我提到过我同意大多数问题。我不建议鼓励您的用户学习 Sql 或允许他们将 Sql 传递到底层数据存储。这为编写恶意代码打开了大门，并可能成为安全噩梦。更不用说大多数业务用户都考虑平面表，而不是分层集。
弄清楚您的用户对什么感到满意，并尝试使您的解决方案适合该领域。我经常发现，对于经验丰富的商业用户来说，像 PowerPivot 这样的东西是完美的。对于更多的日常最终用户来说，拥有可由最终用户通过允许他们修改限制/分组/排序的简单用户界面进行修改的“罐装”报告更有用。
As a follow-up to @Dems post (comment box wasn't bit enough :) )..
Agreed on most counts.. If your data is mostly analytic, then you might want to look into a tool like PowerPivot. In this case, you can write a general query then allow the users to derive reports based on the result set in a familiar tool (Excel).
At the core of every ad hoc reporting engine, you will find a few common themes:
Metadata
There will be some way of describing the schema such that the model may be easily consumed by the user. Sql Server Reporting Services (SSRS) requires you to build a metadata model in order to use the report builder. When using PowerPivot, you can alias column names to make them more readable, but in the end, you are simply providing a flat dataset and allowing the user to build the joins/relationships.
Query Builder
Once the metadata has been manipulated by the user, an intermediary system must be in place to convert the conceptual report into an actual query. Many tools are measured based on the complexity of the Sql that they produce as this can greatly affect performance. One way to get around this is to create views that the reporting engine may build queries against. One of the best open source examples of this that I have seen is the engine that backs Hibernate/NHibernate (look into how the various
Dialectsare used when building queries).Rendering Engine
In my experience building a rendering engine is not a road you want to go down. There are many device-specific concerns as well as look & feel problems (i.e. how do you plan on representing cascading joins/relationships?). Every rendering engine has it's own quirks (PowerPivot uses Excel, SSRS has a service that builds the raw result and return it to the consuming application) that must be accounted for, so be careful how you choose.
Earlier I mentioned that I agreed on most counts. I would not recommend encouraging your users to learn Sql or allowing them to pass-through Sql to the underlying data-store. This opens the door to malicious code being written and can become a security nightmare. Not to mention that most business users think in terms of flat tables, not hierarchical sets.
Figure out what your users are comfortable with and try to fit your solution to that domain. I have often found that for sophisticated business users something like PowerPivot is perfect. For more day-to-day end users, having "canned" reports that might be modified by the end user via a simple user interface that allows them to modify restrictions/groupings/sorting is more useful.
有很多选择，但最好的选择是要花钱的。
我真的很喜欢 QlikView，因为它是一款专为半技术人员设计的易于使用的报告。如果你的用户群更有技术头脑，它可能会有点限制，但如果你的用户群没有逻辑思维能力，那就太复杂了。这是我看到你掉入的最大陷阱...
- 不，我想要的不止这些！
- 不，这对我来说太复杂了！
- 同时...
如果您要在内部构建自己的工具集，您可能最好坚持使用 OLAP 多维数据集。让人们按照自己的喜好对数据进行切片和切块，但所有关系都是预先定义的。如果做得对，您只需将 Excel 数据透视表指向 OLAP 立方体并让它们发挥作用...
正如 Bobby D 所说，下一个可能是 SQL Server Reporting Services 或类似的东西。
但如果您的用户最终想要绝对的灵活性，他们需要的工具就是 SQL 本身。不幸的是，所有工具都遵循相同的趋势：越灵活、越强大，您需要花费的学习/培训时间就越多。
There are many options out there, but the best of them cost money.
I really like QlikView as an easy to use report designed for semi-technical people. If your user base is more technically minded it may be a bit restrictive, but if your user base have no logical thought capabilities, it's too complicated. That's the biggest trap I see you falling in to...
- No, I want more than that!
- No, that's too complicated for me!
- At the same time...
If you were to build your own tool-set internally, you'd probably be best sticking with OLAP cubes. Let people slice and dice the data as they like, but with all the relationships pre-defined. Do it right and you can just point an Excel Pivot Table at the OLAP Cube and let them play...
The next up, as Bobby D says, could be SQL Server Reporting Services, or something similar.
But if your users end up wanting absolute flexibility, the tool they need is SQL itself. Unfortunately, all tools follow the same trend: The more flexible and powerful, the more time you need to spend learning/training.