public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory) { ViewWeekofyearGivenDistanceDurationCategories model = new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory); List <BikeTable> hierarchy = new List <BikeTable>(); hierarchy.Add(new TableWeekday()); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS * [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS * [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare]))) WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1, STRTOSET(@TripCatTripCategory, CONSTRAINED), [TripCat].[Trip Category].currentmember ), IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1, STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED), [Station Pair Distance].[Mile Categories].currentmember ), [Direction].[Direction].&[A-B] )"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return(model); }
public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory) { ViewWeekofyearGivenDistanceDurationCategories model = new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory); List<BikeTable> hierarchy = new List<BikeTable>(); hierarchy.Add(new TableWeekday()); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS * [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS * [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare]))) WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1, STRTOSET(@TripCatTripCategory, CONSTRAINED), [TripCat].[Trip Category].currentmember ), IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1, STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED), [Station Pair Distance].[Mile Categories].currentmember ), [Direction].[Direction].&[A-B] )"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return model; }