Formula Help 1

[bv0719]

I need to create a report that will display the budget amounts for each month per geneeral ledger #, per department. Placing the months across the top of the report and the GL #'s on the side.

The challenge is the budget amounts are not stored in a file, instead they are somehow attached to an index number. The "index" number relates to the month of the year, i.e. 1-12. So, if I run a report and select this year, index 8, GL #'s 600.00 - 699.99, and department .21 (admin) I'll see what that departments individual budget amounts are for August.

It seems that I need to setup a shared variable to make this work but haven't yet been successful. Is there a formula I could use to set this up within 1 report so sub-reports would NOT be neccessary?

any help appreciated.

Thanks,
Bill V

 

[lbass]

What does your data look like if you place the GL#, the dept, and each of the 12 index fields in the detail section?

-LB

 

[bv0719]

Well, There's only one 1 index field. I know that Index 1 = January and so on. So, I can run a report for each month and all the data will display fine or even for the whole year, but it all displays in one column.

Trying to get a report to have the 12 months across the top and each months amount below. i.e.

dept jan feb mar apr may
600.02 10.00 9.00 10.00 8.00 6.00
601.02 100.00 94.00 98.00 100.00 94.00
603.02 5.00 8.00 5.00 8.00 5.00

The amounts again are based on the index numbers. In the example above indexes would be 1,2,3,4,5.

I can't seem to use a shared variable because the amounts display in the details section and are not calculated. I have another report that uses shared variables but they're calculated and work fine.

Does this help make some sense?

Thanks,

Bill V

 

[jdemmi]

That looks like you should be using a cross-tab report.

-- Jason
"It's Just Ones and Zeros

 

[bv0719]

hmm... good idea. I'll try that.

Thanks,
Bill v

 

[lbass]

Your description was confusing to me when you said the budget numbers were not stored in a file, but instead were attached to an index. If there is only one index field, then yes, try a crosstab, where you use index as the column, department, as your row field, and budget as your summary field. To get the monthname to display, after adding {Table.index} as your column field, select "group options"->customize the group name->use a formula to customize the group name->x+2 and enter:

monthname({table.index}) //assuming index is a number datatype

-LB

 

[bv0719]

LB,

Thanks again. The Cross-Tab worked great. Always helpful getting some "fresh eyes" to se through the problem. I use cross tabs on other reports of mine but couldn't see that this was the solution. Most appreciated.

Bill V