nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Question on Report # 2

nuraniuscc

Hi Mike,

Here, I have a parameter called Cycle Month. So I go and plug in all the values from 01 - 12. Now, This value should be converted and shown on the
report Header as

Jan, Feb etc.

So, I put the "Display Text" as 01 and the Value as January

2 Questions

1) What does Data Type refer to? The Text or the Value
2) When I "Preview" the report, The Header value is substituted Ok, But I also
get the following error message.

The following items have errors:

Table (id = 22):
+ An exception occurred during processing. Please see the following message for details:
Cannot convert the parameter value January to type class java.lang.Integer.
Can not convert the value of January to Integer type.

Please explain

Thanks
Nurani Sivakumar

Find more posts tagged with

Comments

nuraniuscc

Hi Mike,

I have a report where the report columns should be populated base on a condition. The condition is based on a Datbase field which is NOT one of the parameters that the user can enter.

So, In my SQL, I am retrieving the column and I am also GROUPING BY that field. BEFORE I populate my Grid, I need to check to make sure that it's for the right field.

Eg : I have 6 markets spread across a GRID. So, when I retrieve a row I need to make sure it's the right market before I plop that value in.

How do I do that? Basically validate BEFORE you populate the field.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

On the parameter value. The actual value field is what the type should correspond with. If you need the integer value to pass to your database, but want to display the string version. Make the parameter integer type, put the values in as, 1,2,3...etc. and the display value as Jan, Feb, Mar...etc. Then when you display the value on your report, use the following in a type string data item:

params["paramName"].displayText

This should get you what you're wanting I believe.

mwilliams

Can you explain more about post #2? Maybe with data and what you want it to look like. I'm not sure that I'm completely understanding.

nuraniuscc

Hi Mike,

My report looks like

Date M01 M02 M03 M04 M05 M06

---- ---- ----

I am selecting rows and GROUP BY market.. I need to make sure that the total
of each row that I am looking at is for the right market.

Eg: If I read that first row and I find the count is for M02, I need to know
that so that the count can be populated under the right column.

Thanks
Nurani Sivakumar

nuraniuscc

Hi Mike,

I made the changes as you suggested to have the TEXT displayed. Got that.

Now, When I do a Preview, It still complains about SQL ORA-933. Here is my script.

1) I have the BASE sql
2) This script (With GROUP BY and ORDER BY)

Tell me what you see might be causing this issue.

if(params["Market"].value != 'ALL'){
this.queryText = this.queryText + " where DATACENTER in (" + reportContext.getParameterValue("Market") + ")";
this.queryText = this.queryText + " and A.ERRORNO = B.EXPERTKEY";
}

if(params["Cycle Month"].value != 'ALL'){
this.queryText = this.queryText + " where CYCLE_RUN_MONTH in (" + reportContext.getParameterValue("Cycle Month") + ")";
this.queryText = this.queryText + " and A.ERRORNO = B.EXPERTKEY";
}

if(params["Cycle Day"].value != 'ALL'){
this.queryText = this.queryText + " where CYCLE_RUN_DAY in (" + reportContext.getParameterValue("Cycle Day") + ")";
this.queryText = this.queryText + " and A.ERRORNO = B.EXPERTKEY";
}

if(params["Cycle Year"].value != 'ALL'){
this.queryText = this.queryText + " where CYCLE_RUN_YEAR in (" + reportContext.getParameterValue("Cycle Year") + ")";
this.queryText = this.queryText + " and A.ERRORNO = B.EXPERTKEY";
}

{
this.queryText = this.queryText + " group by datacenter, CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR,TO_DATE(UPDATEDATE, 'DD-MON-YY') - TO_DATE(CREATEDATE, 'DD-MON-YY')";
this.queryText = this.queryText + " order by DATACENTER, CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR ";
}

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Like you said before, you're ending up with multiple where, group by, and order by statements in your query. That is probably the issue. You'll need to package all of the where statements into 1 string without the "where" part, then add the where statement to the query separately if that string is not null and then add the group by and order by statements after all of that is done.

mwilliams

Nurani,

For the data going under the correct column thing, are you using a crosstab or a table? If you're doing a columnar/parallel type report, you'd have to use filters. For a crosstab, as long as M1, M2, etc is associated with the value in the row, it should put it in the right place. I'm still not sure that I understand your entire setup on this item.

nuraniuscc

Hi Mike,

One step at a time. I created a CROSSTAB and pulled in the columns I want to be shown as part of the Row and Column.

How do I "Link" or say which data or paramter I want to be shown on the crosstab. I clicked on the "Edit expression" window and thought I need to
"Point" to the right data there.

i.e.,

I have a Begin Month and End Month as my parameters. How do I have my CROSSTAB "know" that?

Hope you understand what I am asking?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

For a crosstab, if you have the dataset limited by the parameter to only include a certain range of dates, the crosstab will only display those months of data. If you don't limit the dataSet with the parameters, you'll need to use a filter on the crosstab using the parameters.

nuraniuscc

Hi Mike,

Still not sure. What I want is this?

My input parameter has a Begin Date and an End Date.

1) I need to validate to make sure the difference is no more than 6 months.

2) Once I do That, For Eg: If they are Jan 2008 and Mar 2008

My Cross Tab column headings should Read as

Jan-08 Feb-08

and the data for them like (This value comes from SQL)
53 77

How do I connect the Crosstab column to the data I already have from the SQL.

(Is it like the Regular Drag and Drop? We can't do that here because it's all
Dynamic)

Please tell me How do I accomplish this (Step by Step)

Thanks
Nurani Sivakumar

mwilliams

Nurani,

To create a crosstab, you'll need to create a dataCube. One way to create a cube is the following:

Insert a crosstab element in your table

Drag a field from the dataSet you want to use in the "data explorer" over to the crosstab.

A box will pop up to create a dataCube. You drag values from the box on the left to either a dimension area, like your date and whatever you want on the left of the report, and the measures area, which is what you're going to be displaying in the middle.

Then, you click ok, expand out the dataCube in the data explorer and drag the dimensions and measures from there to your crosstab in the report where they belong. I believe I made an example report in the first thread showing a crosstab.

nuraniuscc

Hi Mike,

I believe I asked this question earlier. I couldn't find it though.

I need to have my Header Read "For M01 January 2009". Ok, I am getting it as
"For M01 January 2009".

My question is How to I eliminate the spaces inbetween so it reads nicely. It's
about Formatting.

Thanks
Nurani Sivakumar

mwilliams

Nurani,

How are you displaying it? What type of report item and what expression?

nuraniuscc

Hi,

This is on Parm Text and Value

Is this correct?

To display Value : Say params["Cycle Month"].value
To display Text : Say params["Cycle Month"].text

Please confirm

mwilliams

Nurani,

Yes, except '.text' should be '.displayText' I believe.

nuraniuscc

Hi Mike,

They are defined as Dynamic Text because they come from the Input Parameters. The expressions are

params["Market"].text

params["Cycle Month"].displayText

params["Cycle Year"].value

Thanks
Nurani Sivakumar

mwilliams

Nurani,

So, you're calling them in defferent text boxes?

nuraniuscc

Yes, You are right

mwilliams

Nurani,

You can call them in one dynamic text box like:

"For " + params["Market"] + " " + params["Cycle Month"].displayText + " " + params["Cycle Year"];

That should solve that problem. Let me know.

nuraniuscc

Hi Mike,

This is the SQL I am contending with. Look at the "undefined" word. I am not sure why it is there.

Can we not use JOINS or Is there a specific way to do this?

Thanks
Nurani Sivakumar

select DATACENTER, CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR as CYCLE, TO_DATE(UPDATEDATE, 'DD-MON-YY') - TO_DATE(CREATEDATE, 'DD-MON-YY') from TBLRMSOLUTIONS A, TBLRMREJECTLIST B where A.ERRORNO = B.EXPERTKEYundefined and DATACENTER in ('M01') and CYCLE_RUN_MONTH in (08) and CYCLE_RUN_DAY in (02) and CYCLE_RUN_YEAR in (2008) group by DATACENTER, CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR,TO_DATE(UPDATEDATE, 'DD-MON-YY') - TO_DATE(CREATEDATE, 'DD-MON-YY') order by DATACENTER, CYCLE_RUN_MONTH || '/' || CYCLE_CODE || '/' || CYCLE_RUN_YEAR

nuraniuscc

if(params["Begin Month"].value != 'ALL') and
(params["End Month"].value != 'ALL') and
(params["Begin Month"].value <= params["End Month"].value)
and
(params["Begin Range Year"].value != 'ALL') and
(params["End Range Year"].value != 'ALL') and
(params["Begin Range Year"].value <= params["End Range Year"].value){

This is the IF..THEN...ELSE I coded in my Script. Ok, the 2nd line is flagged in error saying "Missing ;".

What would that be?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Just as a note, java and javascript syntax documents can be found all over the internet by searching on google, yahoo, etc. For an if statement like the one you are talking about, it needs to be set up like this:

if ((arg_1) && (arg_2) && (arg_3)..etc){
what you want to do;
}
else{
what you want to do;
}

nuraniuscc

Hi Mike,

Yes I went to Google and found that out. Could you please answer to the
SQL question I posted couple of posts before.

What is the "Undefined" means and How to get rid of it?

Thanks
Nurani Sivakumar

mwilliams

Nurani,

I'm not sure on the undefined thing. Can you send me the script that you build the query with for that, so I can see what's happening in the script when the "undefined" is added to the queryText?

nuraniuscc

Hi,

Ok, I am using an if condition in the edit box of one of the elements. Basically,
I want to populate each cell based on a condition. This is how I coded it in the Expression Builder window. Basically, look at each row and if the condition is met populate the cell else make it spaces ot zeros.

if (row["DATACENTER"] = 'M01'){
dataSetRow["SUM(NVL(TO_DATE(UPDATEDATE,DD-MON-YY)-TO_DATE(CREATEDATE,DD-MON-YY),0))"];
}

Is this right. I did validate it and no errors. But when I preview the report I am getting the following error message.

nestedexceptionis:jave.lang.illegalArgument:put value on resultset row is not supported

Thanks
Nurani Sivakumar

mwilliams

Nurani,

Try:

if (row["DATACENTER"] == "M01"){
dataSetRow["SUM(NVL(TO_DATE(UPDATEDATE,DD-MON-YY)-TO_DATE(CREATEDATE,DD-MON-YY),0))"];
}