Group BY function in Universe or Universe using Oracle stored procedure

[Former Member]

Hi All,

I am creating an Xcelsius dashboard with Qaaws as the data connection via universe to Oracle 10g database.

The requirement is that, When I click on a date in Calendar component of Xcelsius, say input_date

The input_date and input_date-180 days is given as input to QaaWS.

The QaaWS should return me the max values for a monthly metric which is updated monthly.

i.e sample data structure

WeekNum,Month,Year, MetricValue,LoadDate

1,Jan,2009,220,7-Jan-09

2,Jan,2009,,330,14-Jan-09

3,Jan,2009,340,21-Jan-09

4,Jan,2009,350,28-Jan-09

5,Feb,2009,620,5-Feb-09

6,Feb,2009,310,12-Feb-09

7,Feb,2009,431,19-Feb-09

8,Feb,2009,983,26-Feb-09

9,Mar,2009,123,6-Mar-09

10,Mar,2009,321,13-Mar-09

11,Mar,2009,432,20-Mar-09

Now I have entered my dates as 15-Mar-09 and 8-Jan-09

The desired output is

Jan,2009,350

Feb,2009,983

Mar,2009,321

i.e As for every month there are set of values it should get me the latest value of every month depending on the date.

A group by function on Month and Year and selecting the value for max week or max date will do.

But the question is how o we implement this. I have the table and Universe . Is there a way where I can create dimension objects using group by.

Can anyone throw me light how to use a oracle stored procedure in creatin an Universe. i.e how to write a stored procedure in oracle for Universe creation.

Please help me out.

Thanks in Advance

[Former Member]

Hi,

Please help me if anyone can do it.

I have one measure called Promoted Unit.

If i go to the properties of this Measure by selecting the Object Properties

In Select Box i have following code:

Sum(dbo.vrActivityFact.PromotedVolumeActuals * dbo.tProduct.BaseProductNumberInPackage)

I am geeting the sum at low level. I want this sum to group by vrActivityFact.tPromotableProductNodeid.

How can i do it.

Relationship between these 2 table is base on low level.

But i want to group by on high Level.

I tried to add group by in Where Box of Object Properties but it gave me error.

Foloowing is the SQL Query. I want to convert this in Universe.

Just need to Know where shoul di put Group by in Universe

Select Activityid,Sum(dbo.vrActivityFact.PromotedVolumeActuals * dbo.tProduct.BaseProductNumberInPackage) From vrActivityFact inner join tProduct

On (tProduct.BaseProductNodeid=vrActivityFact.PromotableProductNodeid

or tProduct.PNLNodeid=vrActivityFact.PromotableProductNodeid)

where Activityid in (155539,161828)

Group by PromotableProductNodeid,Activityid

[Former Member]

Hi,

I can see Last Tab of Parameter. but i could not find Advanced Tab and not able to see Group by when we do Object Properties and see select and Where Boxes.

Please help me.

Thanks

[Former Member]

Sorry for the confusion, there is no advanced tab. It is on the parameters tab. If you scroll through the list you will see one with the name DISTINCT_VALUES and change it from DISTINCT to GROUP BY

Regards

Alan

[Former Member]

Hi,

I could not find the Advanced Tab in Parameter Window of Universe.

Could you please help me in finding it.

Thanks

[Former Member]

Hi,

You need to go to the Designer product

Then choose File->Parameters

This should give you an edit box with 6 tabs on it. The last tab is actually called paremeters.

Hope this helps

Alan

[Former Member]

HI Narenda,

IIf you open the universe parameters and select the last tab (advanced) one of the properties in the distinct value. It will normally be set to DISTINCT. You can change this to GROUP BY and it should create statements like

SELECT Month, Sum(myValue) from Table GROUP BY Month

Regards

Alan

[Former Member]

Hi Alan

thank you for your response. I have changed the setting as said by you to Group By.

Can you please explain me eloborately on how we will use this feature.

Is this will be used while generating reports or can i be able to create objects with this group by clause in universe.

Regards

Narendra

[Former Member]

Hi Narendra,

If you change the setting and export the universe, then you should see the difference when you view the SQL. The simplest way to do this is to create a Web I Report and include the objects you are expecting, then click on the SQL button. This will show you the new code. You should be able to see the group by clause.

Regards

Alan

[Former Member]

Hi Alan

The said functionality works in Web Intelligence. But I want the same result set into Xcelsius 2008 using QaaWS

Regards

Gangadhar