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

 

[ekta22]

Thanks LB. I will try it out and get back to you. Thanks for all your help so far.

 

[ekta22]

I created a subreport, grouped it by the @groupname formula and then linked it to the main report by region, sector and interval and placed in the
Interval group footer.
But the sub-report doesn't seem to showing the right data. It seems to be passing the right region, sector and interval to the subreport
but then it is showing everything. All crew unit data and I am getting thousands of records in the subreport. For example this is what I have

Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: ESU

[b]crew/tech   Equip Not Available   Parts On Order   Timely Complete[/b]
AAA                0                    0            2

----------------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: COMM

[b]crew/tech   Equip Not Available   Parts On Order   Timely Complete[/b]
BBB                0                    0             3
CCC                0                    0             1

------------
Now on next page it will not display any reocrds for equip not available and parts on order as the record count is 0. It will display
5 records for Timely Complete. So it will look like

Region: Central
Secotr: 11
Interval: Weekly or Greater
Entries For Timely Complete

[b]LogID NO.     Type     Area    Crew Unit     No of tasks[/b]  etc.
 111           LPM      ESU        AAA           01
 222           LPM      ESU        AAA           01
 333           LPM      COMM       BBB           01
 444           LCE      COMM       BBB           01
 555           LCE      COMM       BBB           01
 666           LPM      COMM       CCC           01

After this the same thing repeats for Less Than Weekly Interval for same Region, Sector and so on.

 

[lbass]

Sorry, I can't tell how your results are different from what you wanted. Please explain what should be displayed.

-LB

 

[ekta22]

The results should be displayed like my previous post. But right now it is passing the Region, Sector, interval and area correctly to the subreport but it is showing ALL the crew/unit data in the subreport.

 

[lbass]

I'm not following. Your previous posts did not show the detail level at all, as far as I know. Please be more specific.

-LB

 

[ekta22]

Ok.. let me try once more. It first groups the report by region, sector, interval and area. Then within each area it further groups the records by crew unit and then for each crew unit it counts the number of records found under each column heading, which looks like below.

Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
[b]Area: ESU[/b]

[b]crew/tech   Equip Not Available   Parts On Order   Timely Complete[/b]
AAA                0                    0            2

----------------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
[b]Area: COMM[/b]

[b]crew/tech   Equip Not Available   Parts On Order   Timely Complete[/b]
BBB                0                    0             3
CCC                0                    0             1

------------

So far this works just fine. The problem is displaying the number of records found under each column in detail.
It finds a total of 6 records under timely complete formula and now it needs to display these 6 records in detail. If you notice it is displaying records for both the areas, ESU and COMM together in detail section but only for Timely Complete.

So, the the detail records should look like this, i.e. subreport.

Region: Central
Secotr: 11
Interval: Weekly or Greater
Entries For Timely Complete

LogID NO.     Type     Area    Crew Unit     No of tasks  etc.
 111           LPM      ESU        AAA           01
 222           LPM      ESU        AAA           01
 333           LPM      COMM       BBB           01
 444           LCE      COMM       BBB           01
 555           LCE      COMM       BBB           01
 666           LPM      COMM       CCC           01

If there were records under "Equip Not Available" it would display those records the same way in detail on a new page and so on for other columns. I hope I was more clear this time.

 

[lbass]

Yes, I follow this, and it is consistent with what I suggested. What records are actually appearing that shouldn't be appearing? You say it is showing "ALL the crew/unit data" which implies that it shouldn't, but I don't see anywhere that you have indicated the crew/unit data should be limited. Limited to what?

-LB

 

[ekta22]

It should be limited to the grouping we did on crew unit and the records fouond under each horizontal formula.
For example, if you look below. It should display 3 records where crew unit will be BBB and 1 for CCC in detail under Timely Complete.

[b]crew/tech   Equip Not Available   Parts On Order   Timely Complete[/b]
[b]BBB[/b]                0                    0             3
[b]CCC[/b]                0                    0             1

But instead it is showing me all the crew unit data found under that region, sector, interval and area.

LogID NO.     Type     Area    Crew Unit     No of tasks  Region    Sector 
 
 333           LPM      COMM       BBB           01         CE	      11
 444           LCE      COMM       BBB           01         CE        11
 555           LCE      COMM       BBB           01         CE        11
 666           LPM      COMM       CCC           01	    CE        11
 777	       LIR      COMM       [b]XXX[/b]           02         CE        11
 888	       LPM	  COMM	    [b]YYY[/b]         01         CE        11

Do you think I need to modify the grouping formula for the subreport to show records in the subreport only if there are records found under each horizontal formula and not display any records if record count is 0.

 

[lbass]

I think you must have some selection on crew/unit in the main report that you don't have in the subreport. You should apply the same record selection criteria in the subreport as in the main report in addition to linking the sub to the main.

The other thing that could be occurring is if you are using multiple tables in the main report with an equal join to the table containing the same fields as are used in the subreport, then the joins could be limiting records in the main report, without the same thing occurring in the subreport.

Finally, although I don't think this could be the culprit, make sure the formulas you are using in the manual crosstab in the crew group are exactly the same as the ones incorporated into your grouping formula in the subreport.

-LB

 

[ekta22]

I think you make a good point. My main report is based on 3 tables. Table1 and table2 have an inner join which then has a left outer join on table3 and there are certain other record selection criteria's in the stored procedure. My subreport is just based on table1.

 

[ekta22]

Is there a way to show the details without the subreport?

 

[lbass]

Not really. If what I suggested is this case, this is easily fixable. Add the three tables to the subreport, and link them in the visual linking expert as you did in the main report, and then add the same record selection criteria to the subreport. You don't need to insert any groups in the subreport. Be sure to maintain the subreport links shown in the record selection formula that were automatically added when you linked the subreport to the main report on the three group fields. Just add the other criteria. This should get you all the way there, I think.

-LB

 

[ekta22]

My optional parameters (entered by the user), joins for the 3 tables and fixed parameters for the main report were all done in the stored procedure. For the subreport I created a view with the 3 tables and fixed parameters. I don't think I need to add the optional parameters for the subreport? or do I. I didn't do any grouping for the subreport. It is still showing all crew unit data. I don't have any criteria for crew unit. I have just done grouping on it and added the horizontal formulas in the group header for crew/unit for the main report.

Other thing that I am seeing is that right now it is showing all the records under the columns together. I want that detail records for each column should be on a new page with the heading reflecting that.
For example

Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: ESU

crew/tech   Equip Not Available   Parts On Order   Timely Complete
AAA                5                    0            2

----------------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: COMM

crew/tech   Equip Not Available   Parts On Order   Timely Complete
BBB                0                    0             3
CCC                5                    0             1

------------

It should show 10 records for equip not available and then go to a new page to display 6 detail records for Timely Complete.

Another thing that is happening is that the titles for the fields are in the report header for the subreport. But sometimes the detail records go on to other pages and I want to see the field headers on every page. How can I do that for the subreport?

 

[ekta22]

I dont know what's wrong. Is there a way that I can show you my report?

 

[ekta22]

I am wondering if I need to link the subreport to those formula that counts the number of records found for Equip Not available, parts on order etc...
Do you think I should create seperate subreport for each formula and if record count > 0 for each formula then display the records else hide the subreport and move on to the next one.

 

[lbass]

Did you create the formula {@grpname} in the subreport as per my post of 5/24? You should be then inserting a group on this and formatting the group footer for "new page after".

-LB

 

[ekta22]

I didn't do it because I thot you said no grouping needed for the subreport. I didn't reallize you meant the grouping by region.
I don't know but it is still showing me more records. If it found 25 records under equip not available, it will show me about 200 records for equip not available in the subreport. The only parameters that I have is in the stored procedure which looks like below.

CREATE PROCEDURE [dbo].[pPMPERDET] 

@START_DATE----DATETIME,  
@START_TIME----DATETIME,
@END_DATE------DATETIME,
@END_TIME------DATETIME, 
@FAC_REGION----char (2), 		  
@FAC_SECTOR----char (2),
@FAC_SUBSECTOR-char,              
@AREA----------varchar (6),             
@FAC_MPS-------varchar (4),    
@WR_CREW_UNIT--char (4),         
@WR_WATCH------char (1)                  	

AS
BEGIN

SELECT 

	fields from table1, table2 and table3

FROM         ((mmssql.dbo.table1 table1 INNER JOIN
                      mmssql.dbo.table2 table2 ON table1.field1 = table2.field1)

             LEFT OUTER JOIN mmssql.dbo.table3 table3 ON 
		      table2.field1 = table3.field1)

WHERE 
[b]-- fixed selection criteria[/b]
	
	(table2.DIRECTIVE_LEVEL = 'N')
AND     
	(table2.ENTRY_TYPE = 'LPM')
AND
	(table1.VOID_STATUS <> 'V' OR table1.VOID_STATUS <> '') AND

[b]-- Parameter selection criteria[/b]

	(table2.LATEST_DATE >= @START_DATE AND table2.LATEST_DATE <= @END_DATE) AND
	(table2.WR_AREA      = @AREA		OR @AREA = '') AND
	(table2.WR_CREW_UNIT = @WR_CREW_UNIT     OR @WR_CREW_UNIT = '') AND
	(table2.WR_WATCH     = @WR_WATCH         OR @WR_WATCH = '') AND

-- FAC_REGION, SECTOR, SUBSECTOR & SMO

( 
  ( table2.FAC_REGION= @FAC_REGION AND
    ( 
      ( LEN(@FAC_SECTOR)=1 AND table2.FAC_SECTOR LIKE @FAC_SECTOR + '*' )      --search by SMO 
      OR
      ( table2.FAC_SECTOR=@FAC_SECTOR AND
        ( table2.FAC_SUBSECTOR= @FAC_SUBSECTOR OR @FAC_SUBSECTOR= '' )
      ) 
      OR @FAC_SECTOR = '' 
    )
  ) 
  OR  @FAC_REGION = ''
)  

END
GO

I did the same thing for my subreport except that I did not add any parameters that will be entered by the user but I did add the fixed selection criteria. The rest is grouping on the fields in the report. Only for interval I have grouping on formula which is below

If {pPMPERDET.INTERVAL_LENGTH} = "DD" or {pPMPERDET.INTERVAL_LENGTH}="DA" or {pPMPERDET.INTERVAL_LENGTH}="SW" or {pPMPERDET.INTERVAL_LENGTH}="SW4" then "LESS THAN WEEKLY INTERVALS" else
"FOR WEEKLY OR GREATER INTERVALS"

The rest you know what I did.

 

[lbass]

You have to add the exact same criteria to the subreport as are used in the main report, including all parameters.

I don't really know what you did. Did you create {@grpname} and group on it in the subreport?

-LB

 

[ekta22]

Yea, I did add {@grpname} and grouped it on subreport.

If I add the parameters to the subreport as well and try to run it pops up the parameter dialog box twice. Once for main report and once for subreport.

This is the view subreport

CREATE VIEW dbo.vSubPMPERDET
AS
SELECT     
fields from table1, table2 and table3

FROM         ((mmssql.dbo.table1 table1 INNER JOIN
                      mmssql.dbo.table2 table2 ON table1.field1 = table2.field1)

             LEFT OUTER JOIN mmssql.dbo.table3 table3 ON 
              table2.field1 = table3.field1)


WHERE     (table2.DIRECTIVE_LEVEL = 'N') AND (table2.ENTRY_TYPE = 'LPM') AND (table1.VOID_STATUS <> 'V' OR
                      table1.VOID_STATUS <> '')

 

[ekta22]

I found something. The records insubreport display a field called lastest_date which should be between the start_date and end_date entered by the user. Right now if I look at my subreport it shows me ALL the dates for latest_date.

(LOGCOM_PMCERT.LATEST_DATE >= @START_DATE AND LOGCOM_PMCERT.LATEST_DATE <= @END_DATE)

I didn't add this parameter to the subreport because it was popping up the input parameter dialog box twice. Is there a way to not get it twice. I am going to add it for now and enter the parameters twice to see if I get the right results. When the report is deployed the parameters will be entered by the user through web-interface. That is another topic.

Let me try this and get back to you.

Thanks