在 Crystal Reports XI 中重新创建复杂 SQL 查询的可行性

发布于 2024-12-10 17:41:51 字数 2092 浏览 0 评论 0原文

我在 SQL Server 2008 上有大约 10 个相当复杂的 SQL 查询 - 但客户端希望能够通过 Crystal Reports XI 从其内部网络(而不是从非本地 Web 应用程序)运行它们。

客户端的内部网络不允许我们(a)对其专有数据库进行写访问,也不允许我们设置中间SQL服务器(这意味着我们无法设置存储过程或其他数据清理)。

SQL 包含多个 row_number() 实例(按 col1、col2 分区)按 col1、col2 进行分组,并带有立方体|汇总和/或(多个)主元。

这还可以吗?我读过的所有内容似乎都表明这只能通过存储过程实现,我仍然需要首先从专有数据库中提取数据。

以下是其中一个查询的精简版本(例如,与功能不直接相关的 JOIN、WHERE 子句,并且已删除六列)...


select sum(programID)
    , sum([a.Asian]) as [Episodes - Asian], sum([b.Asian]) as [Eps w/ Next Svc - Asian], sum([c.Asian])/sum([b.Asian]) as [Avg Days to Next Svc - Asian]
    , etc... (repeats for each ethnicity)
from (
    select programID, 'a.' + ethnicity as ethnicityA, 'b.' + ethnicity as ethnicityB, 'c.' + ethnicity as ethnicityC
        , count(*) as episodes, count(daysToNextService) as episodesWithNextService, sum(daysToNextService) as daysToNextService
    from (
        select programID, ethnicity, datediff(dateOfDischarge, nextDateOfService) as daysToNextService from (
            select t1.userID, t1.programID, t1.ethnicity, t1.dateOfDischarge, t1.dateOfService, min(t2.dateOfService) as nextDateOfService
            from TABLE1 as t1 left join TABLE1 as t2
                on datediff(d, t1.dateOfService, t2.dateOfService) between 1 and 31 and t1.userID = t2.userID
            group by t1.userID, t1.programID, t1.ethnicity, t1.dateOfDischarge, t1.dateOfService
        ) as a
    ) as a
    group by programID
) as a
pivot (
    max(episodes) for ethnicityA in ([A.Asian],[A.Black],[A.Hispanic],[A.Native American],[A.Native Hawaiian/ Pacific Isl.],[A.White],[A.Unknown])
) as pA
pivot (
    max(episodesWithNextService) for ethnicityB in ([B.Asian],[B.Black],[B.Hispanic],[B.Native American],[B.Native Hawaiian/ Pacific Isl.],[B.White],[B.Unknown])
) as pB
pivot (
    max(daysToNextService) for ethnicityC in ([C.Asian],[C.Black],[C.Hispanic],[C.Native American],[C.Native Hawaiian/ Pacific Isl.],[C.White],[C.Unknown])
) as pC
group by programID with rollup

Sooooooo....这样的东西甚至可以翻译成 Crystal报告十一?

谢谢!

I have about 10 fairly complex SQL queries on SQL Server 2008 - but the client wants to be able to run them from their internal network (as opposed to from the non-local web app) through Crystal Reports XI.

The client's internal network does not allow us to (a) have write access to their proprietary db, nor (b) allow us to set up an intermediary SQL server (meaning we can not set up stored procedures or other data cleaning).

The SQL contains multiple instances of row_number() over (partition by col1, col2), group by col1, col2 with cube|rollup, and/or (multiple) pivots.

Can this even be done? Everything I've read seems to indicate that this is only feasible via stored procedure and I would still need to pull the data from the proprietary db first.

Following is a stripped back version of one of the queries (eg, JOINs not directly related to functionality, WHERE clauses, and half a dozen columns have been removed)...


select sum(programID)
    , sum([a.Asian]) as [Episodes - Asian], sum([b.Asian]) as [Eps w/ Next Svc - Asian], sum([c.Asian])/sum([b.Asian]) as [Avg Days to Next Svc - Asian]
    , etc... (repeats for each ethnicity)
from (
    select programID, 'a.' + ethnicity as ethnicityA, 'b.' + ethnicity as ethnicityB, 'c.' + ethnicity as ethnicityC
        , count(*) as episodes, count(daysToNextService) as episodesWithNextService, sum(daysToNextService) as daysToNextService
    from (
        select programID, ethnicity, datediff(dateOfDischarge, nextDateOfService) as daysToNextService from (
            select t1.userID, t1.programID, t1.ethnicity, t1.dateOfDischarge, t1.dateOfService, min(t2.dateOfService) as nextDateOfService
            from TABLE1 as t1 left join TABLE1 as t2
                on datediff(d, t1.dateOfService, t2.dateOfService) between 1 and 31 and t1.userID = t2.userID
            group by t1.userID, t1.programID, t1.ethnicity, t1.dateOfDischarge, t1.dateOfService
        ) as a
    ) as a
    group by programID
) as a
pivot (
    max(episodes) for ethnicityA in ([A.Asian],[A.Black],[A.Hispanic],[A.Native American],[A.Native Hawaiian/ Pacific Isl.],[A.White],[A.Unknown])
) as pA
pivot (
    max(episodesWithNextService) for ethnicityB in ([B.Asian],[B.Black],[B.Hispanic],[B.Native American],[B.Native Hawaiian/ Pacific Isl.],[B.White],[B.Unknown])
) as pB
pivot (
    max(daysToNextService) for ethnicityC in ([C.Asian],[C.Black],[C.Hispanic],[C.Native American],[C.Native Hawaiian/ Pacific Isl.],[C.White],[C.Unknown])
) as pC
group by programID with rollup

Sooooooo.... can something like this even be translated into Crystal Reports XI?

Thanks!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

南街九尾狐 2024-12-17 17:41:51

当您创建报告而不是选择表或存储过程时,请选择添加命令

这将允许您将任何有效的 TSQL 语句放入其中。使用通用表表达式(CTE)和内联视图我已经成功地针对 Oracle 和 SQL Server 创建了一些相当大的复杂语句(超过 400 行),因此它确实可行,但是如果您使用参数 ,您应该考虑使用sp_executesql 您必须弄清楚如何避免 SQL 注入。

When you create your report instead of selecting a table or stored procedure choose add command

This will allow you to put whatever valid TSQL statement in there that you want. Using Common Table Expressions (CTE's) and inline Views I've managed to create some rather large complex statements (excess of 400 lines) against Oracle and SQL Server so it is indeed feasible, however if you use parameters you should consider using sp_executesql you'll have to figure out how to avoid SQL injection.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文