Greetings.
A template I am using to generate a report from a stored procedure works fine, except when I try to get the value of a result set field in cell D2 (as a title).
So, using Flexcel Report; how can I use a value from a result set field (from a stored procedure) to show in the first line as a title?
Unfortunately, I cannot send it as a parameter with SetValue; since it is the SP that calculates the data for that title. (I could solve this by creating a function in sql server and query this data to form the title before the report generation and user SetValue; maybe it is the simplest for now, but I am interested in knowing if it is possible to use a result field in the Report).
The template is something like this (see cell D2):
<#Config>
Name Manager
Without the tag in D2; I can get the report working:
Testing some tag variants for cell D2 I got this errors:
<#Q.I_Titulo_>
Error in cell VentasSemana!D2: "The expression "Q.I_Titulo_" on the Excel template refers to DataTable "Q" which is not defined. Verify that the cell with the expression is inside a "Q" or similar named range."
<#Q.Titulo>
VentasSemana!D2: "The expression "Q.Titulo" on the Excel template refers to DataTable "Q" which is not defined. Verify that the cell with the expression is inside a "Q" or similar named range."
<#I_Titulo_> or <#II_Titulo_>
Error in cell VentasSemana!D2: "Report Variable "I_TITULO_" is not defined."
This a snip from the code:
procedure TForm1.SetupConnectionExec(const Report: TFlexCelReport);
begin
Report.DisableSQLValidation := True;
Report.AddConnection('ERP',
function (const sql: string): TDataSet //We need to return a new TDataSet here, FlexCel will free it.
var
ds: TADODataSet;
begin
ds := TADODataSet.Create(nil);
ds.Connection := DM1.ADOConnection;
// I plan to use different SPs, so I change the sql from the code
// and in the template sql is an empty string.
ds.CommandText := 'exec ReporteSemana 2014, 0'; // week 0: for previous week; if now is week 40, we calculate week 39
Result := ds;
end);
end;
procedure TForm1.SetupVars(const Template: string);
begin
// Parámetros para todos los reportes; datos útiles.
FReport.SetValue('FechaReporte', Now);
FReport.SetValue('Year', YearOf(Now));
end;
Other tests; using <#R.Titulo>
I just need the value in orange one time in the title, but...
As you found out in the last screenshot, the reason you can't use Q outside the __Q__ range is because Q has 26 records. Which one of the 26 values of the field title should be written in D2?
You can of course create a named range __R__ as you did, but then you would have one entry for each one of the 26 records. This is expected.
Now, the problem here is that FlexCel can't know that those 26 records are the same, so it can't output in D2 the value of them.
There are some ways you can solve this:
As you mentioned, you could use a SetValue and do a different query to get the data. This is probably less efficient in your case because it implies 2 calls to the database, but it also avoids fetching a lot of repeated fields, so if you have say thousands of records, it will be much more efficient to first call the sql once to get the date (a single field), then the rest of the data, than to fetch the "Venta semana 40 al 09/30/2023 al 10/06/2024" once for ever one of the thousands of records. You are fetching much more useless data with the second approach, as you can see in t your last screenshot. Instead of fetching the string once, you fetch it 26 times. As said, for 26 times this isn't a problem, but for bigger datasets it might be.
If the value of R.Titulo is the same for all the records (as it seems to be the case here), you can then get the first value and that's it. You can use FlexCel Reports Tag Reference. | FlexCel Studio for VCL and FireMonkey documentation to fetch a specific value (the first in this case) and that tag doesn't need to be inside a __Range__ since it is a single value.
So, in your original template (without defining R or adding an extra range), you could write in D2:
<#dbvalue(Q;0;"Titulo")>
Of course, this will return the value of the first record of Q, so this solution will work only if all values of Titulo for all the records are the same. Which I think is the case here.
Also note that I am not 100% sure on what would happen if Q has 0 records, you might need to consider that case if that is a possibility in your case. Maybe you'll need to add a
But only if having 0 records is a case that might happen.
Finally, this is not for this case but might help you in similar ones: The reason why we can't output Q.Titulo in D2 outside the named range is because there are 26 different values of Q.Titulo that could be shown: one per record in the db. So, if they are all the same you can use dbvalue to show the first record. But sometimes they are all different, and you want to show them all in a list, again outside a named range.
For that, you can use FlexCel Reports Tag Reference. | FlexCel Studio for VCL and FireMonkey documentation
This will concatenate all values of titulo in all records inside a single cell. Of course for this particular case it would't work (you would get the string "Venta semana 40 al 09/30/2023 al 10/06/2024" concatenated 26 times), but for other cases it might be the option you need.
Both List and dbValue don't need to be inside a range, because they return a single value. Neither needs fields with SetFieldValue, for the same reason. Naked <#db.fields> need to be inside a range, because it will be output once for each record.
Wow! Your recommendation (tag dbvalue) is the solution:
And thanks to the reference I now know to read the PDF with the “FlexCel Reports Tag Reference” section.
I dared to go further; now that I know this is a tag, I remembered something about expressions in the #config sheet so, I made a couple of expressions/tags with the dbValue function, which worked for the worksheet name and the title:
