Hi All,
I am trying to produce a list of sponsored projects (in the columns, using nl(columns)) and for each of the parent projects to show the subgrants and subagreements issued and some financial information for those (e.g. total obligated amount). Please see my attached sample. If I build nl(rows) under the nl(columns), I get the longest list of rows (like if award A has 2 subawards and award B has 3, then total 6 rows). A project might or might not have subgrants and subawards, but it still should show as a column. How should I go about this? nl(columns) seems to do the trick except I cannot format the $$s column. Any ideas would be much appreciated. Happy Holidays.
Milena
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Milena,
I'm looking at your example, but it doesn't have any Jet formulas, so I'm having a hard time figuring out where you're placing the NL(Rows) and NL(Columns) and how that is having a negative effect. Could you be more specific, either by attaching the report with Jet functions or telling us exactly where the functions are on the sheet?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Thank you for offering help. I am attaching a file with two worksheets: a sample and a worksheet with the formulas.The only idea I have of how to accomplish it is to have =NL("Columns=2","vPI_Award_PUBLIC_","no_") and in each column to do a =NL("table","vPI_Award_PUBLIC_",{"no_","Amount_USD"},"parent no_",N$10). I believe there is more efficient way. Note: we are using a sql database.
Thanks again. Happy Holidays.
Milena -
Jet Reports Historic Posts Hi Milena,
Actually your idea of using NL(Table) under the NL(Columns) formula is a pretty good one. You can't use NL(Rows) because it replicates an entire row for each result and that includes the other NL(Rows) formulas in the same row so then you get a multiplicative expansion where each additional group of columns has more and more rows replicators in it and the list gets huge. NL(Rows) isn't really designed to allow you to replicate separate independent groups of rows the way you want. Without NL(Table), I would say you should convert this into a grouping report and change your NL(Columns=3) into an NL(Rows=3) so that you have an outer NL(Rows=3) for your sponsored projects and then on the next row you have NL(Rows) for your subgrants and subagreements.
However, NL(Table) doesn't expand the same way NL(Rows) or NL(Columns) do. With NL(Table) only the table area itself gets expanded; it doens't copy rows or columns, so it is possible to have separate, independent NL(Tables) get expanded on the same row the way you're trying to do it. I think this is probably the only way to get that result with Jet, although I could be wrong.
Regards,
Hughes -
Jet Reports Historic Posts Thank you, Hughes. Your ideas are helpful indeed. Will make sure to share my final product.
Milena
Please sign in to leave a comment.