several date ranges in one report - first/last function?
Hello, I´m working on a report with SSRS 2008 which is meant to show a lot of figures aggregated for the whole calendar year. In all datasets I have the date as a parameter (date with hierarchy: year - month). So to get the sums for the whole calendar year I only have to select all the months of the wanted year and SSRS shows the total for that period. That works fine. The problem: Some figures (for which separate datasets are used) are not meant to be aggregated, eg the number of open cases at the beginning of the period or the number of open cases at the end of the period. I tried using the First/Last-functions on that figures but that doesn´t work because the first/last function doesn´t seem to refer to the date-parameter but to other dimensions (in my case: region because it shows the summed up values for the first region instead of the not-aggregated values for the first month). How can I acchieve that my report shows the first/last value dependent on the choosen date-parameters while summing up the other values for the whole choosen date-parameters? Your help would be much appreciated. NuramonTheWanderer
October 7th, 2010 12:13pm

Perhaps you meant MIN/MAX aggregate functionsBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 1:12pm

Hello Mr Dimant, no, the Min/Max functions do not work for me either, because they deliver only the lowest/highest value of all values in the choosen date-range. But I need the report to show precisly the value for the first/last date of the choosen date-range. Regards NuramonTheWanderer
October 7th, 2010 1:44pm

Well fist date based /order by what? SELECT MIN(datecolumn) FROM ...WHERE dt>...AND dt<.... .will return FIRST datafor given WHERE condition If the above does not help please post sample query you are running and tell us what is the result? Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 1:57pm

Hi Nuramon, Are you sure you're not aggregating the data based on regions in your sql query. Are you sure you're retrieving the data at the lowest level for the regions and then showing it in your report? From what I understand, ideally the First()/Last() functions should suffice your needs. Regards, ShalinShalin P. Kapadia
October 7th, 2010 2:21pm

The MDX-Query which works perfectly if only one month is selected by the date-parameter @DimMonthYearYearMonth looks like: SELECT NON EMPTY { [Measures].[Cases] } ON COLUMNS, { ([Dim Region Team].[Region - Team].[Region].ALLMEMBERS * [Dim Region Team].[Team].[Team].ALLMEMBERS * [Dim Region Team].[Region Nummer].[Region Nummer].ALLMEMBERS * [Dim NewCase].[NewCase].[NewCase].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Dim NewCase].[NewCase].&[2] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimMonthYearYearMonth, CONSTRAINED) ) ON COLUMNS FROM [NewCustomers])) WHERE ( IIF( STRTOSET(@DimMonthYearYearMonth, CONSTRAINED).Count = 1, STRTOSET(@DimMonthYearYearMonth, CONSTRAINED), [Dim Month Year].[Year - Month].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS What I´d like to know is how to chance it, so that if serveral months are selected by the date-parameter @DimMonthYearYearMonth, the query gives back only the value of the measure "Cases" for the first of the selected months.
Free Windows Admin Tool Kit Click here and download it now
October 7th, 2010 2:35pm

Hello Shalin, you are right: The region is not the lowest level. The hierarchy is Region - Team.I retrieve the data at Team-Level but in the report only the region and the total over all regions are to be displayed. I tried the First-/Last-functions and they delivered only the result for the very first/last team and that value was not - as wanted - the value for the first selected month but a total for all selected months which is exactly what I don´t want. I need the "first/last"-functions to refer to the selected months and not the array of values of the measure "Cases". For that purpose I´m using the parameter @YearMonthMonthYear which refers to the Dimension "Month Year". Regards Nuramon
October 7th, 2010 2:58pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics