Passing only selected accounts into the report

[former_member297636]

Hello folks,

I'm trying to create a webi report based on customer master data and am required to fetch information regarding selected accounts only (approx 200,000 accounts), Had it been few handful accounts, i'd use query filter to just bring results for desired accounts but this is way too much info to select from the list of values manually. I'm positive there's a better efficient way to achieve this result which I can't seem to figure it out at all. Can some one please help me with this ?

btw, I've been provided with an excel sheet containing list of accounts required for the report. Can you create two queries (one for customer master data from universe and the other for only required customer accounts from excel) and merge them to filter the report, is that possible ?

I'd really appreciate any insight/thoughts on this .

Thanks

Gaurav

[Former Member]

Hi Gaurav,

Yes you can create report which will have two data providers

first will get data from Excel sheet & second one will get data from universe and then you can merge both the results.

But as you are dealing with huge data I will suggest you to insert the excel data into database table and then join that table with the master customer table & apply filter condition on universe directly which in turns fetch less data & performance will be good.

[former_member297636]

I appreciate your response Swapnil !

My Customer Master universe is based on a Bex query through BICS, hence the data is flowing through BW, Can I apply such a filter condition in BW or at Query designer level to achieve my desired output ?

Can Restricted key figure play a role here ? I'm just guessing.

btw,merging two queries (Excel & Universe) might combine the values from both objects (i.e Customer Account field), more like a UNION operator, I'd need an INTERSECT kind of operation when merging these two queries, What do you think ?

Thanks

Gaurav

[Former Member]

Hi,

Hope below Link will Help you in some Ways...

[Former Member]

I think you can solve your issue with merge option in WEBI.

What you can do is apply merge on common dimensions from both the queries.

Now do the setting as shown in example below:

example:

In query 1: Dim1,Dim2,Measure1,Measure2

In query 2: Dim1,Measure3

Now I want to have common objects between both the queries (i.e. Dim1)

So what I will do here is first merge Dim1 from both the queries & then drag Measure1 from Query1 & Measure3 from Query2 & then create a report using following objects

Dim1(Merged),Measure1,Measure3

Now create one variable

Var1 = If(isNull([Measure1]) = 1 and isNull([Measure3]) = 1 then 1 else 0)

Drag this newly created Variable on existing report and then apply report level condition like

Where Var1 = 1

So this will give you common Dim1 between both the queries.

I think understanding this example and apply same at your report will help you.

Thanks,

Swapnil

[former_member297636]

Thanks Swapnil, I appreciate your reply.

I realized I'd need to pre filter the data so only those required accounts make it to the report and not everything. Prefiltering those random accounts is the main concern here, I've been provided with an excel sheet containing those accounts and there is no way I can use query filters to pass these values.

Another alternative that we are looking into now is using the bex query to bring the required data on Bex analyzer or Analysis for office and take it from there.

Hopefully, excel will come to our rescue here 🙂

Thanks again

Gaurav