Report Tables In SharePoint

[Neil]

In Access I build reports using temporary tables in the front end. But when
putting my Access on SharePoint I can't do that. Any tips for how to compile
report data using SharePoint? Thanks!

 

[Albert D. Kallal]

"Neil" wrote in message

In Access I build reports using temporary tables in the front end. But when
putting my Access on SharePoint I can't do that. Any tips for how to
compile report data using SharePoint? Thanks!

I can quite much say from experience that applications that use temp tables
really break down and work poor with on-line. It not a practial option right
now.

One of my suggestions and less then ideal workarounds is to publish the web
application, but built + use the reports in the Access client.

You can the most certainly send data out to some local (linked) tables on
the users. So in this case, the munching + crunching of data occurs local.

So, the suggesting for such types of reporting is to use the Access client.

 

[Neil]

I know in SQL Server you can create temp tables. No such option in
SharePoint?

I appreciate the suggestion. Unfortunately, not doable in this case. My
client (for whom I'm building the database) uses Access, and I have the
reports in Access. No problem. But their client, for whom they're processing
the data, want to be able to view the data online. Hence the SharePoint
component.

In other words, the whole reason we're using SharePoint is for the client to
be able to query their data online in a browser.

One option I thought of, if true temp tables aren't available, is to have
reporting tables, same as the local Access tables, but use a unique ID for
each report. Then, instead of the report needing exclusive access to the
table, it would only need the records with that unique ID. Then, when the
report is done, the records with that ID can be deleted.

Of course, that would cause serious table bloating over time. But I assume
that there would be some kind of compacting done on the server side as part
of routine maintenance?

That's the only solution I can think of. Surely there must be a method that
people use to build reports with.....

Thanks!

 

[Access Developer]

Depending on why/how you employ the temporary tables, you might be able to
build them in an Access database app in the user's machine, run and create a
PDF from the report, and upload the PDF to Sharepoint, or to a Sharepoint
file/list.

 

[Neil]

Depending on why/how you employ the temporary tables, you might be able to
build them in an Access database app in the user's machine, run and create
a PDF from the report, and upload the PDF to Sharepoint, or to a
Sharepoint file/list.

Thanks, Larry. No, these are going to be run from a web page. So the data
has to be compiled in SharePoint.

Neil

 

[Patrick Finucane]

In Access I build reports using temporary tables in the front end. But when
putting my Access on SharePoint I can't do that. Any tips for how to compile
report data using SharePoint? Thanks!

Are your temp tables finite in count and are the field names the same
for a specific report? If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

 

[Neil]

In Access I build reports using temporary tables in the front end. But
when
putting my Access on SharePoint I can't do that. Any tips for how to
compile
report data using SharePoint? Thanks!

Are your temp tables finite in count and are the field names the same
for a specific report? If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

===========================

Yes, I could do that. I was thinking of doing something along those lines.
Was thinking, though, of using a table with an autonumber field, adding a
record to it, and then using the ID value to link the tables. Is that
basically what you're saying, but to assign the ID value to tempvar and use
it that way?

Also, at what point would I clear the temp data? With my temp tables I'm
just clearing the temp data before the report is run. But with multiple
users using the same table, obviously I wouldn't be able to delete the
entire table. So when would I delete the data associate with the tempvars!ID
value?

Thanks!

 

[Patrick Finucane]

Are your temp tables finite in count and are the field names the same
for a specific report?  If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

===========================

Yes, I could do that. I was thinking of doing something along those lines..
Was thinking, though, of using a table with an autonumber field, adding a
record to it, and then using the ID value to link the tables. Is that
basically what you're saying, but to assign the ID value to tempvar and use
it that way?

Also, at what point would I clear the temp data? With my temp tables I'm
just clearing the temp data before the report is run. But with multiple
users using the same table, obviously I wouldn't be able to delete the
entire table. So when would I delete the data associate with the tempvars!ID
value?

Thanks!

Yes. I was thinking initialize a Tempvars!Vars variable is set for a
current "ID" to symbolize the records processed. Then when you
complete run delete queries that delete the data for that set just
processed. I don't have experience with Sharepoint yet. I understand
your limitations tho. It's clunky but should work.

 

[Neil]

Are your temp tables finite in count and are the field names the same
for a specific report? If so, can you create a blank "holding" table
that mimics each temp table and link all the temp tables together via
a Tempvars!ID value?

===========================

Yes, I could do that. I was thinking of doing something along those lines.
Was thinking, though, of using a table with an autonumber field, adding a
record to it, and then using the ID value to link the tables. Is that
basically what you're saying, but to assign the ID value to tempvar and
use
it that way?

Also, at what point would I clear the temp data? With my temp tables I'm
just clearing the temp data before the report is run. But with multiple
users using the same table, obviously I wouldn't be able to delete the
entire table. So when would I delete the data associate with the
tempvars!ID
value?

Thanks!

Yes. I was thinking initialize a Tempvars!Vars variable is set for a
current "ID" to symbolize the records processed. Then when you
complete run delete queries that delete the data for that set just
processed. I don't have experience with Sharepoint yet. I understand
your limitations tho. It's clunky but should work.

==================================

Problem is that the report (actually a form) will be based on the temp data.
So if I delete it after the form is opened, won't I run into problems?

I suppose I could put the date into the table that gives me the autonumber
ID for each report, and then, when the form is opened, delete all temp data
from previous days. Thus, the cleanup would occur for past days, but not the
current day.

 

[Patrick Finucane]

Yes.  I was thinking initialize a Tempvars!Vars variable is set for a
current "ID" to symbolize the records processed.  Then when you
complete run delete queries that delete the data for that set just
processed.  I don't have experience with Sharepoint yet.  I understand
your limitations tho.  It's clunky but should work.

==================================

Problem is that the report (actually a form) will be based on the temp data.
So if I delete it after the form is opened, won't I run into problems?

I suppose I could put the date into the table that gives me the autonumber
ID for each report, and then, when the form is opened, delete all temp data
from previous days. Thus, the cleanup would occur for past days, but not the
current day.- Hide quoted text -

- Show quoted text -

That works for me. But then, I don't have your problem . Good
luck.