OLAP - Uniqueness for the quarter

Hi All,

We are encountering problems with quater count of records.

Uniqueness is accurate for month; not if you aggregate for quarter or year; for example, user is active in Jan, Feb and Mar The system would accurately show 1 active user for each of these months, but would show 3 active users for the 1st quarter (doesnt recognize that its the same unique user).

We want to count the active user only once for that quarter.

Is the way to count that active users only once for that quarter instead three times. Please help and your input is appreciated.

Thanks and have a nice day.Use this format for your count:

Select Count(Distinct UserID)
From YourTable
Where.........

blindman

Ok got the per page sorted but...

i get a blank page after each record so 13 records, when previewed, adds upto 26 page 13 of which are blank.

I noticed a post here about having nothing after the grouping and as far as i can see i have not, but i dont really know what that means.....

Any help is appreciated, so close yet so far :-)
Which renderer are you using? If it's the PDF or Image renderer, please check the size of your report body and margins to make sure it doesn't exceed the page size.|||Yep found that and all is fixed.

Many thanks for the help

:-)

Odd Sum() problem

Hi everyone, first time posting here.

I am having an odd problem with Crystal Reports XI. I have a small database table in Oracle, only has 8 records in it. I want to do a grand total for each column in the database (adding up all 8 records).

But when I use the Sum(fieldname) command, I get a number that is way off. The other commands like Maximum(fieldname) return the value I expect. But Sum is way off.

I am using the columns in a different part of the report, and the report is broken down into sections that match the different records of this database. What I need is a grand total for each column for the whole report.

I even tried a running total, and it returns the same number that is more than twice what it should be. There has to be something I am missing.

Thanks for the help. I am pretty new to .net and programming so I might have used the wrong terms for something.Is the detail section hidden or supressed? If so, have you checked to see if you are selecting distinct records? When you run te report what is the record count in the lower right corner of the screen? It should only be 8.

odbc-timeout error on a linked table from Access2k

Hi.
I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.

I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.

I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference
So what's the problem?
Does anyone have an idea?
/regards BigOlle(olle@.ylm.se) writes:

Quote:

Originally Posted by

I have the problem that some records in a ms sqlserver table is unable
to update from Access.
I get the error message odbc-time out error in linked table.....
I tried to copy this table to another database, where none but me was
aktive.
And then it worked quit ok when I try to save the record.
>
I am thinking about if there is some trigger och restraint that I
don't know about, but I don't know how to se all of this in the
database.
>
I have changed the odbc-timeout settings in Access but that doesn't
seed to make any difference

Could be a blocking issue. You can check this in Query Analyzer by
running sp_who2. If there is a non-zero value in the Blk column,
that spid is blocking the spid on that line. With the rest of the
information from sp_who2, you may be able to identify the blocker.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table from
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for the
recordsource, it works fine. What am I doing wrong?
You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate controls.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for the
>recordsource, it works fine. What am I doing wrong?
|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subform
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>
|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing the
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:

ODBC Text file to SQL conversion

I have a large comma delimited text file (130K records) that I want to convert to an SQL data file. I want to use this data file as a datasource in a Visual Basic 2005 application.

Can I use the txt file directly?

If not, how can I convert/import the data into an SQL data file?

Thanks, Bob

Steve Kass has good info:
http://www.users.drew.edu/skass/sql/TextDriver.htm

You could also use openrowset() to query the data.

exec master..xp_cmdshell 'echo "this is a test" >> c:\temp\test.txt & echo
"this is a test2" >> c:\temp\test.txt'
go
select *
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=c:\temp\;HDR=NO',
'select * from test.txt')

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC SQLBindColumn to a T-SQL variable not working

Hello,

I am trying to get the count of records in a file in a single round trip to the server. I tried T-SQL code similar to below and used SQLBindColumn to bind to argument #1. The code executes without error, but the result is never returned.

Any ideas what I am doing wrong?

Thanks!
CB

DECLARE @.i AS INTEGER
SELECT @.i COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

This code generates an error for me, but that is because you are not actually assigning the value of the count to the variable. Try this:

Code Snippet

DECLARE @.i AS INTEGER

SELECT @.i = COUNT (*) FROM A_4000.dbo.ADRE_ADU WITH (NOLOCK)

|||Sorry that was a typo. I do have the equals sign. When it does run, the C variable that I bind to the SQL variable i with SQLBindColumn() doesn't contain the value after SQLExecute().

ODBC Program & Heterogeneous queries problem

Best All,

What I am doing is following:
1. I created a stored procedure "testit" which select records from another data source in another SQL Server, like:

------------
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
go

create procedure testit
as begin
select * from [AnotherSQLServer].bugtest.dbo.mssent
end

grant execute on testit to public
------------

2. I can execute this stored procedure in SQL Query Analyser with success.

3. BUT: now I write a ODBC program trying to call this stored procedure, then it give me following "famous" error:
42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

The ODBC program looks like this:
--------
strcpy((char *) sqltext, "{call testit}");
rc = SQLPrepare(hstmt, (unsigned char *) sqltext, SQL_NTS);
rc = SQLExecute(hstmt);
--------

My Question:
1. Does ODBC API support Heterogeneous queries ?
2. If well, how to solve above problem?

Thanks a lot,

MelThe solution can be found here:

http://dbforums.com/t905850.html

use some thing like this (before calling SQLConnect() ):
SQLSetConnectAttr( hdbc,SQL_COPT_SS_ANSI_NPW,(void *) SQL_AD_ON, 0);