nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Set query FROM table using report parameter

amorris

Hi, I have multiple tables with the same data structure, and am designing a report that I can run on them. I want to set a report parameter for the table name that I can use in my msyql database query, but I can't figure out how.

When I create the data set, it's great that I can use parameters in the form of SELECT * FROM <table> WHERE ?, and set them to report parameters, but I get an error if I set the ? to the table, such as:

SELECT * FROM ? WHERE 1

Is there another way I can do this?

I tried setting the query text under property binding (in the Data Set dialog), such as: "SELECT * FROM "+params["DataTable"].value+" WHERE 1", but that was also resulted in an error.

Find more posts tagged with

Comments

bhanley

Try modifying your query in the beforeOpen scripting event. You can create your query to point to one of the target tables int he data set editor. This will allow your data set to function and you can build out your report using real data to preview. Then in the beforeOpen script, you can modify the query however you like. Just modify "this.queryText" to equal your new query, pointing to the table you need. 
 
Something like this: 
 
Original Query: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT * FROM Table_1 WHERE 1
</pre>
 
beforeOpen Script: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
this.queryText = this.queryText.replace("Table_1", params["DataTable"].value);
</pre>
 
Good Luck!

amorris

Thanks - that works great. I got the property binding to work (had an unescaped quotation mark), but that was just really messy to deal with. This makes it so much easier!