Switch or case

[Former Member]

Hi I have two tables as below ; which are input for my report .First Table is Invoice second Table is Messages

key	Language	Region
1	English	USA
2	English	USA

Language	Region	SortOrder	Message
English	USA	1	ABCDEFG IJKLMNOP ...
English	USA	2	QRST UVW
English	USA	3	XYZ

I want to create three custom Formula Fields(Message1,Message2,Message3) which is by SortOrder  column as a condition for a report.

In the end when I look at  SQL Query on the report, the query result should look like as below.

Key	Language	Region	Message1	Message2	Message3
1	English	USA	ABCDEFG IJKLMNOP ...	QRST UVW	XYZ
2	English	USA	ABCDEFG IJKLMNOP ...	QRST UVW	XYZ

I tried to create 'Message1' formula field as below and dropped on Report .I ended up getting duplicates.

if({Invoice.Language}='English' and {Invoice.Region}='USA' and {Messages.SortOrder}=1) then  {Messages.Message}

[Former Member · Accepted solution]

Please see above null values for the SQL Query in the report which is making for duplicate reports.

Note:Invoicestring as Key

select inv.InvoiceString,inv.BusinessUnit,inv.InvoiceLanguage,--term.Message

case when term.[Order]=1  then term.Message end as Message1

from

  t_rpt_invoice inv

  left join  t_rpt_InvoiceTerms term on inv.BusinessUnit=term.BusinessUnit and inv.InvoiceLanguage=term.InvoiceLanguage

[abhilash_kumar · Accepted solution]

Hi Chatrapati,

Since you already have a SQL query, why don't you report off of it using the 'Add Command' option?

It will work much faster and is always more manageable than creating formulas on the report.

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thank you for the Reply..

Please find the SQL Query after inserting a command with also some other tables which are included  into  the  report.

/****************************************************************************************************************/

[SERVER]

SELECT inv.InvoiceString, inv.AccountNumber, inv.InvoiceID, currencySym.Currency, remit.RemitName,

FROM   ((([DATABASE].dbo.t_rpt_bill_messages msg

INNER JOIN [DATABASE].dbo.t_rpt_Invoice inv ON msg.InvoiceID=inv.InvoiceID)

INNER JOIN [DATABASE].dbo.t_rpt_Remittance remit ON ((inv.Currency=remit.Currency)  AND (inv.BusinessUnit=remit.BusinessUnit))

INNER JOIN [DATABASE].dbo.t_rpt_CurrencySymbols cSym ON inv.Currency=cSym.Currency

ORDER BY inv.InvoiceString

EXTERNAL JOIN inv.InvoiceID={?[SERVER]: InvoiceTerms.InvoiceID} AND inv.InvoiceString={?[SERVER]: InvoiceTerms.InvoiceString}

[SERVER]

SELECT InvoiceID,InvoiceString, [1],[2],[3] from

             (

                select InvoiceID,InvoiceString,[Message], [Order]

                from t_rpt_invoice inv

                                  join t_rpt_invoiceterms invterm

                                  on inv.invoicelanguage = invterm.invoicelanguage

                                  and inv.BusinessUnit = invterm.BusinessUnit

            ) x

            pivot

            (

                max([Message])

                for [Order] in ([1],[2],[3])

            ) p

EXTERNAL JOIN invterm.InvoiceID={?[SERVER]: inv.InvoiceID} AND invterm.InvoiceString={?[SERVER]: inv.InvoiceString}

/****************************************************************************************************************/

And it works on my Development Report and want to make sure does it also works on Sever side  also. As when I added the SQL Command with other tables I got below warnings:

"More than one datasource has been used in this report.Please make sure no SQL Expressions not added and sever side group by is performed."

Thanks in advance.

Chatrapathi Chennam

[abhilash_kumar · Accepted solution]

Never Join commands with other commands or tables.

Please consolidate the above SQL as one so that you get improved performance (Create one single SQL that has everything you need and that works when used in the Management Studio).

-Abhilash

[DellSC · Accepted solution]

I think this would be an easier way to do this:

1.  Group by Key.

2.  Suppress the Group Header and Details sections.

3.  Create your formulas for each message like the one you show for message 1.

4.  In the group footer, put your invoice data.  For the messages, put the Maximum of each of the message formulas from step 3.  (Maximum() works with strings as well as numbers!)

-Dell

[Former Member]

This message was moderated.

[Former Member]

Hi DJ,

Please find the T-SQL Query as shown below.

SELECT Key,Language,Region,[Message1],[Message2],[Message3] from

         (

                select Key,Language,Region,[Message], [SortOrder]

                from invoice inv

                                  join Messages msg

                                  on inv.Language = msg.Language

                                  and inv.Region = msg.Region

            ) x

            pivot

            (

                max([Message])

                for [Order] in ([Message1],[Message2],[Message3])

            ) p

.But not sure how it can be implemented on report level as to get  formulas @Messages1, @Message2 , @Message3 to retrieve messages  for each 'Key' record as I have other fields embedded.

Thanks,

Chatrapathi Chennam

[Former Member]

This message was moderated.

[Former Member]

Hi DJ,

The formula still returns Duplicates of Key record. ie For each Key  (Key) X (SortOrder) Messages return ie 3 records for each key.

Thanks,

Chatrapathi Chennam