Variable to count Merged dimensions

[Former Member]

Hello,

In my Webi report, I do not have any measures, only the characteristic account hierarchy. One account hierarchy is sourced from SQL (via universe) and the other account hierarchy is sourced from BW query (via BICS). The 2 hierarchies / dimensions for account are merged successfully in WebI. When I display the 2 hierarchies (2 columns in single table), it shows me only those rows (by text) that match, Those rows that do not match are not displayed, which is good.

How can I get a count of the rows after they are merged? I wanted to create a new variable that will count only those rows that are merged succesfully. I have a variable that tells me the count before merging the dimensions.

Hope my Q makes sense.

Thank you

Ann

[former_member225163]

Hi Ann,

Please try the below formula to get the number of unmatched rows.

(Count(<Merged Dimension>) * 2 ) - ( NumberOfRows(<Data Provider1>) + NumberOfRows(<Data Provider2>) )

e.g., Merge dimension result will be outer join. So, assume that we have 10 rows in DP1 and 10 rows in DP2 and out of those 5 rows are matching.

In this scenario, 15 rows will be the output of Merge Dimension.

No of Unmatched rows = ( 15 *2 ) - (10 + 10) =10 rows ( 5 rows unmatched in each data provider)

Hope this helps..

Cheers,

Bala

[Former Member]

In the WebI document, I have created a conditional format that highlights those rows that do not match. Is there any way I can setup emails to be sent when the conditional format is true?

Basically the requirement is to send out emails when the rows do not match. How can this be achieved?
Any help or guidance will be great.

Thanks

Ann

[Former Member]

This functionality is yet to be available in Webi however using crystal report you can use a conditional formatting as a trigger point.. Also there are work around using creating events to achieve this..

There was a similiar discussion which happened on this topic some times back which I will recommend you to check..

[Former Member]

before merging you can use function numberofrows()   to get row count of the data provider..

After merge try using the below formula and let me know if you face any issue...

You can use any of your report dimension in the formula

= Count([Dimention])  In Report

[Former Member]

Hi Durga,

The variable with formula --> Count([Amount])  In report is not giving me the desired row count after merge. Instead this formula is returning the row count before merge. Please help.

Thanks

Ann

[Former Member]

Try creating a variable :

= IF [query1.ObjectName]=[query2.ObjectName] then 1 else 0

Then try the formula mentioned above ; I have not checked it myself yet but I think it should work..

[Former Member]

Hi Durga,

When I try the above formula, I am getting error message Incompatible object '[query2.ObjectName]' at position 28. (IES 10076).

FYI, query 1 hierarchy is from BW and query 2 hierarchy is from Essbase.

Is there any workaround to get the count of rows that do not match?

Thanks!

Ann

[Former Member]

Hi Ann,

Can you try creating a variable as detail.

Keep the definition of this detail variable as [query2.ObjectName].

Associate this detail variable to the merged dimension.

Now use this detail in the if logic  suggested by Durga.

Let us know, if this works.

Thanks

Gaurav