如何在DevEx Reports和Sql中获得每月销售分析 [英] How to get Monthly Sales Analysis in DevEx Reports and Sql

问题描述

如何按金额和数量检索每月销售分析订单。



我已在Sql中编写此查询但我使用Pivot获得Sum（Qty）。我只想要数量



和金额不等。

How to retreive Monthly Sales Analysis Order By Amount And Qty.

I have Written this Query in Sql but I am getting Sum(Qty) using Pivot. I want only Qty

and Amount Not Sum.

SELECT *
FROM (SELECT QTY,AMOUNT,PRODUCT_NO,AC_NO,
       DATENAME(MONTH, DATE_CREATED) [Month]
      FROM invINVOICEDETAILS where DATENAME(MONTH, DATE_CREATED)  in ('January','February','March','April','May',
    'June','July','August','September','October','November',
    'December')
      GROUP BY QTY,AMOUNT,PRODUCT_NO,AC_NO,
      DATENAME(MONTH, DATE_CREATED)) AS MontlySalesData
      PIVOT(sum([QTY])
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS MNamePivot

我想要检索数量，Product_No，AC_NO和金额A根据当年的销售额计算。在报告中我想显示所有月份，即1月到12月，并根据Prooper月份的销售情况打印基于Product_no的数量和金额。



例如：我已售出在1月份的两个产品。同样我在Febrauary月销售了3个产品。



现在我想显示1月份的数量和金额Febrauary。



同样我想在报告中显示所有月份的空值。



这里我想要显示从1月到12月的所有列。



请你建议我正确查询。

I want to retreive Qty,Product_No,AC_NO and Amount According to the sales in months in the year. In Reports I want to Display All Months i.e January to December and also print Qty and Amount based on Product_no according to sales in Prooper Months.

For Example: I have sold two products in the month of january.Similarly I sold 3 products in the month of Febrauary.

Now I want to display those Qty and Amount For the months of January and Febrauary.

Similarly I want to Display All Months in the report with Null values.

Here I want to display all columns from january to December.

Can u please suggest me right query for this.

推荐答案

如果我理解正确，那么您正在寻找一种解决方案，其中列数会根据每月销售的产品而有所不同。如果这是真的，那么SQL就不是你应该这样做的地方。所有格式都应该在主叫方面完成，而不是在查询中。



如果不了解您的环境，我建议您查询详细程度希望在报告中显示数据并让报告工具处理可视化。

If I understood you correctly, you're searching for a solution where the amount of columns would vary based on the products sold each month. If this is true, then SQL is not the place where you should be doing this. All formatting should be done on the calling side, not in the queries.

Without knowing anything more about your environment, I suggest that you query on the level of detail you wish to show data on your report and let the reporting tool handle the visualization.

这篇关于如何在DevEx Reports和Sql中获得每月销售分析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！