Insert function in title (text field)?

[Former Member]

Hi everybody,

my report period is e.g. for last month (April) in the SQL-Statement.

Is there a way the concerning period automatically could be inserted in the report title (in a text field or whatever), e.g. "Number of incidents assigned by Service Desk in ......".

I hope it´s understandable what I am asking for?

Thanks in advance.

Rgds

Eberhard

[abhilash_kumar · Accepted solution]

Hi Eberhard,

Assuming the report only returns data for one month, you can create a formula with this code:

'Number of incidents assigned by Service Desk in ' & Totext(Maximum({Date Field}), 'MMMM')

Drag and drop this formula field on the header.

-Abhilash

[abhilash_kumar · Accepted solution]

I just realized that Maximum might return the first day of current month per your code.

In that case, just change the formula to:

'Number of incidents assigned by Service Desk in ' & Totext(Minimum({Date Field}), 'MMMM')

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

if I use your formula code, I get an error message about the Date Field, when I want to save.

[abhilash_kumar · Accepted solution]

What is the error you receive?

-Abhilash

[Former Member · Accepted solution]

"Unknown Field Name".

I guess, because I don´t know which Date Field I need to use.

Eberhard

[abhilash_kumar · Accepted solution]

Use the INCIDENT.DATE_LOGGED field.

The formula again:

'Number of incidents assigned by Service Desk in ' & Totext(Minimum({INCIDENT.DATE_LOGGED}), 'MMMM')

-Abhilash

[Former Member · Accepted solution]

Unfortunately still "Unknown Field Name" and the "INCIDENT.DATE_LOGGED" is highlighted.

Eberhard

[abhilash_kumar · Accepted solution]

What date fields do you have on the report?

Use any one that returns dates within the chosen period.

-Abhilash

[Former Member · Accepted solution]

I can only find the INCIDENT.DATE_LOGGED.

here is the query:

SELECT

    INCIDENT_ID,

    COUNT(ACT_TYPE_ID) - (SELECT COUNT(*) FROM act_reg a2 WHERE act_reg.incident_id = a2.incident_id AND act_type_id = 6) AS ZUWEISUNGEN

  FROM

    ACT_REG

  WHERE

    ACT_TYPE_ID = 1 AND

    (ACT_REG.ASSYST_USR_ID != '473' or ACT_REG.ASSYST_USR_ID != '712' or ACT_REG.ASSYST_USR_ID != '323' or ACT_REG.ASSYST_USR_ID != '780') AND

    ACT_REG.SERV_DEPT_ID = 28 AND

    ACT_REG.ASS_SVD_ID <> 28 AND

    INCIDENT_ID IN (

      SELECT

        INCIDENT.INCIDENT_ID

      FROM

        INCIDENT

      LEFT OUTER JOIN INC_DATA ON

        INCIDENT.INCIDENT_ID = INC_DATA.INCIDENT_ID

      WHERE

        INCIDENT.DATE_LOGGED BETWEEN add_months((last_day(SYSDATE))+1,-6) AND add_months((last_day(SYSDATE))+1,-1) AND

        (CSG_ID = '1' OR CSG_ID = '7') AND

        INC_DATA.EVENT_TYPE = 'i' AND

        (INCIDENT.INC_STATUS = 'c' OR INCIDENT.INC_STATUS = 'p')

    )

GROUP BY INCIDENT_ID

ORDER BY ZUWEISUNGEN ASC

So the query returns only two fields in CR, which are INCIDENT_ID and ZUWEISUNGEN.

Sorry for bothering you so much!!!

[former_member205840 · Accepted solution]

Hi Eberhard,

Modify your SQL as below :  added start date and end date to SQL

SELECT

    INCIDENT_ID,

    COUNT(ACT_TYPE_ID) - (SELECT COUNT(*) FROM act_reg a2 WHERE act_reg.incident_id = a2.incident_id AND act_type_id = 6) AS ZUWEISUNGEN, add_months((last_day(SYSDATE))+1,-6) AS START_DATE, add_months((last_day(SYSDATE))+1,-1) AS END_DATE

  FROM

    ACT_REG

  WHERE

    ACT_TYPE_ID = 1 AND

    (ACT_REG.ASSYST_USR_ID != '473' or ACT_REG.ASSYST_USR_ID != '712' or ACT_REG.ASSYST_USR_ID != '323' or ACT_REG.ASSYST_USR_ID != '780') AND

    ACT_REG.SERV_DEPT_ID = 28 AND

    ACT_REG.***_SVD_ID <> 28 AND

    INCIDENT_ID IN (

      SELECT

        INCIDENT.INCIDENT_ID

      FROM

        INCIDENT

      LEFT OUTER JOIN INC_DATA ON

        INCIDENT.INCIDENT_ID = INC_DATA.INCIDENT_ID

      WHERE

        INCIDENT.DATE_LOGGED BETWEEN add_months((last_day(SYSDATE))+1,-6) AND add_months((last_day(SYSDATE))+1,-1) AND

        (CSG_ID = '1' OR CSG_ID = '7') AND

        INC_DATA.EVENT_TYPE = 'i' AND

        (INCIDENT.INC_STATUS = 'c' OR INCIDENT.INC_STATUS = 'p')

    )

GROUP BY INCIDENT_ID

ORDER BY ZUWEISUNGEN ASC

Now you will see 4 fields in your field explorer.  Create a formula like :

'Number of incidents assigned by Service Desk in ' &Totext({START_DATE},'dd/MM/yyyy')&''

&Totext({END_DATE}, 'dd/MM/yyyy')

Thanks,

Sastry

[abhilash_kumar · Accepted solution]

You'd need to include the INCIDENT.DATE_LOGGED field in the select and group by clause.

-Abhilash

[Former Member · Accepted solution]

Hi Sastry,

thks so much, I finally got it to run, altough the solution is very much db time consuming (every value it finds, it adds start and end time). But for the moment, I am fine.

brgds

Eberhard

[Former Member · Accepted solution]

Hi Abilash,

I tried that, but I couldn´t find the solution so fast (how can I use a group by clause for two db fields?

Nevertheless, right now I am fine with that.

Brgds

Eberhard

[Former Member · Accepted solution]

by the way Sastry,

the communicator has eaten part of your code, because the fields name was (ass   igned)............

and I did not realize it immediately, so I was wondering why the code did not work in the beginning 😉

rgds

Eberhard

[former_member205840 · Accepted solution]

Hi Eberhard,

try this :

SELECT

    INCIDENT_ID,

    COUNT(ACT_TYPE_ID) - (SELECT COUNT(*) FROM act_reg a2 WHERE act_reg.incident_id = a2.incident_id AND act_type_id = 6) AS ZUWEISUNGEN,add_months((last_day(SYSDATE))+1,-6) AS START_DATE,add_months((last_day(SYSDATE))+1,-1) AS END_DATE

  FROM

    ACT_REG

  WHERE

    ACT_TYPE_ID = 1 AND

    (ACT_REG.ASSYST_USR_ID != '473' or ACT_REG.ASSYST_USR_ID != '712' or ACT_REG.ASSYST_USR_ID != '323' or ACT_REG.ASSYST_USR_ID != '780') AND

    ACT_REG.SERV_DEPT_ID = 28 AND

    ACT_REG.***_SVD_ID <> 28 AND

    INCIDENT_ID IN (

      SELECT

        INCIDENT.INCIDENT_ID

      FROM

        INCIDENT

      LEFT OUTER JOIN INC_DATA ON

        INCIDENT.INCIDENT_ID = INC_DATA.INCIDENT_ID

      WHERE

        INCIDENT.DATE_LOGGED BETWEEN add_months((last_day(SYSDATE))+1,-6) AND add_months((last_day(SYSDATE))+1,-1) AND

        (CSG_ID = '1' OR CSG_ID = '7') AND

        INC_DATA.EVENT_TYPE = 'i' AND

        (INCIDENT.INC_STATUS = 'c' OR INCIDENT.INC_STATUS = 'p')

    )

GROUP BY INCIDENT_ID,

add_months((last_day(SYSDATE))+1,-6) ,

add_months((last_day(SYSDATE))+1,-1)

ORDER BY ZUWEISUNGEN ASC

I have added start and end date fields to group by caluse.  I hope this will resolve your issue.

Thanks,

Sastry

[Former Member · Accepted solution]

Great, I applied it already.

Thank you so much.

Eberhard

[Former Member]

Yes its possible!!

Insert a Text Field and then just Drag the SQL expression or Formula on that Text Field it will get embedded,

Regards

Niraj

[Former Member]

Hi Niraj,

thank you for your reply.

What does it mean for a beginner?

My period is defined like this: INCIDENT.DATE_LOGGED BETWEEN add_months((last_day(SYSDATE))+1,-2) AND add_months((last_day(SYSDATE))+1,-1)

How do I proceed?

Thanks.

Eberhard

[Former Member]

Ok so Create a Formula lets say with Name Test Where you are getting your periods based on your Calculation or what Abhilash suggested.

Then Insert a Text Field on your report at desired location type your Text on that Text Box "Number of incidents assigned by Service Desk in" and then just pull that formula from the Formula section and drop it on that text field you created So you will see something like

Number of incidents assigned by Service Desk in {@Test} in your text box in design mode. When you run this report the value returned by that formula will get displayed along with the text.

Another solution what Abhilash suggested is also a way to get that done

Regards

Niraj