change underlying query

[Guest]

hi,

I have the following query, which is used as a graph source:

TRANSFORM
IIf(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.[SumOfCountOfLoan
Acct
#])\1=Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.[SumOfCountOfLoan
Acct
#]),Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.[SumOfCountOfLoan
Acct
#]),"#,##0"),Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.[SumOfCountOfLoan
Acct #]),"#,##0.0")) AS [AvgOfSumOfCountOfLoan Acct #]
SELECT [ResolvedStatus] & " " & IIf([line]="line2","resolved
count",IIf([line]="line5","avg # calls to be resolved","wrong")) AS liner
FROM resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w
GROUP BY [ResolvedStatus] & " " & IIf([line]="line2","resolved
count",IIf([line]="line5","avg # calls to be resolved","wrong")),
resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.line,
resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.line
PIVOT
Format(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w.ResolvedDt,'mm/dd');

I would like to know what is the best approach to changing the underlying
query of the above query at runtime. the
"resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w" is a saved
query, and this is the underlying query I am referring to. Here is the
underlying query
"resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w":

SELECT "all resolved" AS ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt,
Format(Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.[CountOfLoan Acct #]),"#,###.#") AS [SumOfCountOfLoan Acct #], "line2" AS line
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY
GROUP BY "all resolved",
tbl_CACS_activity_by_resolvedstatusandcontactcode_DPD130JULY.ResolvedDt
UNION ALL SELECT "all resolved" AS ResolvedStatus,
[1calltotals2Rjuly130_final].ResolvedDt, [1calltotals2Rjuly130_final].Expr1,
"line5" AS line
FROM 1calltotals2Rjuly130_final
GROUP BY "all resolved", [1calltotals2Rjuly130_final].ResolvedDt,
[1calltotals2Rjuly130_final].Expr1;

Basically, I would like to know what is the best way to add a condition, or
WHERE clause to the "SELECT "all resolved" AS ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode..." part.

Thanks in advance,
geebee

 

[pietlinden]

just wondering. What happens if you pass a filter to the report when
you open it?

 

[Guest]

hi,

I am not so sure that will work. When I run the report containing the
graph, it returns nothing, so then I go into the design view, and the source
of the graph is indeed:

TRANSFORM
IIf(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.[SumOfCountOfLoan
Acct
#])\1=Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.[SumOfCountOfLoan
Acct
#]),Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.[SumOfCountOfLoan
Acct
#]),'#,##0'),Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.[SumOfCountOfLoan
Acct #]),'#,##0.0')) AS [AvgOfSumOfCountOfLoan Acct #]
SELECT [ResolvedStatus] & ' ' & IIf([line]='line2','resolved
count',IIf([line]='line5','avg # calls to be resolved','wrong')) AS liner
FROM resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp AS
resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706w
GROUP BY [ResolvedStatus] & ' ' & IIf([line]='line2','resolved
count',IIf([line]='line5','avg # calls to be resolved','wrong')),
resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.line,
resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.line
PIVOT
Format(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp.ResolvedDt,'mm/dd');

which is right, but the query design view is showing nothing for the listed
"resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706wtemp" query
because that is not in fact a saved query.

I received your other message, but I am not so sure what to do with your
suggestion. Could you please help further? I think I am going in the right
directoin, but just need a little bit more bottle feeding.

Thanks in advance,
geebee