Confirm

Expand all | Collapse all

Confirm sql Month and Year

  • 1.  Confirm sql Month and Year

    Posted 28 days ago
    I have created a Dashboard widget and i want to display the widget in Month and year using the log_effective_date field, can you please advise what expression i can use to achieve this.

    Many Thanks

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------


  • 2.  RE: Confirm sql Month and Year

    Posted 28 days ago
    Hi Afzal,

    I use the following code for sorting by month and year on a Dashboard.

    CONVERT(VARCHAR, DATEADD(mm, DATEDIFF(mm, 0, central_enquiry.log_effective_date) + 0, 0), 111)

    This calculates the 1st day of month and changes to format YYYY/MM/DD.

    There is an enhancement Idea which explains why the format has to change when used on a Dashboard.

    https://ideas.pitneybowes.com/ideas/CONFIRM-I-1567

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 3.  RE: Confirm sql Month and Year

    Posted 28 days ago
      |   view attached
    Thanks Hafizur,

    Where do i put this code in? maybe i wasn't clear in my query.  I want to display widgets in Month and year on dashboard (see attached diagram)

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------

    Attachment(s)

    pdf
    Dashboard1.pdf   75K 1 version


  • 4.  RE: Confirm sql Month and Year

    Posted 28 days ago
    Hi Afzal,

    You will need to add the code to your SELECT statement, so that it is part of the SQL query.

    Your widget needs to use the 'Column - Stacked' presentation style, which will activate the Series Expression field, where you can then select the new 'date' field you added in above.

    You may want to consider switching the Category & Series Expression fields, so that the 'date' field goes along the x axis and the 'Enq Status Name' field as y axis. you can try this to see which best suits your requirement.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------



  • 5.  RE: Confirm sql Month and Year

    Posted 27 days ago
      |   view attached
    Thanks Hafizur,

    I have amended my datasource and a compute field was created which is brilliant and i can work with that.  However, when i run the datasource it is displaying the 20190601 as a compute field, it this meant display this at this stage and it will display correct date when i amend the widgets / report etc.  i was trying to display the date and month from the log effective date from customer enquiry table.

    Afzal

    ------------------------------
    Afzal Hussain
    London Borough of Barking & Dagenham
    Dagenham
    ------------------------------



  • 6.  RE: Confirm sql Month and Year

    Posted 27 days ago
    Hi Afzal,

    You can specify an alias name to the date field, e.g.

    CONVERT(VARCHAR, DATEADD(mm, DATEDIFF(mm, 0, central_enquiry.log_effective_date) + 0, 0), 111) AS log_effective_month

    or call it anything you think applicable, in the SQL.

    You can also specify a name in the SQL Data Source Column Name field, which will override any field names in the SQL.

    Looking at the results you attached in your last response, it looks like the new calculated month date field is not matching the log_effective_date field. Looks like you may need to change the calculated field from central_enquiry.log_effective_date to enquiry_status_log.log_effective_date if you need this at the enquiry status log level.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Application Specialist
    Richmond and Wandsworth Councils
    ------------------------------