Hello All,
I have an order query I am running for a report where I show an "Order Summary" and an "Order Detail" page. The summary has your standard date received, order #, customer, total units, etc.
Example output:
Order Summary
Order # | Customer | Total Units |
1234 | Joe Smith | 50 |
Order Detail
Order # | Customer | Product | Total Units |
1234 | Joe Smith | Outstanding Widget | 15 |
Order # | Customer | Product | Total Units |
1234 | Joe Smith | Super Widget | 35 |
What I'd like to do is insert the product with the most units for each Order # into the Order Summary page on each line.
For instance, if I had an order with 3 detail lines, and the line with the most units was a "Super Widget'..
Order Summary
Order # | Customer | Main Product | Total Units |
1234 | Joe Smith | Super Widget | 50 |
Is there a best route in acheiving that? (I'd like to keep it to one query in the report if I can)
Thanks All!
(You didn't specify, so according to the nature of the data I'm assuming relational).
The question of whether or not this will remain "one query" has to do with your data structure. I assume you have two tables - order summary (Or header) and order details. So, in order to get data from the detail into the summary you'll have to use a join. Whether this join is done by typing in SQL by hand, by creating a joined query subject in FM or by joining two RS queries together is irrelevant. The end result would be an SQL that resembles the following (The sample code here doesn't yet take care of getting the "main product"):
select oh.orderNum,oh.customer,od.mainProd,oh.totalUnits
from order_header oh
join order_details od on od.orderNum=oh.orderNum
So, I wonder what the motive is behind insisting on one query?
Anyway, you can keep it one query by setting the join up as a part of the FM.
If I'm ignoring the one query requirement for the moment (Which can be achieved by transferring the same logic to FM) here's what I'd do (In general lines, might be some syntactical or logical slips as I'm not in front of a machine right now) :
First query - Order details main product:
items:
order number
product
total units
rank(=rank(total Units for order number, product).
filter:
rank=1 after auto aggregation.
Second query - order Header
items:
order number,
customer
total units
join both queries on order number. (1..1 1..1)
third query (joined)
all fields from second query + product name from second query.
Hey There and Thanks!!!
I'm just trying to see if there is a more advanced method of getting that info from that same query to save on processing time. This is a relational report with a ton of crazy things being done in the query itself.
I DID FIGURE IT OUT.
In short, I used some ranking in the query and then some style variables in the summary page to hide the detail lines and only show the "main product" line in the summary page. Then I just created the detail page using the same query.
I created a data item cleverly called "rank" like this...
rank([Units],[Product Name] for[Order Number])
I then created a style variable called "Main Product" with the following:
[Orders].[Rank] = 1
Then in List section on the summary page, I set each column with the "Main Product" , then went into Condition Explorer, and then chose "No" out of the "Main Product" variable. Then I set the field to "None" for the box type. I then achieved expected results.
To get different levels of data from single db call I like to nest my queries. I build a base query with lots of granularity, I then use that as my source for another higher level calculation. This causes a single DB call and as many levels of granularity you like calc'd up on the cognos side.
To create a 'higher' level view from your low level query In RS:
- Go to the queries folder
- Drag a new query onto the page.
- Drag your low level query (existing presumably) to the right of your high level query; you should see the drop zone appear on the right of the high level query. see picture attached
- Go to your high level query and drag in the items that you'd like to aggregate at a higher level.
You now have a source for a different container, both queries sourced by the same db call. This is a very good method for isolating data for a singleton, summary line... IMHO.
A cube is probably better for this type of behavior, but a cube isn't always available...