Merge and Manual Records

[former_member402770]

Hi Experts,

Iam using BO IDT 4.1 for an multisource reporting requirement. One Universe for multiple sources with out joins means isolated tables. So In my webi report below:

I have below data for two dimensions coming from one universe ( contains query1 class folders and query2 class folders)

ProjCode.Query1: 

  823 - Data Management

  684 - Micro Management

  542 - Metadata Management

  Proj Bench                       (This row with Bench words are manual entries entered in database)

  Project Bench                   (This row with Bench words are manual entries entered in database)

ProjCode.Query2:

684K

521K

I donot have joins in universe between these two tables/query's/class folders. What iam trying to achieve in my report is i need to merge this above dimensions into one and use this in a report. So my output should be like below:

My webi prompt would be:

   Dragged Fiscal Year Period and ProjCode Dimension from Query1  as Query Filter as Prompt with similar prompt text

   Dragged Fiscal Year Period and ProjCode Dimension from Query2  as Query Filter as Prompt with similar prompt text

While executing the report, i get only one prompt as Fiscal Year Period applied with 008/2014 and other one has ProjCode here iam applying all LOV's executed the report.

1) Question am i applying the prompt with similar text so will it do union of both prompts LOV's

2) In Repor output, how can i achieve the below matching records like from both query's

         Merged ProjCode               BillingAmount

         684 - Micro Management    324737

         Proj Bench                        234

         Project Bench                    989

"Bench" words are manual entries entered in database by specific department only in ProjCode.Query1 dimension which also should be captured in above webi output along with matching records.

3) the report Output will it do inner or left outer or right outer join between queris? I would need here only matching records along with Bench Codes as webi output.

4) What about Billing Amount Key figure formula would be?

Appreciate your much needed help here.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

In the second query K is coming along with the number in the second query? This is you have mentioned here or data is coming like this only.

ProjCode.Query2:

684K

521K

Amit

[former_member402770 · Accepted solution]

Amit,

Thanks for your quick response, yes K along with number is coming in database as well like below:

ProjCode.Query2:

684K

521K

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

If you can exclude the K value from the  universe object then  might be you can follow these steps to achieve.

1) First merge both the dimension objects.

2) create one variable.

Show/Hide=If([Query 1].[Code])=([Query 2].[Code])  Then "Show" Else "Hide"

Put this variable in the final table as i have put in the final output.

4) Right click on the show/Hide variable and  Hide->Hide dimension.

For billing amount put objects in the table like this.

=[Query 1].[Value]+[Query 2].[Value]

5) Create one more table and apply filter on this and select the Proj values only.

6)Right click on the header and remove the row.

😎 Right click on the second table where display Proj values and ->align->relative position.As per screenshot match the values.

9) Final output

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Thanks, yes the steps are really clear and helping me a lot to achieve this in report.

Basically this report is an variance report between multiples sources which means i have to merge dimensions at report level and show the variances.

The above mentioned Proj Code from one of querys having data values as 684 - Micro Management and other one query with Proj code as 684K. Here let me try to get the descriptions of the Proj Code so data value sync with query1 values.

User expecting in merge prompt and merge dimenion as like this whole description format 684 - Micro Management unfortunately query2 doesnt have that kind of data value.

What i tried in report as:

One query data provider i have prompted the user to select in ProjCode dimension  LOV's as 684 - Micro Management.

Second query data provider i have set ProjCode dimension Values in List as 684K.

On execution of the report i have full outer join not inner join(matching records). So

applied formula in the merged dimension of ProjCode column in the report as

(Match(UserResponse("ProjCode");"*684*") = Match([ProjCode];"*684*") ) so it filtered the report only with 684 records from both dataproviders.

Any thoughts on this,

Thansk,

Dinya

[amitrathi239 · Accepted solution]

Hi,

This is going to be static approach.How are you going to be find out matching record  if some one  select the different number.

can you try to create two objects at universe level to extract first three values from the ProjCode.

create two object ProjCode1 and ProjCode2.Use substring or Left function at business layer to extract first three digit.

Drag these object in the webi queries and follow mentioned steps in my earlier post.

Amit

[former_member402770 · Accepted solution]

Amit,

There are multiple scenarios in one report static, dynamic  selections..

The problem i had is iam getting full desc for proj code eg:684 - Micro Management for all sources query's except one source query which is having proj code in this format eg: 684K. I shall try to request for full desc proj code here to the DB Team.

But btw iam trying to apply the below formula to restrict which is static one i agree completely, any ideas here on match pattern with inlist combination for setting multiple numbers if static case. Please write us if you have known..

(Match(UserResponse("ProjCode");"*684*") = Match([ProjCode];"*684*") )

Basically one query will be my source always to reconcile with many source targets.

I shall try to continue to use your match logic and let you know if any issue there.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

With InList operator only static values will work. Like you have to define

=[ProjCode] inlist ("ABC";"CDE")

Amit

[former_member402770 · Accepted solution]

Hi Amit,

tried your show/hide logic, I would need an formula help for the three points below:

1) I have three dataproviders to match only the similar records and show in the report output. Following below screen shows the three query's to be merged and show only the matching records. ie.684.

Formula to be rewritten for below for three dataproviders matching rows/records:

Show/Hide=If([Query 1].[Code])=([Query 2].[Code])  Then "Show" Else "Hide"

2) Another requirement is i have  4 dataproviders like in below screeshot merged. i would like to have variable rewritten to my requirement here.

Created one variable.

Show/Hide=If([Query 1].[Code])=([Query 2].[Code])  Then "Show" Else "Hide"

Formula tobe rewritten for below in one formula variable:

Query 1, Query 2, Query 4 to show only mathcing records.

Query 1, Query 3, Query 4 to show only mathcing records

Query 1, Query 5, Query 4 to show only mathcing records

3) Match Operator doesnot work for inlist case, could you please put one screeshot which is working:

=If(Match(((UserResponse("ProjCode")) Inlist ("*")) = Match(([ProjCode])Inlist("*"))) Then "SHOW" Else "0"

Appreciate your quick help.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Use this..

=If([Query 1].[Code]=[Query 2].[Code] And ([Query 1].[Code]=[Query 3].[Code])) Then "Show" Else "Hide"

Similar to this you can change the   object in the formula fro your point number 2.Also note you needs to merge the dimensions.

See attached screenshot.

Amit

[amitrathi239 · Accepted solution]

Hi,

instead of comparing  user response values you can compare the objects.

Like if you have entered project code 100,200 in the prompt then if you drag the project code object then you will get the values 100,200.

1) entered year values 2003,2002 in the prompt.

2) Dragged Year object in the report and got values 2003,2002. Userresponse you can use when you needs to display the values in the single row.Like 2003;2002.

If more then  one value is coming in the prompt then you can use same object in the  formula.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

Alright, i have an issue here, i shall put your screenshot itself to explain my issue.

Consider my case, Query 1 i donot have any data available for the selection. Then this formula should result the value for merged dimension to be Zero instead it is displaying Query 2 and Query 3 match row values like your values which shouldn't be right as it is using AND operator.

I would only need the value if all the three query's match row for the merged dimension. IF anyone querys doesn't have data row as values for the merged dimension it should result in 0.

=If([Query 1].[Code]=[Query 2].[Code] And ([Query 1].[Code]=[Query 3].[Code])) Then "Show" Else "Hide"

Correct me if iam wrong, appreciate your help.

Thanks,

Dinya

[amitrathi239 · Accepted solution]

Hi,

use this.

=If([Query 1].[Code]=[Query 2].[Code] And ([Query 1].[Code]=[Query 3].[Code]) and And ([Query 2].[Code]=[Query 3].[Code])) Then "Show" Else "Hide"

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  I tried still showing 684 as value,

  =If([Query 1].[Code]=[Query 2].[Code] And ([Query 1].[Code]=[Query 3].[Code]) and And ([Query 2].[Code]=[Query 3].[Code])) Then "Show" Else "Hide"

if any one condition say in my case [Query 1].[Code] = 0, the formula variable should result the value as zero isn't ? but iam getting 684 as value.

Also tried based on similar kind of snapshot in webi it is showing 684, 530  instead 684 (only match of all three query's)

Tried this formula also =If([Query 1].[Code]=[Query 2].[Code] And ([Query 1].[Code]=[Query 3].[Code]) and And ([Query 2].[Code]=[Query 3].[Code])) Then "Show" Else "Hide"  resulting 684, 530 instead 684..

Correct me if iam wrong here.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

Hi,

Have you checked the option "result from the another query" in second & three query prompts.

1) keep as prompt.

2) select the option "result from another query.

3) select the object from query 1 which you want to compare.

similar you can do for third query.

Amit

[amitrathi239 · Accepted solution]

Hi,

let me know if this solution is feasible.

Other wise will see tomorrow for some other solution.

Amit

[former_member402770 · Accepted solution]

Hi Amit,

  Tried result from query option, resulting the same value rather than match value of three or more merged dimensions.

Thanks,

Dinya.

[former_member402770 · Accepted solution]

Amit,

  Any clue on the solution for getting only match records between three dimension or more dimensions merged from different dataprovider.

Thanks,

Dinya.

[amitrathi239 · Accepted solution]

HI

i was bit busy today.will see tommorrow and let you know.

Amit

[amitrathi239 · Accepted solution]

Hi,

might be you can raise the SAP ticket on this with sap support team.

issue is coming when comparing data more then two queries.Saw query 2 code value 684 is coming when dragging query  3 code 684 is coming but which is not in the query 3.Don't know how this is coming.

Might be check with SAP or right now go with 2 queries comparison.

Amit

[amitrathi239]

Hi,

1) Question am i applying the prompt with similar text so will it do union of both prompts LOV's

Prompt with similar text not going to be create union in the LOV's.LOV'S are going to display from one object only depend on the options you  have selected.

Amit

[former_member402770]

Amit,

If there is no union hapening on the Prompt with similar text then will the merge dimension at the report will suffice the union gap.

At any point of time my source with Query1 which i have to reconcile with other source query's.

The webi output should to show only the matching records with variance with source query1 vs query2

source query1 vs query3, source query1 vs query4 etc.

Any thoughts on Apply union between these 2 queries and pull the prompt object from query 1 in report filter panel @ the report level.

Thanks,

Dinya.

.

[amitrathi239]

Hi,

Same prompt text is not creating any union.Same prompt text meaning is just entering the value once and passing in mulitple queries.

can you explain in more detail.

Any thoughts on Apply union between these 2 queries and pull the prompt object from query 1 in report filter panel @ the report level.

Amit

[former_member402770]

Hi Amit,

Any thoughts on Apply union between these 2 queries and pull the prompt object from query 1 in report filter panel @ the report level - > At webi, Combine query with union operator how does this mean is it an other option to my scenario?

Thanks,

Dinya

[amitrathi239]

Hi

With combined queries you can not do the comparison.If you see after combined queries in webi objects panel you will get the objects from first queries only.

In your case you needs to use the individual queries in one single webi report and later at report level use merge dimension to merge the data.

Amit

[former_member402770]

Hi Amit,

On combined queris iam not clear, anyway i shall workaround your Show/Hide logic and continue to post if for any issues.

I really Appreciate your quick help so far with solution, your screenshot with better and quick understandings within little time for implementation.

Thanks,

Dinya

[amitrathi239]

Hi,

If some specific issue will come then you can post here.

Amit

[amitrathi239]

Hi,

Even you can check this also.

Amit