We need to have a semi complex report in CRM that displays some accumulated lead values. The only way I see this report working is writing a stored procedure that creates a couple of temporary tables and calculates/accumulates data utilizing cursors. Then is the issue of getting the data from the stored procedure to be accessible from the Reporting Server report. Does anyone know if that's possible? If I could have the option of writing a custom SQL statement to generate report data, that would be just excellent.
我们需要在CRM中有一个半复杂的报告,显示一些累积的潜在客户价值。我看到此报告工作的唯一方法是编写一个存储过程,该过程创建一些临时表并使用游标计算/累积数据。然后是从Reporting Server报告中获取存储过程中的数据的问题。有谁知道这是否可能?如果我可以选择编写自定义SQL语句来生成报告数据,那就非常好。
Any pointers ?
有什么指针吗?
Edit:
To clarify my use of cursors I can explain exactly what I'm doing with them.
为了澄清我对游标的使用,我可以准确地解释我正在用它们做什么。
The basis for my report (which should be a chart btw) is a table (table1) that has 3 relevant columns:
我的报告(应该是图表btw)的基础是一个表(table1),它有3个相关列:
Start date
Number of months
Value
I create a temp table (temp1) that contains the following columns:
我创建一个包含以下列的临时表(temp1):
Year
Month number
Month name
Value
First I loop through the rows in the first table and insert a row in the temptable for each month, incrementing month, while setting the value to the total value divided by months. I.e:
首先,我循环遍历第一个表中的行,并在每个月的temptable中插入一行,递增月份,同时将值设置为总值除以月。即:
2009-03-01,4,1000 in table1 yields
表1中的2009-03-01,4,1000收益率
2009,03,March,250
2009,04,April,250
2009,05,May,250
2009,06,June,250
in the temp1 table.
在temp1表中。
A new cursor is then used to sum and create a running total from the values in temp1 and feed that into temp2 which is returned to the caller as data to chart.
然后使用新的游标对temp1中的值求和并创建一个运行总计,并将其提供给temp2,temp2作为数据返回给调用者。
example temp1 data:
示例temp1数据:
2009,03,March,250
2009,04,April,200
2009,04,April,250
2009,05,May,250
2009,05,May,100
2009,06,June,250
yields temp2 data:
产生temp2数据:
2009,03,March,250,250
2009,04,April,450,700
2009,05,May,350,1050
2009,06,June,250,1300
Last column is the running totals, which starts at zero for each new year.
最后一列是运行总计,每个新年从零开始。
4 个解决方案
#1
Have you considered using views. Use a heirarchy of views if it is very complicated. Each view would represent one of your temporary tables.
你考虑过使用意见吗?如果它非常复杂,请使用视图。每个视图都代表一个临时表。
EDIT Based on comments
编辑基于评论
I was thinking of SQL views, basically the same SQL as you would have written in your stored procedures.
我在考虑SQL视图,基本上与在存储过程中编写的SQL相同。
#2
I haven't done this - just thinking how I would start. I would make sure when the stored procedures populate the temporary tables they use the Filtered views for pulling data. I would then set the access to execute the SP to have the same security roles as the Filtered views (which should be pretty much to allow members of the PrivReportingGroup).
我没有这样做 - 只是想着我将如何开始。我会确保当存储过程填充临时表时,他们使用Filtered视图来提取数据。然后,我将执行SP的访问权限设置为与Filtered视图具有相同的安全角色(这应该非常适合允许PrivReportingGroup的成员)。
I would think that would cover allowing you to execute the SP in your report. I imagine if you set up the SP before hand, the SSRS designer has some means of showing you what data is available and to select an SP at design time. But I don't know that for sure.
我认为这将涵盖允许您在报告中执行SP。我想如果您事先设置了SP,SSRS设计师可以通过一些方法向您展示可用的数据并在设计时选择SP。但我肯定不知道。
#3
First, since most cursors are unneeded, what exactly are you doing in them. Perhaps there is a set-based solution and then you can use a view.
首先,由于大多数游标都不需要,你究竟在做什么。也许有一个基于集合的解决方案,然后你可以使用一个视图。
Another possible line of thought, if you are doing something like running totals in the cursor, is can you create a view as the source without the running total and have the report itself do that kind of calculation?
另一种可能的想法是,如果你在光标中运行总计,你可以创建一个视图作为没有运行总计的源,并让报告本身进行这种计算吗?
Additionally, SSRS reports can use stored procs as a data source, read about how in Books online.
此外,SSRS报告可以使用存储过程作为数据源,阅读有关书籍在线的方式。
#4
I found the solution. Downloaded Report Builder 2.0 from Microsoft. This allows me to write querys and call stored procedures for the report data.
我找到了解决方案。从Microsoft下载的Report Builder 2.0。这允许我编写查询并为报表数据调用存储过程。
Microsoft SQL Server Report Builder link
Microsoft SQL Server报表生成器链接
#1
Have you considered using views. Use a heirarchy of views if it is very complicated. Each view would represent one of your temporary tables.
你考虑过使用意见吗?如果它非常复杂,请使用视图。每个视图都代表一个临时表。
EDIT Based on comments
编辑基于评论
I was thinking of SQL views, basically the same SQL as you would have written in your stored procedures.
我在考虑SQL视图,基本上与在存储过程中编写的SQL相同。
#2
I haven't done this - just thinking how I would start. I would make sure when the stored procedures populate the temporary tables they use the Filtered views for pulling data. I would then set the access to execute the SP to have the same security roles as the Filtered views (which should be pretty much to allow members of the PrivReportingGroup).
我没有这样做 - 只是想着我将如何开始。我会确保当存储过程填充临时表时,他们使用Filtered视图来提取数据。然后,我将执行SP的访问权限设置为与Filtered视图具有相同的安全角色(这应该非常适合允许PrivReportingGroup的成员)。
I would think that would cover allowing you to execute the SP in your report. I imagine if you set up the SP before hand, the SSRS designer has some means of showing you what data is available and to select an SP at design time. But I don't know that for sure.
我认为这将涵盖允许您在报告中执行SP。我想如果您事先设置了SP,SSRS设计师可以通过一些方法向您展示可用的数据并在设计时选择SP。但我肯定不知道。
#3
First, since most cursors are unneeded, what exactly are you doing in them. Perhaps there is a set-based solution and then you can use a view.
首先,由于大多数游标都不需要,你究竟在做什么。也许有一个基于集合的解决方案,然后你可以使用一个视图。
Another possible line of thought, if you are doing something like running totals in the cursor, is can you create a view as the source without the running total and have the report itself do that kind of calculation?
另一种可能的想法是,如果你在光标中运行总计,你可以创建一个视图作为没有运行总计的源,并让报告本身进行这种计算吗?
Additionally, SSRS reports can use stored procs as a data source, read about how in Books online.
此外,SSRS报告可以使用存储过程作为数据源,阅读有关书籍在线的方式。
#4
I found the solution. Downloaded Report Builder 2.0 from Microsoft. This allows me to write querys and call stored procedures for the report data.
我找到了解决方案。从Microsoft下载的Report Builder 2.0。这允许我编写查询并为报表数据调用存储过程。
Microsoft SQL Server Report Builder link
Microsoft SQL Server报表生成器链接