July 24, 2015

Adding an “As Of” Date to TARGIT Reports

Financial statements in TARGIT are often constructed with comparisons and calculations for more exact formatting. The comparisons can hold elements for current and previous years, quarters, months, or any combination of time you can think of. We have had trouble in the past with adding the actual date to these TARGIT reports, especially if we’re using the dynamic date origin to go back in time to see data before today.

While the “as of” date is visible in the client view, it is (or has been) difficult to see the same date in a report, since the dynamic date origin can’t be added to the printable page.

Two possibilities we tried and immediately rejected were using a detail band with the ‘Posting Date’ dimension (Picture 1) and a crosstab header band with a text element (Picture 2). The detail band attempted to pull every single date and locked up TARGIT with its data request. The text element sort of worked, but it was impossible to line it up to the correct column after data was fetched for the report.

Picture 1

Picture_1_-_Report_detail_band[1]

Picture 2

Picture_2_-_Header_band_text_element[1]

The next solution we tried was adding dynamic captions to the time comparison. Unfortunately, this is an “all or nothing” action: you can’t have a caption on just one column (the current or YTD column), and you can’t hide the other captions on the elements through Properties\Visibility. This is a big deal when you’re using a comparison with many elements; you end up with lots of different dates in the header, making it harder to read.

Picture 3

Picture_3_-_Comparison_before_captions[1]

Picture 4

Picture_4_-_Comparison_after_captions[1]

Finally, we started thinking independently of the crosstab with the comparison or trying to line up a text element with a crosstab column “just so.” We built a separate crosstab with the intent of hiding all of its data and essentially using it just for its column header. This column header shows a comparison with a single element, YTD, and the header uses a dynamic caption to display {posting date:last} from the Date dimension.

Picture 5

Picture_5_-_YTD_definition[1]

Picture 6

Picture_6_-_YTD_dynamic_caption[1]

Only one measure and the comparison are required to make this work. The measure number is masked under a visibility agent (hide all values), and the data row background color of the crosstab theme is changed from grey to white to make it seem more like a simple header than an empty crosstab; this also helps with the spacing in a report.

Picture 7

Picture_7_-_YTD_visibility_agent[1]

Simply add a new crosstab band to the top of any report and import this “As Of [Date]” object into it. “Fit to page width” is an excellent option to apply here, and voila! You can display today’s (or yesterday’s, or any day’s) date on the TARGIT reports easily and with no confusion.

Picture 8

Picture_8_-_Finished_product[1]

 

Learn more on how to improve your Business Intelligence solution and TARGIT reports below!

how_to_hide