Grouping by parameter 1

[ekta22]

Hi,

I have 2 parameter fields in my report - Region and Sector. I want to add 2 groups to my report based on the above parameters entered by the user. But when I go to Insert Group it does not show me the parameter field in the list. How can I group my report by these parameters?

Thanks,
Ekta

 

[synapsevampire]

Parameters aren't data, and you use data to group reports.

How would crystal know what data goes with which parameter choice?

Posting technical information is advised:

Crystal version
Database/connectivity used
Example data
Expected output

Parameters are generally used to filter out data, or in some cases, they are used to alter what is to be grouped or sorted or displayed.

Perhaps what you mean is that you want to alter what is grouped based on this selection?

I would guess at what you want but I've had bad luck today doing that.

-k

 

[ekta22]

Thanks for replying. I am using Crystal 8.5, SQL Server/ODBC. The report that I need to create has a lot of grouping going on. I am a little confused as to how to go about it and if it's even possible to do it in Crystal. I will try and explain it. The report has certain parameters entered by the user.
1. The records that match the criteria are grouped by REGION and within the region by SECTOR. Region is a required parameter while sector is optional. If sector is not specified it will group by all the sector's within that Region.
2. The records that fall under a particular region and sector are further grouped by WEEKLY OR GREATER INTERVALS and LESS THAN WEEKLY INTERVALS.
3. Under each Interval they are further grouped by AREA. Now this is where it gets even more complicated. Lets say it found AREA1 and AREA2 under Weekly and Greater Intervals. Each area is listed on a separate page.
4. Lets take AREA1. Under Area1 there are 5 columns, which are further grouped by another field called CREW UNIT. The report will display the number of records found for each group grouped by CREW UNIT. So the page will look something like this

Performance Report by Region/Sector – CE 2A
Summary of Performance within area by Crew Unit
For Weekly or greater intervals
Area- AREA1

Crew Unit Group1 Group2 Group3 Group4 Group5
AAA 4 0 10 48 0
BBB 0 20 5 5 2
CCC 0 0 0 5 3

5. The same will be repeated for all the area’s found under the weekly or greater interval.
6. Next it will list in detail all the records found under each group(with each group on a new page) for all the areas together. So if there were 4 records in Group1 under AREA1 and 10 records in Group1 under AREA2, it will display 14 records for that group and so on.
7. The report will follow the steps 3-6 for the LESS THAN WEEKLY INTERVALS.
8. Next the report will follow steps 1-7 for the next sector.

I am sorry if it’s confusing for you guys. I am still totally lost on how to do the grouping. I would really appreciate if I can get some guidance on this.

Thanks in advance,
Ekta

 

[synapsevampire]

It sounds like you understand it just fine, add the fields you wish to group by.

the only rub is that if they do not specify the SECTOR

In that case I would prefill the Sector parameter with "All" as the default, and in the record selectioon formula use:

(
if {?SectorParm} <> "All" then
{table.sector} = {?Sectorparm}
else
if {?SectorParm} = "All" then
true
)

If you're still confused, why not post what was requested:

Example data (fields/data)
Required output (based on the example data)

-k

 

[lbass]

Try the following:

Insert groups on:

{table.region}
{table.sector}
{table.interval}
{table.area}

In the area group header, insert a crosstab with crew unit as the row and {tablegroup} (whatever that is as the column).

To get all of the detail records from all areas together, insert a subreport that uses the first three groups only and is linked to the main report on these fields, and then suppress all but the detail section. Place the subreport in the interval group footer. I'm a little unclear where you would want the page breaks, but once you have the data in the correct arrangement, you can probably easy figure that out using the section expert and "new page after".

-LB

 

[ekta22]

Thanks for your replies. I think I know in which direction to head now. LB the cross-tab report that you wrote about makes sense. But I have couple of more questions abt that. I understand that I will add crew unit as the row(its a field in the database) but the groups for the column heading are custom names. They are not any field in the database and each group has a different formula for finding out the total number of records in it. How should that be handled?

Thanks

 

[lbass]

Please provide the contents of at least two of the column formulas. Are you also suggesting that the summaries vary by column? If so, please provide more detail about the summaries.

-LB

 

[ekta22]

for example the two columns are Equip Not Available and Parts On Order. The formula for these 2 are as follows-

The fields in bold are database fields

Equip Not Available - If Completion_status = 'X' AND Maint_Action_Code = 'U' THEN Equip Not Available.

Count of number of records that match the above criteria for that particular crew unit will be displayed under Equip Not Available collumn.

Parts On Order - If Completion_status = 'X' AND Maint_Action_Code = '5' THEN Parts on Order.

formula's for other column are somewhat similar to the above. Summaries don't vary by column.

 

[lbass]

Create the following formula {@concat}:

{table.completionstatus}+" - "+{table.maintenanceactioncode}

Add this field as your column field in the crosstab. While it is selected, click on "group options"->customize group name->use a formula to customize name->x+2 and enter:

select {@concat}
case "X - U" : "Equip Not Available"
case "X - 5" : "Parts on Order" //etc.
default : "Other"

-LB

 

[ekta22]

sorry for the late reply. I am going to give it a try and get back to you. I had one more question. Not all the formula's for the columns are same. Some have absolutely different calculations. Even then I will do it the same way?

 

[lbass]

Please provide a sample of the column formulas showing the kinds of variation you have. If they are based on something other than variations in these two fields, then I think you might have to create a manual crosstab within the area group.

-LB

 

[ekta22]

The formula's do have variations of other than these two fields. Some other forumula's are as follows.

If ({pPMPERDET.COMPLETION_STATUS}) = "X" AND ({pPMPERDET.END_DATE_TIME} < {pPMPERDET.LATEST_DATE}) AND ({pPMPERDET.MAINT_ACTION_CODE}) = "P" then
    formula = Sum({pPMPERDET;1.NO_TASKS}) else
End if

[code]
If [({pPMPERDET.COMPLETION_STATUS}) = "O" AND ({pPMPERDET.INTERVAL_LENGTH = ''}) OR ({pPMPERDET.SCHEDULED_DATE = '0'}) then
formula = Sum({pPMPERDET;1.NO_TASKS})
End if]

OR

If ({pPMPERDET.COMPLETION_STATUS}) = "X" AND ({pPMPERDET.MAINT_ACTION_CODE}) = "P" then
    formula = Sum({pPMPERDET;1.NO_TASKS})
End if]

How would I create a manual cross-tab?

Thanks

 

[ShortyA]

See :
[URL unfurl="true"]http://www.kenhamady.com/form12.html[/url]

for more info about how to do manual crosstabs.
ShortyA

 

[lbass]

What you are calling a "groups 1 to 5" aren't really different instances of the same group then, so what you are doing isn't really a crosstab, manual or otherwise. However, you should be able to create the desired layout by inserting the four groups and then inserting a fifth group on {table.crew} and then changing each formula to read like the following detail level formula (the following uses Crystal syntax):

If ({pPMPERDET.COMPLETION_STATUS} = "O" AND {pPMPERDET.INTERVAL_LENGTH = ''}) OR {pPMPERDET.SCHEDULED_DATE = '0'} then
{pPMPERDET;1.NO_TASKS}

Then you would right click on each formula and insert a summary at the Area group level. Next suppress the details section. When you are creating the formulas, name each formula so that you get the desired page headers and then drag them into a group header_b section for the area group.

-LB

 

[ekta22]

I did as you said. I created the summary of each formula and placed in the group footer of Area and placed the titles for the column in group header_b of area.
I want the summaries to be displayed right next to the crew/unit. Right now beacuse it's in group footer it doesn't look right.
Need help with two other things after this.
1. The summaries for each area need to be on a different page.
2. Displaying all the records under the summary for each column on a new page.

thanks so much

 

[lbass]

Sorry, let's back up a little--I think I may have led you astray. I think we should reconsider whether this could be set up as a crosstab. Can one record meet the criteria of only one of your "groups"? Or can one record be represented in more than one "group"?

-LB

 

[ekta22]

One record cannot be represented in more than one group. For example a same record cannot be fouond under two regions.

 

[lbass]

I wasn't asking about your "real" groups, but instead about the "group 1 to 5" that you want to display horizontally under each area and for which you provided some of the formulas. Could one record meet your criteria for more than one of these "groups"?

-LB

 

[ekta22]

No, it cannot.

Instead of putting the summaries of the fomulas's at the area group level, I placed it next to group header for crew unit and it looks just fine.
The next step would be to display all the records found under each column on a separate page.
For example, the report looks like this right now

Region: Central
Sector: 2A
Interval: Greater than Weekly
Area : Data

Crew/Unit - Parts on Order - Equip not available
GDL ---------10----------------5
LAC ---------4-----------------7

On next page it displays information for different area

Region: Central
Sector: 2A
Interval: Greater than Weekly
Area : COMM

Crew/Unit - Parts on Order - Equip not available
RFC ---------25----------------0
LAC ---------1-----------------10

Now it needs to display the records found under each column in detail but for both areas combined. So for Parts on Order it needs to display 10+4 records under Area-Data and 25+1 under Area-COMM, which is a total of 40 records on a new page and same thing for other columns. I hope it's not confusing.

 

[lbass]

Yes, the display will work in the crew group section, but I later realized that you are better off if you can create an actual group based on your horizontal "group" formulas. (Although you could leave the report as is and skip to my suggestion below about the subreport, just using the formula below in the subreport only.) What you would do is combine all your horizontal formulas into one formula {@grpname}, by doing the following:

If {pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.END_DATE_TIME} < {pPMPERDET.LATEST_DATE} AND {pPMPERDET.MAINT_ACTION_CODE} = "P" then
"Group 1" else //replace "Group 1" with descriptive name

If {pPMPERDET.COMPLETION_STATUS}) = "O" AND (
(
{pPMPERDET.INTERVAL_LENGTH = ''} OR {pPMPERDET.SCHEDULED_DATE = '0'}
) then //not sure how you want the clauses set off with parens,
//but you need some here
"Group 2" else

If {pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.MAINT_ACTION_CODE} = "P" then
"Group 3" else //etc.

You can then use {@grpname} as your column field in a crosstab, and use {table.crewunit} as your row field, with count of some recurring field as your summary. This crosstab would be placed in the Area Group header or footer. There is no need for a group on crewunit.

Next you would insert a subreport that again uses the formula {@grpname}. This time you would insert a group on the formula and add all detail fields that you wanted displayed. Go to the subreport section expert->group footer for {@grpname}->new page after->x+2 and enter:

not onlastrecord

Then place the subreport in the Interval group footer (since you want the detail records to cross areas), and link the subreport to the main report on matching fields for region, sector, and interval.

-LB