Multiple Summaries

[Toxodont]

Hello, I am new to CR, and I am trying to make a crosstab-like report:

New Glasgow Pictou
Single Family 10000 3000

New Glasgow and Pictou are field values in my database from a field called 'Mun_unit'. Single Family is a value from a field called 'type_occ'. The values 10000 and 3000 are associated *totals* of values from a field called 'Value'.

Now, I can get the first value of 10000 no sweat using a grand total. Now, my problem occurs when I try to make another grand total it won't let me. Because I already made a grand total to get my value for new glasgow it will not seem to let me make another grand total for Pictou. I attempted to copy the field and paste it but when I change any criteria in the select expert is changes New Glasgows criteria as well.

Is there a way to have multiple grand totals, or another way to get my field totals without using grand totals? Any comments, questions and rude remarks would be greatly appreciated! Thanks

Toxodont

 

[lbass]

Is there a reason you are not inserting a crosstab to accomplish this? Insert a crosstab, and choose {table.mun_unit} for the column, {table.type_occ} for the row, and {table.value} for the summary field, with sum as the summary.

If for some reason you can't do that, then create a manual crosstab, by grouping on {table.type_occ} and then creating two formulas, one for each column:

if {table.mun_unit} = "New Glasgow" then {table.value} else 0

If {table.mun_unit} = "Pictou" then {table.value} else 0

Place these in the details section and then you can insert summaries on each of these.

If you only want {table.type_occ} = "Single Family" to show in your crosstab, then use this as a record selection criterion.

-LB

 

[Toxodont]

thank you so much lbass! It works great now! Here is my current code to get the report:

{PERMIT.TYPEOCC} = "Single Family"
and {PERMIT.MUNUNIT} = "New Glasgow"
and {PERMIT.DATEBLDG} = #01/01/2003# to #01/31/2003#

Now that just does New Glasgows information as illustrated in my first post. How would I get Pictou's information to show up beside it? Would that be another record selection criteria, or would that be added into this code here? The next typeocc I need in the report is 'two family'. The report would then look like this:

New Glasgow Pictou
Single Family 10000 3000
Two Family 34223 4544

 

[lbass]

All records to be used in the report must be in the record selection formula, so change your record select to:

{PERMIT.TYPEOCC} in ["Single Family","Two Family"] and {PERMIT.MUNUNIT} in ["New Glasgow","Pictou"] and {PERMIT.DATEBLDG} = #01/01/2003# to #01/31/2003#

If you want to use all instances of {PERMIT.TYPOCC} and all instances of {PERMIT.MUNUNIT}, leave them out of the record selection altogether, and just use the date period.

Just try what I suggested in my earlier response to get the crosstab working, and let me know if you need any further help.

-LB

 

[Toxodont]

lbass, thank you so much for your help! I have gotten the crosstab working, but now I have another question for you. Is it possible to have two cross tab reports on one page, and have different selection criteria for each report? Would I have to go the manual route to do this, or can I simply copy and paste one cross tab and then somehow edit it to have a different criteria? Thanks again for all your help on this! I greatly appreciate it!


Toxodont

 

[Toxodont]

Ok, I figured out that I needed to insert a sub-report, so how can I go and make it so that both reports use the same parameter for a date input? Questions, comments, rude remarks? :O)

 

[BobSuruncle]

Create the same parameter in both Main and Sub.
In the Subreport linking dialog, choose the main report's parameter and then in the lower left corner of the dialog, from the drop down list, choose the parameter from the Sub.
You've got to play with the drop-down list-it's very picky.

Set the Record selection in the subreport to use this parameter.




HTH
Bob Suruncle

 

[lbass]

You don't necessarily have to use subreports--it depends on what criteria you want to use in your second crosstab. You can insert any number of crosstabs into one report. So what would you want the second crosstab to show? If you are trying to "break out" the first crosstab into several, that is possible, too. So how about a little more info?

-LB

 

[Toxodont]

Bob Suruncle thank you for your response, I have the sub report working ok now.

lbass, I want to make almost the exact same report again on the same page, but with one criteria change. Here is the code for the first crosstab:

{PERMIT.TYPEOCC} = ["Single Family","Two Family", "Multi-Family", "Accessory Building", "Home Occupation", "Commercial", "Industrial", "Institutional", "Other" , "Mobile Home"]
and {PERMIT.MUNUNIT} = ["New Glasgow","Pictou", "Stellarton", "Trenton", "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Pre-MFGED Building"]

In the second report all I wanted was to change:
{PERMIT.APP} = ["Conventional Building" , "Pre-MFGED Building"]

to this:

{PERMIT.APP} = ["Renovations & Repair"]

I tried copying the report and also tried inserting a new cross tab but it used the same select criteria, so I think using a sub report is prolly the only way to do this.

Now it's just a matter of formatting the report, because I've gotten all the information I need. One thing I noticed was that CR supresses columns that have no data in them. Ideally, I would like to show all the columns I specified in my criteria. There must be a way to make it show them all, even if they do not contain any values. It's a matter of keeping the report looking the same every time you run it, instead of showing only the columns and rows with values (more of a thing my boss wants than I, beleive me).

Also, there is a black box highlighting my sub report, which I am trying to figure out how to get rid of. Other than that the report is pretty much complete! Thank you very VERY much for helping me out with this as well! It would take a whole day of very inefficient work to create the same thing using an excel / access combination (the old way), and even then, the data would be prone to inaccuracy. Thanks again for all your help, I truely appreciate it!


Toxodont

 

[Toxodont]

OK, I got rid of the black box, and now I just need to get all the columns and rows to show, and one more thing. Due to space limitations (one 8.5 X 11 sheet of paper) I need to change the way the cross tab shows up from this:

New Glasgow Pictou
Single Family 100 90
90 54
70 30
to this:

New Glasgow Pictou
Single Family 100 90 70 90 54 30

Sorry to keep tacking more questions on, but each time I get a couple things done I always run into some small thing that causes some sort of problem. Thanks for all your help!


Toxodont

 

[lbass]

Why do you have multiple results for "Single Family"? Did you insert multiple summarized fields? What are they?

To get a row to display even though the summarized value is zero, you could select the same field you used for your row field and use it as a second summary field. Then in design or preview mode click on the summary field-format field->suppress. The field will disappear, but now the rows with zero values in the other summary will reappear.

-LB

 

[Toxodont]

lbass, thank you for your response again!

The reason I have multiple summaries is because I need to view value (permit.value), units(permit.unit) and permits (permit.permit) by the type of occupancy (permit.typeocc) and by municipal unit (permit.mununit). Here is a screenshot:

http://www.nerv.ca/misc/crosstab.jpg

Here is a screen capture of the report it brings up for a january 2003 activity report:

http://www.nerv.ca/misc/jan2003.jpg

I tried inserting the other summary field as you suggested but no luck. It still only shows the fields with data in them.

{PERMIT.TYPEOCC} = ["Single Family","Two Family", "Multi-Family", "Accessory Building", "Home Occupation", "Commercial", "Industrial", "Institutional", "Other" , "Mobile Home"]
and {PERMIT.MUNUNIT} = ["New Glasgow","Pictou", "Stellarton", "Trenton", "Westville"]
and {PERMIT.DATEBLDG} = {?Date}
and {PERMIT.APP} = ["Conventional Building" , "Pre-MFGED Building"]

You can see in my record selection formula the values I specified for permit.typeocc and permit.mununit. The data it brings up in the report IS completely accurate, but I need to see the data(zeros) along with it for the other specified values.

Logically one would think I need only insert a little piece of code forcing all specified values to appear, or just a simple setting in the program. I hope this gives you a better idea of what I'm trying to do here. Thanks again!


Toxodont

 

[lbass]

I'm not sure this will solve the problem, but you could try creating formulas for the fields you are summarizing:

if isnull({PERMIT.VALUE}) then 0 else {PERMIT.VALUE}

if isnull({PERMIT.UNITS}) then 0 else {PERMIT.UNITS}

if isnull({PERMIT.PERMIT}) then 0 else {PERMIT.PERMIT}

Use these for your summary field instead of the fields themselves.

In terms of the horizontal layout, I think the only way you can achieve this is by doing a manual crosstab. You would have to create three formulas, one for each field to be summarized, for each of the five communities in your report. You would group on {PERMIT.TYPE_OCC} and then create 15 formulas like:

if {permit.mun_unit} = "New Glasgow" then
(if isnull({permit.value}) then 0 else {permit.value}) else 0

You would then insert summaries on these and show them in the group header or footer, positioning them in a horizontal row.

Let me know if any of this works.

-LB

 

[BobSuruncle]

Is this version 9 of Crystal?


Bob Suruncle

 

[Toxodont]

@lbass

I tried the above suggestion for displaying all specified field values but alas columns still did not display, just the ones with data in them.

I did get the manual crosstab working though, which will let me display all 5 towns at least. I still cannot display all the types of occupancy, but it's nice to have the towns up.

@BobSuruncle

I am using version 8.5 Developer.

I may post a thread dedicated to the problem of showing all columns, because this thread has changed a lot since it's creation.

Thanks AGAIN, for all your help! It is making things a lot easier on my side! :O) I really think CR is the solution for this type of report because excel just doesn't cut it when it comes to database connecting reports.


Toxodont