July 23, 2018 at 2:46 am
There is a report I print, which includes a list of people, e.g. students, with their relevant content, e.g. their results for Year 1, 2 and 3.
Prior the list I print the titles, e.g. Name, Year 1 Result, Year 2 Result, Year 3 Result.
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'100' AS Year_2_Mark,
'80' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','70','60'
UNION ALL
SELECT 'Corbin',
'60','50','40'
)
SELECT * FROM STU
Now I have a new request - there is only one change required: the titles must be dynamic. In case there is no data in a column, hide the title, so in the case below I will only need to
show Name, Year 1 Result, [NOTHING] , Year 3 Result.
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'' AS Year_2_Mark,
'80' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','','60'
UNION ALL
SELECT 'Corbin',
'60','','40'
)
SELECT * FROM STU
Note there is NO change to the order of the columns or anything else - ONLY need to hide a name of the title.
I can do it locally, from an application, going through the result set, but I wonder if there is an option to know it before hand, e.g. using PARTITION BY as below?
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'' AS Year_2_Mark,
'100' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','','60'
UNION ALL
SELECT 'Corbin',
'60','','40'
)
SELECT *,
LEN(MAX(Year_1_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_1,
LEN(MAX(Year_2_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_2,
LEN(MAX(Year_3_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_3
FROM STU
Many thanks for the help, as usual.
July 23, 2018 at 1:16 pm
Bad idea. Squared. If you used an SSRS Matrix in your report (assuming you are using SSRS), then you would never see that column if you never had any values for it. But in this case, you just have blank values. This complicates everything. Why do you need that functionality? We may be able to develop a much better alternative with more information.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2018 at 2:31 pm
sgmunson - Monday, July 23, 2018 1:16 PMBad idea. Squared. If you used an SSRS Matrix in your report (assuming you are using SSRS), then you would never see that column if you never had any values for it. But in this case, you just have blank values. This complicates everything. Why do you need that functionality? We may be able to develop a much better alternative with more information.
I use T-SQL. The application generates a pdf file, distributed to multiple customers. Some of them have their own OCR systems to scan and process those pdf's (we can discuss how efficient it is, but it will not change customers approach).
The new request is to only hide a column, which has no data, so for instance if the 4th (and last) column is always student's ID it cannot be now the 3rd one only because one of the other columns has no value.
Hope it explains the functionality.
July 25, 2018 at 12:03 pm
BOR15K - Monday, July 23, 2018 2:31 PMsgmunson - Monday, July 23, 2018 1:16 PMBad idea. Squared. If you used an SSRS Matrix in your report (assuming you are using SSRS), then you would never see that column if you never had any values for it. But in this case, you just have blank values. This complicates everything. Why do you need that functionality? We may be able to develop a much better alternative with more information.I use T-SQL. The application generates a pdf file, distributed to multiple customers. Some of them have their own OCR systems to scan and process those pdf's (we can discuss how efficient it is, but it will not change customers approach).
The new request is to only hide a column, which has no data, so for instance if the 4th (and last) column is always student's ID it cannot be now the 3rd one only because one of the other columns has no value.
Hope it explains the functionality.
Ummm... not exactly clear. If you hide a column, in it's entirety, then that column isn't going to occupy ANY space at all in an SSRS report with a Matrix control. However, a tablix-based SSRS report would have to always have the column present, in case there might be data. One could, however, control the visibility of that column if all values for it are NULL. So the exact process by which the PDF is generated really needs to be known in excruciating detail in order to know exactly HOW to go about "hiding" the column. I'm guessing it's SSRS report design as the best way to hide one, but again, without more details on the how, I can't be too sure that such would help. I'm also pretty sure that you have to have a query that consistently delivers an identical set of columns for any given SSRS report. This probably also applies to Crystal Reports or any other similar reporting tool, so I'm doubtful that somehow selecting a limited subset of columns is ever going to a good method. There are too many other good ways to do it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 25, 2018 at 12:56 pm
sgmunson - Wednesday, July 25, 2018 12:03 PMBOR15K - Monday, July 23, 2018 2:31 PMsgmunson - Monday, July 23, 2018 1:16 PMBad idea. Squared. If you used an SSRS Matrix in your report (assuming you are using SSRS), then you would never see that column if you never had any values for it. But in this case, you just have blank values. This complicates everything. Why do you need that functionality? We may be able to develop a much better alternative with more information.I use T-SQL. The application generates a pdf file, distributed to multiple customers. Some of them have their own OCR systems to scan and process those pdf's (we can discuss how efficient it is, but it will not change customers approach).
The new request is to only hide a column, which has no data, so for instance if the 4th (and last) column is always student's ID it cannot be now the 3rd one only because one of the other columns has no value.
Hope it explains the functionality.Ummm... not exactly clear. If you hide a column, in it's entirety, then that column isn't going to occupy ANY space at all in an SSRS report with a Matrix control. However, a tablix-based SSRS report would have to always have the column present, in case there might be data. One could, however, control the visibility of that column if all values for it are NULL. So the exact process by which the PDF is generated really needs to be known in excruciating detail in order to know exactly HOW to go about "hiding" the column. I'm guessing it's SSRS report design as the best way to hide one, but again, without more details on the how, I can't be too sure that such would help. I'm also pretty sure that you have to have a query that consistently delivers an identical set of columns for any given SSRS report. This probably also applies to Crystal Reports or any other similar reporting tool, so I'm doubtful that somehow selecting a limited subset of columns is ever going to a good method. There are too many other good ways to do it.
Not sure why you have assumed SSRS. the call comes from C# application.
July 25, 2018 at 1:25 pm
BOR15K - Wednesday, July 25, 2018 12:56 PMNot sure why you have assumed SSRS. the call comes from C# application.
If that's your tool, then you have no way to avoid including the column in the results, as otherwise you have no means to detect that the column is entirely composed of NULL.values. So what, exactly, is your C# code going to do with the data that comes back from the query? Lacking a reporting tool, I fail to see how anything but a combination of running the original query and using it to determine that the particular column is composed entirely of NULL values, does you any good at all. There's no magic tool in T-SQL to determine that a given column is entirely NULL, without you selecting that column and then finding that perhaps both the MIN value and the MAX value for it are both NULL.. Thus I'm not at all sure why you need to hide the column. If you are going to use C#, you can always determine what columns you want to display or not display within your application. But any query you submit would still somehow have to test a given column in some fashion in order to determine if ALL of it's values are NULL.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 25, 2018 at 1:46 pm
sgmunson - Wednesday, July 25, 2018 1:25 PMBOR15K - Wednesday, July 25, 2018 12:56 PMNot sure why you have assumed SSRS. the call comes from C# application.If that's your tool, then you have no way to avoid including the column in the results, as otherwise you have no means to detect that the column is entirely composed of NULL.values. So what, exactly, is your C# code going to do with the data that comes back from the query? Lacking a reporting tool, I fail to see how anything but a combination of running the original query and using it to determine that the particular column is composed entirely of NULL values, does you any good at all. There's no magic tool in T-SQL to determine that a given column is entirely NULL, without you selecting that column and then finding that perhaps both the MIN value and the MAX value for it are both NULL.. Thus I'm not at all sure why you need to hide the column. If you are going to use C#, you can always determine what columns you want to display or not display within your application. But any query you submit would still somehow have to test a given column in some fashion in order to determine if ALL of it's values are NULL.
My LEN(MAX(Year_1_Mark) OVER (PARTITION BY 1)) does the job and as proper index is in place it has a fairly good show plan too. I will stick to it. Thank you for the help.
July 25, 2018 at 2:37 pm
sgmunson - Wednesday, July 25, 2018 1:25 PMBOR15K - Wednesday, July 25, 2018 12:56 PMNot sure why you have assumed SSRS. the call comes from C# application.If that's your tool, then you have no way to avoid including the column in the results, as otherwise you have no means to detect that the column is entirely composed of NULL.values. So what, exactly, is your C# code going to do with the data that comes back from the query? Lacking a reporting tool, I fail to see how anything but a combination of running the original query and using it to determine that the particular column is composed entirely of NULL values, does you any good at all. There's no magic tool in T-SQL to determine that a given column is entirely NULL, without you selecting that column and then finding that perhaps both the MIN value and the MAX value for it are both NULL.. Thus I'm not at all sure why you need to hide the column. If you are going to use C#, you can always determine what columns you want to display or not display within your application. But any query you submit would still somehow have to test a given column in some fashion in order to determine if ALL of it's values are NULL.
Since NULL values are excluded from MIN/MAX unless there are only NULL values, you do not need to test both the MIN and the MAX values. Either one will return NULL if and only if the entire column is NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 25, 2018 at 3:02 pm
drew.allen - Wednesday, July 25, 2018 2:37 PMsgmunson - Wednesday, July 25, 2018 1:25 PMBOR15K - Wednesday, July 25, 2018 12:56 PMNot sure why you have assumed SSRS. the call comes from C# application.If that's your tool, then you have no way to avoid including the column in the results, as otherwise you have no means to detect that the column is entirely composed of NULL.values. So what, exactly, is your C# code going to do with the data that comes back from the query? Lacking a reporting tool, I fail to see how anything but a combination of running the original query and using it to determine that the particular column is composed entirely of NULL values, does you any good at all. There's no magic tool in T-SQL to determine that a given column is entirely NULL, without you selecting that column and then finding that perhaps both the MIN value and the MAX value for it are both NULL.. Thus I'm not at all sure why you need to hide the column. If you are going to use C#, you can always determine what columns you want to display or not display within your application. But any query you submit would still somehow have to test a given column in some fashion in order to determine if ALL of it's values are NULL.
Since NULL values are excluded from MIN/MAX unless there are only NULL values, you do not need to test both the MIN and the MAX values. Either one will return NULL if and only if the entire column is NULL.
Drew
Thank you, Drew
But I do not have NULL in my example, but an empty string. By "empty" I mean it will never be NULL, but can be space or even two spaces. LEN function ignores them, so even LEN (' ') will still be zero.
July 26, 2018 at 5:09 am
BOR15K - Monday, July 23, 2018 2:46 AMThere is a report I print, which includes a list of people, e.g. students, with their relevant content, e.g. their results for Year 1, 2 and 3.
Prior the list I print the titles, e.g. Name, Year 1 Result, Year 2 Result, Year 3 Result.
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'100' AS Year_2_Mark,
'80' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','70','60'
UNION ALL
SELECT 'Corbin',
'60','50','40'
)SELECT * FROM STU
Now I have a new request - there is only one change required: the titles must be dynamic. In case there is no data in a column, hide the title, so in the case below I will only need to
show Name, Year 1 Result, [NOTHING] , Year 3 Result.
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'' AS Year_2_Mark,
'80' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','','60'
UNION ALL
SELECT 'Corbin',
'60','','40'
)SELECT * FROM STU
Note there is NO change to the order of the columns or anything else - ONLY need to hide a name of the title.
I can do it locally, from an application, going through the result set, but I wonder if there is an option to know it before hand, e.g. using PARTITION BY as below?
WITH STU AS
(SELECT 'Theresa' AS Student_Name,
'90' AS Year_1_Mark,
'' AS Year_2_Mark,
'100' AS Year_3_Mark
UNION ALL
SELECT 'Raad',
'80','','60'
UNION ALL
SELECT 'Corbin',
'60','','40'
)SELECT *,
LEN(MAX(Year_1_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_1,
LEN(MAX(Year_2_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_2,
LEN(MAX(Year_3_Mark) OVER (PARTITION BY 1)) AS Max_Len_Column_3
FROM STUMany thanks for the help, as usual.
Can you post the original data that you make this report from? I ask because the report already has the damage done. There's a really simple way to pull this off but we need the original data that you're making this report from. Please see the article at the first link under "Helpful Links" for how to post the data so that it's easily consumable for us to help you more quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply