Multiple Searches One Report

[Joseph31]

I need to make a excel report that counts the number of case per state. I need this for all 50 states.

I can do this for one (1) state at a time on 50 different reports; but How do I do this with all 50 states on one excel report?

Thank you,

Joseph

[mr_vodka]

Seems like you can just use a subsummary report grouped by state and a summary field that counts.

[Joseph31]

Thanks for the quick response;

Two questions:

1) do I have to make each state as a field to sub summary them?

2) I have to put a few more search criteria in the report is there a way to do this a script?

Thank you

[mr_vodka]

You only need one state field and then create the summary report grouped by that state field. You can delete out the body part since you really dont need in in your case.

The subsummary report will be based off your found set so perform your find prior to having your final found set you want to report on.

[Joseph31]

I think I am close can you show me an example of this.

Thank you

[mr_vodka]

Here is one.

States_report.zip

[Joseph31]

Thank you, You are very good. One more question:

1) I see how you do the summary for the States -- But there is only states listed on each record. How do you do the same kind of report with the first column counting the records per state, the second column counting States Closed and finally the third column counting States Sold. All on one report. Is that possible.

Thank you again

[mr_vodka]

I believe that before we go any further, you should layout all the requirements and state exactly what you need and the prupose of it. It will make a difference on how to report it.

[Joseph31]

Ok my apologies -- Your Right.

I have attached a sample of one of the layouts in the database --

I need to do a report (to excel) that will:

List all States in a column -- for each state;

1) Number of Policies Reviewed

2) Number of Policies Offered on

3) Number of Policies Purchased.

I hope that you can help. I am having trouble making this report that will do all this at once on on report.

Thank you.

Sample_Database.zip

Edited May 20, 2008 by Guest

[mr_vodka]

With your current setup, only a slight modification needs to be done. Create 3 more summary fields that sums up each one of your flag fields, then 3 three summary fields on the report.

[Joseph31]

How do I list all of the states? I might only have 30 of them in the database at time of reporting -- is there a way to list all 50 States everytime?

[Søren Dyhr]

You could make it a cross-tab following Edoshin's recipe here:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

Instead of using the month number in Edoshin's approach for the horizontal ushering, use this:

http://www.briandunning.com/cf/62

...working on say ValueListItems( of all the states! It will as far as I count it reduce the need for summaryfields down to 3?

--sd

[mr_vodka]

The easiest way would be to create a dummy record for each state.

Another way would have been to have a table full of states and then sum up the related records. However, this gets thrown out the window since you said that you would need to use a found set.

2) I have to put a few more search criteria in the report is there a way to do this a script?

Does this still stand true? If so, then you will have to use a more advanced technique.

[Joseph31]

I have made a slight Correction in the Sample data base -- I have moved the seller onto another layout -- this complicates the summary fields --- I know you have done a lot of work on this but I am having a devil of a time fig this out -- Can you show me how to do on the sample layout what you did on your layout example?

Thank you

Joseph

Sample_Database.zip

Edited May 22, 2008 by Guest

[Joseph31]

Yes it would still stand true.

Thank you,

Joseph

[mr_vodka]

How do I list all of the states? I might only have 30 of them in the database at time of reporting -- is there a way to list all 50 States everytime?

I have modified my sample file to adapt to this as well as your narrowing of the found set. As I stated earlier, the method is slightly more advanced than the subsummary report done in the first example.

ALL_States_report.zip

[Joseph31]

Thank you for your help -- I need a few moments to look over this -- Your knowledge is far greater than mine. I will get back with you when I get my hands around it.

Thank you.

Joseph