locked
Using the output table as the source RRS feed

All replies

  • You don't have to use the output query as table source, you can simply "reference" them by clicking the "Reference" button in the query ribbon.

    Peter Q. http://blogs.msdn.com/peter_qian

    Monday, April 22, 2013 11:23 PM
  • Hi Peter,

    Sorry, I've just realised I wasn't clear in my question - I want to be able to use the table that a query outputs as an input (in some cases) to the *same* query. For example, I have a table A and a query that uses this as an input; it outputs to table B. What I want is for my query then, in some cases, to take table B as its input and then output again to table B.

    If I use a reference I get the following error:

    A cyclic reference was encountered during evaluation

    I've partially worked around the problem by using a third table that uses Excel formulas to display the results of the table created by Data Explorer, but that only reduces the number of times the error displays.

    Regards,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, April 23, 2013 7:40 AM
  • I maybe missing something, but I don't quite follow your scenario. You are recursively reshaping a table somehow? 


    Peter Q. http://blogs.msdn.com/peter_qian

    Tuesday, April 23, 2013 8:56 AM
  • Yes. It's not a real customer requirement - I'm trying to implement Jon Conway's game 'Life' using Data Explorer for my blog :-) For this I want to read data from an input table, calculate the number of cells alive in the next iteration, output that to a table, then use that output table as the starting point for the next iteration. As I said, I've got it working more or less, but I keep getting random errors. Chris

    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, April 23, 2013 11:28 AM
  • I see, this scenario isn't really supported today. We aren't very good at dealing with recursive queries :)

    That said, I suspect the random error might come from timing\race conditions. Hopefully you can find a way to cache some of the steps so it becomes faster.

    Regards,

    PQ


    Peter Q. http://blogs.msdn.com/peter_qian

    Tuesday, April 23, 2013 10:31 PM