Hello folks,
I am facing some issue in displaying data from a merged dimension and i hope i can explain this clearly. This is a Webi report and on 4.2 version
I merged both the dimensions. In the cross table, i have the Salesname in the first column and in the Data column i have the TargetValue and Salescount. This is straightforward and have the data as per the requirements. But now, i add a new row at the bottom of the report for RegionCounts, which is a subset of Salescount. There is a line item in the Target table with a value RegionCounts, but does not exist in the SalesTable.
So the last row of the table looks like…
RegionCounts (first column) (manually entered)
TargetValue (= TargetValue where TargetNam= ‘RegionCounts’) - this is from query2. But CANNOT DISPLAY THIS.No error , its just blank.
SalesValue (=TargetValue where salesname in (A,B,C) ) used a formula and i get the correct number.
Any advice and how to provide the TargetValue for RegionCounts in that row which is derived from Query 1.
Yes. The TargetName has more data.
Ex: Regioncounts is a row in Target name and has a target. I need to add this to the report by displaying it. But since it doesnt exist in the Salesname, it does not want to display the value.
What is the example data for TargetName and what is the example data for SalesName? Is it both exactly the same words, with different lists in each? For example, TargetName has North, West East and SalesName has North, West, South?
TargetName || TargetValue || TargetMonth
John || 100 || Jan
Steve || 250 || Jan
Mike || 200 || Jan
Region || 400 || Jan
Sales Table (has three columns)
SalesName || Salescount || SalesMonth || SalesCategory
John || 150 || Jan || Local
Steve || 200 || Jan || Local
Max || 300 || Jan || Region
Bob || 200 || Jan || Region
So the final Report looks/should be
SalesName || Target || Sales
John || 100 || 150
Steve || 250 || 200
Region || 400 || 500
Since Region is not a TM, i create a row in the bottom of the report and manually enter Region and create the below formula to populate the measures.
So for Region(which isnt a Sales but a combination), the formula on the Sales Column is where SalesCategory =“Region”
The formula for Target for Region row is where TargetName= “Region”
Merge Sales.SalesName with Target.TargetName
Merge Sales.SalesMonth with Target.TargetMonth
In the final table, use the Merged objects
MergedName, MergedMonth and then Target.Targetvalue and Sales.Salescount
That’s as far as I presume you’ve got and you’re left wondering how to get Max and Bob to consolidate as “Region”?
The problem you are facing is that SalesCategory does not exist in the TargetTable - can you add it?
You might be able to get round the problem by doing the following:
Create a detail variable, SalesCat, whose master is the MergedName object and is defined as =[SalesCategory]
Now you can build a new LocalName object as:
=If([SalesCategory]=“Local”;[MergedName];“Region”)
Then use this LocalName object in your final table instead of MergedName.
Thanks Mark. That exact method did not work, but you gave me the right idea and i was able to use an IF condiition to get the data. The hard part was that, i had to hardcode the values in the formula as those are fixed targetvalues. And i had to use Tonumber since this field would have to undergo calculations in other sections of the report.