how to make this report?

on a timesheet system, if I want a sum of a user on each project over a given period, how can I do this without creating a view? I mean creating a view will just do the sum for that given period, but not prompting the user for the period.

the table looks like this:

empid, projid, hours,closingDate

now I want the sum of the hours on two closing dates, can crystal report prompt the user for two closing dates then do the sum for each employee on each project?You want to sum the hours on 2 closing dates.
Create 2 parameters for each closing date (in 8.0: Insert | Field Object ... on the menu, then create a new parameter value for each date that the user can enter. Be sure to select Date for data type.)

You want the report to prompt the user, so use the parameters in the report and in the select statement or Select Expert. If you named your parameters Date1 and Date2: ({closingDate} = {?Date1}) or ({closingDate} = {?Date2}).

Insert a Group on the ID.
Insert a Group under that on the projid.
Sum the hours for all groups and you will get a total for each projid and a total for the id.

Format your report to make it look like you want it to.

I hope this helps!
Kymberlie|||thx kstefanski

How to limit the MDX dataset for the last six months

I have the following MDX query and I'd like to put in another condition that the "[Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS" has to be GREATER than OR EQUAL TO last six months. Eg. using today date as 21-Nov-2006 and the dataset should only include the records from 21-May-2006 onward.

SELECT NON EMPTY { [Measures].[Pharmacy DW Count] } ON COLUMNS, NON EMPTY { ([AgencyID].[Agency Id].[Agency Id].ALLMEMBERS * [Account Period].[True Prescription Date].[True Prescription Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.DrugProtocolCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DrugDrugName, CONSTRAINED) ) ON COLUMNS FROM [Patient Hospital and Drug])) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Thanks

It depends how you build your True Prescription Date attribute. If we will assume, that it is rebiult daily, and the last attribute member is today's date (i.e. no dates go to the future), then it would be something like

Lag([Account Period].[True Prescription Date].[All True Prescription Dates].LastChild, 182):[Account Period].[True Prescription Date].[All True Prescription Dates].LastChild)

If the rules are more complex, then it is probably best to build named set inside MDX Script which will resolve to the last 6 months worth of dates, and use it instead in the queries.