locked
How to create a monthly summary of invoices RRS feed

  • Question

  • Hello,

    I have created a small vb.net app using sql server that creates and saves the daily invoices in the database. In this app I have created a report that user can select from any date to any date to create statements. They bill their customers using monthly statements.

    Now I am required to change the report to add a column to display just the summary of the previous statements if they have not been paid yet.

    I know sql has triggers. The solution I came up with is that I create a table and a trigger. At the begging of the each month the trigger would cause the date(month and year), summary and a column for paid statements (yes/no) of the previous month to be saved in the new table. Then the report would check if previous statements are paid or not and if they are not paid then the current report would display the month/year and summary of unpaid statements also.

    I am not sure if this is a correct approach or not. I would appreciate any input.

    Savalan

    Friday, July 27, 2018 1:45 PM

Answers

  • Looks like Power Bi IS  your friend here

    But if you post sample data along with desired result we can provide some T-SQL solution...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 29, 2018 7:30 AM
  • Hi Savalan,

    Power BI is a Interactive Data Visualization BI Tools. We can use it to create reports just like SQL Server Reporting Service, but it is more powerful. 

    Anyhow, at the first place if you can share us your table structure and some sample data along with your expected result. It'll help us a lot to understand your requirement more clearly and provide some SQL Server side solutions since this is a SQL Server forum.

    And if you decide to use Power BI and have some Power BI side issues. Power BI forum is the right place.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 30, 2018 8:19 AM

All replies

  • Looks like Power Bi IS  your friend here

    But if you post sample data along with desired result we can provide some T-SQL solution...


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 29, 2018 7:30 AM
  • Hi Uri,

    I appreciate the reply. I will take a look at Power Bi to see what it is and how I can use it to fix my problem. I will update you.

    Regards,

    Savalan

    Sunday, July 29, 2018 6:30 PM
  • Hi Savalan,

    Power BI is a Interactive Data Visualization BI Tools. We can use it to create reports just like SQL Server Reporting Service, but it is more powerful. 

    Anyhow, at the first place if you can share us your table structure and some sample data along with your expected result. It'll help us a lot to understand your requirement more clearly and provide some SQL Server side solutions since this is a SQL Server forum.

    And if you decide to use Power BI and have some Power BI side issues. Power BI forum is the right place.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 30, 2018 8:19 AM
  • Hi Xi Jin,

    Thank you for the reply. I will post what you requested, asap. 

    As for the application I've created, the user can select 2 dates (From, To) using 2 DateTimePickers and a customer from a combobox and then report produces the correct statement. I will explain in detail the table structure and how I retrieve the data from it.

    Best regards,

    Savalan

    Monday, July 30, 2018 1:57 PM
  • Hi Savalan,

    Did you get the sample data? Since we can' see your actual situation. It's hard for us to understand your requirement with only textual explanation.

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 3, 2018 7:39 AM