Dynamic SQL View not working as expected

[shearerg]

Hi all,

I'm using a Dynamic SQL View to provide data for machine OEE calculations. The resulting data goes to the production shift level, and there are generally several records for a machine for a given shift. The view appears to work fine when creating reports which group the data into daily or greater chunks, but it is not working correctly when a report to the shift level is required.

The image below is showing daily OEE calculated over 5 days, and looks fine.

ScreenShot107.jpg (34.2 KiB) Viewed 7648 times

However, when the report is adjusted to also group by shift, displaying it with the date, the graph remains unchanged and only shift 3 is represented in the date/shift column although there are records for every shift during the week.

ScreenShot108.jpg (34.11 KiB) Viewed 7648 times

I know the following is a stupid report, but it shows when the same data is constructed with the shift grouped in columns, so you can see that the data does exist.

ScreenShot109.jpg (47.69 KiB) Viewed 7648 times

It appears that shift grouping is just being ignored. Please let me know if I'm missing something obvious.

Regards,
Greg Shearer

[shearerg]

Hi,

I can create a work-around for this problem by extending the 'Dynamic SQL View' to include an additional column, which is a string representation of the date and shift combined, although this isn't a very good solution. See the 'dateshift' column in the image below:

ScreenShot112.jpg (65.14 KiB) Viewed 7640 times

The weekly shift based report can than be built to group on the dateshift column:

ScreenShot113.jpg (20.72 KiB) Viewed 7640 times

resulting in the correct report layout:

ScreenShot114.jpg (42.53 KiB) Viewed 7640 times

It still looks to me as if the original problem is a bug associated with grouping by a date field in combination with a different field type ... or am I missing something?

Regards,
Greg Shearer

[Mikkel Jensen]

Hi Greg

Seems like you "merged" the date with the shift information by using "Row Caption".

If you want to include both date and shift information in a line as you showed on your graph you need to create a extra field that combines the fields (as you also did).

FYI from the online help regarding the use of Row Caption:

Caption is used to combine the field with the row grouped field defined just above. This should only be used when having a one-one relation between the data in the fields.

This could be relevant if grouping on account number and account name, or other fields that “comes in pairs”. If you show account name with account number, you’ll get a list, correctly sorted on account number, but showing the account name on the same level.

Regards,
Mikkel

[shearerg]

Hi Mikkel,

Thanks for the reply. From your comments I understand the situation now, but I'm a bit disappointed. I was hoping there would be a means of achieving the kind of grouping I was after other than by generating additional fields.

Particularly in the case where date fields are involved, this is not always a trivial process. In my installations which support 'ansidate' conversion, the sql required to generate an appropriate string for grouping is:

concat(concat(char(ansidate(p.date)),' '),char(p.shift)) as dateshift

This ensures the date field is presented in YYYY-MM-DD format, followed by a space and the shift number. In older systems where 'ansidate' conversion isn't supported, the process is even uglier, requiring construction of a string from YEAR, MONTH and DAY components. This seems a rather awkward way of achieving the simple grouping I require. In essence, I want to group by (in this case) 2 fields, but have the resulting data set fully expanded rather than gradually revealed via progressive drill down levels. That is, I want shift to 'Show with' the date ... but not just as a line caption.

I hope what I've described above makes sense and is clear enough. Perhaps I'm still missing something. Any way, the method I have working at the moment is ok ... just a bit untidy

Regards,
Greg Shearer

[Mikkel Jensen]

Hi Greg

The visual "one line" grouping on the list can be achieved by using "Grouped values on each row" found on the Options tab when designing the report.

However this wont be reflected on the graph. It will only show the data from the "first" group level:

Grouped values on each row

Screen shot 2010-09-20 at 14.11.50 PM.png (100.43 KiB) Viewed 7633 times

If you create it as a pivot tabel you can include all the information on graph, but you need to use either an area or a stacked bar chart:

Pivot with area chart

Screen shot 2010-09-20 at 14.10.35 PM.png (135.76 KiB) Viewed 7633 times

For the time being this is the only way, as I see it, to include both the time and shift information on the graph without creating an additional field.

Regards,
Mikkel Jensen