Getting an error while using a data item from a join query

[Dineshpullagura]

Hello,

I have two queries in my report studio, which i am joining to generate a new query. While i am trying to a use a data item from the join query i am getting the below error.

"RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0020 The V5 Query could not be planned by the Query Service. The 'V5DataItemRef' node is invalid for the OLAP Transformation Library and the Query Service Planner is currently not able to resolve it. "

I am using cube as my data source and using these queries to generate a chart report.

Let's say i have queries Q1 and Q2 which contains data items D1,D2,D3 and D4,D5 respectively. I am generating a join query Q3 from Q1 and Q2 which contains D1,D2,D5, D6(using D2). 

While i try to create the chart report from Q1 using D1, D2 I am able to generate the chart report. But when i try to create the chart report from data items of Q3( which are referring to data items in Q1), i am getting the above error.

Please suggest.

Thanks for your time.

[MFGF]

Hello,

I have two queries in my report studio, which i am joining to generate a new query.

Ok. Joining queries is a relational concept.

While i am trying to a use a data item from the join query i am getting the below error.

"RSV-VAL-0010 Failed to load the report specification. XQE-PLN-0020 The V5 Query could not be planned by the Query Service. The 'V5DataItemRef' node is invalid for the OLAP Transformation Library and the Query Service Planner is currently not able to resolve it. "

I am using cube as my data source and using these queries to generate a chart report.

You are using a dimensional (OLAP) source here. You cannot join queries based on OLAP sources. Joining queries is a concept for relational sources, not OLAP ones.

Let's say i have queries Q1 and Q2 which contains data items D1,D2,D3 and D4,D5 respectively. I am generating a join query Q3 from Q1 and Q2 which contains D1,D2,D5, D6(using D2). 

While i try to create the chart report from Q1 using D1, D2 I am able to generate the chart report. But when i try to create the chart report from data items of Q3( which are referring to data items in Q1), i am getting the above error.

Dimensional sources support functions which deliver the same results as joined relational queries using a single query and appropriate dimensional functions. What is the requirement you are trying to achieve?

MF.

[Dineshpullagura]

Hi,

I have a case transaction database in star schema on top of which my cube is built. In my report user will select a time period and Case owner who resolved it. I have to generate a chart report showing the average time taken by all the case owners to resolve the cases in the selected time period and time taken by the case owner selected by the user.

I am calculating both of these in different queries and joining these queries to show both the Individual and all Case owners average resolution time in the same chart. I have attached the chart for your reference.

Thanks for your time.

[MFGF]

Hi,

I have a case transaction database in star schema on top of which my cube is built. In my report user will select a time period and Case owner who resolved it. I have to generate a chart report showing the average time taken by all the case owners to resolve the cases in the selected time period and time taken by the case owner selected by the user.

I am calculating both of these in different queries and joining these queries to show both the Individual and all Case owners average resolution time in the same chart. I have attached the chart for your reference.

Thanks for your time.

Hi,

The average time for all case owners would be an expression such as:

average([your time taken measure] within set [your case owners level])

The time taken for the chosen case owner would be an expression such as:

tuple([your time taken measure], [your case owners level] -> ?Your case owner parameter?)

You can do both of these in the same query

If you want to take the chosen time period into account too, these might become:

average(tuple([your time taken measure], [Your time period level] -> ?Your time period parameter?) within set [your case owners level])

and

tuple([your time taken measure], [your case owners level] -> ?Your case owner parameter?, [Your time period level] -> ?Your time period parameter?)

MF.

[Dineshpullagura]

Hi MF,

Thanks a lot. It worked.

I have another question. There is a scenario where i have to average only on  time. In the report user is being prompted for time, and the prompt is like last week, last month, last six months, one year, two years, LTD.

I am using a data item named 'Time Caption' in my query which returns the level corresponding to the user input. I am using a case statement. So, if the user input is last six months, it gives month level and it the user input is a month, it gives day level.

This is my Time Caption data item
CASE (?TimeRange?)
WHEN (Last Week) THEN ([Day Level])
WHEN (Last Month) THEN ([ISO Week Level])
WHEN (Last Six Months) THEN ([Month Level])
WHEN (One Year) THEN ([Month Level])
WHEN (Two Years) THEN ([Year Level])
WHEN (LTD) THEN ([Year Level])

Now i am trying to aggregate based on the level returned by the time caption. This is my expression

average([time taken measure] within set [Time Caption])

But i am getting an error

"XQE-PLN-0093

The argument at position 1 of 'within set' from the data item '290' is a 'value' expression, which cannot be coerced into a 'set' expression. "


Please suggest.

Thanks for your time.

[MFGF]

Hi MF,

Thanks a lot. It worked.

I have another question. There is a scenario where i have to average only on  time. In the report user is being prompted for time, and the prompt is like last week, last month, last six months, one year, two years, LTD.

I am using a data item named 'Time Caption' in my query which returns the level corresponding to the user input. I am using a case statement. So, if the user input is last six months, it gives month level and it the user input is a month, it gives day level.

This is my Time Caption data item
CASE (?TimeRange?)
WHEN (Last Week) THEN ([Day Level])
WHEN (Last Month) THEN ([ISO Week Level])
WHEN (Last Six Months) THEN ([Month Level])
WHEN (One Year) THEN ([Month Level])
WHEN (Two Years) THEN ([Year Level])
WHEN (LTD) THEN ([Year Level])

Now i am trying to aggregate based on the level returned by the time caption. This is my expression

average([time taken measure] within set [Time Caption])

But i am getting an error

"XQE-PLN-0093

The argument at position 1 of 'within set' from the data item '290' is a 'value' expression, which cannot be coerced into a 'set' expression. "


Please suggest.

Thanks for your time.

Hi,

The issue here is that your case statement is returning a value not a set. This is because case statements are a relational concept rather than a dimensional concept. Have you taken any training in dimensional reporting?

The approach you really need to take is to use the Member Unique Name or Level Unique Name as the "Use Value" in your prompt. I imagine your prompt has static choices defined for "Last Week", "Last Month" etc?

Change these so that the static choices have a Display Value of "Last Week" and a Use Value of whatever the Level Unique Name is for Iso Week Level. You can see this if you right-click Iso Week Level in the package tree and select "Properties"

Cheers!

MF.

[Dineshpullagura]

Hi MF,

Thanks a lot for your help. My sincere apologies for asking such trivial stuff. I don't have any training on dimensional reporting. I am trying to ramp up.

[MFGF]

Hi MF,

Thanks a lot for your help. My sincere apologies for asking such trivial stuff. I don't have any training on dimensional reporting. I am trying to ramp up.

No apologies are needed - what you're asking isn't trivial if you haven't taken the training and are familiar with dimensional reporting concepts. I can honestly recommend it as a worthwhile investment - although it will take two days of your time, it will help you enormously in understanding how to build reports on dimensional sources. Take a look at Paul's blog too - he has some really useful stuff on dimensional reporting if you look through :)

Cheers!

MF.

[Dineshpullagura]

Thank you  :) . I'll try to attend the training session and will definitely go through Paul's blog.


I have another question.

I tried changing the Use values to 'level unique names' as suggested.  Now i changed the average expression as below.

average([Time Taken Measure] within set ?TimeRange?)

where TimeRange is the parameter of my time prompt. I am getting the below error when i try to run the report.

XQE-PLN-0020

The V5 Query could not be planned by the Query Service. The 'V5AggregateWithinClause' node is invalid for the OLAP Transformation Library and the Query Service Planner is currently not able to resolve it.