Supressing Details Section

[kchaudhry]

I am using CR 8.5 with SQL Server 2000 database. Here is what I am trying to get in the group summary.

*Current Month (CM)
Local Outbound Inbound
------- ------- --------
CM Tier 1: 000 000 000
CM Tier 2: 000 000 000
CM Tier 3: 000 000 000
------- ------- --------
CM Total: 000 000 000
------- ------- --------
YTD: 000 000 000
------- ------- --------

The Year to date (YTD) field will include CM total + YTD value for each category respectively ( i.e. Local, Outbound, Inbound).

I am using the following formula to get the CM Tier 1:

if {Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
then
1
else

I have summaized this field to get the count of all the transactions.The YTD information is saved in the {Archive_Participant_Move_in_Trans} table. I have created another formula to get this required result. After this I am doing a sum of the first formula and the second formula to get the YTD field. My problem is that when I place the second formula (to get YTD) information, I get all the records in the detail section. How can I suppress this information? Is there an easier way of getting this done.

All the help is appreciated.

Thanks,

Kchaudhry

 

[synapsevampire]

Rather than text descriptions of technical information, try posting the following:

Crystal version
Database and connectivity used
Tables used and how and the type of joins
example data
expected output

This is the equivalent of a mini-spec and you'l find that it should help to clarify things for everyone involved.

-k

 

[kchaudhry]

I am using Crystal Reports 8.5 with ODBC to the SQL server 2000 database.
I am using the following tables.
{Participant} Contains the Participant info. Contains ID, Name, Address, Phone number
{Participant_Move_in_trans} Info related to participant's sales. Only contains data for one month. At the end of the month all the information is archived. ID, Customer Name, Customer address, Customer Company, Prod Com Elig (local, outbound, inbound for current month)
{Archive_Participant_Move_in_trans} Same as the previous table only contains the archive information. This will have paricipant id, move in date, reservation date.Prod Com Elig (local, outbound, inbound for previous months)
{Plan_Manager} contains the information about the current month. Only has one row of data.

This was the table structure and data type.
I am trying to get the total in the summary section. I have to only get certain data from a table. For example the table {Participant_Move_in_trans} contains a field Prod Com Elig which had three values (local, outbound & inbound). This is why I have to use formulas to get this information and I have to do this for not only the current month but also for the entire year. This is where I get all the records. I only want to show the data for the current month in the details section. Hope this explains my question in a better way.

Kchaudhry

 

[synapsevampire]

You're trying to get what total in the summary???

Assume nothing, explain what you want specifically, not generalities, it'll keep things unambiguous.

If you have an unrelated table (perhaps {Plan_Manager})that you need data from, creatye a subreport in the report footer to display that data.

Otehrwise it seem that you can join by ID throughout.

-k

 

[kchaudhry]

Ok let me try to explain it again. I have grouped the report on Participant ID. The details section inlcudes the information about different transactions, type of transaction, reservation date, move in date etc. Now I have to create a summary of all the transactions for this ID. This includes the information not only for the current month but also from the entire year. I have linked the {Participant}, {Participant_Move_in_Trans} and {Archive_Participant_move_in_Trans} table with the ID field using the equal join. The {Plan_Manager} table is linked to the {Participant} table with the "keyfield" using left outer join.

Information for current month is coming from {Participant_move_in_trans} and the entire year's information (besides the current month) is coming from {Archive_Participant_move_in_trans}.

Now I am trying to get the Summmary table shown in my first post. I need the total for Prod_com_elig with "local" type. I need the summary for current month, also I need the total number of transactions for YTD. Similarly I have to do the same for "Inbound" and "Outbound". I have been able to get the results using the formulas described in the first post. The problem is that, the details section is showing me records for the entire year. I only want the current month's records to show on the report and supress the other records.

Hope this calrifies what my question is.

Kchaudhry

 

[lbass]

Insert a second detail section details_b and put your formula for the archived records in this section. The formula probably looks like {@archive}:

if {Archive_Participant_Move_in_Trans.Trans_Category} = "Local" then 1
else 0

Create a formula using something like:

sum({@archivelocal})+sum({@local}) //assuming this is for a report footer

...to get your YTD field for the footer, and then go to format->section->detail_b and check suppress. This would leave your detail records in detail_a still displaying.

To get the summaries you are trying to display, you should be using a formula like the following, omitting the reference to the tier:

if {Participant_Move_in_Trans.Trans_Category} = "Local" then 1
else 0

Then you would group on {Participant_Move_in_Trans.Tier_Level} to get the results per tier in the group footer or header, with the grand total in the report footer (or a higher order group footer).

-LB

 

[kchaudhry]

Lbass,

Thanks for pointing me into the right direction. Can you please explain what do you mean by:

Create a formula using something like:

sum({@archivelocal})+sum({@local}) //assuming this is for a report footer

Do you mean that I should create two other formulas i.e. {@archivelocal} and {@local}? If yes, can you please give a little more guidance.

Thanks,

Kchaudhry

 

[lbass]

{@archivelocal} is my name for the formula:

if {Archive_Participant_Move_in_Trans.Trans_Category} = "Local" then 1
else 0

I see that I gave it two different names in my last post--sorry. I am assuming that to get the YTD info from the archive that you need to summarize the archive records, too, and that's what this formula will do. You would need separate formulas for Outbound and Inbound categories.

{@local} is the current month's detail formula which replaces the one you showed earlier that included a tier clause:

if {Participant_Move_in_Trans.Trans_Category} = "Local" then 1
else 0

Again, you would need separate formulas for Outbound and Inbound. Be sure to group on the tier field, and then to get the summaries by tier and across tiers (total), you would right click on each formula and choose insert summary->sum for each group and for the grand total.

To get the YTD which will include the current month's totals, you would create three more formulas like:

sum({@local})+sum({archivelocal})

Place this in the report footer and repeat for Inbound and Outbound formulas.

-LB

 

[kchaudhry]

Lbass,

Thanks, it seems to work the way I wanted it to work. I have another question. Is it possbile to show a summary field without showing the records in the details section? I mean I have added the YTD summary and the supressed the details-b section but the records are still there and take a while to run the report. Is there a way around this?

Thanks,

Kchaudhry

 

[lbass]

I think you could create the formulas necessary for the summary without adding anything to the detail section, but since the formula would be based on evaluating each record, even if not displayed, I'm not sure the report would run any faster.

-LB

 

[kchaudhry]

Lbass,

I am running the SQL server & Crystal reports both on my machine. This is only for developing the report. In actual use the client will have a dedicated server. I think this is my best bet to leave the report like this.

Thanks for all your help.

Kchaudhry

 

[carmenho]

Hi ,,
I got a formula ,let say {@comm}, which formula likes this
({@amtS} / 100 ) * 0.5
which is to calculate for each group ..
however, in the report footer. i want to have sum of {@comm}
thus, i create another formula ..{@gComm} with formula sum({@comm}), however, it said this field can't be summarized. what can i do for this ?

 

[kchaudhry]

Carmenho,

Have you tried just adding the summary field without using a formula. Also try placing the @comm formula in the details section and make sure that you are getting some values.

You can add the summary by going to Insert>Summary and selecting your @comm formula there.

Also if this is based on each group then why are you placing it in the report footer as compared to the group footer?


Kchaudhry

Kchaudhry