Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Chad_C
Starting Member
7 Posts |
 Posted - 2010-03-24 : 09:17:30
|
| I would like to create a summary table at the top of a report, but I'm having trouble figuring out the best way to do it.My report is pre-built in CRM, so adding an additional SQL query is not really an option.The report is grouped by employee and then opportunities won/lost. The opportunties won/lost are each calculated (summed) as a separate group; i.e., there is a sum for "lost" opportunties per employee and a sum for "won" opportunties per employee. A grouping for data on my account may look like this:-----------------------------Chad (Lost: 2) [LostOpportunity1] [LostOpportunity2] (Won: 1) [WonOppportunity1]-----------------------------The report can be filtered by date or other options from within CRM. What I would like to show is a summary of each employee, the number of opportunites they have won, the number of opportunities they have lost, and their win/loss ratio. It would look something like this:-----------------------------Employee | Won | Lost | RatioEmployee1 | 5 | 2 | [ratio]Employee2 | 1 | 1 | [ratio]-----------------------------Because I am completely new to reporting, I haven't been able to find any books that describe this process. Maybe it's because I don't know the correct terminology? I've looked at using group variables, but because the data is dynamic (in terms of the number of groups there would be) I can't really see a way of doing this without putting values in an array list and then somehow recalling those values to populate the top (summary) table. Any ideas or pointers? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-24 : 12:30:17
|
| are you using sql reporting services? then its just a matter of using matrix container using employee as row group and opputunities column as column group (win,lost,..)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Chad_C
Starting Member
7 Posts |
Posted - 2010-03-24 : 12:37:24
|
quote:
Originally posted by visakh16
are you using sql reporting services? then its just a matter of using matrix container using employee as row group and opputunities column as column group (win,lost,..)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Yes, I am using SSRS. I'm modifying the report using Visual Studio 2008 -- it's first created in CRM.Is there a way to reference individual rows by the data they contain? I can't wrap my brain around how I could get the information for a specific employee and put it in a summary table at the top of the report. |
|
|
|
Chad_C
Starting Member
7 Posts |
Posted - 2010-03-24 : 12:44:46
|
| Even if I used a VB function for each "Count" row (the row which contains the count of "Lost" or "Won"), and put the values in an array, how could I reference them in the tablix (the summary) at the top of the report?It's super easy with two queries to SQL; however, I need to use the Microsoft-approved method of using CRM permissions. The easiest way I could think of to comply with this was to take an existing report which contains 99% of the data I need and modify it to contain a summary table.I hope that makes sense! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-24 : 13:24:08
|
quote:
Originally posted by Chad_C
Even if I used a VB function for each "Count" row (the row which contains the count of "Lost" or "Won"), and put the values in an array, how could I reference them in the tablix (the summary) at the top of the report?It's super easy with two queries to SQL; however, I need to use the Microsoft-approved method of using CRM permissions. The easiest way I could think of to comply with this was to take an existing report which contains 99% of the data I need and modify it to contain a summary table.I hope that makes sense!
how is data currently returned by your query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Chad_C
Starting Member
7 Posts |
Posted - 2010-03-24 : 14:32:55
|
quote:
Originally posted by visakh16
quote:
Originally posted by Chad_C
Even if I used a VB function for each "Count" row (the row which contains the count of "Lost" or "Won"), and put the values in an array, how could I reference them in the tablix (the summary) at the top of the report?It's super easy with two queries to SQL; however, I need to use the Microsoft-approved method of using CRM permissions. The easiest way I could think of to comply with this was to take an existing report which contains 99% of the data I need and modify it to contain a summary table.I hope that makes sense!
how is data currently returned by your query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I created a report using the report wizard in Dynamics CRM v4.0. I then downloaded the report from CRM and modified it in Visual Studio 2008. To test changes, I upload the report back to CRM and run it from there.Does that help? |
|
|
|
|
|
|
|
|
Custom Report Summary Table