Confirm

Expand all | Collapse all

Confirm sql Month and Year

  • 1.  Confirm sql Month and Year

    Posted 09-17-2019 06:16
    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 09-17-2019 09:01
    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 09-17-2019 09:52
      |   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 09-17-2019 14:41
    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 09-18-2019 05:03
      |   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 09-18-2019 11:57
    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
    ------------------------------



  • 7.  RE: Confirm sql Month and Year

    Posted 10-17-2019 09:46
    Have you sorted this ??

    Try this kind of stuff where you have what was required.:
    ===================================================
    SELECT
    DATEPART(month,central_enquiry.log_effective_date) 'Month',
    DATEPART(year, central_enquiry.log_effective_date) 'Year'
    FROM
    central_enquiry
    WHERE
    central_enquiry.log_effective_date > = 'OCT-15-2019'
    ===================================================

    ------------------------------
    Barry Jones
    Cheshire West and Cheshire
    Winsford
    ------------------------------



  • 8.  RE: Confirm sql Month and Year

    Posted 10-17-2019 10:50
    Thanks

    ------------------------------
    Afzal Hussain
    Specialist Application Support Officer
    London Borough of Barking and Dagenham
    ------------------------------



  • 9.  RE: Confirm sql Month and Year

    Employee
    Posted 10-23-2019 10:45
    Please be aware that the Dashboard Category Expression will sort based on Alphanumeric's given, meaning April will be first on the far left so to speak.

    See following KB Article, Can you display dates chronologically within Dashboard Widget Categories in Confirm? for possible workarounds.

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    PITNEY BOWES SOFTWARE, INC
    ------------------------------