geoffk

I have a very basic query, which returns a product code, the current stock level and the average amount sold each week. I want to use this data to show if / when the material will run out, over the next 26 weeks and then the impact of any planned deliveries in that time. The goal is to produce a graph that looks something like this: Where the blue element is the stock being used and the green represents a delivery of more stock. To do this (I think) I need two groups: The first is going to be the product - which is straight-forward. The second is going to be a week number, running from 0 to 25 - which has nothing to do with the data coming over in the query and where I am getting stuck. If I can create this second set of groups, I can use it to calculate the stock 'level' by multiplying the 'usage' by the week number and deducting it from the current stock level(0) = current stock - (0 x usage) level(1) = current stock - (1 x usage) level(2) = current stock - (2 x usage) ... level(25) = current stock - (25 x usage) I can also use the week number to calculate a date range - to feed a sub-report that will retrieve the quantity of material due for delivery in that week. Can anyone point me in the right direction?

That was it. For anyone else who may make the same mistake, the permissions on the default "date_label" Input data type was set to "No Access" for the "ROLE_USER" group because I'd hidden this folder from these users and therefore the record inherited the same permission - to simplify the user interface. I was able to change the permissions on the data type, while leaving the menu hidden and my reports started to work. Thanks for your help.

I've created a new user that is part of the "ROLE_BRIGHT" (a new one I created) and the "ROLE_USER" groups - the idea being that this user can access some of the menus (e.g. Data Sources), as they write and publish reports (with iReports), whereas most users just run them and they have their own users (which all belong to the "ROLE_USER" group only). I've found that a couple of the reports that have been published do not run for the general users, but will run for my new user. The system reports that "Access denied: You do not have permission to take the requested action" The reports are in the same folder and have the same permissions: "ROLE_BRIGHT" has administrative permission and "ROLE_USER" has read only permission - both of which are inherited. I can't see a difference between the reports and the way they were published to the server, so cannot understand the problem - it's probably something that I've not done, as I am a new user, but need some help to resolve the issue. Thanks in advance.

Thanks for the reply, but I think I have the rows that I need (Customer, Order and Item) - I want to add extra information into the labels for the final (item) row. For example, I don't want the label to just be the item number, I want to also show (along with the item number) the product description, the original ordered amount, the product dimensions and the warehouse the material is to go from. All of this is "item-level" detail and has no impact on the summarising of the data - e.g. there will only ever be one product associated with an order item. If I were to add a "row group", for each piece of information, each item would take up an extra four lines - when I want to show it as a single one. I've attached a sample from the spreadsheet that is currently creating and I am trying to automate (in an attempt to be clearer about what I am trying to achieve) - it does not include the resultant columns (as that includes pricing information, that I have to keep confidential). The "Customer" column corresponds to my "Customer" column. The "Customer Order No" corresponds to my "Order" column. The "Ref" column corresponds to my "Item" column, but it is actually showing the order header number (which is why there are duplicates). The "Tonnes" column corresponds to my "Total sales_item_due_date" column and has the additional total in the footer (per customer, not per order). The "Product" and "Length" columns are two of the additional fields that I want to add into my "item" row group - they are extra pieces of information that relate to the group and are not groups themselves.If the only way to resolve my problem is the addition of multiple "row groups", is there any way I can tidy up the presentation to bring it up onto a single row? Thanks for you help.

Hi Carl, I've attached two screenshots: the first shows the design and the second shows a sample of the data. The main grouping is the "customer", then the order and finally the item. I then show the total weight for the sales item (row) before the columns - as there may be multiple deliveries associated with an item. I am trying to add extra data into the "order" and "item" row labels. Thanks for your help.

I'm a newbie to iReports and need some help with my crosstab ... I've created a simple crosstab with three rows - customer, order and order item. There is one column, for the due date grouped by month. The "measure" is the outstanding balance. This runs without any problems. However, I want to add some extra fields in to each row header - for example: show the customer's order number next to our order number and show the product code next to the order item number. I've tried dragging the field from the main report list, but it doesn't like that - so I obviously need pointing in the right direction. Any assistance would be greatly appreciated.

I'm fairly new to report writing and iReport. The area where I struggle most is the building of the queries and the application of the query filters. I have a basic grasp of SQL (i.e. I can select tables and fields, then link them together, etc.), but could do with some reference material to fill in the gaps in my knowledge and also highlight / explain any functions I've not uncovered by exploring the application. Can anyone recommend a user guide for the built in query designer - there doesn't appear to be anything on the SQLeonardo project and it's an area all the guides seem to gloss over? I have similar issues with iReport's "expression builder", but this is more a case of not knowing the syntax I'm supposed to be using - for example, when doing something with records where the date field is null or set to a specific date, I've tied myself up in knots using single or double quotes or not using them at all. Again, a decent reference guide will probably bridge most of these gaps - so if anyone can point me towards one, I'd appreciate it. Thanks in advance. Post Edited by geoffk at 09/08/2010 11:28