Will CASE function work in LR?

I've created a very basic script in MS SQL using a CASE statement but getting odd behaviour in LR. The output is two columns from the DAUDITNEW table where I am expecting the first column to have the AUDITSTR name and the second column to have a count. The output is both columns have the count appear. Script is as follows. I think I've read that CASE doesn't work in LR.

select CASE WHEN DAUDITNEW.AUDITSTR = 'Create' THEN 'CREATE' ELSE DAUDITNEW.AUDITSTR END, COUNT(*) from dauditnew GROUP BY AUDITSTR

Ultimately, the end users would like to see something more user friendly instead of the DAUDITNEW AUDITSTR name in the report output.

Thanks,

Dave

Find more posts tagged with

Comments

Colin_Schmidt

In Oracle, I'd have:

select CASE WHEN DAUDITNEW.AUDITID = 1 THEN 'CREATE' ELSE DAUDITNEW.AUDITSTR END, COUNT(*) from dauditnew GROUP BY CASE WHEN DAUDITNEW.AUDITID = 1 THEN 'CREATE' ELSE DAUDITNEW.AUDITSTR END

Colin J.

On Thu, Sep 11, 2014 at 3:38 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Will CASE function work in LR? Posted bydavid.miehl@justice.gc.ca (Miehl, David)On 09/11/2014 03:34 PM

I've created a very basic script in MS SQL using a CASE statement but getting odd behaviour in LR. The output is two columns from the DAUDITNEW table where I am expecting the first column to have the AUDITSTR name and the second column to have a count. The output is both columns have the count appear. Script is as follows. I think I've read that CASE doesn't work in LR. select CASE WHEN DAUDITNEW.AUDITSTR = 'Create' THEN 'CREATE' ELSE DAUDITNEW.AUDITSTR END, COUNT(*) from dauditnew GROUP BY AUDITSTR Ultimately, the end users would like to see something more user friendly instead of the DAUDITNEW AUDITSTR name in the report output. Thanks, Dave
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.

Lindsay_Davies

Hi Dave,

Yes, CASE statements can be used in LiveReports.

Depending upon the version of Content Server you are using you may find that the scripting protection means you have to check the box to ‘Allow Database Modification’.

However, using the case statement as you have written it will not change the column heading in the report output.

All it is doing is it make that one value upper case.

You have two ways to alter the column heading.

First, as a parameter at the bottom of the screen ‘Display Columns:’

Here you have a limited number of places to define a selected column name and give it a title that will appear.

Alternatively you can define a column alias in your SQL statement.

e.g.

select AuditStr as Event, count(*) Count from DAuditNew group by AuditStr order by 1

If you wanted multiple words you must enclose the alias in quotes…

Select AuditStr ‘Event Action’, count(*) Count from…

You will notice I used an ‘order by’ clause to sort the output.

That can be relative (1 = first column) or the column name or its alias.

Hope this helps.

Regards

Lindsay

Lindsay Davies

Principal Product Specialist

European Escalation Team

OpenText

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, September 11, 2014 8:39 PM
To: eLink Recipient
Subject: Will CASE function work in LR?

Will CASE function work in LR?

Posted bydavid.miehl@justice.gc.ca (Miehl, David) On 09/11/2014 03:34 PM

select CASE WHEN DAUDITNEW.AUDITSTR = 'Create' THEN 'CREATE' ELSE DAUDITNEW.AUDITSTR END, COUNT(*) from dauditnew GROUP BY AUDITSTR

Ultimately, the end users would like to see something more user friendly instead of the DAUDITNEW AUDITSTR name in the report output.

Thanks,

Dave

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

David_Miehl

Thank you for the replies. I apologize. I don't think I explained myself very well. I am not trying to change the column heading/title. I am trying to change the ouput date in the column itself. For example, my output is:

AUDITSTR COUNT

RMClassificationAdded 3
OwnerChange 10
RecordDateOrReceivedDateChanged 2

I would like to change the RMClassificationAdded to 'RM Class Add' or something similar. This is strictly for cosmetic purposes so things are more clear for the end user viewing the report. However, when I try the CASE structure as indicated in my original post, I get the results in the following format:

AUDITSTR COUNT

3 3
10 10
2 2

I will of course want to have numerous CASE scenarios to replace most, if not all of the items in the AUDITSTR column.

Thanks again.

Dave

Colin_Schmidt

Easy....the CASE statement will do that...exactly as you had it earlier.

Case DAUDITNEW.AUDITID

when 1 then 'Create'

when 2 then 'Delete'

when 139 then 'RMClassAdd'

when ....etc.

end

Colin J.

On Fri, Sep 12, 2014 at 9:38 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE Will CASE function work in LR? Posted bydavid.miehl@justice.gc.ca (Miehl, David)On 09/12/2014 09:33 AM

Thank you for the replies. I apologize. I don't think I explained myself very well. I am not trying to change the column heading/title. I am trying to change the ouput date in the column itself. For example, my output is: AUDITSTR COUNT RMClassificationAdded 3
OwnerChange 10
RecordDateOrReceivedDateChanged 2 I would like to change the RMClassificationAdded to 'RM Class Add' or something similar. This is strictly for cosmetic purposes so things are more clear for the end user viewing the report. However, when I try the CASE structure as indicated in my original post, I get the results in the following format: AUDITSTR COUNT 3 3
10 10
2 2 I will of course want to have numerous CASE scenarios to replace most, if not all of the items in the AUDITSTR column. Thanks again. Dave
[To post a comment, use the normal reply function]
Topic: Will CASE function work in LR?
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.