Categories
OAS 7.0: Combining multiple columns into one column to show data in the form of tabular list
Hi Gurus,
We have a report with columns that includes attributes: 'X Email ID', 'Y Email ID', 'Z Email ID'. We want to email this report to these three Email IDs when ever there is >0 rows in this report (think of it as re-order alert for inventory or funding alert for bill payment etc.). Therefore, we want to list the values in 'X Email ID', 'Y Email ID' and 'Z Email ID' columns in one column such that the values appearing one after the other (i.e. all X Email IDs then after all Y Email IDs then after all Z Email IDs) in a single field, so that we can use that report to specify the 'Recipients' of this alert. Or alternatively, any other approach to accomplish this requirement, any input is highly appreciated.
Best Answer
-
If it's too complicated to build a new analysis with just the emails and handling the UNION requests, you can also just keep it simple: make 3 agents, each one using a different column to get the emails. It isn't the cleanest ever way, but definitely the easiest ;-)
1
Answers
-
Hello nameless user,
Which product are you referring to? Your post contains no information on this.
Are you really talking about a "report" meaning a BI Publisher report?
Thanks for providing more and more detailed information and making the forums a great place to exchange!
0 -
Hi @User_SI7HK ,For combining 3 columns ,into a single field ,please use concat symbol || in my calculations from dv workbook
concatenation logic:
"Financials - AP Invoices"."Supplier"."Supplier City" || ', ' ||"Financials - AP Invoices"."Supplier"."Supplier State" || ', '||"Financials - AP Invoices"."Supplier"."Supplier Postal Code"
0 -
Thanks Christian for your attention, we are using regular OAS 7.0 analytics report running off of a subject area (not BI Publisher or Direct Database Query or Ext Data source based report). And we are trying to create an agent for this report.
Thanks RAM, as this column (with email Ids or rather a report based on this column) will feed the "Recipients" (which will be random at different times based on when the report is run) in the Alert definition. Concatenation can work but not side by side with comma, the way you have suggested, but by brining all three email Id values to list in one column (in a report, as described earlier), so that this report can be specified for 'Recipients' of the alert.
0 -
If you need your 3 columns to be a single column to be used as recipients for your agent, just make a UNION analysis: you select the column A in the first request into a "recipient" column, then add a UNION and select column B for that same "recipient" column and you do it once more for your column C.
The result is an analysis with a single column and all your emails in different rows, and it should work just fine as recipient in an agent.
1 -
The "recipient" of an "agent" has to be either an email address if your target is email but when its target is "alert" then the recipient must represent a valid user account.
I.e. something that the agent can send it to inside of OAC. If the unique identifier of your user is "name.lastname@company.tld" then that's what you need. If it's "n.lastname" then you need that. If it's "abc123" then you need that.
So the first question you need to answer is : what identifies your users? What are they logging in with?
As Gianni said if you need a UNION of several different recipients - meaning make a list of recipient by having multiple rows - then that's what you will need to do.
An agent pulls its dynamic recipients from the conditional analysis. Meaning you create the union or whatever pulls you up a distinct list of the system user identifiers as an analysis objects. Then you make that analysis object the conditional analysis of the agent (together with whatever criteria you have or need, you didn't specify further), and then the agent can source its recipients dynamically. If it's a query then you can have myself and Gianni as recipient today, just Gianni tomorrow, me the day after, and every forum user on Sunday.
Hope this helps and clarify things in spite of some misleading content in this thread.
0 -
Thanks Gianni, (sorry for responding late, was working on logistics for testing as the alert in non-prod env, as it is flagging ~50k records identifying ~800 email recipients). Anyway, so I have the report (to be sent out) and it has last three columns as 'X Email ID', 'X Email ID', 'Z Email ID' (such as for director, manager, supervisor). When trying to create a union query, as you have suggested (to make a tabular list of these three designation's email ids to be specified as recipients in the agent), it expects the exact same set of columns (not allowing X Email ID in the base report, then 'Y Email ID' in first union and 'Z Email ID' in the second union). Is there a way to allow a union of queries with one field differing from the base report ?
I can generate three separate reports (by hiding all other columns of the alert report) for each of these email ids, but then I need to combine these outputs into one single tabular report. Is there a way to do it, any tip is highly appreciated.
0 -
The structure of the 3 queries in the UNION must be the same (that's typical SQL behavior: you can't union different shaped data).
But you can perfectly have only 1 email in the first part, let's say 'X Email ID', then in the second you edit the formula of that column (you can even rename it to just be "Email ID") and point to the 'Y Email ID' column, and in the third you point to 'Z Email ID'.
If you really want to keep the same structure with the 3 emails columns, you just need to switch 'Y Email ID' and 'X Email ID' in the second request and 'X Email ID' and 'Z Email ID' in the third.
Do what it takes to get your emails into the same column "outside" the UNION request, by mapping the 3 columns to the same one.
0 -
Thanks Gianni, I can use three agents (as I already have three reports listing one email id each, I can use them for recipients for the three agents). Thanks again, much appreciated.
0 -
Sometime it’s just easier like that: you have 3 analyses? Oh well, why to bother creating a new one that would be a union (requiring to replicate the logic and make sure to keep it always aligned with the business logic of the analyses you have) when you can just create 3 agents.
You can even chain the agents so that you schedule only the first one, and they will execute serially (just maybe check if the chained agents are always executed even when the previous one didn’t do anything, because maybe your emails aren’t always there for you 3 columns all the time, that’s something I don’t remember right now). But if you schedule them manually, make them a few minutes apart, just to not overload the system with too many emails to send all at once.
What matters, at the end of the day, is that your emails are sent. Not much how ;-)
0
