How to get the values in the report !!! with different format

Question

Good day. I am new born to this VBA world. please welcome me. I got a requirement to produce the report in the below format

-------------------------------------------------------------------
        ANNUAL FISCALE REPORT - EMPLOYEE HUB
-------------------------------------------------------------------

***Year Name    Designation EmpDate       SALARY        TOTAL***

2015    TEST1   MANAGER    24/05/2015   $12,000.00  
        TEST2   VP         12/05/2015   $15,000.00  
        TEST3   VC         01/04/2015   $13,500.00      $40,500.00  

2014    TEST4   MANAGER    25/03/2014   $15,000.00      $15,000.00  

2013    TEST5   MANAGER    03/12/2013   $12,000.00   
        TEST6   VP         23/08/2013   $18,000.00      $20,000.00

I have no clue whether we can achieve the end result by writing it in single query. I wrote the basic query which generate the list of records with fields Anno (year), Data_Movimento (Date), Note description and Agent description in descending order.

SELECT CessioneCredito.Importo, CessioneCredito.Anno,
   CessioneCredito.Data_Movimento,
   CessioneCredito.Note_Liq_Cessione_Credito, 
   Agenzie.Denominazione
FROM CessioneCredito
    INNER JOIN Agenzie ON CessioneCredito.ID_Agente=Agenzie.ID_agenzia
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Data_Movimento DESC;

YEAR column: Display Value only once for the FIRST ROW for Maximum value of date field value for each set of YEAR. If it has only one record, it display that corresponding year.

TOTAL Column: Display Total Value for SALARY field only once for the LAST ROW of each set of year record. I will use SUM function to add those salary fields to get total value.

While displaying, how to make it available only for first and last record alone for each year?

SELECT Sum(CessioneCredito.Importo) AS SumOfImporto,
    CessioneCredito.Anno, Max(CessioneCredito.Data_Movimento) AS MaxOfData_Movimento,
    Min(CessioneCredito.Data_Movimento) AS MinOfData_Movimento
FROM CessioneCredito 
GROUP BY CessioneCredito.Anno, CessioneCredito.ID_Agente
HAVING (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Anno DESC , Max(CessioneCredito.Data_Movimento) DESC;

This query find the sum of amount field Importo, Max and Min value of Data_Movimento for each and every year from the Cessionecredito table in descending order.

Is there any other way to combine two queries or use DCOUNT and DSUM in the first query to get the report in the required format?

Kindly provide your best in class solutions. Appreciate your time and help.

Cya.. thanks

Holmes IV Holmes IV · Accepted Answer · 2015-07-02T20:32:51

To do this you need to first setup a groups based on the years. Then you need to setup your totals to sum based on the group. This is all done in ssrs with no changes to your SQL query.

How to get the values in the report !!! with different format

1 Answers