Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.
sql

Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.

Need to convert returned values.

I have an application which collects WMI info from servers and stores it in
SQL. The vendor reports for displaying this leave much to be desired. I've
developed numerous replacement reports using SRS, but I'm having trouble
converting the data they collect because of the way it's formatting and
stored....can anyone help?
Sample:
Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
I want to drop all of the [-] and [+] and just leave a space between labels
and values.
Any help woul be greatly appreciated.
RCITGUYMaybe in your SQL statment you could try something like this:
SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
"RCITGuy" wrote:
> I have an application which collects WMI info from servers and stores it in
> SQL. The vendor reports for displaying this leave much to be desired. I've
> developed numerous replacement reports using SRS, but I'm having trouble
> converting the data they collect because of the way it's formatting and
> stored....can anyone help?
> Sample:
> Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> I want to drop all of the [-] and [+] and just leave a space between labels
> and values.
> Any help woul be greatly appreciated.
> RCITGUY
>|||I have to apologize for sounding stupid...but where should I place this in my
existing SQL select statement? "column holding data is
"WMIConfiguration.Configuration"
___________________________________________________________________
SELECT ComputerGroup.Name, Computer.Name AS Server,
WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
Computer.Address, Computer.PhysicalMem,
Computer.PageSize
FROM Computer INNER JOIN
WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID INNER JOIN
ComputerGroup ON Computer.GroupID =ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
_____________________________________________________________
"Aiwa" wrote:
> Maybe in your SQL statment you could try something like this:
> SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> "RCITGuy" wrote:
> > I have an application which collects WMI info from servers and stores it in
> > SQL. The vendor reports for displaying this leave much to be desired. I've
> > developed numerous replacement reports using SRS, but I'm having trouble
> > converting the data they collect because of the way it's formatting and
> > stored....can anyone help?
> >
> > Sample:
> > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> >
> > I want to drop all of the [-] and [+] and just leave a space between labels
> > and values.
> >
> > Any help woul be greatly appreciated.
> > RCITGUY
> >|||Something like this:
SELECT ComputerGroup.Name,
Computer.Name AS Server,
WMIConfiguration.ObjectType,
REPLACE(REPLACE(WMIConfiguration.Configuration,'[+]','
'),'[-]',' ') AS Configuration ,
Computer.OSVer,
Computer.Address,
Computer.PhysicalMem,
Computer.PageSize
FROM Computer
INNER JOIN WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID
INNER JOIN ComputerGroup ON Computer.GroupID = ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
"RCITGuy" wrote:
> I have to apologize for sounding stupid...but where should I place this in my
> existing SQL select statement? "column holding data is
> "WMIConfiguration.Configuration"
> ___________________________________________________________________
> SELECT ComputerGroup.Name, Computer.Name AS Server,
> WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
> Computer.Address, Computer.PhysicalMem,
> Computer.PageSize
> FROM Computer INNER JOIN
> WMIConfiguration ON Computer.ComputerID => WMIConfiguration.ComputerID INNER JOIN
> ComputerGroup ON Computer.GroupID => ComputerGroup.GroupID
> WHERE (ComputerGroup.Name = @.Company)
> _____________________________________________________________
> "Aiwa" wrote:
> > Maybe in your SQL statment you could try something like this:
> > SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> >
> > "RCITGuy" wrote:
> >
> > > I have an application which collects WMI info from servers and stores it in
> > > SQL. The vendor reports for displaying this leave much to be desired. I've
> > > developed numerous replacement reports using SRS, but I'm having trouble
> > > converting the data they collect because of the way it's formatting and
> > > stored....can anyone help?
> > >
> > > Sample:
> > > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> > >
> > > I want to drop all of the [-] and [+] and just leave a space between labels
> > > and values.
> > >
> > > Any help woul be greatly appreciated.
> > > RCITGUY
> > >

Need suggestion for reports software

Hi,

We have Visual Studio 2005 , Crystal Reports, SQL Server 2005 Express Edition.

our Manager asked about the best and easiest reporting software other than Crystal Reports.

I didn't work with reports. Please assist me, if any one have idea.

Thanks in advance

Regards

Jyothi

I have worked with crystal reports and Sql Server Reporting Services and SSRS for all its problems, beats crystal and not just on price. The points with SSRS to watch are:

Choose the right style at the outset as difficult to change afterwards.|||

I tried Crystal it is expensive but has a lot of predefined functions that SSRS doesn't. But I elected to you SSRS I like it better it is easy to use and very flexible. I just wish it would conform to the same formats as excel. I found out you can not have over 160 in in viewable colum space width. Excel allows 256...

|||

Hello,

I'm currently working on SQL Reports and found out that they are better than Crystal and DEV express reports. I'm implementing a localisation functionality with ASP.net and they are very flexible.

Here are the key features.

1- Easy to integrate with ASP.net site

2- Build custom assembly and use it in your report or use VB for embeded coding.

3- Free of cost with SQL Express.

4- Plenty of support/samples and knowledge base.

5- Free of cost report designer (Business IntelligenceProject)

Thanks,

Farooq

|||

Check these links to see the features of reporting services

http://msdn2.microsoft.com/en-us/library/ms159273.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/2005ssrs.mspx

Check this post for a good discussion on Crystal Reports .NEt vs. SSRS :

http://asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=720164

Need something faster than RS for programatic generation of reports...

I have been working on a program for quite some time that prints
invoices programatically with reporting services rs.render.
It was supposed to replace an old program that sent the invoices to a
dot matrix printer. This one sends to two very fast hp laserprinters.
Unfortunately, the rendering process of an EMF file in reporting
services is too slow (slower than the old program), so they have sent
me back to the drawing board to figure out another way to print to the
laser printers other than reporting services. Any one with a clue,
please let me know.
Thanks,
TrintAfter rendering the first page and therefore know the number of pages in the
report (StreamIDs.Length + 1 in VB) you could execute the render method in
separate threads for each of the remaining pages.
Also watch the MaxActiveReqForOneUser setting in your C:\Program
Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config file.
regards
Alan
"trint" wrote:
> I have been working on a program for quite some time that prints
> invoices programatically with reporting services rs.render.
> It was supposed to replace an old program that sent the invoices to a
> dot matrix printer. This one sends to two very fast hp laserprinters.
> Unfortunately, the rendering process of an EMF file in reporting
> services is too slow (slower than the old program), so they have sent
> me back to the drawing board to figure out another way to print to the
> laser printers other than reporting services. Any one with a clue,
> please let me know.
> Thanks,
> Trint
>

Need some help with picture in Crystal Reports, please.

Hello all!

I use VB.NET and Crystal Reports and I need to create a win report which will display pictures.
The issue is that I don't know what pictures should be printed while I design the report, but my application check this in runtime.
What I want to do is create a report that will get pictures by parameters or by any other way that will allow me to add pictures dynamically during runtime.
I heard about OLE Picture but I didn't find how to use it and I don't even sure that I should use it...

May someone explain me how to create such report?

Thanks in advanced,
Tom.Hi Tom,

Problem with Crystal and pictures is that there's no way (up until 8.5) to dynamically add a picture from a file or folder location.

You can, however, create a field in a table structure within your database to store BLOBs (BinaryLargeObjects) such as pictures or binary streams etc, and then embed that field within your report. That's provided your database supports the storage of binary objects.

So if you have a table for Personnel, and the Fields are Id, LastName, FirstName, Address etc, and you wanted to show a picture of the person, you would add a BLOB field named Pic to this table and then store the picture in it. Then when you show the person's details on the report, you will have the picture field available to select from the field list in Crystal, and pop it on the report in the appropriate location.

Some databases support storage of BLOBs 2 ways - directly storing the picture itself, and storing a file reference to a location within the file system.
You'll have to work out which way is gonna work for you.

Good luck

Dave|||I am having this same problem.

When you say "field list" are you refering to the dataset *.xsd file that is created in VB.NET 2002 that is populated from a SQL query and used and the record source for the crystal report?|||Raven (nevermore!)
I don't use Crystal and .NET, so I can't say absolutely yes to your question, but my theoretical understanding is that the schema is exposed via XML, so that anything in the underlying tables on the server side would be visible to the user as a "field".

The real issue here is that your connection can expoise a BLOB. If it can, you store the pictures in a table on the server and then retrieve thme to client side as what is essentially a binary stream. On the client side you have a container that can display binary stream data (like a picture box), and you load the binary data into that object.

Dave|||Thank You.

I have read the pictures into a SQL table via a binary stream and I have now read that data into Crystal Reports via a binary stream.

I have decided that I will bypass storing the pics in the database and simply store the path to the pics in the database and still perform the binary stream read into Crystal Reports.

thank for the help!

(" 'Tis some visitor," I muttered, "tapping at my chamber door;
Only this, and nothing more.") :p|||Raven,
One less midnight dreary, pondering weak and weary over one of those damned Crystal Reports!
My 11yo daughter's fave poem.

Glad to help - streaming is an often misunderstood feature, and once you master the mechanics of it, it's so useful. I first came across it coding with Intersystems Cache OO database, and it was very useful overcoming Crystal's problems with dynamic pictures.
Don't forget to rate the thread......

Dave
"Nameless here for evermore"

Need some help regarding duplicate values....

Hi,

I am using access database and cystal reports XI. Since I am facing some problems.

The following are the details:

Table1 = Sales
Table2 = Collections

Sales (table fields):

1. CustomerName
2. InvoiceNumber
3. InvoiceDate
4. InvoiceAmount

Collections (table fields):

1. CustomerName
2. DepositDate
3. ReceivedAmount

I need these fields in a report. There is no relationship in the database for the CustomerName field.

I tried my best to get the report. But there are repeated values (Duplicate values which are not existing in the table/database) present on the report. Beside to the date values there shows 00:00:00 (time) as well which I dont need.

How to solve this problem. Please help.

Sweetie.The following jpg file shows the duplicate/repeated data.|||Make sure "select Distinct Records" is turned on under the Database menu.|||Make sure "select Distinct Records" is turned on under the Database menu.

Hi, thought I have selected the option "select Distinct Records" the same thing happens that the data in collections.AmountReceived repeats.

Any further help please. (Using Crystal Reports XI).

Sweetei.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.

Hi, Raheem! I did the same as you have advised but it didn't work. Actually though the both Sales & Collections have CustomerName field but there is no relationship between them in the database.

Could you please help me out of this problem?

Sweetie.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

While you try to connect the Sales table to the Collection table using CustomerName (seems like the only relationship you can make between the 2), every distinct record in the Sales table will connect to all records in the Collection table which has the same CustomerName. If you look at the DepositDate on your report, you'll see the pattern. It looks like a duplicate problem. In deed, it just a coincident that for each of the 3 customers, you have 2 records in the Collection table.

You can check this out by adding another record for one of the 3 customers in your Collection table. You'll see your report showing triplicate for this customer.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.

Hi, thanks for your kind help. Actually I need the both ways you have mentioned above. But I need to know it step by step to understand easily.
So, Could you please let me know the first way .... ?

"1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link."

Sweetie.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.

Hi, Raheem. Thanks for the advise. But the problem is, assume that if the customer doesn't pay the full payment for each invoice and pay it in 3 installments and by the time he contines purchasing some items (means he will have another 1 - 2 invoices before he pay the first invoice amount). Then it will be messed.

The example is attached.

Regards,

Seema.|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem

Wow! is it ? Actually what I needed for my program is the same. This is the first time that I heard about that method. Could you please explain me in detail, only if you dont mind?

Seema_S|||when i get free i will reply you.|||when i get free i will reply you.

Hi, Raheem!

I feel happy to get your reply which can solve my problem.

Thanks in advance.

Sweetie.|||Hi, guys!

I am waiting for the reply to my querry. Could somebody help me in this regard?

Sweetie.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.
One such error is the city 'JAYPUR' in Collections when it's 'JAIPUR' in Customers and Sales - explains the lack of duplication for that customer!
And what's the FileNumber column in Sales and Collections about? It seems to uniquely identify a customer but this isn't on the customer table. Messy.
You might want to consider ditching all the duplicated rubbish and putting the CustomerID into the Sales and Collections tables at least.

I've knocked together something I think is more like what you're after.See the attached report.
It assumes that the customer name is unique. If you put the CustomerID into the tables then you should use that instead as it really is (should be) unique.

Anyway, have a play with it to see what it does and how. Hope it's of some use.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.

Anyway, have a play with it to see what it does and how. Hope it's of some use.

Hi,

Thanks for your kind help. I am happy that at last I got my querry answered. There is one problem which is attached as jpg file.

Thanks again.

Sweetie.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

The collections of 17,000 and 4,000 are applied in a First In First Out (FIFO) process to the sales (invoices).

The first invoice (103) was for 8,000 and the second invoice (109) was for 17,000.
The first collection was 17,000 so 8,000 of this is applied to invoice 103.
The remaining 9,000 is left over and applied to the next invoice, 109.
The next collection was for 4,000 and is also applied to invoice 109.

If you want to make it clear that a single collection has been split over multiple invoices then you might want to add the original collection amount / collection id to the report.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

Thanks. It is better that it should show the full amount received.

Regards.

Sweetie.|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.

In Collections table, I added invoice number in it and check two

queries. JaganEllis did the same thing. if u remove invoice date then u will get correct collections amounts displayed.

collecionn 1 for 4000 adjusted in 105
collection 2 for 2000 adjusted in 105
collection 3 for 5200 adjustted in 105 and 111

so if u want to omit invoice date, then u can use query2 to make ur report ..else u u can use query1..

Atlast Hope I helped you.

Raheem|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.
Raheem

Thanks for your kind help. I need to learn more about Normalizing database and Crystal reports. If you know any good tutorials site for the above, plz let me know.

Regards.

Sweetie.sql

Need immediate help with drill down on deployed reports

When using a group by in the query rendering a report, the drill down menus
donâ't work. When the collapsed icon is clicked, nothing happens. Exporting
to an Excel file shows the additional rows and they also show up in preview
mode. When exporting to a PDF they are still hidden. Has anyone else had
this problem? Any fixes?Amanda,
Are you running your query directly in the dataset Text box, or have you
saved it as a stored procedure and run it as such? If running directly, try
creating a user SP and running that. See if that makes a difference.
The only other thing I can think of is if you have your data hidden based on
a parameter and, for some reason, the Expression in your hidden property
isn't set right.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Amanda Adcock" wrote:
> When using a group by in the query rendering a report, the drill down menus
> donâ't work. When the collapsed icon is clicked, nothing happens. Exporting
> to an Excel file shows the additional rows and they also show up in preview
> mode. When exporting to a PDF they are still hidden. Has anyone else had
> this problem? Any fixes?
>|||Thank you for the suggestion. For anyone else having the same problem here
is the solution (at least in my case):
I found that the problem was related to my machine name. Our machine name
contained an underscore (server_name). After correcting this and making
necessary updates to my reports, the drill downs work.