Hello,

I'm trying to create a Year-Over-Year report for Year-To-Date data. The .Y data item correctly pulls in the the current YTD data but there does not seem to be a corresponding data item for the previous year's cummulative YTD data. I would have expected to see a .LY data item.

Am I correct in concluding that I can't do this or am I just missing something obvious? Is there some work around? Is there a way to define new data types?

Thank you.

Richard

Hi Richard,

Are you using Adagio Ledger or FX? And if Ledger, is it version 90A or an earlier version?

If Ledger 90A, use the code {n}Y which would be the YTD changes for n years ago. Thus {1}Y would be YTD last year, {2}Y would be YTD 2 years ago, and so on.

If you are using Adagio FX or an earlier version of Ledger, LYY is the YTD last year code. LY gives the full previous year (not YTD).

If you are not sure what particular field results will give, a sample specification GL!SuperSpec90 (or AF!SuperSpec for Adagio FX) shows a list of all available fields and their outputted values and fiscal date ranges, based on an account and fiscal period you select. This sample form is a useful tool.

Hi Richard,

".LY" exists in both products to return the entire prior year number. ".LYY" returns Last Year YTD figures.

Thanks very much. .LYY is what I was looking for. I did not discover it before because I was only looking in the data types under "Prv. Yr. Net Change In Balance". Didn't occur to me to look in "Relative Change In Balance".

Thanks very much.

Richard

I have a report which contains lines with all zero values that refuse to go away even though I have zero suppression turned on.

I have a test line that I use to observe the behavior and have verified that I know how to toggle the behavior successfully.

I have an account group containing a single range line referencing a single account. A cell G33 in that line contains "F33/F$50" which returns a zero value because F33 contains zero.

Cell G34 lies in the Total line, contains "=SUM(G33)", and, of course, returns a zero value.

In my test line, if I enter the text "=SUM(G33)" (contents of cell G34) into a cell, I get the proper zero suppression behavior. But! If I copy the contents cell C34 ("=SUM(G33)") and paste it into my test line, zero suppression ceases to work.

If I copy the contents of cell G33 ("F33/F$50") zero suppression appears to work properly.

So, there appears to be something fishy going on in the case that I copy the contents of a cell that resides in a Total line.

This is actually a big issue because I frequently create new reports by copying and pasting entire columns. Doing this one time essentially turns off zero suppression for the entire document which I then have to laboriously hand edit.

Is this a know bug? Is there a workaround or am I just doing something wrong.

Thanks again.

Richard

Hello Richard,

If you highlight the total line and right mouse button on the Row number, what is the setting of the Zero suppress option on the pop-up menu?

Also, if you actually print (or print preview) the statement, is the zero suppression working (sometimes the design view shows rows that will be suppressed when the statement is printed).

Hi Andrew,

Thanks again. I did not realize that the setting could be overridden at the cell level. Zero suppression was off for the cell that was not working. So, I selected my entire sheet, right clicked on the mouse and set zero suppression. I think it is working now.

Sorry, I made this request as a reply when I intended a new post and then re-submitted as a new post thinking you would not see the reply.

Thanks again for your prompt response.

Richard

Andrew,

I've got a hand entered cash flow line in my report that sums the net income number (Cell D368) with a depreciation number (Cell D339) contained in a generated line. Trouble is, when I collapse and re-expand, adagio resets the sum to add D368 + D350 which is meaningless.

I tried D$339 but had no better luck.

Can you tell me what is going on? I'm still struggling a bit with understanding the behavior of collapsed vs. expanded mode. I'd also like to "hide when printed" certain generated lines but adagio does not appear to remember the setting across a cycle of collapsing and expanding.

Thanks much.

Richard

Generally, you want to be able to be able to prevent a total row from being suppressed, even if the total is zero. You have control of this on a row-by-row basis.

Remember, with the financial reporter, "if you need it, it is there" (although it might be tough to find!).

Just ask here and we'll point you in the right direction. You might also learn a few tips and tricks by reviewing the recorded webinar on the financial reporter.

Hi Richard,

When you expand a SmartSheet, this creates rows based on the formulas and formatting of the original row that has .Range in column A.

If you collapse a SmartSheet, this deletes all of the generated rows, not just hiding the rows.

Because of this, you don't want to be making adjustments to cells in generated rows, because as soon as you refresh (or collapsed and re-expand), those changes will be gone. Or basing formulas on cell references to generated cells, because it will get changed the next time you collapse the sheet.

It will be tricky to attempt to add the amount of a specific cell that is found in a generated row with another cell that is not generated. This has to do with cell-referencing and that the depreciation amount is not guaranteed to always be in the same row (depending on zero-suppressed rows or if you add new accounts). You would be better off having the depreciation account in a separate row of the report, not getting generated and thus not subject to the whims of an unexpected cell-reference or being deleted.

To hide particular Generated rows, you cannot use the 'Hide when Printed' format, but you could potentially hide the rows. This would involve using the 'RowFormat' function to conditionally hide a generated row, based on criteria you can define in an IF formula. This is done by putting .RowFormat in the top-right cell, and a formula such as the one's below in the the right-most column, in a row with .Range in column A.

=IF($B10 = "1000" , "FormatHidden" , "")
=IF($D336 >= 5000 , "FormatHidden" , "")

Ok. I'm making progress.

I tried: =IF(1, "FormatHidden", "FormatHidden") to try to force hiding in all conditions. All that happens is columns A,B&C show blank and the rest of the columns are unaffected.

Even if I could get the entire line to show blank would I not simply see a lot of blank lines on my printed report?

Is the "FormatHidden" style supposed to suppress printing of the line? I can't find in the manual where it describes what this style specifically means.

Thanks again.

Richard

Hi Richard,

'FormatHidden' is one of the pre-defined Styles that you can see from the Format menu and 'Styles'. Essentially, this is pre-defined cell formatting. This hides the row, not just blanking it out. Not only from printing but also viewing.

The 'IF' formulas I used as examples in the prior post typically require some sort of condition in conjuction with 'FormatHidden'. When you hid everything, that could have confused things - if everything is hidden, you won't see anything. You could try a different format such as 'HighlightRed' to get a more visual feel of what is happening.

If you are trying out this 'FormatHidden' formatting, I recommend doing it first without '.RowFormat' in the top-right cell. This allows you to see which rows will be hidden (and which won't be) if .RowFormat was present.

The Help file within the Financial Reporter may give more information that the manual might. In the 'Advanced Report Features' section of the help file (on the home page), there is a link for Row Formatting, which describes this function.

I see what you mean by removing the .RowFormat function and when I use "HighlightRed", the behavior is exactly as I would expect. Without .RowFormat, I see "HighlightRed" in the far right column of every generated row.

I'm using =IF(1, "HighlightRed", "") which I assume logically asks the question "If any cell in this row has a non-zero value the apply the "HighlightRed" style.

Now when I change "HighlightRed" to "FormatHidden": If I remove .RowFormat, I do see "FormatHidden" in the far right column of every generated row as expected. But, with .RowFormat, the strange thing is that the rows are not hidden at all. What happens is that columns A,B&C are blanked out while the rest of the columns remain unchanged. "HighlightRed" applies to All columns in the row but "FormatHidden" seems to only apply to columns A,B&C and would be more aptly called "FormatBlank".

Does this make sense?

Thanks.

Richard

Hi Richard,

=IF(1, "HighlightRed", "") will put "HighlightRed" in the cell regardless of anytghing else, since "1" is always TRUE. If you want to check whether a group of cells are equal to zero, then test =IF((A1+B1+c1+d1)=0,"HighlightRed","") - add the cells up and see if the result is still zero.

I think that we're missing something simple here. If you ZIP up your data and send it to me in an e-mail with the name of the statement that's giving you a problem, then I can take a look and see what it might be. Once a row gets the attribute "FormatHidden", it can be hard to remove and may cause unexpected results. Zero suppress is normally the best way to proceed.

Also, was this statement started by dragging and dropping account ranges from the group tree, conversion of a DOS specification, editing a sample specification or just starting from scratch and typing formulas.

Hi Andrew,

Just emailed a zipped file to techsupport@softrak.com with your name in the subject line.

Thanks again.

Richard

If you change formatting on the cell, this overrides any conditional RowFormat formatting of the same 'type'. For example, if you specifically set a cell to have blue font, this will overrride any .RowFormat format that would change the font colour, but cell background colour or bold/italic font would still work.

FormatHidden is essentially setting the row height to zero. What probably has happened is that you have specifically set the row height and this is overriding the FormatHidden. It's possible that the original testing by setting all rows to FormatHidden has caused the scenario of giving unexpected results now.

Andrew is right that using the ZeroSuppress button in the toolbar is a better (and easier) way to simply hide accounts that have all zero amounts.

There is a bit of confusion here. Possibly I should have started a new post. In this particular case, I want to suppress all the generated rows for sales items (regardless of whether or not they have zero values) but I want to print all the generated rows for expense items. Essentially, I'm trying to simulate a partial expansion of expense account groups only.

If all you want is to not 'expand' the sales, first collaspe the form. Then delete .Range from column A for the Sales line, but not the row for Expenses. Thus when you expand the SmartSheet, the sales will remain summarized, but the Expenses will show each account on separate rows.

Oh! Perfect! That was easy. Thanks so much.

Richard