Limit the number of rows

[Jsadas]

I have Comments field in a table, there can be multiple comments for a given project. I need to display only the latest comment added. These comments are differentiated as Issues, Accomplishments etc., based on one more field comment type.

I was able to pull only Issues creating a measure using Case When function. As mentioned earlier this is retrieving multiple rows, I need to display only the last entered Issue.

I am not sure how to add Rowcount condition in BO, any help would be appreciated.

Regards
Jags

 

[Sridharan]

Couple of ways of accomplishing your reporting needs. Either you can go for a Correlated SubQuery to get the Latest Comment for each project and show it off in the report. Or else take all the details and in the report insert a break and show only the latest comment.

BTB how do you identify the latest comment. Do you have any date column to do that or someother means.

Sri

 

[Jsadas]

Hi Sri,

I do have a date field with which I can use to identify the latest comments.

Its already a long day for me, I am in no position to think of new options, below is my code. Please help me in solving this problem.

How do I use correlated subquery?

Case when (@Select(Work Notes&Descriptions\Note Date) <= sysdate and @Select(Work Notes&Descriptions\Note Criteria Code) = '36' and @Prompt('07 Show Monthly Progress Summary (Y/N)','A',,mono,free) = 'Y') THEN DM_VW_NOTES_DIM.LINE_TEXT END


Thanks & Regards
Jags

 

[Sridharan]

Jags,

I don't understand why you are having a CASE Statement and also a Prompt and other stuff to get the Latest Comment Field for each Project. I know it will toooooooo tiring. Take a break. If possible gimme a glimpse of your Table Structure and I will sort it out.

Sri

 

[Jsadas]

Sri,

The prompt is to decide whether the user wants to see Issues in the report or not. If the user inputs option 'N' for that prompt I will not be displaying any issues.

As indicated in my first message the Note criteria code will differentiate between Issues, Accomplishments etc., in this case 36 is for issues.

Coming to the Sysdate part, I want issues which are prior to the date the report is generated.

Please update me if this can be done in a easier and different way.

Thanks & Regards
Jags

 

[Sridharan]

Do a normal report and in the Query Panel Condition choose Note Date = and choose Calculation which will start a Wizard and there you can choose the Maximum date of Note Date and choose it it evaluate for each Project ID and also for the Criteria Code. You should be through.

Good Luck
Sri