Passing a list of values to a report from oracle table

[organickarma]

Hi,

I am using CR XI with an Oracle 9i backend. I am kinda new to CR so please be patient and bear with my long winding rambling explaination

This is the outline of my report.

It consists of 2 subreports A and B referencing tables TabA and TabB respectively.

The two reports are linked to the main report which basically just holds some text objects and passes 3 parameters to the subreports.

The record selection on the subreports is based on these 3 parameters.

The subreports are calculating some summaries.

The fields in the 2 tables that I use in the subreports look like this

TabA TabB

ID | Date1 | Date2 | FieldA | ID| Date1 | Date2 | FieldB |


Each subreport has 2 groups Group1 is on Date 1 and Group 2 is on Date 2

I am summarizing FieldA and FieldB at end of each Group2 for every value of Group1

and then I am picking out the group for which Date2 = Maximum(Date2,Date1)

This works fine when I select discrete values for the ID from a parameter.

If I remove the parameter for ID then it should cycle through all values but the summaries are all off and messed up

I tried creating a 3rd group on ID but no avail cos the summary seems to be calculated for all values of IDs rather than for each discrete ID groupwise. Also the number of distinct IDs in TabA and TabB are different so the grouping goes out of synch somehow.

I figured best way would be to somehow pass the IDs from an oracle view one at a time to the report and subreports and have it display the subreports for each ID on a different page.

I might be wrong with this approach and there might be an obvious answer far more simple staring me in the face but I am not able to see it.

Please help me here

Thanks

 

[lbass]

If want the results for all IDs, but still broken down by the ID group, then you wouldn't change any of your groups. It's a matter of how you set up your record selection formula which should look like the following in each subreport:

(
if {?ID} <> "All" then//assuming ID is a string
{table.ID} = {?ID} else
if {?ID} = "All" then
true
)

You would have to have "All" in your parameter list of values. This assumes that you are linking the subreport to the main report on the parameters, using the dropdown list in the subreport linking screen to link {?ID} to {?ID}, NOT {?pm-?ID}.

-LB

 

[organickarma]

I tried changing the links in the subreport linking screen to the parameter values as you suggested but this doesnt seem to be working.

It keeps reverting back from {?ID} to {pm-?ID}

Also I have 2 other parameter fields. Should I apply this to them also

 

[lbass]

Yes. This should work fine as long as the parameters have the same name in both main and subreports and if they are of the same datatype. You would use the same record selection formula in both main and subreport. I'm guessing you might not have added the selection formula to the subreportf when you had the above reverting.

-LB

 

[organickarma]

Thanks a lot,

I realized the mistake there. I was letting Crystal create the sub report parameters which were defaulting to the pm prefix. I renamed those and it works fine now(except my report hangs crystal) I might just have to rebuild the whole thing from a view or use a command object to cut down my record set size.

Just as an aside ... Do you think this function is causing a major overhead in the processing

I am using it in the group selection to pick out the group for max Date2

{Date2} = Maximum({Date2},Date1) cos it probably does not push the processing to the server

If so what could I do to push that to server

 

[lbass]

Group selection only occurs locally, so you are returning all dates to the report (not sure what your other two parameters are), and then the group selection occurs. If you used a command, you could return the most recent {table2.date} directly as a function in the command, e.g.:

Select max(`table2`.`date`) as maxdate2, `table2`.`date1`,`table2`.`ID`
From `table2`
Group by `table2`.`ID`

Of course punctuation and syntax are specific to your datasource/connectivity.

-LB

 

[organickarma]

Thanks for the help. Command is the way to go on this one