Report headers lookup

General Discussion
1

I’m using Ignition 7.8.0 reporting module.
I need to develop a crosstab report like in the following picture


Each column header represents a component, whose numeric code is coming from the query. Is there any lookup function that could substitue the component numeric code with its name?

Thank you in advance,
regards

2

How are you generating that data now? You might be able to join the source data with a table of names vs. IDs, then pivot on the name.

3

I was going to suggest trying the PIVOT functionality of the view() function in my Simulation Aids module, but I remembered that the view() function was available only in client or designer scope. That was based on the fact that gateway tags couldn’t be datasets . . . which is no longer true in v7.8. :smiley:
So, I perused the code and it looks like it should just work™. I made that one change and uploaded to the marketplace today :slight_smile:
You might find it useful for this problem. Examples showing the use of the PIVOT clause are embedded in the demo project for this module, found at the bottom of this thread.

4

Thank you pturmel. I generate my data in a MS SQL Server stored procedure: hence I chose to generate my PIVOTed data with the PIVOT T-SQL statement and let Ignition show the data through a simple table.
My decision is also due to some wrong calculations made by the crosstab object: sometimes the <N/A> row (please refer to my previous screenshot) appears as a row with a value of 1 as row header (instead of <N/A>, which is correct, because some unpivoted values are NULL) and the corresponding values are all 0 (the screenshot shows the correct values, i.e. a 23 for component 2 and a 1 for component 13).

5

[quote=“pgmo”]I chose to generate my PIVOTed data with the PIVOT T-SQL statement and let Ignition show the data through a simple table.[/quote]Ok. So T-SQL can join the names before pivoting. If you share your T-SQL, I may be able to help you with that.[quote=“pgmo”]My decision is also due to some wrong calculations made by the crosstab object[/quote]Which crosstab object are you referring to? Results from my view() function? If so, I’d love to have a sample to use to fix it.

6

Don't worry pturmel, I mean the Ignition crosstab object which I first used to generate the attached table:


You can see here, there is a row header <N/A>, which is correct (I have some "general" stuff in the table that carry no StepNr: the field StepNr for that stuff is NULL - I'll filter it out): switching between the report editor and the preview pane, instead of <N/A> randomly it shows 1 and the associated values on the row are all 0 (the query behind is a simple SELECT, no T-SQL calculation, no pre-PIVOTING, a very simple SELECT on a normalized table: StepNr | ComponentID | Quantity).

7

Ok. So make a table (if you don't already have one) that has ComponentID & ComponentName columns, join it, and select the name instead of the ID. Then the crosstab will have the right raw data. Something like:Select StepNr, ComponentName, Quantity From stepqty Inner Join Components On stepqty.ComponentID=Components.ComponentID Where StepNr Is Not Null;