Creating measures in universe

[Former Member]

Hi,

I'm trying to create some measures in my universe but I'm having trouble on how to go about it. I'm new to this so bear with me:)

I have two Y/N flags in my table that I want to be able to do counts on I created 1 measure, "RecordCount", as count(*). I then created two additional dimension objects. "1Status" and "2Status".

1Status = Case when Field1 = 'Y' then '1At Goal' else '1Not at Goal' End

2Status = Case when Field2 = 'Y' then '2At Goal' else 2Not at Goal' End.

I also have an AgeBucket dimension object "AgeBuckets"

AgeBuckets =

CASE WHEN @Select(CurrentAge) between 1 and 30 then 'Ages 1-30'

     WHEN @Select(CurrentAge) between 31 and 40  THEN 'Ages 31-40'

     WHEN @Select(CurrentAge) between 41 and 50  THEN 'Ages 41-50'

     WHEN @Select(CurrentAge) between 51 and 60  THEN 'Ages 51 - 60'

     WHEN @Select(CurrentAge)> 60 THEN 'Ages > 60'

END

In my webi report i created a table with "AgeBuckets","1Status" and "RecordCount" and another table with "AgeBuckets", "2Status" and "RecordCount".

It seems to be working fine until I add "1Status" and "2Status" as a selection in the Filter Bar. Then the counts don't work.

Do I have it setup correctly in the universe?

Any help would be greatly appreciated.

thanks.

[mhmohammed]

Hi Richie,

In the Count Object in the Universe, did you select the Projection Function as Database Delegate Measure? If not, do that. Also, there are different scenarios, where creating Count objects in universe works better and some other times, creating Count objects in the reports work better. Let us knw if that's what you were looking for.

Check this out for Database Delegated Measure in Universe Design Tool (UDT)

Dave's Adventures in Business Intelligence; Designer XI 3 New Feature: Database Delegated Measures

And in IDT:

Using Database Delegated Measures or Smart Measures in Web Intelligence - Business Intelligence (Bus...

Thanks,

Mahboob Mohammed

[Former Member]

Hi, thanks for the info. I created the count variable in my webi report and I still am seeing the same results. I must not be doing something right. Below are some screen prints showing what is happening

[mhmohammed]

Hi Richie,

Are there any patients that are under both A1c and BP list? If yes, then the selection in one drop down the others as well. Make sense? Can you check the common patients?

Also, do you want the values in BP drop down list to change depending on selection in A1c filter? Then create a Cascading LOV in Universe Design Tool (UDT) or Custom Navigation Path in Information Design Tool (IDT).

Information design tool 4.0: Create a custom navigation path

Thanks,

Mahboob Mohammed

[Former Member]

Hi,

I just saw. Yes that is what its doing its counting patients that are "Y" to both BP and A1c. I'll look at the custom navigation path to see if I could figure it out.

thanks

[mhmohammed]

Hi Richie,

If its OK with your users, you can use Input Controls instead of those Quick Filters, that way you can make only certain tables dependent of value selection in A1c or BP filters. Make sense?

Thanks,
Mahboob Mohammed

[Former Member]

Yes, I'm just trying to figure out how the cascading LOV will work for this. I've done cascading LOVs before but just to show the hierarchy in the prompts.

[mhmohammed]

Hi Richie,

In which tool is the Universe created? Universe Design Tool (UDT) or Information Design Tool (IDT)?

Follow this for IDT:

Information design tool 4.0: Create a custom navigation path

Follow this for UDT:

http://davidlai101.com/blog/2009/01/08/cascading-list-of-values/

And use those objects in the Quick filters.

Thanks,
Mahboob Mohammed

[Former Member]

Hi ,

Sorry, I'm not quite following. Do i keep the object dimensions I created with the case statements in the universe? Not sure how to set it up if I have one dimension for BP and one for A1c and 1 patientcount measure.

[mhmohammed]

Hi Richie,

How about creating one object for 'Status' of both Field1 and Field 2 as below and then create the Custom Navigation Path with Age Bucket and Status objects.

Case

when Field1 = 'Y' then '1At Goal'

when Field1 = 'N' then '1Not at Goal'

when Field2 = 'Y' then '2At Goal'

when Field2 = 'Y' then '2Not at Goal'

else ''

End

Please try to create few variables and check which one works best for you.

Thanks,

Mahboob Mohammed

[Former Member]

Hi,

that works but I can't get the separate counts that way. they all come in together.:(

[mhmohammed]

Hi Richie,

How about keeping it the way you have and then create 2 new Custom Navigation Paths, one with Age Bucket and BP objects and the other with Age Bucket and A1c objects?

Thanks,
Mahboob Mohammed

[Former Member]

Hi,

thanks for the suggestions, its still not working though. In addition, when creating the objects in the Universe it takes quite a while to create them when they belong to a hierarchy. I'll keep trying different things. it keeps getting confused and changes the numbers no matter what status i choose.

thanks

[Former Member]

Hi, for some reason, creating the list of values from the hierarchies takes a very long time.