Automate Same Query (and Export) for Multiple Values

Question

I am working with a database with 5 tables, all of which contain different sets of information about clients and their employees. If you drill down in any table for data relating to a particular client, many rows will return according to the number of their employees in the dataset.

If I were to manually run the reports I need, I would query each table, one at a time, for all results where a particular client number is specified. Then I would export each table to a .csv, and then copy those exports into the same excel workbook with 5 tabs (corresponding to the 5 tables in the SQL database). At the end I would have an individual workbook for each client.

A complicating factor is that not every client ID appears in each of the 5 tables. Preferably, I would not export empty datasets and clients with data in only three of the tables would have only three tabs in the final workbook.

Is there way of giving to SQL server a list of Client IDs, for which it should query the 5 tables, export the existing data, and (possibly / hopefully) combine in a workbook on separate tabs.

Yes there is - retrieving data is what databases do. However, I fear you need to expand your understanding of this before anyone can help you. Your question is so vague as to be unanswerable. — Dale M
– Dale M, Commented Feb 10, 2013 at 8:56

Community · Accepted Answer · 2017-05-23 12:04:49Z

3

Your question is rather vague and broad, but here's the key bits of information you'll need to investigate to get things going:

Create five different datasets, each querying one table.
Create five tablixes, add a PageBreak before each tablix so in Excel they will land on different tabs.
Either set a NoRowsMessage or hide the tablix along these lines using an expression based on the RowNumber function
Create a parameter for selecting the client ID, and use that in your WHERE clause of the datasets.

The tricky bit would be how to generate multiple Excel files. SSRS does one export at a time, so your basic options:

Put multiple clients in one XLS (i.e. don't use a parameter, but include clientId as a column on the worksheets)
Have the user select one client at a time, and export one XLS at a time.
Automate generating the reports.

edited May 23, 2017 at 12:04

CommunityBot

11 silver badge

answered Feb 10, 2013 at 9:26

Jeroen

64.4k47 gold badges231 silver badges372 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1893148 Over a year ago

Thanks just the kind of direction I was looking for.

Collectives™ on Stack Overflow

Automate Same Query (and Export) for Multiple Values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related