Hi,
I have a request to show the Time level (month, quarter, half-year, year) on a custom report for all measures, loaded and user-entered.

Is this information in PASS1 or do I need to get it from another table?
If so, what table(s) and field(s)?

Also, Time is the second dimension of the User-Entered measures, if this makes any difference.

Thanks.
Greg

Not clear what you're trying to do... and most importantly why. If you just do an analysis designer report you can run it for any time level.

Is this a type of admin report you're needing to create so the admin can know the linking level of all measures? If so the existing Measure admin reports show this info so you don't need to create a report.

Is this report searching for measure values that needed to be deleted because full refresh wasn't run after changing the linking level for a measure?

Or is this for another purpose?

thanks

Thanks for the questions, Bob.

This is a very specific report design:
By perspective, by objective by measure displaying YTD actual, current target, next year target, and next year+1 target.
They also want the Frequency of the time for the load of the measure, to be displayed, along with objective owner and measure owner.

I found all the information except Time frequency.

Thanks.

You will find that information in the ldsched table, which joins to the measure types using the measure ID as key. You'll have to join that to the hold result of PASS1. The information about loads is not sent into the PASS1 hold; it is after all administrative data, not facts from the fact table.

Hope this helps.

Thanks for the information, Bob.

I don't see ldsched in the database. Do I need to create this table?
Also, I need this information for both user-entered and loaded measures. Does this table hold info for both types?

Thanks.

ldsched is a cluster join that joins the reportcaster tables with pmf's schedule management tables.

No, by definition user entered measures aren't loaded or scheduled... there is no load frequency for a user entered measure... each record is created by user entry - typing its formula values in and calculating a result.


I feel we're not being clear what we need. What information specifically does your customer want to see?

* The level linked in Time
* The schedule frequency of the load
* When each measure record was created?

You can find out the time linkage level from the pm_meas_src table; you can find out the scheduled time for measure loads from ldsched; you can find out the date the record was created from the measures table, where there is a creation date field on each measure record in the system, but that's not load frequency or time level.

From your questions I'm understanding this better.
The customer wants to see at what level of Time the measure is loaded at.
Let's just talk about user-enterd for the moment.
The challenge is getting the user-entered Time dimension level out of PMF when it's the second dimension on the user-entered data entry screen. I can't figure out where this Time dimension info is stored.

Thanks.

I think the best way on this one is just to give you the code to use. You'll be able to understand the concepts from it.
The User Entered and Loadable Measures have the exact same high level metadata. It's only the values at the Measure level that are stored in separate places. That's all about to change, in 5.3.2.

You can only run the below if you're logged in to PMF. An OWNER_ID must be set. Note that we follow coding standards here:

• Language soft titles on the report using PMF's translation interface.
  
• Using PMF standard INCLUDE code for language translation, styling, and system defaults.
  

But there's no error handling in this example. It's unlikely this report will have errors, but error handling is always a good idea.

-INCLUDE A_DEFAULTS
-INCLUDE &PMF_LANGUAGE._PMF_RPT_STD_TITLES
-INCLUDE &PMF_LANGUAGE._PMF_RPT_COLTITLES_META
-INCLUDE &PMF_LANGUAGE._PMFCOMMON
-INCLUDE PMF_LANG_MULTIDRILLS

JOIN FILE PM_MEAS_SRC_PROPERTIES_VW AT DIM01_AGG_LVL   TAG LEFT TO UNIQUE
     FILE PM_DIM_DEF_LVL            AT DIMENSION_LEVEL TAG RIGHT    AS J1
     WHERE DIM01_AGG_LVL EQ EDIT(DIMENSION_LEVEL) ;
END
-RUN

TABLE FILE PM_MEAS_SRC_PROPERTIES_VW
   PRINT
      MEASURE_SERIES_NAME
      RECORD_TYPE
      MEASURE_AGGREGATE_TYPE
      FIXED_TARGET_FLAG
      THRESHOLD_FLEX_DIRECTION
      THRESHOLD_FLEX_TYPE
      UNIT_SYMBOL
      DIM01_AGG_LVL
      DIMENSION_LEVEL_NAME      
   BY SCORECARD_NAME
   BY PERSPECTIVE_NAME
   BY OBJECTIVE_NAME
   BY MEASURE_SERIES_NAME NOPRINT
   WHERE (DIMENSION_TYPE EQ 'T') ;
   WHERE SCORECARD_ID NE MISSING;
ON TABLE HOLD AS PASS1_MEASURES_DATA
END
-RUN

DEFINE FILE PASS1_MEASURES_DATA
-INCLUDE PMF_PM_MEAS_SRC_DEFINE01
END
-RUN
TABLE FILE PASS1_MEASURES_DATA
PRINT 
      MEASURE_SERIES_NAME AS 'Measure'
      DISP_RECORD_TYPE_SH              AS &RPTCOL_TYPE.QUOTEDSTRING
-*      DISP_MEASURE_AGGRREGATE_TYPE_SH  AS &RPTCOL_AGGAB.QUOTEDSTRING
-*      DISP_THRE_DIRE_SH                AS &RPTCOL_DRCTN.QUOTEDSTRING
-*      UNIT_SYMBOL                      AS &RPTCOL_SYMBL.QUOTEDSTRING
      DIMENSION_LEVEL_NAME             AS &RPTCOL_DIMLVLNAM.QUOTEDSTRING
   BY SCORECARD_NAME                   AS &MISC_OBJ_SC.QUOTEDSTRING
   BY PERSPECTIVE_NAME                 AS &MISC_OBJ_PE.QUOTEDSTRING
   BY OBJECTIVE_NAME                   AS &MISC_OBJ_OB.QUOTEDSTRING
ON TABLE SET HTMLCSS ON
ON TABLE SET LINES 9999
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
-INCLUDE A_STYLE01
END

Thank you Bob.
I look forward to working with this code!

Is there a specific tab I should be running this from?
I ran this as Saved report on the Analytics tab and in Conten\Preview and got the following error:
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: PM_DIM_DEF_LVL

I passed a parameter of &OWNER_ID='pmfadmin'.

What did I miss?
Thanks.

Set it up as a Content item. Run from any category except saved - That's for

You don't need to set OWNER ID. It's set on login. Don't set it.

I coded this in 5.3 in the lab. The table was renamed between 5.2 and 5.3. In 5.2 it's called DIMENSIONS_SOURCE.

Hope that helps.

Thanks, Bob, I really appreciate your help.
I made the changes you suggested.

I got another error:
(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE: DIM01_AGG_LVL

Thanks.

Try DIM01_AGGRGTN_LVL. One of our programmers is a mad for renaming things release to release.

B

That worked, Bob.
Thanks!