SSRS Grouping Problem

Question

SSRS Grouping Problem

sgmunson

SSC Guru

Points: 110640
More actions
June 12, 2018 at 8:47 am

#351090

I have a report that has a dataset problem that I'm not sure I can fix. The query for it is dynamic SQL because the company won't let me use a string splitting function like Jeff Moden's DelimitedSplit8K due to a fair amount of "not invented here" syndrome. I would have coded to use the splitter, but as they insist on using a user defined table type to pass the values, I have to code an insert for all the values and then execute the stored procedure. This runs into the problem of SSRS using SET FMTONLY ON before running the code, so I can't get the dataset to REFRESH the fields collection. Thus I manually edited the RDL file to contain the proper data types. However, having done that so that I actually HAVE a dataset, I've found that when I run the report, it returns an error indicating that the grouping expression returned a value that is of the wrong data type. As I'm just using one of the columns from the dataset as the Grouping expression, I figured that if it doesn't like the data type, then perhaps I just need to convert it to string, so I slapped a CStr() function around it and then tried again. Now it says that CalculatedFieldWrapperImpl isn't a valid data type for conversion to string. Any ideas, folks? Sue H?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

Just want to double check, but I assume you're saying that the fields can't refresh because the query is dynamic and, as the parameters don't have a value, SSRS can't create a first data set (as the dynamic SQL doesn't have a value). Correct?

When I've used dynamic SQL for an SSRS report, it comes up with a dialogue box (like below), where you can manually enter values for the needed parameters. Does that help Refresh the fields? If so, the datatypes returned will be the correct ones, which might mean the issue resolves itself, or puts you in a better place.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

You have a couple of options:

sp_describe_first_result_set

IF 1=0
SELECT ... FROM ... WHERE -- this should return and define all columns that would normally be returned

EXEC ... WITH RESULT SETS ( ) -- you should be able to use this in the dataset for SSRS to execute the procedure and describe the result set

With that said - I am not really clear why you have to create dynamic SQL if they are passing in a table type. I must be missing something in this process...

And finally - you can always create an in-line tally and splitter without creating a separate function. This would just be dedicated to splitting a specific parameter instead of dynamically splitting any string passed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Sue_H SSC Guru Points: 90860 More actions · Answer 3

sgmunson - Tuesday, June 12, 2018 8:47 AM
I have a report that has a dataset problem that I'm not sure I can fix. The query for it is dynamic SQL because the company won't let me use a string splitting function like Jeff Moden's DelimitedSplit8K due to a fair amount of "not invented here" syndrome. I would have coded to use the splitter, but as they insist on using a user defined table type to pass the values, I have to code an insert for all the values and then execute the stored procedure. This runs into the problem of SSRS using SET FMTONLY ON before running the code, so I can't get the dataset to REFRESH the fields collection. Thus I manually edited the RDL file to contain the proper data types. However, having done that so that I actually HAVE a dataset, I've found that when I run the report, it returns an error indicating that the grouping expression returned a value that is of the wrong data type. As I'm just using one of the columns from the dataset as the Grouping expression, I figured that if it doesn't like the data type, then perhaps I just need to convert it to string, so I slapped a CStr() function around it and then tried again. Now it says that CalculatedFieldWrapperImpl isn't a valid data type for conversion to string. Any ideas, folks? Sue H?

Did you check for cached data? That can result in plenty of odd things from previews and you often need to delete it. Extension .rdl.data

Sue

sgmunson SSC Guru Points: 110640 More actions · Answer 4

Thom A - Tuesday, June 12, 2018 9:55 AM
Just want to double check, but I assume you're saying that the fields can't refresh because the query is dynamic and, as the parameters don't have a value, SSRS can't create a first data set (as the dynamic SQL doesn't have a value). Correct?
When I've used dynamic SQL for an SSRS report, it comes up with a dialogue box (like below), where you can manually enter values for the needed parameters. Does that help Refresh the fields? If so, the datatypes returned will be the correct ones, which might mean the issue resolves itself, or puts you in a better place.

Good idea, but as my SQL query is an expression, the parameters portion of the dataset properties has no need to be populated. For some reason, I've now gotten past the problem, but am not entirely sure how.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 5

Jeffrey Williams 3188 - Tuesday, June 12, 2018 12:53 PM
You have a couple of options:
sp_describe_first_result_set
IF 1=0
SELECT ... FROM ... WHERE -- this should return and define all columns that would normally be returned
EXEC ... WITH RESULT SETS ( ) -- you should be able to use this in the dataset for SSRS to execute the procedure and describe the result set
With that said - I am not really clear why you have to create dynamic SQL if they are passing in a table type. I must be missing something in this process...
And finally - you can always create an in-line tally and splitter without creating a separate function. This would just be dedicated to splitting a specific parameter instead of dynamically splitting any string passed.

I think we both know that there are some places and people who just don't listen to reason. A string splitting function like Jeff Moden's DelimitedSplit8K would be a much easier option for this purpose, but they're insisting on not using anything "new", and "especially from the internet"... Fear and paranoia... not a good mix. As you and I both know, that splitter is not only not new, but is pretty much the gold standard, but again, some folks just don't have much exposure to the real world. Anyway, what I have no choice but to do is use an existing user-defined table type that has one column (ObjectItem) that is of the varchar(50) data type. And there are other SSRS reports that use that. They insert into a declared variable of that "type", and then execute the stored procedure using the declared variable as a parameter to the EXEC statement, along with adding any other parameters by including the parameter values in the expression, effectively creating dynamic SQL.

Thus the grief. Anyway, for some reason I am now past the problem, and I can't explain why. I think that Sue H may be on to something with the .rdl.data file. I've since then had to add another column to the stored procedure's result set to help with grouping, and I again ran right back into the problem, and as soon as I expanded the date parameters I was supplying, the report ran again, which suggests the possibility that such forced the deletion of the cached data. I will have to look further into that possibility tomorrow. The report seems to run okay on the server, but I'm going to want to see what happens when my data parameters cause there to be no data.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Sue_H - Tuesday, June 12, 2018 12:54 PM
sgmunson - Tuesday, June 12, 2018 8:47 AM
I have a report that has a dataset problem that I'm not sure I can fix. The query for it is dynamic SQL because the company won't let me use a string splitting function like Jeff Moden's DelimitedSplit8K due to a fair amount of "not invented here" syndrome. I would have coded to use the splitter, but as they insist on using a user defined table type to pass the values, I have to code an insert for all the values and then execute the stored procedure. This runs into the problem of SSRS using SET FMTONLY ON before running the code, so I can't get the dataset to REFRESH the fields collection. Thus I manually edited the RDL file to contain the proper data types. However, having done that so that I actually HAVE a dataset, I've found that when I run the report, it returns an error indicating that the grouping expression returned a value that is of the wrong data type. As I'm just using one of the columns from the dataset as the Grouping expression, I figured that if it doesn't like the data type, then perhaps I just need to convert it to string, so I slapped a CStr() function around it and then tried again. Now it says that CalculatedFieldWrapperImpl isn't a valid data type for conversion to string. Any ideas, folks? Sue H?
Did you check for cached data? That can result in plenty of odd things from previews and you often need to delete it. Extension .rdl.data
Sue

As noted in my response to Jeffrey Williams, I think you are on to something (not that you aren't usually on to whatever problem you post a reply to) with regard to the cached data. See my response to him for what I think might be occurring. I'll try again tomorrow to see if I can influence this one way or the other and post back with results.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Sue_H SSC Guru Points: 90860 More actions · Answer 7

sgmunson - Tuesday, June 12, 2018 1:54 PM
As noted in my response to Jeffrey Williams, I think you are on to something (not that you aren't usually on to whatever problem you post a reply to) with regard to the cached data. See my response to him for what I think might be occurring. I'll try again tomorrow to see if I can influence this one way or the other and post back with results.

Yeah what you posted to Jeffrey is often exactly what happens - adding columns, get the issues. And the error message you originally posted I've gotten from the cached data before at least a few times. Could be something else but we'll see if you can nail it down tomorrow.
And thanks for your kind words 🙂

Sue

sgmunson SSC Guru Points: 110640 More actions · Answer 8

My apologies for not posting back sooner, but here's what happened. It just started working when I changed the date range provided as parameters. I know for sure that once you change the data range in a way that requires SSRS to re-run the query, that the cached data file will get wiped out, so I'm pretty sure that Sue pretty nailed the problem here. Unfortunately, I did not get the opportunity to actually test it the way I would have liked, and given some new assignments, I'm going to have to spend my time on other things and won't be able to get back to this in any timeframe in which I could possibly remember to try again... Thanks everyone for helping out!

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)