Hidden reference??

[Danielbryanuk]

Hi all

I have a very strange issue with a crosstab report I have created. When I run the report I get the following error message:

"QE-DEF-0359 The query contains a reference to at least one object '[Revenues YTD0]' that does not exist."

BUT...this reference is not in the report at all!!!

I have copied the report in text format to the notepad and searched through, viewed the MDX and SQL, not to mention everything in the queries and report/input pages.

SO...is this a known bug, or am I missing something?? Does anyone have any suggestions of where to find the reference?

Thanks in advance

Dan

 

[drlex]

Daniel,
Which application? If it's Impromptu, have you used the "Magic SQL" to view what is being sent to the DB?
(In the Report > Profile tab, Show SQL and hold Shift+Ctrl+Alt when clicking on the Edit button.)
I've had similiar where the query included a conditional formatting sub-query which was not visible in the 'normal' SQL.

soi là, soi carré

 

[Danielbryanuk]

Sorry, should have said - I'm using report studio 8.3.

I'm baffled to be honest!!

 

[blom0344]

Did you check:

Filters / variables?

(though I imagine these would also be in the xml text)

Ties Blom

 

[Danielbryanuk]

Yes Ties, all checked! Would it help if I attached a text version of the report? I also checked the XML to no avail.

Could it be anything to do with grouped totals in the crosstab - I'm not too familiar with them (in terms of the calculated items that are produced in the query as a result of grouping and adding total headers on the report), so thought they could be the issue?

The other idea I has was that it is something to do with duplicate names in the query items. The report has two queries - the base, and the report queries. In the base query I have a calculation called "Revenues ytd", which is then summed in the report query, but is called "Revenues YTD". The aggregate functions in both queries are set to "Total". However neither query references '[Revenues YTD0]' but maybe using lower and upper case names is having some effect?

Thanks for your help
Dan

 

[blom0344]

Did you try to run EACH seperate query in isolation (tabular data option) or tried to generate the SQL involved?

The problem may be outside the report (text / xml) but with the query itself (though I would expect a different error message in that case.)

8.3 gave problems with crosstabs that were the result of copy actions in earlier versions. Older versions never protested, but in 8.3 nodes existed in crosstabs that should not be there (and then we needed to rebuild, as nodes can not be deleted as such)

Ties Blom

 

[Danielbryanuk]

The base query runs fine, but the report query only runs when I delete the 4 total calc's I mentioned before, which also deletes the rows from the report:

total(currentMeasure within detail [Team])
total(currentMeasure within detail [Submarket])
total(currentMeasure within detail [Sub-GMT])
total(currentMeasure within detail [RM])

It's strange though because on the report there are 5 totals, so I would expect to see the following of the other grouping (for [GMT])in the query but do not!

Does this help?

 

[Danielbryanuk]

By way of an update, I guess in case anyone else has this problem, I solved the issue by creating a duplicate of the existing [Revenues YTD] data item in the offending query and replaced the measure on the report page. Bit of a fluke really, but bizarrely this only works when the old data item remains in the query. I had initially tried to create a replacement data item, but calling it something different, however the new data item name appeared as a "missing" reference. I'd love to know how/why, but another deadline looms!!

Thanks to those replying to this thread.