An error ocurred at or near the position '0'. Please Help =(

[shougki]

Dear Cognos Experts,

Pls help....i am only a business student who got assigned an IT job for my school attachment.
i been task to do a project, for some reason unknown i keep getting error :"An error ocurred at or near the position '0'. The variable named '[Current Month A].[GOR Actual]' is invalid."

i check theres nothing wrong with the expression in gor actual, i try googling the error, and from there ( report page has 3 pages Region, Area and province) the above expression is in "area" page", i try clicking the ancestral button on the page and changing properties to define "area" as well as changing master detail relationship to area > area....at the page explorer i try changing their properties to area and master detail r/s to area too, but still cant =(

i have attach a pic of report page explorer too,so you guys can have a better idea what i am talking....please pardon me if you do not understand what i am talking, i still trying to figure things out and how to use this program.

Please help me  :-[

[Bark]

So by your pic, you are trying to do a set of reports like this:

- Region 1
   - Area 1
      - Province A
      - Province B
   - Area 2
- Region 2
   - Area 3
   - Area 4
      - Province C
   - Area 5

Am I right? (I am guessing the Province is your next step).

If I am not wrong, you need 3 nested page sets, one for each of the iterations. All the pages should be under at least one "detail" in order to be repeated.

Hope it helps.

Regards,

Bark

[shougki]

So by your pic, you are trying to do a set of reports like this:

- Region 1
   - Area 1
      - Province A
      - Province B
   - Area 2
- Region 2
   - Area 3
   - Area 4
      - Province C
   - Area 5

Am I right? (I am guessing the Province is your next step).

If I am not wrong, you need 3 nested page sets, one for each of the iterations. All the pages should be under at least one "detail" in order to be repeated.

Hope it helps.

Regards,

Bark

hi bark,

i am trying to do 3 reports which are Region, Area, Lastly Province....The reigon on top is usually just an expression such as filter by [blahblah][region], so the region page below is the actual page,,,, same for the area.As usual, Area on the toppest level is the expression, the "area" below is the acutal page itself. The report came with these Region and area already built, and i was supposed to do "province"

so i actually copied all the "queries" ( not sure thats the term) from Area then pasted it and rename it as B and placed them in province page. The orignial files were named as blahblah  A. After changing all the references and the filter to province, the 2nd original folder which is area stop working.with the error mention in the first post...

[Bark]

Sorry but I still don't understand what you're trying to achieve here, is it only 1 report? or you want to repeat the page for each province within the Area (which I guess is also repeated within the Region)?

Anyway, If you say you copied the queries and changed the reference, it seems that you forgot one reference to the old query. If you say you checked the whole quiery and it looks there is nothin wrong, try to look for conditional formats (within the cells) or variables that use expresions still referring to the old quiery.

If you validate the report, it should show you the list of errors and it might give you the ability to find the expression (I say might because is not always possible to trace it back from there).

Regards,

Bark

[shougki]

Sorry but I still don't understand what you're trying to achieve here, is it only 1 report? or you want to repeat the page for each province within the Area (which I guess is also repeated within the Region)?

Anyway, If you say you copied the queries and changed the reference, it seems that you forgot one reference to the old query. If you say you checked the whole quiery and it looks there is nothin wrong, try to look for conditional formats (within the cells) or variables that use expresions still referring to the old quiery.

If you validate the report, it should show you the list of errors and it might give you the ability to find the expression (I say might because is not always possible to trace it back from there).

Regards,

Bark

sorry for the messup , i am pretty lost myself too. Generally there is 3 pages... each of these pages have almost same template, just the fliters for 1st page is region, 2nd page is area, and lastly is province.

At the prompt page, there are 3 options... first compulsary choosing a month such as jan 2012, 2nd option is the user can select the area which is optional, and thirdly user can choose province which is optional too....

and i click validate the report, and the error mention in the first post was given...so i guess its 1 report that i am trying to do here?

[RKMI]

Hi,

Can you upload the xml in txt file for both before the error occured and another from the time you received the error. Your explaination is hard to understand and not sure what you are doing wrong. My guess is you might have linked the queries or something to do with mater detail relationship which is broken. With the screenshots provided can't really isolate the issue.

Thanks,
RK

[shougki]

hi Rk,


heres the file...i click tools>show specification ,hopefully its the correct one.

Before:i copied the queries named as ***** A and suceessfuly renamed as "B", and run it sucessfully.

next i proceed to built the prompt page,and it failed. The first prompt was choosing the date which was the orginial, i procceed to add optional region option as well as a optional province option....

then it all went crazy =(

[shougki]

also my error is the same as :https://www-304.ibm.com/support/docview.wss?uid=swg21339433 exactly....
however i dun know what they are talking...

is the report object that has the conditional variable "GOR acutal" in this case?

[RKMI]

Hi,

I was looking into you xml. Here are the steps for the fix,

1. Goto page explorer where your page Region, Area, Provience are allocated.
2. Click on PageSet1 and on the left bottom you will see grouping and sorting property. Click the (Defined) elipses "..."
3. Drag the Data Item City into Groups you now see, Region, Area, City. Click Ok and you see the same change on the page explorer.
4. Then move the Area page under area group by draging it, similarly drag the provience page into the City> Detail Pages.

Please have a look at the Screenshot to see how it look. Sorry for not attaching the XML back since I have 10.2 and your report was upgraded.

Another thing I would recommend is start from your XML by doing all the work you did to get to after xml since you might have changed out items which you exploring and might have messed some other master detail relationship.

I hope this all makes sense, if you have any questions, please IM.

Thanks,
RK

[shougki]

Hi RK,

i have done the above steps but its still not working.attached is new xml file after i added the steps above...not sure if i did it right.

Neitherless i realized that there is a validation button, all along i been clicking the "play" icon and was shocked to see even more errors.they are :

RSV-VAL-0032 The following expression is not valid: [Current Month A].[GOR Actual]-([Current Month A].[Rooms Revenue Actual]+[Current Month A].[F&B Revenue Actual]). If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named [Current Month A].[GOR Actual] is invalid.


RSV-VAL-0032 The following expression is not valid: [YTD A].[GOR Actual]-([YTD A].[Rooms Revenue Actual]+[YTD A].[F&B Revenue Actual]). If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named [YTD A].[GOR Actual] is invalid.


RSV-VAL-0032 The following expression is not valid: [Balance of the Year A].[GOR Actual]-([Balance of the Year A].[Rooms Revenue Actual]+[Balance of the Year A].[F&B Revenue Actual]). If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named [Balance of the Year A].[GOR Actual] is invalid.


RSV-VAL-0032 The following expression is not valid: [Year End A].[GOR Actual]-([Year End A].[Rooms Revenue Actual]+[Year End A].[F&B Revenue Actual]). If the item exists in a query but is not referenced in the layout, add it to a property list. CRX-API-0005 An error ocurred at or near the position '0'. The variable named [Year End A].[GOR Actual] is invalid.


After i created province queries, i remember click anyway on that provnince page, then clicking the ancestor button, then list before setting the query to Current month A.

On the top of the ancestor button , there is a page button, and the query is listed as "select".

did i do anything wrong?

[Bark]

I think the issue is that the calculations that are failing are on a "Report Expression" not in a Data Item.

From the XML:
<reportExpression>[Year End A].[GOR Actual]-([Year End A].[Rooms Revenue Actual]+[Year End A].[F&amp;B Revenue Actual])</reportExpression>
....
<reportExpression>[Current Month A].[GOR Actual]-([Current Month A].[Rooms Revenue Actual]+[Current Month A].[F&B Revenue Actual])</reportExpression>

Try creating the same calculation within the query and then reporting the data item instead of the report expression.

Regards,

Bark

[shougki]

I think the issue is that the calculations that are failing are on a "Report Expression" not in a Data Item.

From the XML:
<reportExpression>[Year End A].[GOR Actual]-([Year End A].[Rooms Revenue Actual]+[Year End A].[F&amp;B Revenue Actual])</reportExpression>
....
<reportExpression>[Current Month A].[GOR Actual]-([Current Month A].[Rooms Revenue Actual]+[Current Month A].[F&B Revenue Actual])</reportExpression>

Try creating the same calculation within the query and then reporting the data item instead of the report expression.

Regards,

Bark

hi bark sir,

sorry i am a noob, i dun quite understand.May i trouble you to write down the steps for me please? :-[

Thanks!

[Bark]

I'll try...

First unlock the report (the blue lock on the top menu bar). Then in the line "Other Revenue" you will see that the first column of each of the vertical sections (Current Month, YTD, ...) the expression begins with "%". This means it is a Report Expression and not a Data Item, and if you select it, in the properties pane you will see that says "Source Type: Report Expression".

Now repeat these steps for each of those colums:
- Doble click on it.
- Copy the expression (and close the dialog).
- Right Click -> Go To Query.
- Create a new Data Item (drag from the insertable objects pane).
- Paste the expression (and ok).
- Rename to whatever you want (to be consistent it would be something like "Other Revenue Actual").
- Go back to the report page.
- Select the report expression (the one u double clicked before).
- In the property "Source Type" select "Data Item".
- In the property below "Data Item Value" select the data item you just created.


Once you finished all of them (please double check there are no more a part from the Actuals in the Other Revenue Column), run the report and see if it works.

Regards,

Bark

[shougki]

hi bark sir,


Many thanks to your effort, the report is working at last...( Some hope as last)( i upped yr +1 and RK too). However i noticed that the report prompt page doesnt seem to be working as what i wanted it to be...

i wanted the report to have 3 possible outcomes, hence the compuslary date and the 2 optional prompt Area and province.

At prompt page, if user select date only, automically >>> a region report generated.
                       if user select date and area>>>>>> an area page will be generated
                        if user select date and province>>>> an city page will be generated.

thats is why there are 3 pages under report, in addition i have also arranged the pages according to RK screenshot pic.

Anything i might be doing wrong?Many Thanks in advance!

[RKMI]

Hi,

The report xml that you sent is for page breaking report where you see each area in the region and each city within the area.

Now to change your request from a page breaking to rendering based on user prompt you need to add a render variable where you can set selections or even get all objects into one page and use a conditional block just depends on you.

Steps to setting up render variable.
1. Create a value prompt where you enter 3 Static choices. Region, Area, and City.
2. Name the parameter something easy to identify later ex: p_userselection
3. Then goto conditional explorer drag a string variable. See the expression below,

if (ParamDisplayValue('p_userselection
') = 'Region') then ('Region')
else if (ParamDisplayValue('p_userselection
') = 'Area') then ('Area')
else ('City')

4. Goto your page explorer link your render variable with the string variable you created to the page repectively. Note only check the one not more than one otherwise you see mixed results.
5. Congrats you are done, run report. :)

Thanks,
RK

[shougki]

Hi guys,

i think i completed the report, seem ok so far. At last, i get to have some peaceful sleep.Thanks guys espcially to Bark and RK.

+1 again to you guys and Merry Christmas!  :D and thanks again!