... I feel sorry if this report outlasts me and someone else has to maintain it, though. The this-is-barely-a-formula box stripped out the spacing to store the 16 nested if statements, so it's even less readable than you might imagine.
Execute each test in sequence and add 1 if test is true. You only get the right number if all tests are true - a single if test at the end checks for that.
This part of the software won't even give me a case statement, much less a loop structure or variables that I can alter the value of. The only test I can do is an if statement, and I can only have the code execute the if statement once, not "execute a sequence of if statements until you get the result you want". Think "trying to code using an Excel cell, without acces to macros". Except that doesn't work either, because Excel has tons of functions and this ... has an if statement.
No, it's like trying to code in ONE Excel cell. Just one! I don't get acess to as many cells as I want. It has to give the right result on a single pass without ever being able to look at anything else that happened anywhere else. I can't construct a series of other functions for it to look at and see what their results are, because it is too stupid for that.
SQL is a set-operating tool, not a row-by-row processor, so it can take only one pass.
Was gonna suggest case, but I see you looked for that. That's a more advanced SQL, so your tool might be very old and basic.
OTOH, I do have an Excel spreadsheet that has serious nested calculations going on - hideous. I have to copy the thing into Word to edit it, then paste it back. Without all the formatting I used in Word to make it readable.
Yeah, the full explanation (which I will type in since I'm home now and not writing on my phone) goes like this:
I'm using SQL Server Report Builder. The people who control the database they want me to build reports on will not give me access to the actual database. Instead, they built a shared dataset. This means that instead of being able to use SQL to write an actual query, my report is effectively handed their pre-built query which I cannot modify.
What my report needs to do is look at one the fields in the dataset and then, based on what the field contains, give one of a few different results. But I can't make converting Field Y into Result X part of my query because I don't get to write a query.
SQL Server Report Builder will let me put "expressions" on my report in place of fields, and "expressions" can contain formulas. The functions available for these expressions are incredibly limited. Like, you'd think "this is an SQL report builder; why wouldn't it use SQL syntax for its functions?" But it doesn't. The expressions use their own unique horrible syntax (the if function is called iif, presumably because "if" was too easy to remember). And just like Excel, you can't format the expressions to make them readable. I wrote my 16 nested if statements in Notepad so I could have line breaks while I was constructing it. The line breaks get stripped out when I paste them into the expression window.
I saved the Notepad version of it in case I ever need to edit it someday. Y'know. To jam a 17th if statement in there. x_x
![[identity profile]](https://www.dreamwidth.org/img/silk/identity/openid.png){kind=small}
![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png)
timeasmymeasure
no subject
Date: 2014-11-13 10:06 pm (UTC)No way to do anything less of a cludge?
no subject
Date: 2014-11-14 10:05 pm (UTC)no subject
Date: 2014-11-14 12:13 am (UTC)no subject
Date: 2014-11-14 10:06 pm (UTC)no subject
Date: 2014-11-14 02:31 pm (UTC)-TG
no subject
Date: 2014-11-14 10:08 pm (UTC)no subject
Date: 2014-11-14 04:24 pm (UTC)no subject
Date: 2014-11-14 10:09 pm (UTC)... I feel sorry if this report outlasts me and someone else has to maintain it, though. The this-is-barely-a-formula box stripped out the spacing to store the 16 nested if statements, so it's even less readable than you might imagine.
no subject
Date: 2014-11-15 04:01 pm (UTC)no subject
Date: 2014-11-14 05:59 pm (UTC)no subject
Date: 2014-11-14 09:35 pm (UTC)Take a variable and set it to 0
Execute each test in sequence and add 1 if test is true. You only get the right number if all tests are true - a single if test at the end checks for that.
no subject
Date: 2014-11-14 10:04 pm (UTC)Really, all I have is a hammer.
no subject
Date: 2014-11-14 10:14 pm (UTC)Cell a1
IF(test1,1,0)
Cell a2
IF(test2,1,0)
...
Cell b1
SUM(A:A)
The sum will be equal to the number of tests only if every test is true
no subject
Date: 2014-11-14 10:34 pm (UTC)no subject
Date: 2014-11-15 03:03 am (UTC)SQL is a set-operating tool, not a row-by-row processor, so it can take only one pass.
Was gonna suggest case, but I see you looked for that. That's a more advanced SQL, so your tool might be very old and basic.
OTOH, I do have an Excel spreadsheet that has serious nested calculations going on - hideous. I have to copy the thing into Word to edit it, then paste it back. Without all the formatting I used in Word to make it readable.
So I feel your pain.
no subject
Date: 2014-11-15 03:25 am (UTC)Yeah, the full explanation (which I will type in since I'm home now and not writing on my phone) goes like this:
I'm using SQL Server Report Builder. The people who control the database they want me to build reports on will not give me access to the actual database. Instead, they built a shared dataset. This means that instead of being able to use SQL to write an actual query, my report is effectively handed their pre-built query which I cannot modify.
What my report needs to do is look at one the fields in the dataset and then, based on what the field contains, give one of a few different results. But I can't make converting Field Y into Result X part of my query because I don't get to write a query.
SQL Server Report Builder will let me put "expressions" on my report in place of fields, and "expressions" can contain formulas. The functions available for these expressions are incredibly limited. Like, you'd think "this is an SQL report builder; why wouldn't it use SQL syntax for its functions?" But it doesn't. The expressions use their own unique horrible syntax (the if function is called iif, presumably because "if" was too easy to remember). And just like Excel, you can't format the expressions to make them readable. I wrote my 16 nested if statements in Notepad so I could have line breaks while I was constructing it. The line breaks get stripped out when I paste them into the expression window.
I saved the Notepad version of it in case I ever need to edit it someday. Y'know. To jam a 17th if statement in there. x_x
no subject
Date: 2014-11-17 04:17 am (UTC)I imagine they called it "iif" because it's not really "if". It's "if if sucked, this is what if would be".
no subject
Date: 2014-11-17 06:54 am (UTC)