Weekly report of pipeline, actions, and funds by solicitor

Guy Lowicz

Hi all,


I am trying to build a weekly report that would track solicitor performance according to (1) how many constituents they have on each stage of the pipeline (through Prospect Status), (2) a tally of all the actions each solicitor had, and total of gifts per solicitor. 

I still don't know how to use SAP Crystal Reports, so have to stick to what RE has to offer. 


Any advice would be greatly appreciated! 


Attached a sample of what I'm looking for

 

Brian Davies

I create a report using pivot tables. The pivot tables are used to get the data and I copy and paste the info into a template. I can see you creating a similar process to get the data you are looking for.



 

Debra Holcomb

Brian Davies:

I create a report using pivot tables. The pivot tables are used to get the data and I copy and paste the info into a template. I can see you creating a similar process to get the data you are looking for.



 

Hi Brian,  

This is very helpful to see and thanks for sharing it. Would you be willing to post a snapshot of Output fields you use in your query that the pivot report is based upon?

Thank you,

Debra

Guy Lowicz

Brian Davies:

I create a report using pivot tables. The pivot tables are used to get the data and I copy and paste the info into a template. I can see you creating a similar process to get the data you are looking for.



 

Thank you, Brian, for your response. This is really helpful. And it's very similar to the report I'm trying to create. The only difference seems to be that I also want to measure actions. I played with pivots, but as you mentioned, I couldn't configure it so that it will display the data the way I wanted. So I'll take your suggestions and just take the data from pivot and paste it in my report. 


I would like to join Debra's requests for the output fields. Would be tremendously helpful


Thanks again for both of you!

Guy

David Ritchie

Hi Guy,

As an alternative, if you are happy using excel files and downloading data from RE, there are other alternatives that can really provide easier report generation for you and possibly your organization. You should look at QlikSense Cloud, http://www.qlik.com/us/products/qlik-sense/qlik-sense-cloud, and try it out. There are a few options, and free to try out. We have been using an On Premise Install for 18 months, and our staff love it. It has really changed our reporting. 


You can easily just download the raw data, and not try to format pivots and outputs in RE. You could then build dashboards like this. Then on the next sheet in the visualization you can provide Action counts, and the next sheet can contain all the details. It provides a completely different experience for your users.



Cheers,

David

Brian Davies

Here is my query output and pivot report screenshots. I belive by carefully thinking thorugh your action types you should be able to create a pivot report to get the output you are looking for. It may take looking at multiple views or running a few different reports to get everything you need. I create my report every other week and it takes about 10 minutes.


I use this view to determine porfolio balancing



I use two versions of this pivot, first using ask amount, second using funded amount. I use ask amount to populate Qualification, Cultivation, Verbal Commitment, Decline, DQ and Closed. I use Amount Funded to polulate Partial Funded and Stewardship. We rarely use Amount Expected, we did I would pull it for Verbal Commitment. If you name your proposal stages 1. Identification, 2. Qualification, 3. Cultivation, etc. then this report would list in order.



Here are my query outputs, pretty simple. Do note that if you list multiple solicitors on a proposal you will get duplicate records and may throw off your totals. At another organization we listed the primary solicitor as an attribute in which case you would pull that attribute rather than solicitors to eliminate duplicate records. The query criteria I use is Proposal type, but Prospect Proposal Is Inactive may also work depending on how you enter your proposals.




David do you have to data warehouse to use QlikSense? We are currently looking at a few options to dive deaper into our data with less hands on data manipulation, but that is for another converation...

Brian Davies

Guy Lowicz:

 

Thank you, Brian, for your response. This is really helpful. And it's very similar to the report I'm trying to create. The only difference seems to be that I also want to measure actions. I played with pivots, but as you mentioned, I couldn't configure it so that it will display the data the way I wanted. So I'll take your suggestions and just take the data from pivot and paste it in my report. 


I would like to join Debra's requests for the output fields. Would be tremendously helpful


Thanks again for both of you!

Guy

 

Hey Guy one more thought, at another organization we put in "clearance to..." actions. This is how we tracked aging and how prospects move through our pipeline. The solicitor would put in a "Clearance to Qualify" action when the proposal was moved to Qualification then a "Clearance to Cultivate" action when the proposal went to Cultivation. In this way you can track movements per time period as well as determine how long prospects stay in each step of your pipeline.

Guy Lowicz

David Ritchie:

Hi Guy,

As an alternative, if you are happy using excel files and downloading data from RE, there are other alternatives that can really provide easier report generation for you and possibly your organization. You should look at QlikSense Cloud, http://www.qlik.com/us/products/qlik-sense/qlik-sense-cloud, and try it out. There are a few options, and free to try out. We have been using an On Premise Install for 18 months, and our staff love it. It has really changed our reporting. 


You can easily just download the raw data, and not try to format pivots and outputs in RE. You could then build dashboards like this. Then on the next sheet in the visualization you can provide Action counts, and the next sheet can contain all the details. It provides a completely different experience for your users.



Cheers,

David

This looks really great, David! Thanks so much for sharing. I'll give it a try. It's definitely more presentable than RE products.

Guy Lowicz

Brian Davies:

Here is my query output and pivot report screenshots. I belive by carefully thinking thorugh your action types you should be able to create a pivot report to get the output you are looking for. It may take looking at multiple views or running a few different reports to get everything you need. I create my report every other week and it takes about 10 minutes.




David do you have to data warehouse to use QlikSense? We are currently looking at a few options to dive deaper into our data with less hands on data manipulation, but that is for another converation...

Thanks, Brian! Super helpful!

David Ritchie

Hi Brian,

We have built our own ODS. I decided it was not worth investing in a DW at this time, as QlikSense can easily do all the work that a DW does. Our ODS allows us to easily build SSRS Reports for transactional based reporting as well. It allows us to implement all our data policies as part of the ETL process. It also gives us the benefit that if those rules change, we change it one place and we are done.


QlikSense will take Excel Files, SQL Server, Oracle, etc. You can even mix data loads and Excel files together in a project. If you have a way of auto exporting an excel file out of RE, then you can drop that somewhere for Qlik to load in. We are an on-premise install of QlikSense, so I cannot speak if there are any difference in load features between the Cloud version and on premise.

Cheers,

David