2
=switch(
    Fields!HEORG_REFNO.Value=7535,"public", 
    Fields!HEORG_REFNO.Value=7539,"public",
    Fields!HEORG_REFNO.Value=7609,"public",
    Fields!HEORG_REFNO.Value=7541,"public",
    true,"private"
)

I want to group my result based on weather the clinic is private or public, hence to do so i am trying to create a new field based on the above switch condition. if(heorg_refno=7539 or heorg_refno= 7609 or heorg_refno=7541) then it would be public otherwise the clinic should be private.

Any suggestions what am i doing wrong in the switch statement.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
Devesh Sharma
  • 25
  • 1
  • 7

1 Answers1

1

One reason for this could be that Fields!HEORG_REFNO.Value does not actually contain integer values, but String values. In that case you'd have to compare against strings:

 =Switch 
 (
    Fields!HEORG_REFNO.Value="7535","public", 
    Fields!HEORG_REFNO.Value="7539","public",
    Fields!HEORG_REFNO.Value="7609","public", 
    Fields!HEORG_REFNO.Value="7541","public",
    true, "private"
 )

You need to check the type for the HEORG_REFNO column in the SSRS dataset.

Also in my experience it is a good idea for stuff like this to create a fake column in your data set which you can filter over instead of putting too much stuff into expressions.

The latter leads to confusion because sooner or later.

Thorsten Dittmar
  • 55,956
  • 8
  • 91
  • 139
  • Yes, i am adding another column in my data set, and using this expression to create that column if this is what you are trying to say? – Devesh Sharma Dec 02 '16 at 10:36
  • No. I'm saying that for your report there must be a `SELECT` statement to query information from the database. Sometimes it is a good idea to put a "calculated column" in that select statement instead of using an expression in the report. Something like `SELECT CASE WHEN RefNo = 7535 THEN 'public' ELSE 'private' END AS FakeColumn FROM tablename`. – Thorsten Dittmar Dec 02 '16 at 10:39
  • Changed my answer, because the previous answer was wrong. – Thorsten Dittmar Dec 02 '16 at 11:10
  • it gives an output with only one row if i add this column into my report, but if i exclude the column it fetches me 121495 rows. Moreover , it takes the same amount of time to render. The error is the same as mentioned in the main question – Devesh Sharma Dec 02 '16 at 11:14
  • Well, then your statement is wrong. Try your statement in Sql Server Management Studio first and then add it to your report. I can't tell what's wrong. Also, the SQL statement is not the topic of your question. You could try my improved answer. Maybe that helps? – Thorsten Dittmar Dec 02 '16 at 11:15
  • @ThorstenDittmar - I really appreciate this answer. My SWITCH statement in the last evaluation (*the else*) only had the value `, "MyElseValue"` but I from your answer I did `,true, "MyElseValue"`. SSRS is a tool that can blow you away with its awesomeness, or have you stunned with its ridiculousness... [Answer: SSRS “OR” and "IN" Filter](https://stackoverflow.com/questions/52653422/ssrs-or-filter-logic-to-filter-from-and-in-operators/54207722#54207722) and [Answer: Report Description glitch](https://stackoverflow.com/questions/23724177/add-ssrs-report-description/51047297#51047297) – SherlockSpreadsheets Mar 01 '19 at 17:47