Hi
I am using MSAccess 97, SQL Server 6.5 and VB.
While I am running reports I am getting the Following error.
Runtime Error '3146'
ODBC--Call Failed
Can anyonre please help me to solve this problem
Cheers
RaviCan you post you db connection code ?
Best regards
Mirador.
Showing posts with label reports. Show all posts
Showing posts with label reports. Show all posts
Friday, March 30, 2012
Runtime design of report in crystal reports 8.0
Hello All,
I want to change design of report through VB, I mean to say how can I change any textbox location to other location within same report only?
My problem is that we have more than 50 reports & if any customer wants to change location of textbox to other location within same report then he not able to do that & for that small reason support engineer have to visit him for minor change in report.
So saving time as well as man power, can we provide such interface where user can do minor changes in report ?
Any ideas are appreciated...thanx......Which Framework are you programming.
If you work with .Net, it has a solution to control report objects.
But it needs user interface programming.|||See if you find solutiojn at support section of this site
www.BusinessObjects.com
I want to change design of report through VB, I mean to say how can I change any textbox location to other location within same report only?
My problem is that we have more than 50 reports & if any customer wants to change location of textbox to other location within same report then he not able to do that & for that small reason support engineer have to visit him for minor change in report.
So saving time as well as man power, can we provide such interface where user can do minor changes in report ?
Any ideas are appreciated...thanx......Which Framework are you programming.
If you work with .Net, it has a solution to control report objects.
But it needs user interface programming.|||See if you find solutiojn at support section of this site
www.BusinessObjects.com
Monday, March 26, 2012
running total of differences in Crystal Reports
Hi everyone. I need help writing a function in Crystal Reports. I need a running total of the difference of two columns. I know the starting value and I need something like:
Net := Start + (SubCol - AddCol)
in a loop I guess.
So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)
Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2Why don't you try creating new formula for each runnig total, add a group, copy all fields from detail into group, hide detail and do the difference after using the running total formulas??
Field1 field2 field3
1 1 1
3 4 6
--------------------
detail rt1 rt2 rt3
--------------------
group rt1-rt2
Rob
Hi everyone. I need help writing a function in Crystal Reports. I need a running total of the difference of two columns. I know the starting value and I need something like:
Net := Start + (SubCol - AddCol)
in a loop I guess.
So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)
Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2|||Hi Rob. Thanks for the response. I finally got it to work by declaring a Shared variable and putting it in the report header. Global wouldn't work for some reason. Then, using that variable, I used the same function:
Shared Numbervar x;
x := x + ({Col.Sub} - {Col.Add})
and put it in the details section. Seems to work!
Net := Start + (SubCol - AddCol)
in a loop I guess.
So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)
Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2Why don't you try creating new formula for each runnig total, add a group, copy all fields from detail into group, hide detail and do the difference after using the running total formulas??
Field1 field2 field3
1 1 1
3 4 6
--------------------
detail rt1 rt2 rt3
--------------------
group rt1-rt2
Rob
Hi everyone. I need help writing a function in Crystal Reports. I need a running total of the difference of two columns. I know the starting value and I need something like:
Net := Start + (SubCol - AddCol)
in a loop I guess.
So, if the starting number is 20, the report should look somewhat like:
(Its always adding the difference to the previous net value)
Field1 Field2 Add Sub Net
------------
12 A+ 10 10 '20 + (0-10) = 10
13 A 5 25 '10 + (5 - 0) = 15
11 C 12 37 '15 + (0 - 12) = 3
01 D 10 27 ' 3 + (10 - 0) = 13
12 B 11 38 '13 + (0 - 11) = 2|||Hi Rob. Thanks for the response. I finally got it to work by declaring a Shared variable and putting it in the report header. Global wouldn't work for some reason. Then, using that variable, I used the same function:
Shared Numbervar x;
x := x + ({Col.Sub} - {Col.Add})
and put it in the details section. Seems to work!
Friday, March 23, 2012
Running total for Cell Reference in SQL Reports
Hi,
I'm trying to do running total of the calculated field. Here is the detail of my RDL:
In a table, I have "textbox34" with the formula =count(Fields!ADF_NO.Value) in it and "textbox30" with the formula =first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value in it. I have divided "textbox 34" with "textbox30", and placed the results in "textbox36". Now, I want to do a cummulative total (Running total) of "textbox36", using the formula =runningvalue(reportitems!textbox36.Value, sum, nothing) in "textbox14". Its giving me an error saying:
"[rsAggregateReportItemInBody] The Value expression for the textbox 'textbox14' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."
Any suggestions? I appreciate your help on this.
Thanks
Perm
Hello Perm,
Could you try to remove the ReportItems from your RunningValue calculation? Something like this, but you'll have to take out the 'reportitems!TOTSTKS.value' and replace it with the field it's pulling from.
=runningvalue(count(Fields!ADF_NO.Value) / (first(Fields!TOTSTKS2.Value, "ProdServSales") - reportitems!TOTSTKS.value)), sum, nothing)
Jarret
|||Hi Jarret,
Thanks for your reply.
Value in "reportitems!TOTSTKS.value" is also a runningvalue. It is: runningvalue(Fields!PSR_NO.Value, countdistinct, nothing)
When I try putting this in my query, I get an error saying that "An aggregate function cannot be nested inside another aggregate function".
Is it possible in VBasic...through Report Properties--> Code? Because, all I'm trying to do is to do a cummulative total (RunningTotal) of textbox36.
I appreciate your help on this.
Perm
running the same report different parameters
Is there a way to run the same report with different parameter values?
I have 159 different parameter values (In other words - 159 reports to
save). I have already written a VB.NET application (with reporting
services web service) to automate the saving of these reports (by way
of pdf files).
I was wondering if there is a *better way* to do this. I thought
subscriptions would be good idea; however, I have 159 different
subscriptions to set up not a good idea! Is there a way to configure
subscriptions under SQL Server (via, jobs), so I would not have to
create a custom application using a webservice.
Thanks in advance!
TravisTry a linked report, and there is an app to help you if you go that way.
http://www.sqldbatips.com/showarticle.asp?ID=73
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I can't help you with your issue, but maybe you can help me with mine. Im
trying to get my reports to PDF via a VB.NET application, and I'm having no
luck. If you can help maybe contact me at specialk_ca@.hotmail.com
Michael C
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||You can kick off subscriptions via sql server agent. (MS would probably not
recommend or support you if you did it). I have done this in the past to
overcome some of Reporting Service's shortcomings regarding executing
reports based on a fiscal calendar. If you're good at SQL scripting you
shouldn't have much trouble. The downside of doing it this way is that RS
creates new GUIDS for subscriptions anytime that they are updated. The sql
job's stored proc call is going to reference one of the subscription guids
and of course if you have alot of people tinkering with your subscriptions
you could have issues with reports not firing off because the guid
referenced in your job changes. In hindsight I think I could have written
the sql job to dynamically obtain the guid's it was about to execute but
I've been away from RS for almost a year and I don't remember the specifics
of it. Look on your report serverdb for the available stored procs. I
don't think the name of the execute one was too obfuscated. Good luck!
-Michael Abair
"Travis" <tswill54@.bellsouth.net> wrote in message
news:1187734326.317571.264180@.19g2000hsx.googlegroups.com...
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I read that a little too fast and my previous answer was way off.
I would think setting up a subscription would be the best, as stated in the
previous reply. Setting up 159 reports will be fun. If you want to run it
through your vb.net app you can path out to it, but then I'm not sure if you
can force the save at that point, but you might want to look at calling the
service directly from your app and then save the stream. Not sure on the
total details.
Sorry about the first post.
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I think you can use Data Driven Subscriptions to fullfill your task.
Refer to steps involved in it from
http://msdn2.microsoft.com/en-us/library/aa225509(SQL.80).aspx
Thanks
Subhash
I have 159 different parameter values (In other words - 159 reports to
save). I have already written a VB.NET application (with reporting
services web service) to automate the saving of these reports (by way
of pdf files).
I was wondering if there is a *better way* to do this. I thought
subscriptions would be good idea; however, I have 159 different
subscriptions to set up not a good idea! Is there a way to configure
subscriptions under SQL Server (via, jobs), so I would not have to
create a custom application using a webservice.
Thanks in advance!
TravisTry a linked report, and there is an app to help you if you go that way.
http://www.sqldbatips.com/showarticle.asp?ID=73
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I can't help you with your issue, but maybe you can help me with mine. Im
trying to get my reports to PDF via a VB.NET application, and I'm having no
luck. If you can help maybe contact me at specialk_ca@.hotmail.com
Michael C
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||You can kick off subscriptions via sql server agent. (MS would probably not
recommend or support you if you did it). I have done this in the past to
overcome some of Reporting Service's shortcomings regarding executing
reports based on a fiscal calendar. If you're good at SQL scripting you
shouldn't have much trouble. The downside of doing it this way is that RS
creates new GUIDS for subscriptions anytime that they are updated. The sql
job's stored proc call is going to reference one of the subscription guids
and of course if you have alot of people tinkering with your subscriptions
you could have issues with reports not firing off because the guid
referenced in your job changes. In hindsight I think I could have written
the sql job to dynamically obtain the guid's it was about to execute but
I've been away from RS for almost a year and I don't remember the specifics
of it. Look on your report serverdb for the available stored procs. I
don't think the name of the execute one was too obfuscated. Good luck!
-Michael Abair
"Travis" <tswill54@.bellsouth.net> wrote in message
news:1187734326.317571.264180@.19g2000hsx.googlegroups.com...
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I read that a little too fast and my previous answer was way off.
I would think setting up a subscription would be the best, as stated in the
previous reply. Setting up 159 reports will be fun. If you want to run it
through your vb.net app you can path out to it, but then I'm not sure if you
can force the save at that point, but you might want to look at calling the
service directly from your app and then save the stream. Not sure on the
total details.
Sorry about the first post.
Reeves
"Travis" wrote:
>
> Is there a way to run the same report with different parameter values?
> I have 159 different parameter values (In other words - 159 reports to
> save). I have already written a VB.NET application (with reporting
> services web service) to automate the saving of these reports (by way
> of pdf files).
> I was wondering if there is a *better way* to do this. I thought
> subscriptions would be good idea; however, I have 159 different
> subscriptions to set up not a good idea! Is there a way to configure
> subscriptions under SQL Server (via, jobs), so I would not have to
> create a custom application using a webservice.
> Thanks in advance!
> Travis
>|||I think you can use Data Driven Subscriptions to fullfill your task.
Refer to steps involved in it from
http://msdn2.microsoft.com/en-us/library/aa225509(SQL.80).aspx
Thanks
Subhash
Running Sum on DataReport
Hye Guys,
I am a beginer programmer.. I have started 2 build a simple database application. I am now in the stage of showing the data in reports 2 print. I am suggested to use datareport available in VB. I also did my some of the report in it. It worked fine. But now I am stucked in one step..of displaying the calculateable field in the detail section..
Eg. in database:
Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5
In Report
Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10
Plz Help me...
Always ready 2 learn..
Kabindra Bakey
BALQty expression could be
=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)
|||Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server
Hoping for your kind information..
Always Ready 2 Learn
Kabindra Bakey
Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.
Steps:
1.) Added New Dataenvironment to my project
2.) Managed the properties of the connection and pointed to my database. Test Connection also preceeded.
3.) Added New Command object to the connection.
4.) In Properties of the Command Object Managed its Name to TestObj and Its Database object to table and then ObjectName to the Name of Table.
5.) Save and Quit.
6.) Added New DataReport to my Project
7.) Set its DataSource to DataEnvironment
8.) Set its DataMember to the TESTObj
9.) Now Right CLicked on the DataReport and Click on Retreive Structure.
10.) Created text Controls for the Each of the Columns to be displayed.
11.) Now but I couldn't draw the Function control in the detail section.
So plz Help me..
Running Sum on DataReport
Hye Guys,
I am a beginer programmer.. I have started 2 build a simple database application. I am now in the stage of showing the data in reports 2 print. I am suggested to use datareport available in VB. I also did my some of the report in it. It worked fine. But now I am stucked in one step..of displaying the calculateable field in the detail section..
Eg. in database:
Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5
In Report
Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10
Plz Help me...
Always ready 2 learn..
Kabindra Bakey
BALQty expression could be
=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)
|||Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server
Hoping for your kind information..
Always Ready 2 Learn
Kabindra Bakey
Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.
Steps:
1.) Added New Dataenvironment to my project
2.) Managed the properties of the connection and pointed to my database. Test Connection also preceeded.
3.) Added New Command object to the connection.
4.) In Properties of the Command Object Managed its Name to TestObj and Its Database object to table and then ObjectName to the Name of Table.
5.) Save and Quit.
6.) Added New DataReport to my Project
7.) Set its DataSource to DataEnvironment
8.) Set its DataMember to the TESTObj
9.) Now Right CLicked on the DataReport and Click on Retreive Structure.
10.) Created text Controls for the Each of the Columns to be displayed.
11.) Now but I couldn't draw the Function control in the detail section.
So plz Help me..
Running Sum on DataReport
Hye Guys,
I am a beginer programmer.. I have started 2 build a simple database application. I am now in the stage of showing the data in reports 2 print. I am suggested to use datareport available in VB. I also did my some of the report in it. It worked fine. But now I am stucked in one step..of displaying the calculateable field in the detail section..
Eg. in database:
Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5
In Report
Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10
Plz Help me...
Always ready 2 learn..
Kabindra Bakey
BALQty expression could be
=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)
|||Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server
Hoping for your kind information..
Always Ready 2 Learn
Kabindra Bakey
Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.
Steps:
1.) Added New Dataenvironment to my project
2.) Managed the properties of the connection and pointed to my database. Test Connection also preceeded.
3.) Added New Command object to the connection.
4.) In Properties of the Command Object Managed its Name to TestObj and Its Database object to table and then ObjectName to the Name of Table.
5.) Save and Quit.
6.) Added New DataReport to my Project
7.) Set its DataSource to DataEnvironment
8.) Set its DataMember to the TESTObj
9.) Now Right CLicked on the DataReport and Click on Retreive Structure.
10.) Created text Controls for the Each of the Columns to be displayed.
11.) Now but I couldn't draw the Function control in the detail section.
So plz Help me..
Subscribe to:
Posts (Atom)