CR XI - Main Report to Sub Report links 1

[CrystalLion]

We have a CR XI report that uses a table called Fund in both the main report and the subreports. The table's primary index is FundID. Some Funds are connected to other Funds via a field called Master_FundID. So, in the Fund table the fields would be populated like this:

FundID Master_FundID
JonesMaster null
Jones1 JonesMaster
Jones2 JonesMaster

The Main report needs to pass the FundID/Master_FundID to the subreports so that it can group transactions by Master_FundID. ie- Jones1, Jones2, and JonesMaster need to be summarized.

However, when we do this, the query takes forever to execute because the transaction tables are huge and it appears to be reading the entire table for EACH evaluation of FundID. We think it's acraully evaluating 72 million times.

We know that properly constructed sql select statements is necessary, but have not been able to come up with a syntax that works.

Any help would be greatly appreciated.
Thanks

 

[lbass]

There might be a better approach if we understood more about the main report and subreport, and the function of each. Why do you think a subreport is necessary?

-LB

 

[CrystalLion]

Hi LBass

The multiple subreports are necessary because they grab data from: two transaction tables (one for gifts and one for grants) that need to be displayed in separate sections of the report, one GLYear table which contains account balances for period1-period13 for each fund which appears at the main body of the report on page 1, and another version of the GLYear table that performs a custom calculation that looks like a single field on page one.

MAIN REPORT
Allows the user to enter parameters that get passed to each of the subreports as needed. The main report selection criteria are:

(if {?Fund ID} = "All Funds" then true else {Fund.fundid} = {?Fund ID}) and
(if {?Fund Class} = "All Classes" then true else {Fund.Fund_class} = {?Fund Class}) and
(if {?Fund Type} = "All Types" then true else {Fund.fund_type} = {?Fund Type}) and
(if {?Fund Sub-Type} = "All Types" then true else {Fund.sub_type} = {?Fund Sub-Type}) and
(isnull ({Fund.date_inactive}) or {Fund.date_inactive} > {@BegYDate})

GLSUMMARY subreport parameters are:
({sql_GLYR.AcctType} startswith ["Q", "R", "X", "T"] and
{sql_GLYR.GLYear} = {?Pm-?FiscalYear} and
((if isnull ({sql_Fund.master_fundid}) then {sql_GLYR.FundId} = {?Pm-@FundID}
else {sql_Fund.master_fundid} = {?Pm-@FundID} or {sql_GLYR.FundId} = {?Pm-@FundID}) and
({sql_GLYR.AcctType} startswith ["Q", "R", "X", "T"]))

where
@FundID formula :
(if (isnull ({Fund.master_fundid}) or {Fund.master_fundid} = "")
then {Fund.fundid} else {Fund.master_fundid})

GIFT subreport uses the FundDetailHistory.trantype and tran_dates to connect to the GiftHistory table, where it picks up fields for display in the subreport. The select statement is:

{sql_FundDetailHistory.trantype} = "gi" and
{sql_FundDetailHistory.tran_date} >= {?Pm-?BeginDate} and
{sql_FundDetailHistory.tran_date} <={?Pm-?EndDate} and
{sql_Fund.fundid} = {?Pm-sql_GLYR.FundId}

GRANT Subreport is the same as the Gift Subreport, but connects to the GrantHistory table.
{sql_FundDetailHistory.trantype} = "gr" and
{sql_FundDetailHistory.tran_date} >= {?Pm-?BeginDate} and
{sql_FundDetailHistory.tran_date} <={?Pm-?EndDate} and
{sql_Fund.fundid} = {?Pm-sql_GLYR.FundId}

?

 

[lbass]

I do think the "or" in the GL Summary sub is one (or maybe the key) problem, and then it references a formula with a similar "or". I think you could speed this up by using a command as your datasource for this sub and using union statements, where each of the unioned components contains one condition like this:

Select ...
From...
where ...
Master.FundID = GLYear.FundID
union
Select ...
From...
where ... and
Master.FundId is null and
Fund.FundID = GLYear.FundId

Do you see? It's hard to be specific without knowing even more detail. You would build the parameters directly into the command and then link the parameters to the parameters of the main report.

-LB

 

[CrystalLion]

Thanks LBass - here is the actual SQL Query for the GL Summary subreport.
I am not sure how the command should be constructed, specifically regarding the
FROM area which includes the join statements. I've spaced the statement
so you can see the sections more easily.

Can you take a look at it and advise me on how to construct the command.

Am I correct to assume that you suggest I completely replace the Current subreport
datasources with one command?
Thanks - L

SELECT
"sql_GLYR"."Actual__1", "sql_GLYR"."Actual__2", "sql_GLYR"."Actual__3",
"sql_GLYR"."Actual__4", "sql_GLYR"."Actual__5", "sql_GLYR"."Actual__6",
"sql_GLYR"."Actual__7", "sql_GLYR"."Actual__8", "sql_GLYR"."Actual__9",
"sql_GLYR"."Actual__10", "sql_GLYR"."Actual__11", "sql_GLYR"."Actual__12","sql_GLYR"."Actual__13",
"sql_GLYR"."BeginActBal", "sql_GLYR"."FundId", "sql_GLYR"."GLYear",
"sql_GLYR"."sg6", "sql_GLYR"."AcctDescr", "sql_GLYR"."natural_acct",
"sql_Fund"."fundid", "sql_GLYR"."AcctType", "sql_Fund"."master_fundid"

FROM "FOUND"."sql_Fund" "sql_Fund" INNER JOIN "FOUND"."sql_GLYR" "sql_GLYR" ON "sql_Fund"."fundid"="sql_GLYR"."FundId"

WHERE
NOT ("sql_GLYR"."natural_acct"='75000' OR "sql_GLYR"."natural_acct"='77000') AND AND "sql_GLYR"."GLYear"=2007
(the year is actually a parameter that gets passed from the main report)
AND ("sql_GLYR"."AcctType" LIKE 'Q%'
OR "sql_GLYR"."AcctType" LIKE 'T%') OR "sql_GLYR"."AcctType" LIKE 'X%') OR "sql_GLYR"."AcctType" LIKE 'R%') OR "sql_GLYR"."AcctType" LIKE 'X%')


(I use the following select to collect Fiscal date info that is used by ALL funds), therefore no join statement is required.
SELECT
"sql_Syscontrol"."Fiscal_Begin", "sql_Syscontrol"."Fiscal_End",
"sql_Syscontrol"."fiscalyr", "sql_Syscontrol"."fiscalmo",
"sql_Syscontrol"."fiscalday", "sql_Syscontrol"."next_cycle"
FROM "FOUND"."sql_Syscontrol" "sql_Syscontrol"

And again, here is the Record Selection Formula
({sql_GLYR.natural_acct}) <> ["75000","77000"] and
{sql_GLYR.AcctType} startswith ["Q", "R", "X", "T"] and
({sql_GLYR.GLYear} = {?Pm-?FYStartYear} and
(if isnull ({sql_Fund.master_fundid}) then {sql_GLYR.FundId} = {?Pm-@FundID}
else {sql_Fund.master_fundid} = {?Pm-@FundID} or {sql_GLYR.FundId} = {?Pm-@FundID}) and
({sql_GLYR.AcctType} startswith ["Q", "R", "X", "T"]))

 

[lbass]

SELECT 'GLYRFundID' "Type",
"sql_GLYR"."Actual__1", "sql_GLYR"."Actual__2", "sql_GLYR"."Actual__3",
"sql_GLYR"."Actual__4", "sql_GLYR"."Actual__5", "sql_GLYR"."Actual__6",
"sql_GLYR"."Actual__7", "sql_GLYR"."Actual__8", "sql_GLYR"."Actual__9",
"sql_GLYR"."Actual__10", "sql_GLYR"."Actual__11", "sql_GLYR"."Actual__12","sql_GLYR"."Actual__13",
"sql_GLYR"."BeginActBal", [red]"sql_GLYR"."FundId"[/red], "sql_GLYR"."GLYear",
"sql_GLYR"."sg6", "sql_GLYR"."AcctDescr", "sql_GLYR"."natural_acct",
"sql_Fund"."fundid", "sql_GLYR"."AcctType"

FROM "FOUND"."sql_Fund" "sql_Fund" INNER JOIN "FOUND"."sql_GLYR" "sql_GLYR" ON "sql_Fund"."fundid"="sql_GLYR"."FundId"

WHERE
NOT ("sql_GLYR"."natural_acct"='75000' OR "sql_GLYR"."natural_acct"='77000') AND AND "sql_GLYR"."GLYear"=2007
(the year is actually a parameter that gets passed from the main report)
AND ("sql_GLYR"."AcctType" LIKE 'Q%'
OR "sql_GLYR"."AcctType" LIKE 'T%') OR "sql_GLYR"."AcctType" LIKE 'X%') OR "sql_GLYR"."AcctType" LIKE 'R%') OR "sql_GLYR"."AcctType" LIKE 'X%')

union

SELECT 'MasterFundID' "Type",
"sql_GLYR"."Actual__1", "sql_GLYR"."Actual__2", "sql_GLYR"."Actual__3",
"sql_GLYR"."Actual__4", "sql_GLYR"."Actual__5", "sql_GLYR"."Actual__6",
"sql_GLYR"."Actual__7", "sql_GLYR"."Actual__8", "sql_GLYR"."Actual__9",
"sql_GLYR"."Actual__10", "sql_GLYR"."Actual__11", "sql_GLYR"."Actual__12","sql_GLYR"."Actual__13",
"sql_GLYR"."BeginActBal", [red]"sql_Fund"."master_fundid"[/red],"sql_GLYR"."GLYear",
"sql_GLYR"."sg6", "sql_GLYR"."AcctDescr", "sql_GLYR"."natural_acct",
"sql_Fund"."fundid", "sql_GLYR"."AcctType"

FROM "FOUND"."sql_Fund" "sql_Fund" INNER JOIN "FOUND"."sql_GLYR" "sql_GLYR" ON "sql_Fund"."fundid"="sql_GLYR"."FundId"

WHERE
NOT ("sql_GLYR"."natural_acct"='75000' OR "sql_GLYR"."natural_acct"='77000') AND AND "sql_GLYR"."GLYear"=2007
(the year is actually a parameter that gets passed from the main report)
AND ("sql_GLYR"."AcctType" LIKE 'Q%'
OR "sql_GLYR"."AcctType" LIKE 'T%') OR "sql_GLYR"."AcctType" LIKE 'X%') OR "sql_GLYR"."AcctType" LIKE 'R%') OR "sql_GLYR"."AcctType" LIKE 'X%')

The union allows you to combine like fields into one field, so now GLYearFundID and MasterFundID (in red) are in one field to use for linking. You could do the same in the main report, so you can simply link {command.FundID} (from the sub) to the main report fundID.

If you have added the sql_Syscontrol table into the same subreport without linking it to other tables, this also is contributing to the speed problem. I would remove that and place it in its own subreport, maybe in the report header or footer.

-LB

 

[CrystalLion]

Thanks - I've removed the syscontrol portion. When I try to save the command I get syntax error 7587.

In your statement you use:

SELECT 'GLYRFundID' "Type",
as the first line in the first select
and
SELECT 'MasterFundID' "Type",
as the first line in the second select (after the union).

I am not sure what these lines mean, since neither of these are actual field names in the db.

Also, does line spacing matter?

 

[kskid]

Lbass is inserting a character string with a field name of "Type"

-lw

 

[lbass]

I'm not sure what the syntax is for your datasource. This is how I would do it in Oracle to create a text field with name "type" to use to identify which part of the union statement the fund ID came from, since it will now be found in only one field. In Access it would be:

select 'MasterFundID' as type

-LB

 

[kskid]

A little more clarification

With the "Type" designation, you will know whether you are working with GLYRFindID or MasterFundID record.

 

[CrystalLion]

LB - Thanks for your help. I am getting closer, I think.

I get data from the Command, however, my leftside column of drilldowns shows + but no field name (FundIDs). So, I have no way of navigating through the report. I do see the FundID in the body of the report where the GroupHeader# = FundID.

Also, the report includes detail lines for both the Master Fund AND the Sub Fund if a relationship exists. I don't want to see the SubFund information, because it's already included in the master.

Any ideas?

Thanks for all your help.
L

 

[lbass]

This is really too complex to follow. I don't even know what you mean by subfund. Note that you can change the where clause on one side of the union statement if you want to eliminate certain fields.

I don't see why the group tree would be affected by using a union statement.

-LB

 

[CrystalLion]

Thanks for your help LBass. It is too complex. I have grown to disdain this report.