Question about programming reporting services

Hi there,
We have ASP.NET application and we want to use SQL Server Reporting Services
to display the report links to the end user. Report Server however won't be
accessible from the internet for security reasons. The server side code in
ASP.NET pages on the IIS server can access this report server on intranet. I
was exploring the choices I have to build this functionality.
I think the obvious choice of using URL addressability of SQL RS won't work
because it would mean report server can be accessed from internet. For the
same reason I think ReportServer ASP.NET control that Microsoft ships as
samples also won't work because it uses IFRAME which in turn depends on URL
addressability. So it seems programming using Report Server web service is
the only choice. This means writing code for pagination, laying out report in
landscape/portrait manner as defined in RDL, ability to export the report
etc. Is there a better alternative that anyone can suggest?
Thanks
YogeshYou are understanding it correctly. One point though, are you aware that
Report Server and SQL Server can be on different boxes, so your SQL Server
can be behind the firewall and RS can be accessed from the internet, then
URL integration will work. You would need another license though. Otherwise
until V2 comes out with the web controls your only other option is web
services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:BE238509-4BBC-46E6-B4FC-6765C4A114F0@.microsoft.com...
> Hi there,
> We have ASP.NET application and we want to use SQL Server Reporting
Services
> to display the report links to the end user. Report Server however won't
be
> accessible from the internet for security reasons. The server side code in
> ASP.NET pages on the IIS server can access this report server on intranet.
I
> was exploring the choices I have to build this functionality.
> I think the obvious choice of using URL addressability of SQL RS won't
work
> because it would mean report server can be accessed from internet. For the
> same reason I think ReportServer ASP.NET control that Microsoft ships as
> samples also won't work because it uses IFRAME which in turn depends on
URL
> addressability. So it seems programming using Report Server web service is
> the only choice. This means writing code for pagination, laying out report
in
> landscape/portrait manner as defined in RDL, ability to export the report
> etc. Is there a better alternative that anyone can suggest?
> Thanks
> Yogesh|||Thanks Bruce. I was aware that Report Server and SQL Server can be on
different boxes, but our organization has decided that they don't want report
server available over internet. Do you know of any books or articles that
explain handling pagination or ability to export the report using web
services?
Yogesh
"Bruce L-C [MVP]" wrote:
> You are understanding it correctly. One point though, are you aware that
> Report Server and SQL Server can be on different boxes, so your SQL Server
> can be behind the firewall and RS can be accessed from the internet, then
> URL integration will work. You would need another license though. Otherwise
> until V2 comes out with the web controls your only other option is web
> services.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>|||I have a couple at work and on Monday will give you the titles to them.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:7246EF1E-0174-4CFA-A7AC-478ADF0AAEEC@.microsoft.com...
> Thanks Bruce. I was aware that Report Server and SQL Server can be on
> different boxes, but our organization has decided that they don't want
> report
> server available over internet. Do you know of any books or articles that
> explain handling pagination or ability to export the report using web
> services?
> Yogesh
>
> "Bruce L-C [MVP]" wrote:
>> You are understanding it correctly. One point though, are you aware that
>> Report Server and SQL Server can be on different boxes, so your SQL
>> Server
>> can be behind the firewall and RS can be accessed from the internet, then
>> URL integration will work. You would need another license though.
>> Otherwise
>> until V2 comes out with the web controls your only other option is web
>> services.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>|||Besides web services you will also need to be implementing your own
authentication too. Two books I use (both have some on Web services, enough
to get you going but are also good for other topics as well) are: Microsoft
Reporting Services in Action by Teo Lachev and Hitchhiker's Guide to SQL
Server 2000 Reporting Services by Peter Blackburn and William Vaughn.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"yogeshprabhu" <yogeshprabhu@.discussions.microsoft.com> wrote in message
news:7246EF1E-0174-4CFA-A7AC-478ADF0AAEEC@.microsoft.com...
> Thanks Bruce. I was aware that Report Server and SQL Server can be on
> different boxes, but our organization has decided that they don't want
report
> server available over internet. Do you know of any books or articles that
> explain handling pagination or ability to export the report using web
> services?
> Yogesh
>
> "Bruce L-C [MVP]" wrote:
> > You are understanding it correctly. One point though, are you aware that
> > Report Server and SQL Server can be on different boxes, so your SQL
Server
> > can be behind the firewall and RS can be accessed from the internet,
then
> > URL integration will work. You would need another license though.
Otherwise
> > until V2 comes out with the web controls your only other option is web
> > services.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
>

Question about Null value in cross tab

Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

This will display 0 if that field contains Null and the actual value if it is not null.

Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue

for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue

both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,

For the first question, Hope you have written the formula in "Display String" (Format)section.

Try to check CurrentFieldValue instead of {table.field}.

I checked with zero, it worked.

For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database

How come this happens??

Thanks~|||I have a new question again.

The crystal report have not show the record that is zero
How can I show the zero record??

Thanks very muchsql

Question about Joins

Hi,

I have a stored Procedure called rpt_FundLipperRatings and i want to display a image which is store in the Database which a particular fundid doesnt exist. Below is the stored procedure that exist, I tried putting a case statement like totalreturnScore = CASE When TotalReturnScore IS NULL then 0 else TotalreturnScore END, but it doesnt work if the the fund doesnt exist in the database. So can some one please tell me how can i do it. and is it possible to put a where statement in a inner join

Like

Select * from fund f

Inner Join LipperLeaderPix ll on f.TotalReturnScore = ll.score where Condition

inner join .....

ALTER

PROCEDURE [dbo].[rpt_FundLipperLeaderRatings]

@.FundId

int

AS

SELECT

NumberOfYears

,

TotalReturnScore,

ll1.OverallBW TotalReturnImage,

ConsistentReturnScore

ll2.OverallBW ConsistentReturnImage,

PreservationScore,

ll3.OverallBW PreservationImage,

ExpenseScore,

ll4.OverallBW ExpenseImage

FROM

FundLipperLeader f

INNERJOIN LipperLeaderPix ll1ON f.TotalReturnScore= ll1.ScoreINNERJOIN LipperLeaderPix ll2ON f.ConsistentReturnScore= ll2.ScoreINNERJOIN LipperLeaderPix ll3ON f.PreservationScore= ll3.ScoreINNERJOIN LipperLeaderPix ll4ON f.ExpenseScore= ll4.Score

WHERE

f

.FundId= @.FundId

There are a number of ways you could handle this but I would have to see sample data in your tables to understand it.

You can always try using a LEFT OUTER JOIN instead of INNER JOIN on the two tables if you still want to return records that have NULL on the joined field

|||

Rossoneri,

Thanks for your reply and sorry for replying late.

The Fund Lipper Leader has the following Columns - FundId,,Numberof Years(0,3,5,10), DataValidDate, TotalReturnScore, ConsistentreturnScore, PreservationScore, ExpenseScore, addDate, LastChangeDate

All the score have data ranging from 0 - 5 and each score has a different picture associated with it.

The LipperLeaderPix has Columns which store Binary data(or the pictures) the columns are Score(0,1,2,3,4,5) OverallColor, ConsistentReturnColor, ExpenseColor, PreservationColor, TaxEfficineyColor, TotalReturnColor and OverallBW.

So the score is been associated with each picture. Is this information helpful or do u need more.

Regards,

Karen

|||

Rossoneri,

I tried running it using Left Outer Join but it didnt tried doing some like for some other sproc but i had to write a union to get to handle the non existent funds, but couldnt do it for this sproc.

Regards,

Karen

Question about Interactive Sort

I use a matrix to display data,and I find the Interactive Sort seems no use in the matrix. I am confused with the "SortExpressionScope". What should it been when there are multigroup in a table or in a matrix.

Thanks for help.

In matrices, data is always grouped (even in cells), so detail sort wouldn't do anything. You can set the "SortExpressionScope" to one of the group in the matrix rows or column if you want to sort data by that row or column. Here is an example of how to do that:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>30d8ee62-a72d-48fa-ad77-66fdebc3f620</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.5in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CompanyName">
<rd:DefaultName>CompanyName</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.5in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Width>2.5in</Width>
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox12">
<Top>0.25in</Top>
<ZIndex>1</ZIndex>
<Width>0.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>
</UserSort>
<Value>Rows</Value>
</Textbox>
<Textbox Name="textbox11">
<Left>0.75in</Left>
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<UserSort>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
<SortExpressionScope>matrix1_CompanyName</SortExpressionScope>
</UserSort>
<Value>Cols</Value>
</Textbox>
</ReportItems>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Rectangle>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Quantity">
<rd:DefaultName>Quantity</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.75in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 30 Products.ProductName, Products.UnitPrice, Customers.CompanyName, [Order Details].Quantity, Categories.CategoryName,
Products.CategoryID
FROM Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="CompanyName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CompanyName</DataField>
</Field>
<Field Name="Quantity">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>Quantity</DataField>
</Field>
<Field Name="CategoryName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CategoryName</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code />
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

question about Gridview and sqlDataSource

i am now writing a web page which can allow users to edit the field(display in the gridview->gridview source is from sqldatasource), there is a problem(it seems that i have followed all steps in the book):

when the i edit the field and click update, in the web site, i can see those changes. however, i cant see any change in database table.

i cant find solution on this, could anyone help me to solve this problem? thx!

Hi, are you checking the right database's table? If you can see the records changed on your gridview, it is CHNAGED in your source table. Please check again.

|||it seems that i have made some mistake while configure data source...but now is ok...thx!!

question about display mask & select number N to M records

Two questions:
1. With SQL, how can I setup display mask for value:
eg. display 100000 as $100,000
2. How can I select number N to M records in a table.
eg. select No. 50 - 100 records from a table. ( not top 50)
Thanks,
Guyang> 1. With SQL, how can I setup display mask for value:
> eg. display 100000 as $100,000
(a) I wouldn't rely on Enterprise Manager for data viewing / modification...
use a development tool for that, like Query Analyzer.
(b) there is no such thing as a "display mask" in SQL Server... this is
something that cute GUIs do. The data is not stored that way; if you want
it to be *presented* that way, write a view, e.g. SELECT CONVERT(VARCHAR,
moneyColumn, 1) FROM table
> 2. How can I select number N to M records in a table.
> eg. select No. 50 - 100 records from a table. ( not top 50)
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC
If you really need it to come back 50 -> 100, then
SELECT * FROM
(
SELECT TOP 50 * FROM
(SELECT TOP 100 * FROM table
ORDER BY some_column) x
ORDER BY some_column DESC) y
ORDER BY some_column

Question about authentication

Hello :).

I have a web application running under .NET 1.1 and Reporting Services 2005. What I need is to display reports in my app.

Here I have two problems:

1. SSRS uses web service as a data source (it is in my application). Access to the service should be permitted only for known (and logged in) users.

2. Access to SSRS should be permitted only for known (and logged in) users.

So, what is the best approach to integrate Reporting Services and my application according to the security limitations?

Any advise on this subject is more than welcome :)

Thank you.

You have basically two options - trusted account and custom security. See this article for more info. I am curious to know how SSRS uses a web service as a data source. Did you write a custom data extension?|||

Teo, thank you very much for your great articles and reply. They were very helpful for me.

Talking about web service as a data source I mean simple XML data source. I.e. DataProvider is 'XML', and connection string refers to the web service. So, I didn't write any custom data extension, as it was already written in the SSRS 2005 :).

Sorry if original post was unclear (maybe it's because I'm really novice in the Reporting Services :) ).