Linking Tables of Different Structure 2

[bmacdo]

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

I have an existing report that counts attendance in our facilities for each day within a parameter date range (up to a month) by funding source, facility, division (not shown) etc. and averages by day:

Oct. 1 2 3 4 5 […] Average
Fac1
Source1 5 5 5 6 5 5
Source2 10 10 10 10 10 10
Fac1Total 15 15 15 16 15 15

The existing report is grouped by Division, Facility, Funding Source, and Client_ID. It’s working fine.

I have been asked to add a budget figure to each of the lines above & to calculate the variance. The budget figures are daily goals by month.

The change should result in the report looking like this:

Oct. 1 2 3 4 5 [….] Average Budget Var.
Fac1
Source1 5 5 5 6 5 5 6 (1)
Source2 10 10 10 10 10 10 10 0
Fac1Totl 15 15 15 16 15 15 16 (1)

My difficulty is that the budget figures are in a table not currently used in the report and that table is structured in such a way as I don’t know how to link to it. It looks something like this below, where “Fund_Type_x” holds the same values as funding source (Source1, Source2, etc.) in the existing report above:

Field Value
Facility (key) Fac1
Fund_Type_1 Source1
Fund_Type_2 Source2
Oct_1 6
Oct_2 10

Oct_1, Oct_2, Oct_3 etc. don’t refer to the days in October, but the funding source budget goals in October per facility, up to six. For each month in a year, there are six fields of “Fund_Type” and six fields of related budget numbers, although there may be only one or two funding sources/budget numbers in use for a given facility. The funding source names can vary in place between facilities, e.g. the same “Source1” funding type could appear in Fund_Type_1 in one facility and Fund_Type_2 in another facility.

The funding source detail currently used in the report is a single field with values that vary by row. The funding source detail in the to-be-added table can be in one of six fields. I’m thinking I have to create six left outer joins to the same new table to test for matching values of funding source, haven't had success getting that far and am uncertain what I'd do next if I figured that much out.

Any help will of course be appreciated and thank you for reading this far.

Brad Macdonald

 

[IanWaterman]

Sounds like you need to create a view of your budget data and put it into a structure that you can easily link to your existing report.

Failing that you may have to build a subreport which calculates budget on fly. SR would have to execute for each Source.

Ian

 

[bmacdo]

I don't have the means to create views, alas.

 

[bmacdo]

Wouldn't you like to elaborate about how I might create a subreport at the funding group level of the main report?

Brad

 

[IanWaterman]

YOu know your data structure better than me.

Build a subreport based on your budget table, and design so that it brings back budget figure for facility and Source as required.

YOu then link SR from main report by facility and source. If you want to pass data from the SR to main report you will need to use a Shared Variable

Ian

 

[bmacdo]

I'm drawing a blank as to how to populate a single formula field unconditionally with values from various database fields ... which is what I believe is required.

 

[lbass]

You could create a command using the budget table where you use a union all to combine fields, as in:

select budget.`facility`, budget.`source1` as FundingSource, budget.`Oct_1` as BudgetVal
union all
select budget.`facility`, budget.`source2` as FundingSource, budget.`Oct_2` as BudgetVal
union all
select budget.`facility`, budget.`source3` as FundingSource, budget.`Oct_3` as BudgetVal
//etc.

Then you should be able to link {command.facility} and {command.FundingSource} to your main report table and then just add the corresponding {command.BudgetVal} to the report. I'm not sure how many unions a command will accept, though, and it looks like you will need 72, six per month.

-LB

 

[bmacdo]

I will give that a try. You're very kind!

Brad