question

yxzhou624 avatar image
yxzhou624 asked

Define the display value in SQL Server Report

Hi, I am new to SQL Server. If I do not explain very clearly, please forgive me. I am using SQL Server 2008 and Visual Sutdio 2005. I have a question about SQL Server Report. I need to build a report based on a database. In the database, the value under Bill To is "1" or "0" based on the database, and the number indicates different explanation sentences. I would like to replace "1" and "0" with sentences in the report. Should I write a statement in SQL Server or in Visual Studio? What should I do? Thank you very much. ![alt text][1] [1]: /storage/temp/4026-1.jpg
reporting-servicesreport-viewerreportserver
1.jpg (47.2 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry for confused. "1" and "0" come from the database. I want to replace "1" with "A" and "0" with "B" in the Report. Thank you!
0 Likes 0 ·
mdawini avatar image
mdawini answered
You can do it SQL using a CASE statement or create a simple lookup table like so: CREATE TABLE dbo.lkpReportVal(ReportValID INT, ReplaceVal CHAR(1)); GO INSERT INTO dbo.lkpReportVal(ReportValID, ReplaceVal) VALUES(1, 'A'),(0,'B') GO Also create a primary key on this table then in your dataset from SQL JOIN to this table and use the column ReplaceVal in your report. But this need testing to see how it performs. Am against burying business logic in visual studio and mantaining and making changes might be cumbersome. Although you can use an expression on the report designer. The fundamental issue to consider also is performance. Do you want a quick data extract and display if so then go with the first option I suggested above.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much
0 Likes 0 ·
Vote it please for me... thanks
0 Likes 0 ·
Gman avatar image
Gman answered
You can write an expression, under the column Bill write the expression, if your column name is Bill, The expression evaluates if the value is = 1, then it will display the sentence you want inside "" else the second sentence inside "" =IIf(Bill.value = "1", "your sentence when the vale is = 1", "if the value is<>1 Then your sentence")
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gazz avatar image
Gazz answered
If you want to be able to change the values in SQL then I would do the change in visual studio. (You should be able to make it display A and B, but the actual value still be 1 and 0.) If you want it just as a read only list, then I would do it in SQL.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.