Calcultion done in query not showing in ssrs report.
-
I'm doing all my calculations in the query, and showing them on ssrs.
However, the following calculated field's value is not appearing is ssrs (it's blank). I don't know what could I be doing wrong here. When I run my query in toad, I get the values for perc_onsite.
Any help will be greatly appreciated!
The following is the code snippet. All the other fields's values are appearing except for perc_site, perc_service, and perc_report.
--display data select distinct market_location, market_code, onsite, lab_service, self_report, total_records, sum_site, sum_Service, sum_Report, sum_total_records, nvl(sum_onsite, 0)/nvl(sum_total_records, 1) perc_site, nvl(sum_LabService, 0)/nvl(sum_total_records, 1) perc_Service, nvl(sum_SelfReport, 0)/nvl(sum_total_records, 1) perc_fReport from rpt_report_data - soldout6000 - Monday, April 23, 2018 2:23 PM
Looks like you are running this query against an Oracle database (not just because you said Toad either). How are you connecting SSRS to the Oracle database?
- soldout6000 - Monday, April 23, 2018 2:23 PM
Your query is clearly PL/SQL, which means Oracle. Is there any significance to the presence of "--display data" appearing prior to the actual query ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I've connected my ssrs report with oracle. All the other fields from the query are showing up in the report.
However, the perc_site, perc_service, and perc_fReport fields are not showing any results in the matrix.
It is kind of odd because these fields do have values when I run them in oracle (toad). -
I've attached the screen shot of the fields in oracle, to show that I'm getting data for those fields, but their values are not appearing in ssrs. It is very odd. The names of the fields are slightly different than what I've specified in my code snippet.
- soldout6000 - Tuesday, April 24, 2018 7:48 AM
I've attached the screen shot of the fields in oracle, to show that I'm getting data for those fields, but their values are not appearing in ssrs. It is very odd. The names of the fields are slightly different than what I've specified in my code snippet.
Given the number of decimal places, there might be a need to round them down to fit what an ODBC connection will support, or alternatively, CAST them to varying character and then convert in the report and then use the converted values in the report. If you are using a Microsoft provided ODBC Driver for Oracle, please see the following page:
Some data types are not supported by it, and the page recommends not using it in future development. I'd be sure to get the Oracle driver. However, even there, not sure what limits ODBC might place on values with a lot of decimal places, so conversion to character and back on your own may work best.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
For what ever reason, I can't enlarge your pictures to where I can read them, just an FYI.
- Lynn Pettis - Tuesday, April 24, 2018 9:10 AM
try right-clicking on them and "Open in a new tab". The whole site has that kind of problem lately. Some pics pop up, and others are invisible. I stumbled across that alternative working...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, April 24, 2018 9:30 AM
You don't think I haven't done that? I even tried using a different browser, no joy. Like I said, for what ever reason, I can't see them.
- Lynn Pettis - Tuesday, April 24, 2018 9:34 AM
Never hurts to be sure of the obvious, but that is definitely NOT what happens to me. I just end up being FORCED to use that option on a regular basis. Inconvenient is the least I can call that. In your case - serious PITA. Re-boot? Said I, with a silly you-know-what-eating grin on my face... :hehe::hehe::hehe:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Steve (SSC Guru),
Thank you so much for your feedback. Yes, I needed to round the numbers because they were too big to fit in.
This resolved the issue and I'm able to display the values on my ssrs report.
Thank you once again for your help! - soldout6000 - Tuesday, April 24, 2018 10:38 AM
Glad I could help out. Just to be sure you understand, if you round a number down on decimal places, you are going to lose accuracy on that number, and it's not a problem that can't be solved. Just go ahead and CAST it to varying character on the Oracle side, and CONVERT it to a large enough decimal data type on the way into SQL Server.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Steve,
Up to how many decimals places are allowed in ssrs? - soldout6000 - Wednesday, April 25, 2018 9:42 AM
SQL Server supports data types for numbers with up to 38 overall decimal digits, and you primarily would have to take the character data (I'm assuming you would convert to varying character on the Oracle side) and use the CDec or CDbl conversion function as the expression in the text box where that column is to appear, which would then allow you to format the text box with as many decimal places as you want. The formatting isn't going to increase the precision of the data being converted to the data type of double, which is floating point, or decimal, which is exact representation. Not sure which will work better in your case, so you'll just have to test.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply