Noting Budget Figure Where There Is No Matching Detail 1

[bmacdo]

Reporting with Crystal v.10, off MS-SQL Server data...

I have a report that for days in a prompted range prints counts of clients in our schools by funding source. Three columns on the right of the report average the daily counts, print the target per funding source and the variance between the average daily total and budgeted amount. The counts are then subtotaled by School, and School Type (not shown).


Dec 1 2 3 4 5 6 […] 31 Avg. Budget Variance
Funder A 9 9 9 9 9 9 9 9.00 9.59 (0.59)
Funder B 50 50 50 50 50 50 50 50.03 47.96 2.07
-----------------------------------
School A 59 59 59 59 59 59 59 50.19 57.55 (0.36)

The report is grouped Client_Master_View.Client_ID (hidden detail section), Client_Funding.Funding_Source, Client_Master.School and Client_Master.School Type. Getting the budget figure requires going to a referential budget table linked to the client master by school and finding the budgeted amount based on month of the year and matching funding source.

My difficulty is that in some cases population has been budgeted for a funding source where there are no clients in attendance. So, no row prints and there is no indication as there should be of that budgeted amount in the report. I can’t see how to add the budget table into the groupings and counts, as it does not contain client data and a good portion of the budgets have not been entered where there are in fact named funding sources in the Client_Funding source table.

Hope I’ve explained this coherently and thanks in advance for your thoughts…

Brad

 

[lbass]

Since you have both missing budget info and missing client data, I think you would have to report this in two steps.
I believe your groups are as follows: Grp#1 = school type, Grp#2 = school, Grp#3 = funding source, Grp#4 = Client (I think you were showing the group footer order which is in reverse?) Anyway, if I'm correct, then first, use a subreport for the budget that is linked on school and funding source (and type, if a school can appear in more than one type), and place the subreport in the funding source group section.

Then add a second subreport in the report footer that references the budget table, and is not linked to the main report, but which displays the monthly budgets where there are no client records. How to do this depends upon what fields you have available in the budget table and how they relate to the client table.

-LB

 

[bmacdo]

Thanks, LBass!

I did list the grouping from last to first, 4-1. And a school can only be of one school type.

The db link from the budget table to the client_funding table is obscure to me, as the respective funding source in the budget table can be in one of six fields w/in a given month. The particular funding source can be in one of these only, but which it is can vary from school to school. The following is the December portion of the budget formula I am currently using:

whileprintingrecords;
numbervar FundingBudgetValue;
[…]
If (not isnull({Foo_Budget_File.Funding_Type_1}) and
{Foo_Client_Funding.Funding_Source} = {Foo_Budget_File.Funding_Type_1})
and {@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_1_Amount} else

If (not isnull({Foo_Budget_File.Funding_Type_2}) and
{Foo_Client_Funding.Funding_Source} = {Foo_Budget_File.Funding_Type_2})
and {@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_2_Amount} else
[…]
If (not isnull({Foo_Budget_File.Funding_Type_6}) and
{Foo_Client_Funding.Funding_Source} = {Foo_Budget_File.Funding_Type_6})
and {@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_6_Amount}

Funding sourse “Acme” could appear in Type 1 for one school and Type 5 for another in the budget file, but I can link to the Client Funding table by school.

Thanks again for your consideration, very much.

 

[lbass]

Can you please clarify whether you are successfully getting the budget amount into the report where there IS client data? In other words, is your only problem how to include budgeted amounts where there is no client data?

Is {Foo_Client_Funding.Funding_Source} null when there is a budgeted amount but no client data? Could you show some sample detail level data of how the funding source fields from both tables look when there is and isn't data?

You might be able to use a formula like this to get the missing budget amounts to display:

If (not isnull({Foo_Budget_File.Funding_Type_1}) and
isnull({Foo_Client_Funding.Funding_Source}) and
{@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_1_Amount} else
//etc.

-LB

 

[bmacdo]

That's correct ... I am only trying to get the budget figure noted where there is no matching client data. My budget formula does seem to work fine matching the budget figure to the Client_Master + Client_Funding summary rows, except in cases where there is a figure for a funding source in the budget table, but no client records matching with that funding source.

And only records with Client_Funding.Funding_Source values are being counted, as according to spec.

Thanks again for your help.

Brad

 

[lbass]

You answered my first question, but not the next. Did you try my last idea?

-LB

 

[bmacdo]

Sorry, got distracted with another task on Friday.

Am trying to get a handle on trying your suggestion as the Client_Funding.Funding_Source is one of report groups and the selection criteria includes Client_Funding.Primary_Funding flag = Yes (nonprimary funding sources are ignored) and also it also selects certain Client_Master_View.School_Types, all of which would be missing in the case where there was a budgetted amount in the budget file, but no client rows ...

Brad

 

[lbass]

The report groups don't matter. I meant to say that you might be able to use a formula that tests for a null client field in a subreport in the report footer, where you start with the budget table, and have a left join TO the client funding table--with no selection criteria on the client funding table (in order to maintain the left join).

-LB

 

[bmacdo]

Joining each of the six Budget_File Funding_Type possibilities to the one Client_Funding.Funding_Source field?

Brad

 

[lbass]

You would link it on the school field only, and then add a formula like your original one, except checking for a null field:

If (not isnull({Foo_Budget_File.Funding_Type_1}) and
isnull({Foo_Client_Funding.Funding_Source}) and
{@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_1_Amount} else
else
If (not isnull({Foo_Budget_File.Funding_Type_2}) and
isnull({Foo_Client_Funding.Funding_Source}) and {@DispBudgPopMo01} = 12 then
FundingBudgetValue := {Foo_Budget_File.Dec_2_Amount} else
//etc.

-LB