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.
Pinned topic Report Studio: How to concatenate two strings - date2 and char?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-02-28T09:42:29Z at 2012-02-28T09:42:29Z by SystemAdmin
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-20T10:08:42ZThis is the accepted answer. This is the accepted answer.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.
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-22T11:53:30ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
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?
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-23T06:29:19ZThis is the accepted answer. This is the accepted answer.
- igorM 110000Q388
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.
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.
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-27T13:14:38ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
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?
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-28T06:30:51ZThis is the accepted answer. This is the accepted answer.
- igorM 110000Q388
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.
IgorMUpdated on 2014-03-25T07:52:57Z at 2014-03-25T07:52:57Z by iron-man
Re: Report Studio: How to concatenate two strings - date2 and char?2012-02-28T09:42:29ZThis is the accepted answer. This is the accepted answer.
- igorM 110000Q388
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