Concatenate multiple columns in a report

Hi Girish,

Is the actual data year, quarter, month etc from different columns or are they in a single column but separated by commas?

If the data is from different columns you could simply use a calculated field in the view and combine them for visual purposes (for example)

and using filters in the report (in this example a year filter) would produce

If the data is from a single column (separated by commas), then a virtual table or Calculated field using (Freehand SQL ) would be the best way forward,

Is this the sort of look you are trying to achieve?

Regards,

Paul

Thanks for your reply. I'll try this out. But will this calculated field work well in both Oracle and SQL Server data sources?

Also I what I don't get is year(dim), month(dim) and quarter(dim) in your example.

the year seems to be a function and dim a field. But can I use any function in the calculated field of formula type simple? Again just FYI, I'm trying this out in YF version - 7.4.7

Thanks,

Girish

Hi Giresh,

Yes it is very simple SQL and should work fine on both if not all SQL DB's. I used these fields purely as an example, but the concept should be the same for you. Let me know how it goes.

Cheers,

Paul

But the version I'm using, I cannot add any function over the fields. All I can do is available operations (#, #!, %, Min, Max, +, -, /) over any field. I'm still not sure how could you manage year(dim) for example. What I can do is use "dim" in the calculated field from the available field list but where do I get the year() function in this calculated field dialogue?

Also, just to specify what I'm trying to do:

I've a table called d_date where I store year, quarter, month and week of year for any date:

I use this table in my report. Now I want to use a concatenated string of year, quarter, month and week of year for date - (2018-11-20) as "2018,4,11,47" to be used in a histogram to display results of each of these values.

If that gives a better idea of what i'm trying to do, suggest a solution that I can use which will let work same model and report on both Oracle and SQL Server data sources without any change.

Thanks in advance for your help.

Girish

Hi Girish,

Sorry for late reply as I was not in yesterday. What I have suggested should work fine, regardless of the DIM in the calculate field, however as you have mentioned, that these are from d_date where you store year, quarter, month and week of year for any date. From advice from Big Dave in our support team he has also suggested that you could add a function to the custom functions, which would probably make your life a little easier to, when you want to use this.

So what we can suggest here is to add the following code to your custom-functions.xml file (where you can find info on here).

Once this has been done, you should then be able to see and use this in the Calc field as a Pre-Defined type as follows.

When you use this calculated field you would then get the result as such.

This will also work fine in both SQLServer and Oracle.

Regards,

Paul

Hi Giresh,

Hope you're having a good week.

Just wanted to check-in and see how it's all going. Was there anything you were needing from me to help get this resolved?

Regards,

Paul

Hi Giresh,

Just touching base to see how you went with this and whether you have everything you need now.

Regards,

Paul

Hello Paul, thanks for your help on this.

I tried a database level view concatenating the four strings, and referred the view instead of a table in the yellowfin view. But this another method of custom function sounds interesting. I'd do that next week and let you know.

Until then if you can keep this item open, i'll be grateful.

Thanks again for your help.

Do expect some update from me early next week.

Best,

Girish

BMC software.

Hi Giresh,

No problems. I will leave it with, but might touch base again later on next week if I have not heard back from you.

Cheers,

Paul

Hi Giresh,

Yes me again :) . How did you go with this?

Thanks,

Paul

Hi Paul, Sorry for the late reply. I was pulled into something else and hence could not pay attention on this.

My need has changed.

Now that I want to use these 4 fields (year, quarter, month, week of year) together on a chart on horizontal axis. Thus rather concatenating, can i use them all at a same time on chart? If that is possible, I would not have to concatenate them in the report.

Please let me know.

Best,

Girish

BMC Software.

Hi Giresh,

Unfortunately, this is not possible. The only way you could possibly achieve this would be to perhaps use an Overlay chart or a combine Category Chart, but I feel this would look very poor and messy.

Regards,

Paul

Hi Giresh,

Hope you're having a good week.

Just wanted to check-in and see how it's all going. Was there anything you were needing from me to help get this resolved?

Regards,

Paul

Hi Giresh,

Just checking in again on this ticket to see if you need anything else?

Regards,

Paul

Hi Giresh,

Just letting you know, that I am going to close off this ticket as I have not heard back from you for some time. If you need anything else with this, please get back in touch.

Regards,

Paul