Multiple select statements in one LiveReport?

Good afternoon,

I was wondering if it is possible to have multiple SELECT statements in one livereport? Essentially, I would like multiple selects statement results to appear on one results page. Sub reports don't seem to do the trick. Does anyone know if this is possible?

Thanks kindly.

Dave

Find more posts tagged with

Comments

Mukesh_Giri

You can use Union all function in your SQL server for utilising multiple Select statements, if you can post why you are behind multiple selects someone\ me will be able to post some ideas.

Cheers,

Mukesh Giri

Quoted David Miehl on 2013-03-05 13:48:

Good afternoon,

Thanks kindly.

Dave

David_Miehl

Mukesh,

Eseentially, we have received requests from our clients who want numerous reports for one user between a period of time. I don't think a UNION would work in these cases. For example,

Number of Physical Objects returned (number of ‘return’ actions they did)

They would like all of these stats to show up on one report but I may need to dig into the DAUDITNEW table so I don't believe I can easily lump this into one statement. I thought (hoped) that I could just write several completely separate SELECT statements and place them in one LiveReport and the results would just output sequentially..

Thanks again!

Dave

Number of Folders Created
Number of Folders Closed (Status changes to CLO)
Number of Reclassifications
Number Physical Objects circulated to others (number of ‘borrow’ actions they did)

Matthew Pearson

David – we have written a similar Transaction report in the past. The client was similarly interested in the activities of a particular storage location in a given time range, but this could easily be adjusted to your needs of a user-centric report.

The client designed a report layout where the columns were the same across all transactions, and certain headings were sufficiently generic to accommodate the multiple scenarios of the report. So, Transaction Type and Transaction Date covered combinations of “Borrowed … Borrowed Date” and “Returned … Returned Date”. All OT Services had to do was then write separate SQL statements with UNION joining them. Depending on the transaction, we looked at PhysItemCO (for Borrowed), PhysItemHistory (for Returned), PhysItemRequest (for Requested), DTree (for Created). The snippet below is intended as an example of our approach to joining two such statements – it is NOT tested to work as written:

SELECT 'BORROWED' AS "Transaction Type", CONVERT(VARCHAR(10),b.ChargeDate,101) AS "Transaction Date", Description=convert(varchar(150), a.DComment),

d.Area AS "Area", d.BoxLocator AS "Locator",b.UniqueID AS "Unique ID", d.DefaultLocation AS "Storage Facility", e.FirstName + ' ' + e.LastName AS "Request User"

FROM DTree AS a INNER JOIN

PhysItemCO AS b ON a.DataID = b.NodeID INNER JOIN

PhysItemData AS d ON a.DataID = d.NodeID INNER JOIN

Kuaf AS e ON b.UserID = e.ID LEFT OUTER JOIN

rimsNodeClassification AS m ON a.DataID = m.nodeID

WHERE (LEN(b.ChargedTo) > 0) AND (m.rimsStatus != 'DESTROYED') ~1 ~2 ~3

UNION

SELECT 'RETURNED' AS "Transaction Type", CONVERT(VARCHAR(10),e.ReturnDate,101) AS "Transaction Date", Description=convert(varchar(150), a.DComment), d.Area AS "Area", d.BoxLocator AS "Locator",b.UniqueID AS "Unique ID",d.DefaultLocation AS "Storage Facility", f.FirstName + ' ' + f.LastName AS "Request User"

FROM DTree AS a INNER JOIN

PhysItemCO AS b ON a.DataID = b.NodeID INNER JOIN

PhysItemData AS d ON a.DataID = d.NodeID INNER JOIN

PhysItemHistory AS e on a.DataID = e.NodeID LEFT OUTER JOIN

Kuaf AS f ON e.UserID = f.ID LEFT OUTER JOIN

rimsNodeClassification AS m ON a.DataID = m.nodeID INNER JOIN

WHERE m.rimsStatus != 'DESTROYED' ~1 ~4 ~5

Matthew Pearson
Principal Solutions Consultant | Western Region, United States

Phone:	(608) 237 1885
Mobile:	(608) 358 4293


Website:	www.opentext.com

This email message is confidential, may be privileged, and is intended for the exclusive use of the addressee. Any other person is strictly prohibited from disclosing or reproducing it. If the addressee cannot be reached or is unknown to you, please inform the sender by return email and delete this email message and all copies immediately.

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, March 06, 2013 6:07 AM
To: eLink Recipient
Subject: Multiple select statements in one LiveReport?

Multiple select statements in one LiveReport?

Posted bydmiehl@justice.gc.ca (Miehl, David) On 03-06-2013 08:58

Mukesh,

Eseentially, we have received requests from our clients who want numerous reports for one user between a period of time. I don't think a UNION would work in these cases. For example,

Number of Physical Objects returned (number of ‘return’ actions they did)

Thanks again!

Dave

Number of Folders Created
Number of Folders Closed (Status changes to CLO)
Number of Reclassifications
Number Physical Objects circulated to others (number of ‘borrow’ actions they did)

[To post a comment, use the normal reply function]
Topic:	Multiple select statements in one LiveReport?
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

image001.gif

David_Miehl

Thank you Matthew for your response. After further investigation, it just doesn't seem that the generic LiveReports will have enough firepower/flexibility to do what we are looking for. We may have to investigate the LiveReports extensions and/or webreports. I'm sure OT's ears are ringing with $$ to hear me say that......