January 10, 2017 at 7:43 am
I am currently working with a SQL database that stores data logged from engines so the columns are the channels (Speed, Torque, Temperatures etc.) and the rows are single log events that have identifiers to show where in the test the log occured.
I have been using RRS for a couple of years to run queries on the database and create plots where the channels (columns) are fixed and the rows that are returned are defined by Parameters which apply filters to certain channels in the database.
What I cannot seem to do is use Parameters to define which channels (columns) are returned in the query and hence plotted.
I am unsure whether I am getting confused with the syntax or whether the process for defining columns is different to that used for filtering rows.
Any help would be great.
January 10, 2017 at 1:02 pm
I'm afraid I'm a not quite sure I understand how your data looks from your description. This is a bit of a stab in the dark, as I don't have an DDL/DLM but are you saying that depending on the parameter you want a different column's value returned? For example, something like:
SELECT @kpi AS ReturnValueName,
CASE @kpi WHEN 'Speed' THEN Speed
WHEN 'Temp' THEN Temperature
WHEN 'Torque' THEN Torque
ELSE 'Invalid Option'
END AS ReturnValue
FROM MyTable;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 11, 2017 at 1:14 am
Thanks for the response.
Didn't realise you could use Case like that and I can see that working but that's a lot of Case statements for the large number of columns in the database and the database is also dynamic so new columns can be added on the fly.
Also how would I then apply the same logic to a chart so it plots that column?
January 11, 2017 at 1:26 am
Here's an example of one of the current queries if that helps
SELECT
TestRuns.AlternateTestRunId
,[HDCW.553.STARS.Report].StepName
,[HDCW.553.STARS.Report].DynoSpeed
,[HDCW.553.STARS.Report].DynoTorque
,[HDCW.553.STARS.Report].EngPower
,[HDCW.553.STARS.Report].rFuelEngIn
,[HDCW.553.STARS.Report].tCoolHeadOut
,[HDCW.553.STARS.Report].tCoolEngOut
,[HDCW.553.STARS.Report].tOilEngSump
,[HDCW.553.STARS.Report].tAirPostIC
,[HDCW.553.STARS.Report].pExhMain
,[HDCW.553.STARS.Report].tFuelEngIn
,[HDCW.553.STARS.Report].Counter
,[HDCW.553.STARS.Report].tExhMain
,[HDCW.553.STARS.Report].[Time]
FROM
TestRuns
INNER JOIN [HDCW.553.STARS.Report]
ON TestRuns.DataFileId = [HDCW.553.STARS.Report].DataFileId
WHERE
TestRuns.AlternateTestRunId LIKE @AlternateTestRunId
AND [HDCW.553.STARS.Report].StepName LIKE @StepName
Rather than the columns being fixed then I want the selection of them to be based on Parameters.
January 11, 2017 at 2:45 am
You could achieve this by using expression for the visibility of the columns. So you have every column in your design view, however, your speed column might only be displayed if your KPI has a value of Speed or Torque. For example:
=iif((Parameters!KPI.Value = "Speed") OR (Parameters!KPI.Value = "Torque"), FALSE, TRUE)
You could do the same on your chart as well.
What you can't do is have a varying number of columns, or vary names of your columns. SSRS won't like this, as it expects a certain dataset. If the column [speed] no longer exists (or it's now called [cc]), as you selected 'Engine Size' for your parameter, then your report will fail.
Either way, you're going to need to do the leg work on getting your front end to display what you need. Either by using CASE statements, or Expressions, there is no magic "I only wanna display this when..." button.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply