3

I am trying to output information from one of my org's pricebooks into excel for another department to review. I'm capable of getting the information with a basic select statement, but they would like it formatted differently. I'm currently using the following query:

SSELECT Product2Id, Name, CurrencyIsoCode, UnitPrice FROM PricebookEntry WHERE Pricebook2Id = '1234567890' ORDER BY Name, CurrencyIsoCode

This gets me a table that looks like this:

Product2Id      NAME        CURRENCYISOCODE UNITPRICE
12345           Example1    EUR             4960
12345           Example1    GBP             4000
12345           Example1    NOK             80000
12345           Example1    USD             5000
12346           Example2    EUR             1000
12346           Example2    GBP             950
12346           Example2    NOK             50000
12346           Example2    EUR             900

Instead I would like to consolidate every row that shares the same productname or associated product2id, to get an output that looks more like this:

Product2Id      NAME        EUR     GBP     NOK    USD
12345           Example1    4960    4000    80000  5000             
12346           Example2    1000    950     50000  900

Is there a clean way to do this using just the SOQL query editor in the data loader, or would I need something more advanced to consolidate the data in this fashion?

  • 2
    You want to group by a specific field, which is aggregate queries. It isn't fully supported by dataloader, you can try a creating grouping in your output excel file. – Raul Mar 6 '17 at 22:36
5

Adding this as an answer, after noticing the expected result.

Such a advanced query is not supported by a SOQL through dataloader and would not be even possible by a standard report, as you want to create different columns based on currency.

You would have two options:

  1. Easy but manual- Manipulate the exported excel sheet through dataloader manually.
  2. Customization- Create a Visualforce page (having contentType as application/octet-stream" with logic to display data. When you view this page it would be downloaded automatically.

Based upon the requirement you can choose the option.

|improve this answer|||||
4

An alternative is to use Conga Composer where the output goes into an Excel template that includes a pivot table.

You have two worksheets in Excel

  • The data worksheet that Conga assembles from your report as rows one per PriceBookEntry
  • A pivot table worksheet that uses as source the Excel table from the first worksheet. When you save the template into Conga, make this the worksheet the active worksheet so when the user opens the report, it goes straight to the rendered pivot table

Conga works well if the # of rows retrieved is within their limits (5,000 last I looked per query/report - 25,000 in aggregate)

You can launch Conga from a variety of ways including home page links, buttons on pages, or scheduled Conga workflows.

Great support from the vendor (I don't work for them but loved using the product whenever the users asked for Excel output)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.