public HttpResponseMessage GetDataPieByDimension(string dimension, string order, string[] values) { string elementsToSearch = ""; foreach (var item in values) { elementsToSearch += ($@"{dimension}.&[{item}],"); } elementsToSearch = elementsToSearch.Remove(elementsToSearch.Length - 1); string MDX_QUERY = $@" SELECT ({{ {elementsToSearch} }}) ON ROWS,{{ [Measures].[VENTAS] }}ON COLUMNS FROM [PUBS DW] "; List <string> dim = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dim, datosVenta = ventas, datosTabla = lstTabla }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { //cmd.Parameters.Add("Dimension", valoresDimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dim.Add(dr.GetString(0)); ventas.Add(dr.GetDecimal(1)); dynamic objTabla = new { descripcion = dr.GetString(0), valor = dr.GetDecimal(1) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage comparativa(DatosComparar comp) { string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string WHERE = ""; string dimension = @""; string coma = ""; foreach (var dato in comp.Nombres) { switch (comp.dimension) { case 1: dimension += coma + @"[Dim Cliente].[Dim Cliente Nombre].&[" + dato + "]"; break; case 2: dimension += coma + @"[Dim Producto].[Dim Producto Nombre].&[" + dato + "]"; break; case 3: dimension += coma + @"[Dim Producto].[Dim Producto Categoria].&[" + dato + "]"; break; case 4: dimension += coma + @"[Dim Empleado].[Dim Empleado Nombre].&[" + dato + "]"; break; } coma = ", "; } string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } * { " + dimension + " } ON COLUMNS,"; string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE; List <Comparativa> ventasUsuario = new List <Comparativa>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { int fi = dr.FieldCount; while (dr.Read()) { Comparativa v = new Comparativa(); List <decimal> listaUsu = new List <decimal>(); v.fecha = dr.GetString(1) + " " + dr.GetString(0); for (int i = 2; i < fi; i++) { decimal valor = dr.IsDBNull(i) ? 0 : dr.GetDecimal(i); listaUsu.Add(valor); } v.Datos = listaUsu; ventasUsuario.Add(v); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public HttpResponseMessage Top(Reporte info) { string nombreDimension = @""; switch (info.dimension) { case 1: //Clientes default: nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } "; break; case 2: //Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } "; break; case 3: //Categorias nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } "; break; case 4: //Empleadoss nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } "; break; } string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) "; string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, "; string ROWS = @"NON EMPTY { HEAD([TopVentas], " + info.top + ") } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string MDXQuery = WITH + "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME; List <Venta> ventasUsuario = new List <Venta>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension)); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { Venta v = new Venta(); v.mes = dr.GetString(0); v.ventas = dr.GetDecimal(1); ventasUsuario.Add(v); } dr.Close(); } } }; return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public HttpResponseMessage ventas(Filtro info) { //string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,"; string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string WHERE = ""; if (info.Item != 0 && info.Nombre != "") { string dimension = @""; switch (info.Item) { case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].&[" + info.Nombre + "]"; break; case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].&[" + info.Nombre + "]"; break; case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].&[" + info.Nombre + "]"; break; case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].&[" + info.Nombre + "]"; break; } WHERE = " WHERE " + dimension; } string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE; List <Venta> ventasUsuario = new List <Venta>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { Venta v = new Venta(); //v.ano = ; v.mes = dr.GetString(1) + " " + dr.GetInt16(0); v.ventas = dr.GetDecimal(2); ventasUsuario.Add(v); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public HttpResponseMessage GetDataBar(string[] values) { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Ventas], DESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { [OrderDimension] } ON ROWS "; string CUBO_NAME = "[DHW Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <string> dimension = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dimension, datosVenta = ventas, datosTabla = lstTabla }; string valoresDimension = string.Empty; foreach (var item in values) { if (item == "1996" || item == "1997" || item == "1998") { valoresDimension += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],"; } else if (item == "Enero" || item == "Febrero" || item == "Marzo" || item == "Abril" || item == "Mayo" || item == "Junio" || item == "Julio" || item == "Agosto" || item == "Septiembre" || item == "Octubre" || item == "Noviembre" || item == "Diciembre") { valoresDimension += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],"; } else { valoresDimension += "[Dim Cliente].[Dim Cliente Nombre].[" + item + "],"; } } valoresDimension = valoresDimension.TrimEnd(','); valoresDimension = string.Format(valoresDimension); valoresDimension = @"{" + valoresDimension + "}"; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", valoresDimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); ventas.Add(Math.Round(dr.GetDecimal(1))); dynamic objTabla = new { descripcion = dr.GetString(0), valor = Math.Round(dr.GetDecimal(1)) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage Histograma(string dim, string order = "DESC") { string dimension; List <string> clients = new List <string>(); List <string> employees = new List <string>(); List <string> products = new List <string>(); List <int> years = new List <int>(); switch (dim) { case "Cliente": dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; break; case "Producto": dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN"; break; case "Empleado": dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN"; break; case "Año": dimension = "[Dim Tiempo].[Dim Tiempo Año].CHILDREN"; break; default: dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; break; } string WITH = @" WITH SET [TopVentas] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Ventas], " + order + @") ) "; string COLUMNS = @" NON EMPTY { [Measures].[Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { HEAD([TopVentas], 10) } ON ROWS "; string CUBO_NAME = "[DHW Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result2; switch (dim) { case "Cliente": result2 = new{ datosDimension = clients, datosVenta = ventas, datosTabla = lstTabla }; break; case "Producto": result2 = new { datosDimension = products, datosVenta = ventas, datosTabla = lstTabla }; break; case "Empleado": result2 = new { datosDimension = employees, datosVenta = ventas, datosTabla = lstTabla }; break; case "Año": result2 = new { datosDimension = years, datosVenta = ventas, datosTabla = lstTabla }; break; default: result2 = new { datosDimension = clients, datosVenta = ventas, datosTabla = lstTabla }; break; } using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", dimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { switch (dim) { case "Cliente": clients.Add(dr.GetString(0)); break; case "Producto": products.Add(dr.GetString(0)); break; case "Empleado": employees.Add(dr.GetString(0)); break; case "Año": years.Add(dr.GetInt32(0)); break; default: clients.Add(dr.GetString(0)); break; } ventas.Add(Math.Round(dr.GetDecimal(1))); dynamic objTabla = new { descripcion = dr.GetString(0), valor = Math.Round(dr.GetDecimal(1)) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result2)); }
public HttpResponseMessage Top5(string dimension, int order, int top) { string orderString = ""; switch (order) { case 0: orderString = "ASC"; break; case 1: orderString = "DESC"; break; default: orderString = "ASC"; break; } string topString = ""; if (top > 0) { topString = top.ToString(); } else { topString = dimension; } var mdxQuery = $@" WITH SET [TopVentas] AS NONEMPTY( ORDER( {{ {dimension}.CHILDREN}}, [Measures].[VENTAS],{orderString})) SELECT NON EMPTY {{ ([Measures].[VENTAS]) }} ON COLUMNS , NON EMPTY {{ HEAD(TopVentas, {topString} ) }} ON ROWS FROM [PUBS DW]"; List <string> dim = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dim, datosVenta = ventas, datosTabla = lstTabla }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn)) { //cmd.Parameters.Add("Dimension", valoresDimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dim.Add(dr.GetString(0)); ventas.Add(dr.GetDecimal(1)); dynamic objTabla = new { descripcion = dr.GetString(0), valor = dr.GetDecimal(1) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage GetDataByDimension(string dim, [FromBody] dynamic values) { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Ventas], DESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { ([OrderDimension], STRTOSET(@Years), STRTOSET(@Months)) } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <string> dimension = new List <string>(); List <string> years = new List <string>(); List <string> months = new List <string>(); List <decimal> sales = new List <decimal>(); List <dynamic> tableList = new List <dynamic>(); dynamic result = new { dimensionData = dimension, yearsData = years, monthsData = months, salesData = sales, tableData = tableList }; string dimensionValues = string.Empty; Console.WriteLine(values); foreach (var item in values.clients) { dimensionValues += "{0}.[" + item + "],"; } dimensionValues = dimensionValues.TrimEnd(','); dimensionValues = string.Format(dimensionValues, dim); dimensionValues = @"{" + dimensionValues + "}"; string yearsValues = string.Empty; foreach (var item in values.years) { yearsValues += "[Dim Tiempo].[Año].[" + item + "],"; } yearsValues = yearsValues.TrimEnd(','); yearsValues = @"{" + yearsValues + "}"; string monthsValues = string.Empty; foreach (var item in values.months) { monthsValues += "[Dim Tiempo].[Mes].[" + item + "],"; } monthsValues = monthsValues.TrimEnd(','); monthsValues = @"{" + monthsValues + "}"; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", dimensionValues); cmd.Parameters.Add("Years", yearsValues); cmd.Parameters.Add("Months", monthsValues); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); years.Add(dr.GetString(1)); months.Add(dr.GetString(2)); sales.Add(Math.Round(dr.GetDecimal(3))); dynamic objTable = new { description = dr.GetString(0), years = dr.GetString(1), months = dr.GetString(2), valor = Math.Round(dr.GetDecimal(3)) }; tableList.Add(objTable); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage GetDataPieByDimension(string dim, string order, [FromBody] dynamic values) { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], DESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { ([OrderDimension], STRTOSET(@Anios), STRTOSET(@Meses)) } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; //WITH = string.Format(WITH, dim); string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <string> dimension = new List <string>(); List <string> anios = new List <string>(); List <string> meses = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dimension, datosAnios = anios, datosMeses = meses, datosVenta = ventas, datosTabla = lstTabla }; string valoresDimension = string.Empty; Console.WriteLine(values); foreach (var item in values.clients) { valoresDimension += "{0}.[" + item + "],"; } valoresDimension = valoresDimension.TrimEnd(','); valoresDimension = string.Format(valoresDimension, dim); valoresDimension = @"{" + valoresDimension + "}"; string valoresAnios = string.Empty; foreach (var item in values.years) { valoresAnios += "[Dim Tiempo].[Dim Tiempo Año].[" + item + "],"; } valoresAnios = valoresAnios.TrimEnd(','); valoresAnios = @"{" + valoresAnios + "}"; string valoresMeses = string.Empty; foreach (var item in values.months) { valoresMeses += "[Dim Tiempo].[Dim Tiempo Mes].[" + item + "],"; } valoresMeses = valoresMeses.TrimEnd(','); valoresMeses = @"{" + valoresMeses + "}"; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", valoresDimension); cmd.Parameters.Add("Anios", valoresAnios); cmd.Parameters.Add("Meses", valoresMeses); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); anios.Add(dr.GetString(1)); meses.Add(dr.GetString(2)); ventas.Add(Math.Round(dr.GetDecimal(3))); dynamic objTabla = new { descripcion = dr.GetString(0), años = dr.GetString(1), meses = dr.GetString(2), valor = Math.Round(dr.GetDecimal(3)) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage TablaComparativa(Filtro objFiltro) { string nombreDimension = @""; switch (objFiltro.Dimension) { case "1": // Clientes nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } "; break; case "2": // Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } "; break; case "3": // Categorías nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } "; break; case "4": // Empleados nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } "; break; } string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) )" ; string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,"; string ROWS = @"NON EMPTY { ( [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN, [Dim Tiempo].[Dim Tiempo Año].CHILDREN ) } * { HEAD([TopVentas],1000) } ON ROWS"; string CUBO_NAME = @"[DWH Northwind]"; string MDXQuery = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; List <string> fecha = new List <string>(); List <decimal> valor = new List <decimal>(); dynamic data; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension)); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { fecha.Add(dr.GetString(0) + "-" + dr.GetString(1)); valor.Add(Math.Round(dr.GetDecimal(3))); } dr.Close(); } } data = new { fecha = fecha, valor = valor }; } return(Request.CreateResponse(HttpStatusCode.OK, (object)data)); }
public HttpResponseMessage Top5(string dimension) { string nombreDimension = @""; switch (dimension) { case "1": // Clientes nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } "; break; case "2": // Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } "; break; case "3": // Categorías nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } "; break; case "4": // Empleados nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } "; break; default: //Dimension por Default nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } "; break; } string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) )" ; string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,"; string ROWS = @"NON EMPTY { HEAD([TopVentas],5) } ON ROWS"; string CUBO_NAME = @"[DWH Northwind]"; string MDXQuery = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Dictionary <string, decimal> structure = new Dictionary <string, decimal>(); List <string> name = new List <string>(); List <decimal> value = new List <decimal>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension)); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { //structure.Add(dr.GetString(0), dr.GetDecimal(1)); name.Add(dr.GetString(0)); value.Add(dr.GetDecimal(1)); //Debug.Write(structure); } dr.Close(); } } } var data = (name, value); return(Request.CreateResponse(HttpStatusCode.OK, data)); }
public HttpResponseMessage SerieHistoricaPost(Filtro objFiltro) { //Debug.Write(objFiltro); //return Request.CreateResponse(HttpStatusCode.OK, objFiltro); //Se puso en filtro 0 para no tomarlo en cuenta y traer todas las de la categoría, sin embargo en el MDX no funciona, se trae todas las ventas string nombreDimension = @""; if (objFiltro.Item == "0") { switch (objFiltro.Dimension) { case "1": // Clientes nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } "; break; case "2": // Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[Côte de Blaye], [Dim Producto].[Dim Producto Nombre].&[Thüringer Rostbratwurst], [Dim Producto].[Dim Producto Nombre].&[Raclette Courdavault], [Dim Producto].[Dim Producto Nombre].&[Tarte au sucre], [Dim Producto].[Dim Producto Nombre].&[Camembert Pierrot] } "; break; case "3": // Categorías nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[Beverages], [Dim Producto].[Dim Producto Categoria].&[Dairy Products], [Dim Producto].[Dim Producto Categoria].&[Confections], [Dim Producto].[Dim Producto Categoria].&[Meat/Poultry], [Dim Producto].[Dim Producto Categoria].&[Seafood] } "; break; case "4": // Empleados nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[Margaret Peacock], [Dim Empleado].[Dim Empleado Nombre].&[Janet Leverling], [Dim Empleado].[Dim Empleado Nombre].&[Nancy Davolio], [Dim Empleado].[Dim Empleado Nombre].&[Andrew Fuller], [Dim Empleado].[Dim Empleado Nombre].&[Laura Callahan] } "; break; default: //Dimension por Default nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[QUICK-Stop], [Dim Cliente].[Dim Cliente Nombre].&[Ernst Handel], [Dim Cliente].[Dim Cliente Nombre].&[Save-a-lot Markets], [Dim Cliente].[Dim Cliente Nombre].&[Rattlesnake Canyon Grocery], [Dim Cliente].[Dim Cliente Nombre].&[Hungry Owl All-Night Grocers] } "; break; } } else { if (objFiltro.Item == "Meat") { objFiltro.Item = "Meat/Poultry"; } switch (objFiltro.Dimension) { case "1": // Clientes nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } "; break; case "2": // Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].&[" + objFiltro.Item + "] } "; break; case "3": // Categorías nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].&[" + objFiltro.Item + "] } "; break; case "4": // Empleados nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].&[" + objFiltro.Item + "] } "; break; default: //Dimension por Default nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].&[" + objFiltro.Item + "] } "; break; } } string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,"; string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } * { STRTOSET(@Dimension) } ON ROWS"; string CUBO_NAME = @"[DWH Northwind]"; string MDXQuery = @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Dictionary <string, decimal> structure = new Dictionary <string, decimal>(); List <string> year = new List <string>(); List <string> month = new List <string>(); List <decimal> value = new List <decimal>(); List <string> filter = new List <string>(); dynamic data; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension)); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { year.Add(dr.GetString(0)); month.Add(dr.GetString(1)); filter.Add(dr.GetString(2)); value.Add(Math.Round(dr.GetDecimal(3))); } dr.Close(); } } data = new { years = year, months = month, filters = filter, values = value }; } return(Request.CreateResponse(HttpStatusCode.OK, (object)data)); }
public HttpResponseMessage GetDataGeneric(string dim, string[] anios, string[] meses, string[] values) { string dimension = string.Empty; switch (dim) { case "Cliente": dimension = "[Dim Cliente].[Dim Cliente Nombre]"; break; case "Producto": dimension = "[Dim Producto].[Dim Producto Nombre]"; break; case "Empleado": dimension = "[Dim Empleado].[Dim Empleado Nombre]"; break; default: dimension = "[Dim Cliente].[Dim Cliente Nombre]"; break; } string WITH = @" WITH SET [Info] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Hec Ventas Ventas], DESC ) ) "; string COLUMNS = @" { STRTOSET(@Anio) } ON COLUMNS, "; string ROWS = @" { [Info] }* { STRTOSET(@Mes) } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; List <string> dimen = new List <string>(); List <decimal> ventas = new List <decimal>(); List <string> mes = new List <string>(); List <string> anio = new List <string>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dimen, datosVenta = ventas, datosMes = mes, datosAnio = anio, datosTabla = lstTabla }; string valoresDimension = string.Empty; foreach (var item in values) { valoresDimension += "{0}.[" + item + "],"; } valoresDimension = valoresDimension.TrimEnd(','); valoresDimension = string.Format(valoresDimension, dimension); valoresDimension = @"{" + valoresDimension + "}"; string valoresMes = string.Empty; foreach (var item in meses) { valoresMes += "[Dim Tiempo].[Mes Espaniol].[" + item + "],"; } valoresMes = valoresMes.TrimEnd(','); //valoresMes = @"{" + valoresMes + "}"; string valoresAnio = string.Empty; foreach (var item in anios) { valoresAnio += "[Dim Tiempo].[Anio].[" + item + "],"; } valoresAnio = valoresAnio.TrimEnd(','); //valoresAnio = @"{" + valoresAnio + "}"; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", valoresDimension); cmd.Parameters.Add("Anio", valoresAnio); cmd.Parameters.Add("Mes", valoresMes); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimen.Add(dr.GetString(0)); ventas.Add(Math.Round(dr.GetDecimal(1))); mes.Add(dr.GetString(2)); anio.Add(dr.GetString(3)); dynamic objTabla = new { descripcion = dr.GetString(0), data = Math.Round(dr.GetDecimal(1)), label = dr.GetString(3) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage GetItemByDimension(string dim) { string dimension = string.Empty; string dim2 = string.Empty; switch (dim) { case "Cliente": dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME"; break; case "Producto": dimension = "[Dim Producto].[Dim Producto Nombre].CHILDREN"; dim2 = "[Dim Producto].[Dim Producto Nombre].CURRENTMEMBER.MEMBER_NAME"; break; case "Empleado": dimension = "[Dim Empleado].[Dim Empleado Nombre].CHILDREN"; dim2 = "[Dim Empleado].[Dim Empleado Nombre].CURRENTMEMBER.MEMBER_NAME"; break; case "Tiempo": dimension = "[Dim Tiempo].[Anio].CHILDREN"; dim2 = "[Dim Tiempo].[Anio].CURRENTMEMBER.MEMBER_NAME"; break; default: dimension = "[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; dim2 = "[Dim Cliente].[Dim Cliente Nombre].CURRENTMEMBER.MEMBER_NAME"; break; } string WITH = @" WITH SET [OrderDImension] AS NONEMPTY( ORDER( STRTOSET(@Dimension), " + dim2 + @", ASC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Hec Ventas Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { [OrderDImension] } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; List <string> clients = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = clients, datosVenta = ventas, datosTabla = lstTabla }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", dimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { clients.Add(dr.GetString(0)); ventas.Add(Math.Round(dr.GetDecimal(1))); dynamic objTabla = new { descripcion = dr.GetString(0), valor = Math.Round(dr.GetDecimal(1)) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public HttpResponseMessage GetDataByDimension(string dim, string order, string[] values) { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( STRTOSET(@Dimension), [Measures].[Hec Ventas Ventas], DESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Hec Ventas Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { [OrderDimension] } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <string> dimension = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dimension, datosVenta = ventas, datosTabla = lstTabla }; string valoresDimension = string.Empty; foreach (var item in values) { valoresDimension += "{0}.[" + item + "],"; } valoresDimension = valoresDimension.TrimEnd(','); valoresDimension = string.Format(valoresDimension, dim); valoresDimension = @"{" + valoresDimension + "}"; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", valoresDimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); ventas.Add(Math.Round(dr.GetDecimal(1))); dynamic objTabla = new { descripcion = dr.GetString(0), valor = Math.Round(dr.GetDecimal(1)) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }