+ Reply to Thread
Results 1 to 12 of 12

Create a report from a Data Model that shows values only

  1. #1
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Create a report from a Data Model that shows values only

    I have a Data Model with two tables in it. Simply these are;

    Order - which has an Order ID and a Creation Date field
    Order Line - which has a line for each line against the order and has fields for Item Code and Quantity

    A 1:Many relationship exists between Order and Order Line.


    I want to be able to create a table/report that shows each Order (Order ID) and the Item Codes against that order. A Timeline slicer will allow me to restrict the period of time that is viewed.
    (Adding Item Code as a Row is the format I want, but it shows all of the Item Codes against each Order, not just the Item Codes for that Order)

    A Pivot Table does not really work here because the Item Code field is text, so it only shows Count if used as a Value. I want to see the actual Item Code.

    Can someone please guide me to a better solution (bearing in mind that my source tables will have a lot more fields in time).

    Thanks
    Attached Files Attached Files
    Last edited by Nerdio; 04-13-2021 at 05:45 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    Are you still using Excel 2010? If not, please update your profile. This would be a prime candidate for PowerQuery, if you have a newer version of Excel.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Re: Create a report from a Data Model that shows values only

    I am using Excel 365 (upated profile - sorry did not realise it was so out of date!)

    Sample spreadsheet attached

    Thank you

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    Sorry - the workbook is useless!

    We need to see the source data (just a few rows, not thousands!) and a small mock-up showing how you want the processed data to look.

    Thanks - when this is provided, I'll take a look.

  5. #5
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Re: Create a report from a Data Model that shows values only

    I have uploaded a stripped down version with a few orders and a few order lines. The spreadsheet shows where I would like to get to with an ouput.

    Ultimately I would like to be able to use the Timeline Slicer to restrict the orders shown. From there I can develop I think.

    Thank you.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    You cannot get from the pivot table to what you want because the data you need isn't in the pivot table.

    This is why I asked for RAW data - forget the pivot table.

  7. #7
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Re: Create a report from a Data Model that shows values only

    I have uploaded two CSV files that represent the source data.
    The original source data holds a lot more fields, some of which I cannot share - hence I have stripped it down to make these CSVs

    I hope this is what you are after.

    Thank you

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    No, no, no!!!

    PLEASE put a sample of raw data into one workbook along with the layout you want. Sorry - you are making this unnecessary fiddly for us!

  9. #9
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Re: Create a report from a Data Model that shows values only

    Sorry - I don't mean to be awkward.

    Attached is a workbook. On one worksheet there is a table of Order IDs and Creation Dates. On a second worksheet are the Order Lines with Order IDs providing a relationship back to the order table. The Order Line table includes the Item Code.

    On the 3rd worksheet there is a table that I have made 'by hand' that shows the sort of thing I want to get to. The key here is that despite all of this information being available in the Order Line table I want to be able to filter the table (Timeline Slicer or similar) by the Creation Date in the Order Table.

    Thank you.

    (I would like to be able to use a Data Model to store these tables as I will have other requirements that require Measures and Pivot Tables - one step at a time though).

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    In which of your attachments will I find everything together?

  11. #11
    Registered User
    Join Date
    12-05-2011
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    42

    Re: Create a report from a Data Model that shows values only

    Quote Originally Posted by AliGW View Post
    In which of your attachments will I find everything together?
    Order-OrderLine Worksheet.xlsx (looks like it may not have been attached).

    Thanks

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,079

    Re: Create a report from a Data Model that shows values only

    Really sorry - I am not getting this at all. Thank you for putting everything into one workbook - i was going to suggest a PowerQuery solution, but I see you know PQ so should be able to work it out that way yourself. Therefore, I will assume that a PQ solution will not be suitable for you.

    In the workbook, what do you mean by filtering the list using the smaller source table? I don't believe you are actually showing us what you want, and if you are, it doesn't match your annotations.

    I'll step aside - you need fresh eyes on this.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 06-19-2020, 04:06 PM
  2. VBA to Open Monarch10 and run Model > Report and save final report : Please Assist
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-08-2016, 04:23 AM
  3. [SOLVED] Acess Report the shows data across then down
    By jik_ff in forum Access Tables & Databases
    Replies: 1
    Last Post: 03-12-2014, 08:04 PM
  4. Making a report that shows only values below a certain percentage
    By pbatt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-13-2014, 02:05 AM
  5. Create a model that will generate a column of numbers based on model parameters
    By tncanoeguy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-01-2013, 05:47 PM
  6. Replies: 9
    Last Post: 05-17-2012, 09:36 AM
  7. Replies: 2
    Last Post: 06-29-2011, 04:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1