Using SQL Stored Procedure as data for a Microsoft Dynamics CRM report

Question

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.

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:

Start date
Number of months
Value

I create a temp table (temp1) that contains the following columns:

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:

2009-03-01,4,1000 in table1 yields

2009,03,March,250
2009,04,April,250
2009,05,May,250
2009,06,June,250

in the temp1 table.

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.

example temp1 data:

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:

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.

Answer 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.

Answer 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).

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.

Answer 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.

Answer 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 SQL Server Report Builder link

Answer 5

reporting services are the way to go here, you can embed the report in a crm iframe.