If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Return only Minimum Day record as output - Dimensional Reporting

Started by imeu, 21 Sep 2015 07:14:01 AM

Previous topic - Next topic

imeu

Hi, Please help.

After breaking my head for a long time im seeking all of your help.

The requirement is, a company is having a Stationary Department and N number of Employees will taking Items from there. We need to display the first time on which the employee has taken an item. For eg

On Sep 15 2015 Emp1 has taken Eraser
On Sep 15 2015 Emp1 has taken Pencil
On Sep 17 2015 Emp1 has taken Eraser
On Sep 18 2015 Emp1 has taken Notebook
On Sep 20 2015 Emp1 has taken Eraser
On Sep 21 2015 Emp1 has taken Pencil

Now in the Prompt we will be selecting Month-Year Sep-2015, Employee Emp1 and Item Pencil so the expected result is

Sep2015  15  Emp1  Pencil

I tried to bring out the least Day in a Month according to the item for each Employee but none of my MINIMUM, RANK, BOTTOMCOUNT is working on this dimensional model.

Please help me.

MFGF

Quote from: imeu on 21 Sep 2015 07:14:01 AM
Hi, Please help.

After breaking my head for a long time im seeking all of your help.

The requirement is, a company is having a Stationary Department and N number of Employees will taking Items from there. We need to display the first time on which the employee has taken an item. For eg

On Sep 15 2015 Emp1 has taken Eraser
On Sep 15 2015 Emp1 has taken Pencil
On Sep 17 2015 Emp1 has taken Eraser
On Sep 18 2015 Emp1 has taken Notebook
On Sep 20 2015 Emp1 has taken Eraser
On Sep 21 2015 Emp1 has taken Pencil

Now in the Prompt we will be selecting Month-Year Sep-2015, Employee Emp1 and Item Pencil so the expected result is

Sep2015  15  Emp1  Pencil

I tried to bring out the least Day in a Month according to the item for each Employee but none of my MINIMUM, RANK, BOTTOMCOUNT is working on this dimensional model.

Please help me.

Hi,

Here you are not dealing with dates in a table, but with members in a set. How are you identifying the members you indicated above? What mechanism in the report is your prompt driving to return these members?

If it was me, I'd be using a filter() function to get the month members for Pencil withdrawals

eg filter(children([your Month level] -> ?pMonth?),tuple([your measure],[your Item level] -> ?pItem?,[your Employee level] -> ?pEmployee?) > 0)

You can then use an item() function around this resultant set of members to return only the first member in the set:

item(filter(children([your Month level] -> ?pMonth?),tuple([your measure],[your Item level] -> ?pItem?,[your Employee level] -> ?pEmployee?) > 0),0)

Cheers!

MF.
Meep!

imeu

MFGF hats off to your solution :-)

I was bushing with all necessary functions but missed only Measure in the tuple and instead of ITEM i used HEAD which was returning only the 1st Index Number.

When i replaced both of that your solution worked exactly to my requirement.

[Cube].[Dim].[Hierarchy] -> ?pMonth?

I was using a descendant on this to get the Day Level

decendants ([Cube].[Dim].[Hierarchy] -> ?pMonth?;[Cube].[Dim].[Hierarchy],[Day])

now i replaced that with Children.

And for the TUPLE i need to convert all to MEMBER as i use a SET in the Parameter of Item and Employee.

But it works brilliant. Thanks a lot.