Topic
  • 6 replies
  • Latest Post - ‏2012-02-28T09:42:29Z by SystemAdmin
igorM
igorM
302 Posts

Pinned topic Report Studio: How to concatenate two strings - date2 and char?

‏2012-02-20T07:55:08Z |
Hi,
using Cognos BI v8.4.1 Report Studio accessing TM1 v9.5.2 cube as data source. In cube I have a member 'FAKT 2012-02-20' which is actually current date plus prefix. In Report Studio I would like to generate exactly the same string which must become a member in report.

In Report Studio in crosstab Columns I added "Query Expression" formula: current_date and it displays 2012-02-20 which is ISO representation of date (See attachment). But I need to add "FAKT " as a prefix, so the result should be "FAKT 2012-02-20" and this kind of generated string should become a valid cube member. How to generate/concatenate two strings "FAKT" which is char data type and current_date which is date2.
Regards,
IgorM
Updated on 2012-02-28T09:42:29Z at 2012-02-28T09:42:29Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-20T10:08:42Z  
    current_date is a relational function. There is no equivalent with OLAP sources and using this function will cause the query to use local processing.

    Also, the result of the current_date function does not have any relationship to the actual data in your cube so it is difficult to see how you would want to use this as a "member" in your query to the TM1 data source.

    If this date and the word "FAKT" are only supposed to be labels in the crosstab (without actual association to data) then you could use a crosstab space. Once you have added the crosstab space to the layout you can unlock the layout (the padlock icon on the Report Studio toolbar) and insert a layout calculation into the crosstab space. The layout calculation can be used to display the current date. You could add a second text item to display the word "FAKT" next to the layout calculation. Another option is to use the casting functions in the layout functions to convert the date to a string and then you could concatenate this in one expression to "FAKT " but, since you can do this more easily with two separate text items, there isn't much point to the cast from date to string.

    In any case, it seems like there is a more fundamental issue that you are trying to address. If would be much more useful if you could explain exactly what you are trying to accomplish with the calculations and how it is meant to work with your existing TM1 data.
  • igorM
    igorM
    302 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-22T11:53:30Z  
    current_date is a relational function. There is no equivalent with OLAP sources and using this function will cause the query to use local processing.

    Also, the result of the current_date function does not have any relationship to the actual data in your cube so it is difficult to see how you would want to use this as a "member" in your query to the TM1 data source.

    If this date and the word "FAKT" are only supposed to be labels in the crosstab (without actual association to data) then you could use a crosstab space. Once you have added the crosstab space to the layout you can unlock the layout (the padlock icon on the Report Studio toolbar) and insert a layout calculation into the crosstab space. The layout calculation can be used to display the current date. You could add a second text item to display the word "FAKT" next to the layout calculation. Another option is to use the casting functions in the layout functions to convert the date to a string and then you could concatenate this in one expression to "FAKT " but, since you can do this more easily with two separate text items, there isn't much point to the cast from date to string.

    In any case, it seems like there is a more fundamental issue that you are trying to address. If would be much more useful if you could explain exactly what you are trying to accomplish with the calculations and how it is meant to work with your existing TM1 data.
    Hi,
    I would like to create a report that shows current date info from cube time dimension. So I though to get current date info from current_date relational function, add prefix FAKT and so create exactly the member like it is in cube: "FACT 2012-02-20". Then use parent multidimensional function to get current mount out of current date member. Then run a report against cube with current month info.

    I know I can create a current month member in TM1 cube, but I don't want to mess cube's time dimension with some dummy member like "current month". So instead I would just like to create "current month" member in Report Studio report. Any idea how?
    Regards,
    IgorM
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-23T06:29:19Z  
    • igorM
    • ‏2012-02-22T11:53:30Z
    Hi,
    I would like to create a report that shows current date info from cube time dimension. So I though to get current date info from current_date relational function, add prefix FAKT and so create exactly the member like it is in cube: "FACT 2012-02-20". Then use parent multidimensional function to get current mount out of current date member. Then run a report against cube with current month info.

    I know I can create a current month member in TM1 cube, but I don't want to mess cube's time dimension with some dummy member like "current month". So instead I would just like to create "current month" member in Report Studio report. Any idea how?
    Regards,
    IgorM
    If I understand you correctly, you don't actually want to create a new member. In actual fact, you are trying to reference an existing member from your TM1 cube. If this is correct then you can use macro functions to construct the member unique name of your existing member. You will then be able to use this as a member reference in subsequent expressions.

    I would recommend that you look at the following document for an introduction on creating member unique names using macro expressions. This will give you some ideas regarding the use of these concepts. However, you will not be using the prompt macro function for your report expression.

    http://www.ibm.com/developerworks/data/library/cognos/page232.html

    In your case you would be constructing the member unique name based on the current date. The trick here is that you should not be using the relational current_date function because this will disconnect the query from the OLAP processing and force the query to use local processing. To avoid this scenario you can use the $current_timestamp and timestampmask macro functions. The macro functions are processed before generating the query MDX/SQL so you will be able to avoid the local processing from relational functions. You will use these functions to create the member unique name to match the MUN of the member from your time dimension.

    http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.1.doc/ug_cr_rptstd_i_macro_functions.html
  • igorM
    igorM
    302 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-27T13:14:38Z  
    If I understand you correctly, you don't actually want to create a new member. In actual fact, you are trying to reference an existing member from your TM1 cube. If this is correct then you can use macro functions to construct the member unique name of your existing member. You will then be able to use this as a member reference in subsequent expressions.

    I would recommend that you look at the following document for an introduction on creating member unique names using macro expressions. This will give you some ideas regarding the use of these concepts. However, you will not be using the prompt macro function for your report expression.

    http://www.ibm.com/developerworks/data/library/cognos/page232.html

    In your case you would be constructing the member unique name based on the current date. The trick here is that you should not be using the relational current_date function because this will disconnect the query from the OLAP processing and force the query to use local processing. To avoid this scenario you can use the $current_timestamp and timestampmask macro functions. The macro functions are processed before generating the query MDX/SQL so you will be able to avoid the local processing from relational functions. You will use these functions to create the member unique name to match the MUN of the member from your time dimension.

    http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cr_rptstd.8.4.1.doc/ug_cr_rptstd_i_macro_functions.html
    Phil.W,
    thanks a lot for help. I have tried using macros and it is working fine. But now I have found out that in our cube can happen that "current date" member does NOT exists in cube every day. So I need to use a current month member instead.

    To get a current year member "FAKT 2012" I have written formula:
    
    [my_cube].[time_dimension].[time_hierarchy]->#
    '[FAKT ' + timestampMask ( $current_timestamp, 
    'yyyy' ) + 
    ']'#
    


    Above formula works fine and when I execute report, report is working fine (displaying correct values).

    But I need to create a month data member which in our case is like "FAKT JAN 2012" for January 2012, "FAKT FEB 2012" for February 2012 etc. So I have written formula:

    
    [my_cube].[time_dimension].[time_hierarchy]->#
    '[FAKT ' + timestampMask ( $current_timestamp, 
    'mm' ) + 
    ' ' + timestampMask ( $current_timestamp, 
    'yyyy' ) + 
    ']'#
    


    I get error message: "FAKT 02 2012" is not a proper name. Sure error is logical in our case there should be FEB instead of 02. So I need to replace 01 with JAN, 02 with FEB, 03 with MAR, ... , 12 with DEC. So I have tried to write (only for February):
    
    [(sl) TERJATVE].[TERJATVE_CAS_FAKTURE].[TERJATVE_CAS_FAKTURE]->#
    '[FAKT ' + 
    
    case when timestampMask ( $current_timestamp, 
    'mm' ) = 
    '02' then 
    'FEB' end + 
    ' ' + timestampMask ( $current_timestamp, 
    'yyyy' ) + 
    ']'#
    


    But getting error: "Parsing error before or near position:<number>". The <number> is exactly at first letter of timestampMask function.

    How can month numbers be replaced with month name?

    Regards,
    IgorM
  • igorM
    igorM
    302 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-28T06:30:51Z  
    • igorM
    • ‏2012-02-27T13:14:38Z
    Phil.W,
    thanks a lot for help. I have tried using macros and it is working fine. But now I have found out that in our cube can happen that "current date" member does NOT exists in cube every day. So I need to use a current month member instead.

    To get a current year member "FAKT 2012" I have written formula:
    <pre class="jive-pre"> [my_cube].[time_dimension].[time_hierarchy]-># '[FAKT ' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'# </pre>

    Above formula works fine and when I execute report, report is working fine (displaying correct values).

    But I need to create a month data member which in our case is like "FAKT JAN 2012" for January 2012, "FAKT FEB 2012" for February 2012 etc. So I have written formula:

    <pre class="jive-pre"> [my_cube].[time_dimension].[time_hierarchy]-># '[FAKT ' + timestampMask ( $current_timestamp, 'mm' ) + ' ' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'# </pre>

    I get error message: "FAKT 02 2012" is not a proper name. Sure error is logical in our case there should be FEB instead of 02. So I need to replace 01 with JAN, 02 with FEB, 03 with MAR, ... , 12 with DEC. So I have tried to write (only for February):
    <pre class="jive-pre"> [(sl) TERJATVE].[TERJATVE_CAS_FAKTURE].[TERJATVE_CAS_FAKTURE]-># '[FAKT ' + case when timestampMask ( $current_timestamp, 'mm' ) = '02' then 'FEB' end + ' ' + timestampMask ( $current_timestamp, 'yyyy' ) + ']'# </pre>

    But getting error: "Parsing error before or near position:<number>". The <number> is exactly at first letter of timestampMask function.

    How can month numbers be replaced with month name?

    Regards,
    IgorM
    Hi,
    now I have used substitute function and it is working fine.
    [my_cube].[time_dimension].[time_hierarchy]->
     
    #'[FAKT ' +
        substitute('12'; 'DEC ';
            substitute('11'; 'NOV ';
                substitute('10'; 'OCT ';
                    substitute('09'; 'SEP ';
                        substitute('08'; 'AUG ';
                            substitute('07'; 'JUL ';
                                substitute('06'; 'JUN ';
                                    substitute('05', 'MAY ';
                                        substitute('04'; 'APR ';
                                            substitute('03'; 'MAR ';
                                                substitute('02'; 'FEB ';
                                                    substitute('01'; 'JAN '; timestampMask ( $current_timestamp, 'mm')
                                                           )
                                                        )
                                                    )
                                                )
                                            )
                                         )
                                      )
                                   )
                                )
                             )
                          )
                       )
    + timestampMask ( $current_timestamp, 'yyyy') + ']'#
    

    Is there any other simpler way to replace '01' with 'JAN', '02' with 'FEB' etc? I don't really like substitute function is looks hard to read.
    Regards,
    IgorM
    Updated on 2014-03-25T07:52:57Z at 2014-03-25T07:52:57Z by iron-man
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Report Studio: How to concatenate two strings - date2 and char?

    ‏2012-02-28T09:42:29Z  
    • igorM
    • ‏2012-02-28T06:30:51Z
    Hi,
    now I have used substitute function and it is working fine.
    <pre class="java dw" data-editor-lang="java" data-pbcklang="java" dir="ltr">[my_cube].[time_dimension].[time_hierarchy]-> #'[FAKT ' + substitute('12'; 'DEC '; substitute('11'; 'NOV '; substitute('10'; 'OCT '; substitute('09'; 'SEP '; substitute('08'; 'AUG '; substitute('07'; 'JUL '; substitute('06'; 'JUN '; substitute('05', 'MAY '; substitute('04'; 'APR '; substitute('03'; 'MAR '; substitute('02'; 'FEB '; substitute('01'; 'JAN '; timestampMask ( $current_timestamp, 'mm') ) ) ) ) ) ) ) ) ) ) ) ) + timestampMask ( $current_timestamp, 'yyyy') + ']'# </pre>
    Is there any other simpler way to replace '01' with 'JAN', '02' with 'FEB' etc? I don't really like substitute function is looks hard to read.
    Regards,
    IgorM
    The macro functions are limited. The series of substitute function is likely the only real way to get what you are after.

    An alternative is to set an attribute in the members of the time dimension to identify the current period. You could then use the filter function to get the current period member:

    item(filter([Cube].[Time dimension].[Time hierarchy].[Day level], [Cube].[Time dimension].[Time hierarchy].[Day level].[Attribute] = 1), 0)
    
    Updated on 2014-03-25T07:52:52Z at 2014-03-25T07:52:52Z by iron-man