SQL PIVOT with Dynamic Columns in Report Designer

Ignition
1

Hello, If anyone is familiar with the SQL pivot command with dynamic columns, you’ll know that you need to first create a query of your columns in an XML Path format, then later use that result in the second query where you pivot your row data into its respective columns. I have tried implementing this in the report designer and have had no luck. I have tried using nested queries where the pivot command references the result of the parent query as a parameter. This almost works but the XML that results does not embed the column names into the data. Here is the snippit of code and XML. (Note: the images show hard-coded columns in the PIVOT command for testing. Using the parent query {Products} as a parameter returns an incorrect syntax by the designer possibly because the designer adds ‘@P2’ in quotes to reference the third parameter and PIVOT command needs the columns to be specified without quotes)

image.png900×376 21.4 KB

image.png1056×400 26 KB 

<?xml version="1.0" ?>
<sample-data>
	<!--This is the raw data used to create the report preview.-->
	<EndDate>2017-05-30 09:44:29</EndDate>
	<Report>
		<Gateway>Ignition-SERVER</Gateway>
		<Name>Test</Name>
		<Path>Test</Path>
		<Timestamp>2017-05-30 09:44:29</Timestamp>
	</Report>
	<StartDate>2017-05-25 09:44:29</StartDate>
	<query>
		<row-0>
			<Products>[03081],[03086],[22605],[23016],[32400],[81010]</Products>
			<sub_query>
				<row-0>
					<DATE>2017-05-25</DATE>
					<__>0</__>
					<__>0</__>
					<__>0</__>
					<__>292</__>
					<__>27</__>
					<__>243</__>
				</row-0>
			</sub_query>
		</row-0>
	</query>
</sample-data>

Note that while columns with data are returned, they do not contain the names of the columns provided. This makes stacked bar chart not display anything.

For reference, this is what SQL Mgmt. Studio returns:

image.png815×293 10.4 KB


Any help is appreciated…even if this involves scripting.

Thanks,
David

2

This might have to do with the names of the columns being returned. Our report rendering backend relies on datakeys, and purely numeric datakeys tend to do unusual things. I know that you’re escaping the column names correctly (with the [] syntax) but try adding a leading character or something else so that they’re treated as strings, eg [s03081] or something similar.

3

Thanks PGriffith. This helped. I added a letter and the columns now have names. However, the Bar Chart still does not display the data and I still can’t use {Products} as a parameter from the parent query as the columns will vary based on the date range provided.

Any ideas on that one?

4

Making progress. If using a sub-query, the Bar Chart must be in a Table Details so that it can access the first row of the parent results. I have moved the sub-query into its own second parent query. Bar Chart now displays data accordingly.

Now, still need for parameters to be passed without quotes and this might work.