on 11-20-2015 1:47 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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.
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.
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
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
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.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
.
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
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
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.