Static Values from Multiple Queries/Datasets in Single SSRS Tablix

0
votes

ok so I have this report I have to write in SSRS with a very specific format. It looks like the screenshot below at the bottom. Ignore the arrows and colors. It's pulling from an Oracle database. Each number value cell in this table/matrix has a different sql query to pull it in because they come from different tables, etc.

the top half of the numbers in the table are each from a query. the bottom half of the table is all calculations from the numbers in the top half. I already have the queries for the top half and was trying to figure out how I could just use those to make this table in SSRS with just those and then creating calculations in the bottom half for the report. I can't use a table or a matrix because each query is a different dataset and you can only have one dataset per tablix.

I was thinking maybe doing textboxes and drawing the grid manually, which would be a huge pain. I get errors about not having an aggregate and being out of scope or something and haven't figured out the reason for this yet as it is not my ideal solution.

My current solultion that will eventually get me there is unioning every single query and then creating columns with static values for the rows and columns in the grid and turning it into a matrix. Problem with this is it continues to increase in complexity as I create each further down the table calculation, and the code becomes larger and larger, and takes a long time to create, and I have to do like 6 reports similar in nature to the format of this one. Will probably be a thousand lines of sql and force me to have to make a stored proc because of the ssrs character limitation.;

So my question in a more simple way is, how can I take multiple sql queries that return a static value and make them a single value in a tablix that doesn't repeat, then create more blank rows in that tablix that are calculations of other cells values, i.e. Textbox1 - textbox2, textbox3/textbox4 ?

enter image description here

1
sqlreporting-servicesssrs-tablix
did you tried SubReports: docs.microsoft.com/en-us/sql/reporting-services/report-design/… I think subreport can help you solve your complex problem. Have a look at it – AnkUser
Honestly, a single stored procedure is usually the best way to combine so many tables. Each piece of data really comes from a different table? I would imagine using JOINs would be a better way than a UNION, but it's hard to say without seeing any of the schema. I have procs that about 700-800 lines for this exact purpose. Using a proc is easier in the case that you need to make a change. Instead of 25 smaller queries, you just have one single query to troubleshoot, but maybe that's just my preference. – Steve-o169
Sub reports would work, but a sub report for each cell? I'll pass. Yes the stored proc with unions that I am doing will work too, but inefficient still. Not all from different tables, but have to sum up multiple rows from some of the same tables for each column, but each cell has a different where clause, so group and sum wouldn't work. Looking more for the fastest easiest way to get this done. Should be able to make a grid and pop the values in there, then do calculations of the other cells values. – Brandon Frenchak
I mean, a tablix will work if you choose to keep separate queries. It will just require copious use of the Lookup function to lookup the values from different datasets. Perhaps it would be possible to contain your data into 3-5 procs to simplify the lookups, but you might just be doomed to have a complex report design. I know the feeling. I had a report with 4 datasets and 6 tablixes containing a few hundred textboxes. It's complex, but gets the job done and looks good. – Steve-o169
Didn't think of the look up function. That would work too it seems, but still tedious like you said. – Brandon Frenchak

1 Answers

0
votes

I got it figured out using expressions with multiple datasets. The answer seemed too easy once I found it. Basically just created a table tablix using my first dataset. Created more detail rows with insert row inside group below. Then I went to the expression builder for each one and found the other dataset and double clicked it to get the expression to pull from the other dataset. For example the bac_labor dataset value would look like this. =Sum(Fields!BAC_LABOR.Value, "BAC_Labor")

Then for calculations can use either same thing like =Sum(Fields!BAC_LABOR.Value, "BAC_Labor") + Sum(Fields!BAC_LABOR_OVERHEAD.Value, "BAC_Labor") or could do something like this =ReportItems!Textbox2.Value - ReportItems!Textbox1.Value to reference a cell value. This saves a ton of time, development effort, and reduction of code for calculations, compared to adding together 500 character select statements to make calculations. Also no need to use stored procs and union or join every select statement together with this method.

enter image description here